Using pivot_longer with multiple paired columns in the wide dataset

Question

I have a dataset that looks like this:

input <- 
  data.frame(
    event = 1:2,
    url_1 = c("g1", "g3"),
    name_1 = c("dc", "nyc"),
    url_2 = c("g2", "g4"),
    name_2 = c("sf", "la"))

Essentially there are pairs of indexed columns that are stuck together in wide form. I want to convert to long to give this output:

output <- 
  data.frame(
    event = c(1,1,2,2),
    url = c("g1", "g2", "g3", "g4"),
    name = c("dc", "sf", "nyc", "la"))

I want to do this using pivot_longer. I've tried this:

input %>% 
  pivot_longer(contains("_"))

How can I get the function to recognize the column-pairs?

Answer 1

You want to use .value in the names_to argument:

input %>%
  pivot_longer(
    -event, 
    names_to = c(".value", "item"), 
    names_sep = "_"
  ) %>% 
  select(-item)

# A tibble: 4 x 3
  event url   name 
  <int> <fct> <fct>
1     1 g1    dc   
2     1 g2    sf   
3     2 g3    nyc  
4     2 g4    la   

From this article on pivoting:

Note the special name .value: this tells pivot_longer() that that part of the column name specifies the “value” being measured (which will become a variable in the output).