Pivoting data so that new variables are created for paired columns

[mattwarkentin]

Hi,

I'm not sure if this is possible with the current pivot_longer() interface, but my use-case is that essentially two quantities are measured, a and A, and each were measured at 3 different time points a1-a3 and A1-A3. So you essentially have paired columns a1/A1, a2/A2, and a3/A3.

Is it possible to pivot_longer() to go from the first table to the second table:

library(tidyverse)

tribble(
  ~id, ~a1, ~a2, ~a3, ~A1, ~A2, ~A3,
  1, "a", "b", "c", "A", "B", "C"
)
#> # A tibble: 1 x 7
#>      id a1    a2    a3    A1    A2    A3   
#>   <dbl> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1     1 a     b     c     A     B     C

tribble(
  ~id, ~instance, ~lower, ~upper,
  1, 1, "a", "A",
  1, 2, "b", "B",
  1, 3, "c", "C"
)
#> # A tibble: 3 x 4
#>      id instance lower upper
#>   <dbl>    <dbl> <chr> <chr>
#> 1     1        1 a     A    
#> 2     1        2 b     B    
#> 3     1        3 c     C

Currently, the only way I have got this to work is by pivoting each set of variables separately and joining them. But it's sort of cumbersome, hoping to do this in a single pivot.

library(tidyverse)

dd <- tribble(
  ~id, ~a1, ~a2, ~a3, ~A1, ~A2, ~A3,
  1, "a", "b", "c", "A", "B", "C"
  )

tbl1 <- dd %>% select(1:4) %>% pivot_longer(-id, names_prefix = "a", values_to = "lower", names_to = "instance")
tbl2 <- dd %>% select(1, 5:7) %>% pivot_longer(-id, names_prefix = "A", values_to = "upper", names_to = "instance")

left_join(tbl1, tbl2)
#> Joining, by = c("id", "instance")
#> # A tibble: 3 x 4
#>      id instance lower upper
#>   <dbl> <chr>    <chr> <chr>
#> 1     1 1        a     A    
#> 2     1 2        b     B    
#> 3     1 3        c     C

[AlexisW]

You can give a vector with 2 values in names_to. In that case you also have to use names_pattern to specify what part goes in what column.

DF %>%
  pivot_longer(a1:A3,
               names_to=c("type", "instance"),
               names_pattern = "(\\w)(\\d)")
# A tibble: 6 x 4
#      id type  instance value
#   <dbl> <chr> <chr>    <chr>
# 1     1 a     1        a    
# 2     1 a     2        b    
# 3     1 a     3        c    
# 4     1 A     1        A    
# 5     1 A     2        B    
# 6     1 A     3        C  

So we're closer, but now the result is too long now. We can use the special .value to put the value in the column name:

DF %>%
  pivot_longer(a1:A3,
               names_to=c(".value", "instance"),
               names_pattern = "(\\w)(\\d)")
# A tibble: 3 x 4
#      id instance a     A    
#   <dbl> <chr>    <chr> <chr>
# 1     1 1        a     A    
# 2     1 2        b     B    
# 3     1 3        c     C  

[mattwarkentin]

Thanks for your help, @AlexisW!