using pivot_long to accommodate the date column

November 12, 2021

I have a timeseries dataset that I am extend using pivot_longer, However, upon using, the dates get messed up.

Code

library(dplyr)

ID = c("297557", "279557", "280485", "280485")
Date = c("1/1/2020", "1/2/2020", "1/1/2020", "1/2/2020")
Var1 = c("1", "3", "5", "7")
Var2 = c("2", "4", "6", "8")

df = data.frame(ID, Date, Var1, Var2)
df = df %>% pivot_longer(-ID)

Output

# A tibble: 12 x 3
   ID     name  value   
   <chr>  <chr> <chr>   
 1 297557 Date  1/1/2020
 2 297557 Var1  1       
 3 297557 Var2  2       
 4 279557 Date  1/2/2020
 5 279557 Var1  3       
 6 279557 Var2  4       
 7 280485 Date  1/1/2020
 8 280485 Var1  5       
 9 280485 Var2  6       
10 280485 Date  1/2/2020
11 280485 Var1  7       
12 280485 Var2  8

Desired output

ID     Date     Value  
279557 1/1/2020 1 
279557 1/1/2020 2
279557 1/2/2020 3
279557 1/1/2020 4 
280485 1/1/2020 5       
280485 1/1/2020 6
280485 1/2/2020 7
280485 1/2/2020 8

>Solution :

You may try

df %>%
  pivot_longer(-c(ID, Date)) # %>% select(-name) # to remove name column

  ID     Date     name  value
  <chr>  <chr>    <chr> <chr>
1 297557 1/1/2020 Var1  1    
2 297557 1/1/2020 Var2  2    
3 279557 1/2/2020 Var1  3    
4 279557 1/2/2020 Var2  4    
5 280485 1/1/2020 Var1  5    
6 280485 1/1/2020 Var2  6    
7 280485 1/2/2020 Var1  7    
8 280485 1/2/2020 Var2  8