Use pivot_wider create new value columns based on id/group column

Question

Probably this is can be done fairly easily, but my own tries did not give the desired output.

I have a simple data frame with some descriptive statistics.

set.seed(0712)
tibble(group = rep(1:3, each = 2),
       scale = rep(LETTERS[1:3], 2),
           n = c(100, 100, 150, 150, 135, 135),
        mean = runif(6, 1, 5),
          sd = runif(6))

    A tibble: 6 x 5
  group scale     n  mean    sd
  <int> <chr> <dbl> <dbl> <dbl>
1     1 A       100  2.42 0.387
2     1 B       100  2.27 0.343
3     2 C       150  4.61 0.709
4     2 A       150  4.51 0.207
5     3 B       135  4.93 0.653
6     3 C       135  3.05 0.253

The first column indicates the group, and each group has three values (n, mean, sd) for two different scales so that the number of groups covers all possible scale combinations (e.g. AB, AC, BC).

I would simply like to rearrange my data frame into the following overview in a wide format where the values of each group are placed within two columns per value instead of two rows.

   group scale_1 scale_2  n_1  n_2  mean_1  mean_2  sd_1   sd_2
1     1        A       B  100  100  2.42    2.27    0.387  0.343
2     2        C       A  150  150  4.61    4.51    0.709  0.207
3     3        B       C  135  135  4.93    3.05    0.653  0.253

However, using pivot_wider the situation gets messy as every value x scale combination receives its own column, but not every group has values for each scale. I understand that from a tidy data perspective that is useful, but for a simple overview I would rather have the columns lumped together into value_1 and value_2, etc.

> df %>% tidyr::pivot_wider(id_cols = group, names_from = scale, values_from = n:sd)
# A tibble: 3 x 10
  group   n_A   n_B   n_C mean_A mean_B mean_C   sd_A   sd_B   sd_C
  <int> <dbl> <dbl> <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1     1   100   100    NA   2.42   2.27  NA     0.387  0.343 NA    
2     2   150    NA   150   4.51  NA      4.61  0.207 NA      0.709
3     3    NA   135   135  NA      4.93   3.05 NA      0.653  0.253

Any ideas on how to collapse this data frame?

Answer 1

To get the 1s and 2s in your names rather than A, B and C you could add a new temporary value by row_number() before pivoting:

library(tidyverse)

set.seed(0712)
df <- tibble(group = rep(1:3, each = 2),
       scale = rep(LETTERS[1:3], 2),
       n = c(100, 100, 150, 150, 135, 135),
       mean = runif(6, 1, 5),
       sd = runif(6))

df |> 
  group_by(group) |> 
  mutate(scaleno = row_number()) |> 
  pivot_wider(names_from = scaleno, values_from = scale:sd)
#> # A tibble: 3 × 9
#> # Groups:   group [3]
#>   group scale_1 scale_2   n_1   n_2 mean_1 mean_2  sd_1  sd_2
#>   <int> <chr>   <chr>   <dbl> <dbl>  <dbl>  <dbl> <dbl> <dbl>
#> 1     1 A       B         100   100   2.42   2.27 0.387 0.343
#> 2     2 C       A         150   150   4.61   4.51 0.709 0.207
#> 3     3 B       C         135   135   4.93   3.05 0.653 0.253

This correctly lines up the value 1 and value 2 of each comparison by assigning a universal key, then uses these keys in naming the new columns.