r - How to pivot_longer this dataframe?

Question

I have a dataframe with the following structure:

x <- data.frame(
x1_dte = as.Date(c("2001-01-01", "2001-01-02", "2002-01-02"), format = "%Y-%m-%d"),
x1_val1 = c(10, 12, 13),
x1_val2 = c(200, 250, 300),
x2_dte = as.Date(c("2003-01-01", "2003-04-02", "2003-04-02"), format = "%Y-%m-%d"),
x2_val1 = c(9, 11, 14),
x2_val2 = c(110, 140, 200),
x3_dte = as.Date(c(NA, NA, NA), format = "%Y-%m-%d"),
x3_val1 = c(NA, NA, NA),
x3_val2 = c(NA, NA, NA)
)

      x1_dte x1_val1 x1_val2     x2_dte x2_val1 x2_val2 x3_dte x3_val1 x3_val2
1 2001-01-01      10     200 2003-01-01       9     110   <NA>      NA      NA
2 2001-01-02      12     250 2003-04-02      11     140   <NA>      NA      NA
3 2002-01-02      13     300 2003-04-02      14     200   <NA>      NA      NA

I want to transform it to a dataframe with the following structure:

x_longer <- data.frame(var = c("x1", "x1", "x1", "x2", "x2", "x2","x3", "x3", "x3"),
date = as.Date(c("2001-01-01", "2001-01-02", "2002-01-02", "2003-01-01", "2003-04-02", "2003-04-02", NA, NA, NA), format = "%Y-%m-%d"),
val1 = c(10, 12, 13, 9, 11, 14, NA, NA, NA),
val2 = c(200, 250, 300, 110, 140, 200, NA, NA, NA)
)

  var       date val1 val2
1  x1 2001-01-01   10  200
2  x1 2001-01-02   12  250
3  x1 2002-01-02   13  300
4  x2 2003-01-01    9  110
5  x2 2003-04-02   11  140
6  x2 2003-04-02   14  200
7  x3       <NA>   NA   NA
8  x3       <NA>   NA   NA
9  x3       <NA>   NA   NA

I don't understand how to take the multiple columns from dataframe x to create x_longer. Can anyone help me?

question from:https://stackoverflow.com/questions/66046896/how-to-pivot-longer-this-dataframe

Answer 1

This works:

x %>% pivot_longer(everything(), names_to = c('var', '.value'), names_sep = '_')