2

As noted in Reshaping multiple sets of measurement columns (wide format) into single columns (long format) and
Elegant solution for casting (spreading) multiple columns of character vectors,
you can use the ".value" component of the names_to argument in tidyr's pivot_wider() to make multiple columns longer at once:

tibble(
  name = LETTERS[1:10],
  a_x = 1:10,
  a_y = -1:-10,
  b_x = 1:10,
  b_y = -1:-10,
  c_x = 1:10,
  c_y = -1:-10,
  d_x = 1:10,
  d_y = -1:-10
) %>% 
 pivot_longer(
  cols = -name,
  names_to = c(".value", "group"),
  names_sep = "_",

)

which produces this:

# A tibble: 20 x 6
   name  group     a     b     c     d
   <chr> <chr> <int> <int> <int> <int>
 1 A     x         1     1     1     1
 2 A     y        -1    -1    -1    -1
 3 B     x         2     2     2     2
 4 B     y        -2    -2    -2    -2
 5 C     x         3     3     3     3
 6 C     y        -3    -3    -3    -3
 7 D     x         4     4     4     4
 8 D     y        -4    -4    -4    -4
 9 E     x         5     5     5     5
10 E     y        -5    -5    -5    -5
11 F     x         6     6     6     6
12 F     y        -6    -6    -6    -6
13 G     x         7     7     7     7
14 G     y        -7    -7    -7    -7
15 H     x         8     8     8     8
16 H     y        -8    -8    -8    -8
17 I     x         9     9     9     9
18 I     y        -9    -9    -9    -9
19 J     x        10    10    10    10
20 J     y       -10   -10   -10   -10

This is useful, but it presumes that the suffix provides the grouping component on which to pivot. However, sometimes one might want to pivot on the prefix, for instance where abcd would go in the group column and xy would be the two columns.

This was apparently talked about in the original discussion around this feature here but if there's a solution to implement it I can't seem to make any headway.

I can do this other ways, for instance with base reshape() and varying, or with creating a pivoting specification, but can this be done cleanly with this tool?

Joe
  • 119
  • 5

1 Answers1

2

The position of ".value" in the names_to vector determines which component is used as the new column names. To get xy as the new columns, you would use names_to = c("group", ".value"):

library(tidyverse)

tibble(
  name = LETTERS[1:10],
  a_x = 1:10,
  a_y = -1:-10,
  b_x = 1:10,
  b_y = -1:-10,
  c_x = 1:10,
  c_y = -1:-10,
  d_x = 1:10,
  d_y = -1:-10
) %>% 
 pivot_longer(
  cols = -name,
  names_to = c("group", ".value"),
  names_sep = "_",
)
#> # A tibble: 40 x 4
#>    name  group     x     y
#>    <chr> <chr> <int> <int>
#>  1 A     a         1    -1
#>  2 A     b         1    -1
#>  3 A     c         1    -1
#>  4 A     d         1    -1
#>  5 B     a         2    -2
#>  6 B     b         2    -2
#>  7 B     c         2    -2
#>  8 B     d         2    -2
#>  9 C     a         3    -3
#> 10 C     b         3    -3
#> # ... with 30 more rows

Created on 2019-08-14 by the reprex package (v0.3.0.9000)

Mikko Marttila
  • 10,972
  • 18
  • 31
  • Thanks, but that's distinctly not the outcome I was getting from doing just that. Let me check my versions and try it again. – Joe Aug 14 '19 at 20:11
  • 1
    I ran my example with the latest GitHub master branch. If you're not getting the same result, I'd recommend opening an issue. – Mikko Marttila Aug 14 '19 at 20:17
  • Belated follow up to say that although I didn't get to re-check this at the time, I've since used it effectively, so whatever I was seeing seems to have resolved either in an update or when I somehow corrected my misunderstanding. – Joe Mar 21 '20 at 16:15