A Tricky Pivot Longer-CodePudding

I have a dataframe like this :

data.frame(id = c(1,2,3),
           first_value=c("A","B","NA"),second_value=c("A","NA","D"), 
           first_date=c("2001",2010,2003),second_date=c("2003",2014,"2007"))

  id first_value second_values first_date second_date
1  1           A             A       2001        2003
2  2           B            NA       2010        2014
3  3          NA             D       2003        2007

I'am looking to transform it to longer dataframe like this with the samplist way:

 id timing value date
1  1  first     A 2001
2  1 second     A 2003
3  2  first     B 2010
4  2 second    NA 2014
5  3  first    NA 2003
6  3 second     D 2007

I wasn't successful with the tidyr pivot_longer

CodePudding user response：

You can do:

library(tidyverse)
df %>%
  pivot_longer(cols = -id,
               names_pattern = '(.*)_(.*)',
               names_to = c('timing', '.value'))

Which gives:

# A tibble: 6 x 4
     id timing value date 
  <dbl> <chr>  <chr> <chr>
1     1 first  A     2001 
2     1 second A     2003 
3     2 first  B     2010 
4     2 second NA    2014 
5     3 first  NA    2003 
6     3 second D     2007

NOTE: this only works if you rename your second_values column to second_value. I assume the „values“ was just a typo?

Alternative and a bit simpler, based on @Maël‘s suggestion:

df %>%
  pivot_longer(cols = -id,
               names_sep = '_',
               names_to = c('timing', '.value'))

CodePudding user response：

Could try something like this?

library(tidyverse)

df <- data.frame(id = c(1,2,3),
              first_value=c("A","B","NA"),
              second_values=c("A","NA","D"), 
              first_date=c("2001",2010,2003),
              second_date=c("2003",2014,"2007"))

bind_rows(
  df %>% 
    select(id, first_value, date = first_date) %>% 
    pivot_longer(cols = "first_value", names_to = "timing"), 
  df %>% 
    select(id, second_values, date = second_date) %>% 
    pivot_longer(cols = "second_values", names_to = "timing")
  ) %>% 
  relocate(id, timing, value, date) %>% 
  arrange(id)

The last two lines are just there to get the same formatting / order as you posted, so probably could be ommitted.