-1

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.

Bryan Han
  • 67
  • 6

2 Answers2

1

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
Pierre L
  • 28,203
  • 6
  • 47
  • 69
0

We can use table from base R

v1 <- unlist(df[1:2])
lvls <- min(v1):max(v1)
data.frame(Time = lvls, do.call(cbind, lapply(df[1:2], 
          function(x) table(factor(x, levels=lvls), df$Type))))
akrun
  • 874,273
  • 37
  • 540
  • 662