Simplifying gather() with pivot_longer() using multiple ID columns and value columns

user_5539732 0
Viewed 80

I'm working to get a better handle on pivot_longer, coming from a gather user. From the source documents it seems like I should be able to do the following in a single command using name_pattern or names_sep but I've been unable to find a working solution.

Data

id1 <- c("person1","person2","person3")
id2 <- c("1001","1002","1003")
id3 <- c("2001","2002", "2003")
value_1 <- c(10,50,100)
value_2 <- c(20,200, 2000)
status_1 <- c("OK","BAD","GOOD")
status_2 <- c("AWFUL","EXCELLENT","AVERAGE")

df <- data.frame(id1,id2,id3,value_1,value_2,status_1,status_2)

Expected output:

      id1  id2  id3 gradeLevel    status value
1 person1 1001 2001          1        OK    10
2 person1 1001 2001          2     AWFUL    20
3 person2 1002 2002          1       BAD    50
4 person2 1002 2002          2 EXCELLENT   200
5 person3 1003 2003          1      GOOD   100
6 person3 1003 2003          2   AVERAGE  2000

I can achieve this with a gather statement and a few extra lines:

df %>% 
  gather(key, value,-id1, -id2,-id3) %>% 
  separate(key, c('cat', 'gradeLevel'),sep ="_") %>% 
  distinct() %>% 
  spread(cat,value)

Is there a way to simplify this with pivot_longer? I think names_pattern is promising but I struggle with regex. Most of my attempts are attempting to combine different types of columns (double & factor)

df %>%
  pivot_longer(cols = value_1:status_2, names_to = c('col', '.value'), names_pattern = "(.*)_(.)")
tidyr dplyr tidyverse r
1 Answers

You can use the following solution. In this case we create 2 capture groups. What .value does here actually is to define for pivot_longer the part of the name that contains the name of the value we are trying to measure. Here the left side of the underscore are values value and status. The right side of the underscore which is in fact the result of our second capturing group defines the id. And it should be noted that the length of names_to argument should be the same as the number of capturing groups in names_pattern or possibly names_sep.

library(tidyr)

df %>%
  pivot_longer(!c(id1, id2, id3), names_to = c(".value", "gradelevel"), 
               names_pattern = "(\\w+)_(\\d+)")

# A tibble: 6 x 6
  id1     id2   id3   gradelevel value status   
  <chr>   <chr> <chr> <chr>      <dbl> <chr>    
1 person1 1001  2001  1             10 OK       
2 person1 1001  2001  2             20 AWFUL    
3 person2 1002  2002  1             50 BAD      
4 person2 1002  2002  2            200 EXCELLENT
5 person3 1003  2003  1            100 GOOD     
6 person3 1003  2003  2           2000 AVERAGE
user_143145200
Related