R - Loop through a data table with combination of dcast of sum

Question

I have a table similar this, with more columns. What I am trying to do is creating a new table that shows, for each ID, the number of Counts of each Type, the Value of each Type.

df

ID   Type  Counts  Value
1     A     1       5
1     B     2       4
2     A     2       1
2     A     3       4
2     B     1       3
2     B     2       3

I am able to do it for one single column by using

dcast(df[,j=list(sum(Counts,na.rm = TRUE)),by = c("ID","Type")],ID ~ paste(Type,"Counts",sep="_"))

However, I want to use a loop through each column within the data table. but there is no success, it will always add up all the rows. I have try to use

sum(df[[i]],na.rm = TRUE)
sum(names(df)[[i]] == "",na.rm = TRUE)
sum(df[[names(df)[i]]],na.rm = TRUE)
j = list(apply(df[,c(3:4),with=FALSE],2,function(x) sum(x,na.rm = TRUE)

I want to have a new table similar like

ID   A_Counts  B_Counts  A_Value   B_Value
1         1      2          5           4
2         5      3          5           6

My own table have more columns, but the idea is the same. Do I over-complicated it or is there a easy trick I am not aware of? Please help me. Thank you!

see the link https://stackoverflow.com/questions/29775461/how-can-i-spread-repeated-measures-of-multiple-variables-into-wide-format — BENY, Jul 12 '17 at 15:26
Hi Wen, Thank you for the link. I did not do enough research online to find this link. — VeraShao, Jul 12 '17 at 15:58

moodymudskipper · Accepted Answer · 2017-07-12T15:49:07.430

You have to melt your data first, and then dcast it:

library(reshape2)
df2 <- melt(df,id.vars = c("ID","Type"))
#    ID Type variable value
# 1   1    A   Counts     1
# 2   1    B   Counts     2
# 3   2    A   Counts     2
# 4   2    A   Counts     3
# 5   2    B   Counts     1
# 6   2    B   Counts     2
# 7   1    A    Value     5
# 8   1    B    Value     4
# 9   2    A    Value     1
# 10  2    A    Value     4
# 11  2    B    Value     3
# 12  2    B    Value     3
dcast(df2,ID ~ Type + variable,fun.aggregate=sum)

#   ID A_Counts A_Value B_Counts B_Value
# 1  1        1       5        2       4
# 2  2        5       5        3       6

Another solution with base functions only:

df3 <- aggregate(cbind(Counts,Value) ~ ID + Type,df,sum)
#   ID Type Counts Value
# 1  1    A      1     5
# 2  2    A      5     5
# 3  1    B      2     4
# 4  2    B      3     6

reshape(df3, idvar='ID', timevar='Type',direction="wide")
#   ID Counts.A Value.A Counts.B Value.B
# 1  1        1       5        2       4
# 2  2        5       5        3       6

Data

df <- read.table(text ="ID   Type  Counts  Value
1     A     1       5
1     B     2       4
2     A     2       1
2     A     3       4
2     B     1       3
2     B     2       3",stringsAsFactors=FALSE,header=TRUE)

Hi Moody. Thank you so much! it is simple and works. I have so many more needs to learn — VeraShao, Jul 12 '17 at 15:57

R - Loop through a data table with combination of dcast of sum

1 Answers1