tidyr::pivot_longer() for multiple matching sets of names/values

Viewed 196

Is it possible to pivot longer two matching sets of name-value columns in one pivot_longer() call?

Here's a toy example. I have two value types (a, b), and two types of values (a numeric value, and a binary indicator for the value being negative). Is it possible to reshape them both longer in one function call?

This example uses two pivot_longer() calls and them joins them together.

set.seed(1)

a_val = rnorm(10)
b_val = rnorm(10)
a_neg = factor(as.numeric(a_val < 0), levels = 0:1, labels = c("No", "Yes"))
b_neg = factor(as.numeric(b_val < 0), levels = 0:1, labels = c("No", "Yes"))

# random data
df <- data.frame(
    id = seq(1:10),
    a = a_val,
    b = b_val,
    a_low = a_neg,
    b_low = b_neg
)

full_join(
    df %>%
        select(id, a, b) %>%
        pivot_longer(cols = a:b, names_to = "value_type", values_to = "value"),
    df %>%
        select(id, a_low, b_low) %>%
        pivot_longer(cols = a_low:b_low, names_to = "value_type", names_pattern = "([ab])_low", values_to = "low"),
    by = c("id", "value_type")
)
#> # A tibble: 20 x 4
#>       id value_type   value low  
#>    <int> <chr>        <dbl> <fct>
#>  1     1 a          -0.626  Yes  
#>  2     1 b           1.51   No   
#>  3     2 a           0.184  No   
#>  4     2 b           0.390  No   
#>  5     3 a          -0.836  Yes  
#>  6     3 b          -0.621  Yes  
#>  7     4 a           1.60   No   
#>  8     4 b          -2.21   Yes  
#>  9     5 a           0.330  No   
#> 10     5 b           1.12   No   
#> 11     6 a          -0.820  Yes  
#> 12     6 b          -0.0449 Yes  
#> 13     7 a           0.487  No   
#> 14     7 b          -0.0162 Yes  
#> 15     8 a           0.738  No   
#> 16     8 b           0.944  No   
#> 17     9 a           0.576  No   
#> 18     9 b           0.821  No   
#> 19    10 a          -0.305  Yes  
#> 20    10 b           0.594  No

Created on 2020-06-17 by the reprex package (v0.3.0)

1 Answers

We could rename the columns 'a', 'b' to add a suffix "_value", and then use names_sep="_" in pivot_longer

library(dplyr)
library(tidyr)
df %>% 
  rename_at(vars(a, b), ~ str_c(., "_value")) %>%
  pivot_longer(cols = -id, names_to = c("grp", ".value"), names_sep="_")
# A tibble: 20 x 4
#      id grp     value low  
#   <int> <chr>   <dbl> <fct>
# 1     1 a     -0.626  Yes  
# 2     1 b      1.51   No   
# 3     2 a      0.184  No   
# 4     2 b      0.390  No   
# 5     3 a     -0.836  Yes  
# 6     3 b     -0.621  Yes  
# 7     4 a      1.60   No   
# 8     4 b     -2.21   Yes  
# 9     5 a      0.330  No   
#10     5 b      1.12   No   
#11     6 a     -0.820  Yes  
#12     6 b     -0.0449 Yes  
#13     7 a      0.487  No   
#14     7 b     -0.0162 Yes  
#15     8 a      0.738  No   
#16     8 b      0.944  No   
#17     9 a      0.576  No   
#18     9 b      0.821  No   
#19    10 a     -0.305  Yes  
#20    10 b      0.594  No   
Related