Pivot wider with merging rows in R-CodePudding

I have the data like this:

id	group	A	B	C	D
id1	2	0	81	0.01	81
id1	3	115	66	0.05	181
id2	2	0	58	0.01	58
id2	3	0	110	0.01	110
id3	2	95	81	0.03	176
id4	2	40	126	0.02	166

and I need to reshape it to df which looks like this:

id	A_2	A_3	B_2	B_3	C_2	C_3	D_2	D_3
id1	0	115	81	66	0.01	0.05	81	181
id2	0	0	58	110	0.01	0.01	58	110
id3	95	0	81	0	0.03	0	176	0
id4	40	0	126	0	0.02	0	166	0

To do this I used (I set values_from sepretely for each variable A, B, C, D):

test <- pivot_wider(data, names_from = group, values_from = A)

but the result I obtaied look like this:

  id           B   C    D   `2`   `3`   `1`   `4`
  <chr>       <int> <int> <int> <dbl> <dbl> <dbl> <dbl>
1 id1     0    81    81  0.01 NA       NA    NA
2 id1   115    66   181 NA     0.05    NA    NA
3 id2     0    58    58  0.01 NA       NA    NA
4 id2     0   110   110 NA     0.01    NA    NA
5 id3   95    81   176  0.03 NA       NA    NA
6 id4    40   126   166  0.02 NA       NA    NA

Is there any way to correct this so I can obtain the results that I need ( one id = one row) or rather I should use another function?

CodePudding user response：

Two-step:

tidyr::pivot_longer(dat, -c(id, group)) %>%
  tidyr::pivot_wider(id, names_from=c("group", "name"),
                     names_glue="{name}_{group}", values_from="value")
# # A tibble: 4 x 9
#   id      A_2   B_2   C_2   D_2   A_3   B_3   C_3   D_3
#   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 id1       0    81  0.01    81   115    66  0.05   181
# 2 id2       0    58  0.01    58     0   110  0.01   110
# 3 id3      95    81  0.03   176    NA    NA NA       NA
# 4 id4      40   126  0.02   166    NA    NA NA       NA

though I suspect there's a way to do it with names_glue.

Or a one-step:

pivot_wider(dat, names_from = "group", values_from = A:D, values_fill = 0)
# # A tibble: 4 x 9
#   id      A_2   A_3   B_2   B_3   C_2   C_3   D_2   D_3
#   <chr> <int> <int> <int> <int> <dbl> <dbl> <int> <int>
# 1 id1       0   115    81    66  0.01  0.05    81   181
# 2 id2       0     0    58   110  0.01  0.01    58   110
# 3 id3      95     0    81     0  0.03  0      176     0
# 4 id4      40     0   126     0  0.02  0      166     0

Neither are immediately filling out missing letter/number combinations.

CodePudding user response：

We may need to expand the data with complete before doing the pivot_wider

library(dplyr)
library(tidyr)
data %>%
   complete(id, group = 1:3, fill = list(A = 0, B = 0, C = 0, D = 0)) %>% 
   pivot_wider(names_from = group, values_from = A:D)

-output

# A tibble: 4 × 13
  id      A_1   A_2   A_3   B_1   B_2   B_3   C_1   C_2   C_3   D_1   D_2   D_3
  <chr> <int> <int> <int> <int> <int> <int> <dbl> <dbl> <dbl> <int> <int> <int>
1 id1       0     0   115     0    81    66     0  0.01  0.05     0    81   181
2 id2       0     0     0     0    58   110     0  0.01  0.01     0    58   110
3 id3       0    95     0     0    81     0     0  0.03  0        0   176     0
4 id4       0    40     0     0   126     0     0  0.02  0        0   166     0