R tidyverse how to do two pivot_longer after each other instead of a single one

Question

Assume the following data:

structure(list(uuid = c("abc", "def", "hij"), Q1r1 = c(0L, 1L, 
1L), Q1r2 = c(1L, 1L, 1L), Q1r3 = c(1L, 0L, 1L), Q2r1c1 = c(4L, 
3L, 5L), Q2r1c2 = 2:4, Q2r1c3 = c(1L, 5L, 2L), Q2r2c1 = c(3L, 
3L, 4L), Q2r2c2 = c(2L, 5L, 4L), Q2r2c3 = c(1L, 4L, 5L), Q3r1 = c(5L, 
9L, 7L), Q3r2 = c(10L, 3L, 8L), Q3r3 = c(6L, 8L, 5L)), class = "data.frame", row.names = c(NA, 
-3L))

which gives:

  uuid Q1r1 Q1r2 Q1r3 Q2r1c1 Q2r1c2 Q2r1c3 Q2r2c1 Q2r2c2 Q2r2c3 Q3r1 Q3r2 Q3r3
1  abc    0    1    1      4      2      1      3      2      1    5   10    6
2  def    1    1    0      3      3      5      3      5      4    9    3    8
3  hij    1    1    1      5      4      2      4      4      5    7    8    5

Now assume that I want to pivot_longer the data for all Q1 and Q3 columns (where r1, r2 and r3 in these columns indicate the to-be-created rows).

This is relatively straightforward with:

dat %>%
  pivot_longer(cols      = c(starts_with("Q1"), starts_with("Q3")),
               names_sep = "r",
               names_to  = c('.value', 'brand’))

which gives:

# A tibble: 9 x 10
  uuid  Q2r1c1 Q2r1c2 Q2r1c3 Q2r2c1 Q2r2c2 Q2r2c3 brand    Q1    Q3
  <chr>  <int>  <int>  <int>  <int>  <int>  <int> <chr> <int> <int>
1 abc        4      2      1      3      2      1 1         0     5
2 abc        4      2      1      3      2      1 2         1    10
3 abc        4      2      1      3      2      1 3         1     6
4 def        3      3      5      3      5      4 1         1     9
5 def        3      3      5      3      5      4 2         1     3
6 def        3      3      5      3      5      4 3         0     8
7 hij        5      4      2      4      4      5 1         1     7
8 hij        5      4      2      4      4      5 2         1     8
9 hij        5      4      2      4      4      5 3         1     5

Now, here's my question: would there also be a way to separate both pivotings from each other, i.e. first pivot_longer Q1 and then afterwards pivot_longer Q3?

The reason I'm asking is that alternatively I also want to pivot Q2 and Q3 (but in Q2 the row identifiers are c1, c2, c3 and I want to have two resulting columns Q2r1 and Q2r2 after pivoting_longer, whereas the row identifier is r1, r2, r3 for Q3, so the above simple code with names_sep and names_to doesn't work anymore). A colleague told me that in other software you can kind of concatenate the single pivot_longers so I'm wondering if the same is possible in R.

Note: I know how to do the pivoting for Q2 and Q3 in one round. I really just want to know if it is possible to split up the pivotings and do them one after the other.

The expected output from the first example where I pivot on Q1 and Q3 would be:

# A tibble: 9 x 10
  uuid  Q2r1c1 Q2r1c2 Q2r1c3 Q2r2c1 Q2r2c2 Q2r2c3 brand    Q1    Q3
  <chr>  <int>  <int>  <int>  <int>  <int>  <int> <chr> <int> <int>
1 abc        4      2      1      3      2      1 1         0     5
2 abc        4      2      1      3      2      1 2         1    10
3 abc        4      2      1      3      2      1 3         1     6
4 def        3      3      5      3      5      4 1         1     9
5 def        3      3      5      3      5      4 2         1     3
6 def        3      3      5      3      5      4 3         0     8
7 hij        5      4      2      4      4      5 1         1     7
8 hij        5      4      2      4      4      5 2         1     8
9 hij        5      4      2      4      4      5 3         1     5

The desired output from the second example where I want to pivot on Q2 and Q3 would be:

# A tibble: 9 x 8
  uuid   Q1r1  Q1r2  Q1r3 brand   Q2r1  Q2r2    Q3
  <chr> <int> <int> <int> <chr>  <int> <int> <int>
1 abc       0     1     1 brand1     4     3     5
2 abc       0     1     1 brand2     2     2    10
3 abc       0     1     1 brand3     1     1     6
4 def       1     1     0 brand1     3     3     9
5 def       1     1     0 brand2     3     5     3
6 def       1     1     0 brand3     5     4     8
7 hij       1     1     1 brand1     5     4     7
8 hij       1     1     1 brand2     4     4     8
9 hij       1     1     1 brand3     2     5     5

Answer 1

Ok, after understanding the question better, the only answers I can think of are hackish. You mentioned one in the comments; here is another. This revolves around having flexible regex for selecting columns. Then it joins the dataframes together via Reduce() (or you can swap if for purrr::reduce() if you prefer). Also, note, this is performing the wide-to long multiple independent times (and combining) instead of doing it sequentially.

col_starts <- c("Q2", "Q3")

lapply(col_starts, function(x) {
  
  df %>%
    pivot_longer(matches(paste0("^", x)),
                 names_pattern = "(Q\\d.*)[rc](\\d)$",
                 names_to = c(".value", "brand")) %>%
    select(uuid, brand:ncol(.), everything(), -matches(paste0("^", setdiff(col_starts, x), collapse = "|")))
  
}) %>% Reduce(function(x, y) left_join(x, y, by = intersect(names(x), names(y))), .)
# A tibble: 9 x 8
  uuid  brand  Q2r1  Q2r2  Q1r1  Q1r2  Q1r3    Q3
  <chr> <chr> <int> <int> <int> <int> <int> <int>
1 abc   1         4     3     0     1     1     5
2 abc   2         2     2     0     1     1    10
3 abc   3         1     1     0     1     1     6
4 def   1         3     3     1     1     0     9
5 def   2         3     5     1     1     0     3
6 def   3         5     4     1     1     0     8
7 hij   1         5     4     1     1     1     7
8 hij   2         4     4     1     1     1     8
9 hij   3         2     5     1     1     1     5

Here is a version that only keeps the uuid, brand and derived columns (it is a little easier to read imo)

lapply(c("Q2", "Q3"), function(x) {
  
  df %>%
    pivot_longer(matches(paste0("^", x)),
                 names_pattern = "(Q\\d.*)[rc](\\d)$",
                 names_to = c(".value", "brand")) %>%
    select(uuid, brand, starts_with(x))
  
}) %>% Reduce(function(x, y) left_join(x, y, by = c("uuid", "brand")), .)