CODEWITHSUNDEEP
rdata.tabledplyrtidyr
feinmann
feinmann

Reputation: 1122

Efficient spread of values in data.table

I have a data.table like that

   ID event meas1 meas2 meas3
1:  1     A   100   500   900
2:  1     B   200   600  1000
3:  1     C   300   700  1100
4:  2     A   400   800  1200
5:  3     A   500   900  1300
6:  3     B   600  1000  1400

Every observation is distinct by its ID, so I want one row for one ID.

  ID A_meas1 B_meas1 C_meas1 A_meas2 B_meas2 C_meas2 A_meas3 B_meas3 C_meas3
1  1     100     200     300     500     600     700     900    1000    1100
2  2     400     NaN     NaN     800     NaN     NaN    1200     NaN     NaN
3  3     500     600     NaN     900    1000     NaN    1300    1400     NaN

Is there a way to do it efficiently since I have a data.table with 10 mio. rows?

At the moment I tried this:

library(data.table)
library(dplyr)
library(tidyr)

dt %>% 
  mutate(key = paste(event, "meas1", sep="_")) %>% 
  spread(key, meas1)  %>% 
  mutate(key = paste(event, "meas2", sep="_")) %>% 
  spread(key, meas2)  %>% 
  mutate(key = paste(event, "meas3", sep="_")) %>% 
  spread(key, meas3)  %>% 
  select(-event) %>%
  group_by(ID) %>%
  summarise(A_meas1=mean(A_meas1, na.rm=T),
            B_meas1=mean(B_meas1, na.rm=T),
            C_meas1=mean(C_meas1, na.rm=T),
            A_meas2=mean(A_meas2, na.rm=T),
            B_meas2=mean(B_meas2, na.rm=T),
            C_meas2=mean(C_meas2, na.rm=T),
            A_meas3=mean(A_meas3, na.rm=T),
            B_meas3=mean(B_meas3, na.rm=T),
            C_meas3=mean(C_meas3, na.rm=T)) -> data

But cannot afford so much memory, I'm afraid.

I appreciate your ideas and comment on my code and/or my idea. Thank you.

Upvotes: 1

Views: 2742

Answers (1)

curious
curious

Reputation: 51

You can try with the following:

dt <- melt(dt, id.var=c(1,2)) #to get all the values from meas1, meas2 and meas3 columns into one column

dt$combined<- with(dt, paste0(event, variable)) #combine the strings from two columns into one to get the column names that you want

dt[, c("event", "variable") := NULL] #delete unnecessary variables

dt <- dcast(dt, ID ~ combined, value.var = "value") #get the final format

You can reorder the columns now. Hope this helps.

Upvotes: 2

Related Questions