Bryan Han Bryan Han - 2 months ago 23
R Question

Sumif and Pivoting in R (reshape2, melt, cast, merge..)

I have a dataframe that looks like:
df->

Elmt1 Elmt2 Type
1 1 8 Red
2 5 3 Blue
3 5 1 Blue
4 7 2 Red
5 3 2 Red
6 5 2 Red


And a vector that looks like:
Time <- seq(1,8,1)

I need to create a new dataframe that looks like
newdf->

Time Elmt1Red Elemt1Blue Elmt2Red Elmt2Blue
1 1 1 0 0 1
2 2 0 0 3 0
3 3 1 0 0 1
4 4 0 0 0 0
5 5 1 2 0 0
6 6 0 0 0 0
7 7 1 0 0 0
8 8 0 0 1 0


Basically, using countif for each Time and Type.
Its like combination of sumif, merge, melt/dcast.

I cant seem to figure how to combine these. especially when Time is a vector.

Thank you.

Answer

Here is a combination of melt, cast, and merge:

library(reshape2)
m <- melt(df1, measure.var=c("Elmt1", "Elmt2"))
newdf <- dcast(m, value ~ variable + Type)
mrg <- merge(data.frame(value=Time), newdf, all.x=TRUE)
mrg[is.na(mrg)] <- 0L
mrg
#   value Elmt1_Blue Elmt1_Red Elmt2_Blue Elmt2_Red
# 1     1          0         1          1         0
# 2     2          0         0          0         3
# 3     3          0         1          1         0
# 4     4          0         0          0         0
# 5     5          2         1          0         0
# 6     6          0         0          0         0
# 7     7          0         1          0         0
# 8     8          0         0          0         1