reshape2: dcast when there are multiple values for one cell but keep this values

Question

I have a data frame like that:

    HLA_Status    variable      value
1     PP            CCL24       9.645
2     PP            CCL24       56.32
3     PP            CCL24       7.268
4     PC            CCL24       5.698
5     PC            CCL24       89.457
6     PC            CCL24       78.23
7     PP            SPP1        23.12
8     PP            SPP1        36.32
9     PP            SPP1        17.268
10    PC            SPP1        2.698
11    PC            SPP1        9.457
12    PC            SPP1        8.23

I want to reshape my data frame with reshape2::dcast() and get this:

   HLA_Status        CCL24        SPP1
1      PP            9.645       23.12
2      PP            56.32       36.32
3      PP            7.268       17.268
13     PC            5.698       2.698
14     PC            89.457      9.457
15     PC            78.230      8.23

But I didn't manage to do this.

I tried this:

dcast(mydt, HLA_Status ~ variable, value.var = "value")

But it didn't work.

And I see on the documentation of reshape2 that if we have multiple values per cell we need to tell dcast how to aggregate the data.

I think my problem is to don't know what to give to fun.aggregate.

How can I get the wanted data frame by using reshape2 or others packages?

try this: `library(tidyr); mydt %>% spread(variable, value)` — Stephan, May 10 '19 at 10:09
When you provide a unique identifier to every row of your original dataframe (e.g. row number) it should work. — MrNetherlands, May 10 '19 at 10:10

Ronak Shah · Answer 1 · 2019-05-10T10:27:27.853

2

We can use spread from tidyr

library(dplyr)
library(tidyr)

df %>%
  group_by(HLA_Status, variable) %>%
  mutate(row = row_number()) %>%
  spread(variable, value) %>%
  ungroup() %>%
  select(-row)

# A tibble: 6 x 3
#  HLA_Status CCL24  SPP1
#  <fct>     <dbl> <dbl>
#1   PC       5.70  2.70
#2   PC       89.5  9.46
#3   PC       78.2  8.23
#4   PP       9.64  23.1 
#5   PP       56.3  36.3 
#6   PP       7.27  17.3

data

df <- structure(list(HLA_Status = structure(c(2L, 2L, 2L, 1L, 1L, 1L, 
2L, 2L, 2L, 1L, 1L, 1L), .Label = c("PC", "PP"), class = "factor"), 
variable = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L, 2L, 2L), .Label = c("CCL24", "SPP1"), class = "factor"), 
value = c(9.645, 56.32, 7.268, 5.698, 89.457, 78.23, 23.12, 
36.32, 17.268, 2.698, 9.457, 8.23)), class = "data.frame", row.names = 
c(NA, -12L))

edited May 10 '19 at 10:27

answered May 10 '19 at 10:14

Ronak Shah

377,200
20
156
213

Ok it works. May be add df=as.data.frame(df) to be precise? – Adam Bellaïche May 10 '19 at 10:25
@AdamBellaïche isn't it already a dataframe ? – Ronak Shah May 10 '19 at 10:32
function class() give me: [1] "tbl_df" "tbl" "data.frame" – Adam Bellaïche May 10 '19 at 10:35
it should still work irrespective of that I believe. – Ronak Shah May 10 '19 at 10:36
Sorry? I don't understand – Adam Bellaïche May 10 '19 at 10:39
does the above solution work if you don't do `df = as.data.frame(df)` ? – Ronak Shah May 10 '19 at 10:40
Yes it works without df=as.data.frame(df) ! Your answer is very good. – Adam Bellaïche May 10 '19 at 10:42

score 2 · Accepted Answer · answered May 10 '19 at 10:25

This can be done with dcast (here from data.table) though you need a row identifier.

library(data.table)
dcast(dt, HLA_Status + rowid(HLA_Status, variable) ~ variable)
#   HLA_Status HLA_Status_1  CCL24   SPP1
#1:         PC            1  5.698  2.698
#2:         PC            2 89.457  9.457
#3:         PC            3 78.230  8.230
#4:         PP            1  9.645 23.120
#5:         PP            2 56.320 36.320
#6:         PP            3  7.268 17.268

data

dt <- fread("    HLA_Status    variable      value
     PP            CCL24       9.645
     PP            CCL24       56.32
     PP            CCL24       7.268
     PC            CCL24       5.698
     PC            CCL24       89.457
     PC            CCL24       78.23
     PP            SPP1        23.12
     PP            SPP1        36.32
     PP            SPP1        17.268
     PC            SPP1        2.698
     PC            SPP1        9.457
     PC            SPP1        8.23")

Great, I didn't know `rowid` before! With `reshape2` we could use `ave`, see my answer. — jay.sf, May 10 '19 at 11:16

jay.sf · Answer 3 · 2019-05-10T11:36:00.750

If reshape2::dcast is actually needed, there's a solution with ave (to get the identifiers, see @markus' answer):

reshape2::dcast(d, HLA_Status + ave(rep(1, nrow(d)), d[1:2], FUN=seq) ~ variable)
#   HLA_Status ave(rep(1, nrow(d)), d[1:2], FUN = seq)  CCL24   SPP1
# 1         PC                                       1  5.698  2.698
# 2         PC                                       2 89.457  9.457
# 3         PC                                       3 78.230  8.230
# 4         PP                                       1  9.645 23.120
# 5         PP                                       2 56.320 36.320
# 6         PP                                       3  7.268 17.268

Data

d <- structure(list(HLA_Status = c("PP", "PP", "PP", "PC", "PC", "PC", 
"PP", "PP", "PP", "PC", "PC", "PC"), variable = c("CCL24", "CCL24", 
"CCL24", "CCL24", "CCL24", "CCL24", "SPP1", "SPP1", "SPP1", "SPP1", 
"SPP1", "SPP1"), value = c(9.645, 56.32, 7.268, 5.698, 89.457, 
78.23, 23.12, 36.32, 17.268, 2.698, 9.457, 8.23)), row.names = c(NA, 
-12L), class = "data.frame")

MrNetherlands · Answer 4 · 2019-05-10T12:09:30.277

I highly recommend to switch to tidyr instead of using reshape2. However, if you really would like to use dcast this is the way to go

library(dplyr)
library(reshape2)
df <- structure(list(HLA_Status = c("PP", "PP", "PP", "PC", "PC", "PC", 
"PP", "PP", "PP", "PC", "PC", "PC"), variable = c("CCL24", "CCL24", 
"CCL24", "CCL24", "CCL24", "CCL24", "SPP1", "SPP1", "SPP1", "SPP1", 
"SPP1", "SPP1"), value = c(9.645, 56.32, 7.268, 5.698, 89.457, 
78.23, 23.12, 36.32, 17.268, 2.698, 9.457, 8.23)), row.names = c(NA, 
-12L), class = "data.frame")


df %>% 
  group_by(variable, HLA_Status) %>%
  mutate(id = row_number()) %>% 
  dcast(HLA_Status+id ~ variable, value.var = "value") %>%
  select(-id)

  HLA_Status  CCL24   SPP1
1         PC  5.698  2.698
2         PC 89.457  9.457
3         PC 78.230  8.230
4         PP  9.645 23.120
5         PP 56.320 36.320
6         PP  7.268 17.268

I don't care about which package is used. I see it is a little complicated with reshape2. But thank you — Adam Bellaïche, May 10 '19 at 10:27

reshape2: dcast when there are multiple values for one cell but keep this values

4 Answers4