pivot_longer multiple variables of different kinds

Question

I am trying to find a simple way to pivot_longer a dataframe that has multiple columns containing different data for each case. Using multiple names in names_to doesn't seem to solve the problem.

Here is a worked example:

#create the dataframe:
library('dplyr')
set.seed(11)
x <- data.frame(case = c(1:10),
                X1990 = runif(10, 0, 1),
                flag.1990 = rep(c('a','b'), 5),
                X2000 = runif(10, 0, 1),
                flag.2000 = rep(c('c', 'd'), 5))

> x
   case        X1990 flag.1990     X2000 flag.2000
1     1 0.2772497942         a 0.1751129         c
2     2 0.0005183129         b 0.4407503         d
3     3 0.5106083730         a 0.9071830         c
4     4 0.0140479084         b 0.8510419         d
5     5 0.0646897766         a 0.7339875         c
6     6 0.9548492255         b 0.5736857         d
7     7 0.0864958912         a 0.4817655         c
8     8 0.2899750092         b 0.3306110         d
9     9 0.8806991728         a 0.1576602         c
10   10 0.1232162013         b 0.4801341         d

Obviously I cannot just pivot_longer using cols = -case as that will combine year and flag data. If i try using a chr vector in names_to (from here: https://dcl-wrangle.stanford.edu/pivot-advanced.html (6.1.3):

x %>%
  setNames(c('case','value.1990', 'flag.1990', 'value.2000', 'flag.2000')) %>%
  pivot_longer(cols = -case, 
               names_to = c('value', 'flag'), 
               names_sep = '.', 
               values_to = 'value')

Things don't work, because the flag data isn't in the variable name.

The only way I can think to solve this is to break the dataframe into two data frames, pivot them and then join them. For example:

#create temporary data frame for year data, then pivot
temp1 <- x %>%
  select(1,2, 4) %>% #select year data
  pivot_longer(cols = c(X1990, X2000), #pivot longer on year data
               names_to = 'year', 
               values_to = 'value') %>%
  mutate(year = gsub('X', '', year)) #remove 'X' so that I can use this to join

#create temporary data frame for flag data, then pivot
temp2 <- x %>%
  select(1, 3, 5) %>% #select flag variables
  pivot_longer(cols = c(flag.1990, flag.2000), #pivot longer on flag data
               names_to = 'flag.year', 
               values_to = 'flag') %>%
  mutate(year = gsub('flag.', '', flag.year)) %>% #get year data so that I can join on this
  select(-flag.year) #drop flag.year as its no longer useful information
 
final <- full_join(temp1, temp2, by = c('case', 'year')) #full join the two datasets to get the final data

> final
# A tibble: 20 x 4
    case flag  year     value
   <int> <chr> <chr>    <dbl>
 1     1 a     1990  0.277   
 2     1 c     2000  0.175   
 3     2 b     1990  0.000518
 4     2 d     2000  0.441   
 5     3 a     1990  0.511   
 6     3 c     2000  0.907   
 7     4 b     1990  0.0140  
 8     4 d     2000  0.851   
 9     5 a     1990  0.0647  
10     5 c     2000  0.734   
11     6 b     1990  0.955   
12     6 d     2000  0.574   
13     7 a     1990  0.0865  
14     7 c     2000  0.482   
15     8 b     1990  0.290   
16     8 d     2000  0.331   
17     9 a     1990  0.881   
18     9 c     2000  0.158   
19    10 b     1990  0.123   
20    10 d     2000  0.480

I assume there is a quicker way to do this. Am I just misreading the documentation on using multiple names in names_to. Any ideas?

Answer 1

In this case one has to use names_to combined with names_pattern:

library(dplyr)
library(tidyr)
> head(x,3)
   case        X1990 flag.1990     X2000 flag.2000
1     1 0.2772497942         a 0.1751129         c
2     2 0.0005183129         b 0.4407503         d
3     3 0.5106083730         a 0.9071830         c
> x %>% 
    pivot_longer(cols = -case, 
                 names_to = c(".value", "year"), 
                 names_pattern = "([^\\.]*)\\.*(\\d{4})")
# A tibble: 20 x 4
    case year         X flag
   <int> <chr>    <dbl> <chr>
 1     1 1990  0.277    a    
 2     1 2000  0.175    c    
 3     2 1990  0.000518 b    
 4     2 2000  0.441    d    
 5     3 1990  0.511    a    
 6     3 2000  0.907    c    
 7     4 1990  0.0140   b    
 8     4 2000  0.851    d    
 9     5 1990  0.0647   a    
10     5 2000  0.734    c    
11     6 1990  0.955    b    
12     6 2000  0.574    d    
13     7 1990  0.0865   a    
14     7 2000  0.482    c    
15     8 1990  0.290    b    
16     8 2000  0.331    d    
17     9 1990  0.881    a    
18     9 2000  0.158    c    
19    10 1990  0.123    b    
20    10 2000  0.480    d