Search code examples
rtidyr

pivot_longer() for multiple columns

df <- data.frame(year = c("2001", "2001", "2001", "2002", "2002", "2002", 
                          "2003", "2003", "2003", "2004", "2004", "2004"), 
             city = c("Rome", "Rome", "Rome", "London", "London", "London", 
                      "New York", "New York", "New York", "Paris", "Paris", "Paris"),
             position_1 = c("first", "first", "first", "second", "second", "second", 
                            "third", "third", "third", "fourth", "fourth", "fourth"),
             position_2 = c("fifth", "fifth", "fifth", "sixth", "sixth", "sixth", 
                            "seventh", "seventh", "seventh", "eighth", "eighth", "eighth"), 
             position_3 = c("ninth", "ninth", "ninth", "eleventh", "eleventh", "eleventh", 
                            "twelfth", "twelfth", "twelfth", "thirteenth", "thirteenth", "thirteenth"))

I need a table as follows:

enter image description here

I have tried the following command, but it didn't work.

df <- df %>% 
  pivot_longer(cols = position_1:position_3,
               names_to = "new_position")
Solution

  • Do you need the following ?

    library(tidyr)
df |>
  pivot_longer(
    cols = position_1:position_3,
    values_to = "new_position",
    names_to = NULL
  )
#> # A tibble: 36 × 3
#>    year  city   new_position
#>    <chr> <chr>  <chr>       
#>  1 2001  Rome   first       
#>  2 2001  Rome   fifth       
#>  3 2001  Rome   ninth       
#>  4 2001  Rome   first       
#>  5 2001  Rome   fifth       
#>  6 2001  Rome   ninth       
#>  7 2001  Rome   first       
#>  8 2001  Rome   fifth       
#>  9 2001  Rome   ninth       
#> 10 2002  London second      
#> # ℹ 26 more rows