Using pivot_wider in R to reshape biomarkHD data?-CodePudding

I have the following snippet of my dataframe. The values aren't important. I am trying to get it to wide form using pivot_wider.

Dataset
id_1 <- c("S78-A01", "S78-A04", "S90-A01", "S90-A04", "S82-A01", "S82-A04", "S94-A01", "S94-A04", "S75-A01", "S75-A04", "S87-A01", "S87-A04")
id_2 <- c("CB-2_1:1024", "CB-2_1:1024", "CB-2_1:1024", "CB-2_1:1024", "CB-2_1:128", "CB-2_1:128", "CB-2_1:128", "CB-2_1:128", "CB-2_1:16", "CB-2_1:16", "CB-2_1:16", "CB-2_1:16")
Gene <- c("AIM2", "AIM2", "AIM2", "AIM2", "AIM2", "AIM2", "AIM2", "AIM2", "AIM2", "C1QB", "C1QB", "C1QB")
value1 <- c(-1.9975984661369099, 4.7789368498721396 , 3.3080754647069801, 6.9507934374320604, 2.55279730894866, 1.38567349551152, 1.99, 4.5115336458523103, 3.2588723737573799, 6.9433118002602097, 2.5897834603682202, 1.4031532547429899)

df <- data.frame(id1 =id_1, 
id2=id_2, 
Gene=Gene,
value1=value1)

# Pivot
df %>% pivot_wider(names_from = c("id2","Gene"),
                   values_from =  "value1")

However when I pivot I get the data as such with the headers like the below :

id1	CB-2_1:1024_AIM2	CB-2_1:128_AIM2	CB-2_1:16_AIM2	CB-2_1:16_C1QB
S78-A01	-2	NA	NA	NA
S78-A04	4.78	NA	NA	NA

What I want is it in the form of the below. I will take as close to that as I can get. The data is too large too do manually with 9218 rows. Any ideas of where I am pivoting wrong? DOes what I want to do even make sense with pivot?

id2	gene	val1	val2	val3
CB-2_1:1024	AIM2	-1.997589	4.778937	3.308075
CB-2_1:128	AIM2	2.552797	2.589783	1.403153
CB-2_1:16	C1QB	6.943312	1.385673	1.990000

CodePudding user response：

Do you mean this?

library(dplyr)
library(tidyr)
df %>%
  group_by(id2, Gene) %>%
  mutate(rn = paste0("value", row_number())) %>%
  ungroup() %>%
  pivot_wider(c(id2, Gene), names_from = "rn", values_from = "value1")
# # A tibble: 4 x 6
#   id2         Gene  value1 value2 value3 value4
#   <chr>       <chr>  <dbl>  <dbl>  <dbl>  <dbl>
# 1 CB-2_1:1024 AIM2   -2.00   4.78   3.31   6.95
# 2 CB-2_1:128  AIM2    2.55   1.39   1.99   4.51
# 3 CB-2_1:16   AIM2    3.26  NA     NA     NA   
# 4 CB-2_1:16   C1QB    6.94   2.59   1.40  NA