0

I have a table, that has the following format

time    ID
 1     4024
 1     4024
 1     4025
 1     4016
 2     4024
 2     4025
 2     4025
 3     4024
 3     4016
 3     4016

I want to count the frequence for each ID in each time point. For instance, I would like to generate the following table based on the above table

time  4024   4025 4016
1      2      1    1
2      1      2    0
3      1      0    2

It seems that aggregate function can help, but how to use it for this case. Thanks.

milan
  • 4,782
  • 2
  • 21
  • 39
user288609
  • 12,465
  • 26
  • 85
  • 127

1 Answers1

1

We can just use table

as.data.frame.matrix(table(df1))
#  4016 4024 4025
#1    1    2    1
#2    0    1    2
#3    2    1    0

Or use dcast from data.table

library(data.table)
dcast(setDT(df1), time ~ID, length)
#     time 4016 4024 4025
#1:    1    1    2    1
#2:    2    0    1    2
#3:    3    2    1    0

Or with spread from tidyr

library(dplyr)
library(tidyr)
df1 %>%
   group_by(time, ID) %>%
   summarise(Count = n()) %>%
   spread(ID, Count, fill = 0)
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    thanks a lot. It works. By the way, are there any difference between as.data.frame and as.data.frame.matrix? – user288609 Jun 20 '16 at 04:35
  • @use288609 If you use `as.data.frame` it will convert to `long` format. So, first the `table` is converted to `matrix` and then to `data.frame`. it is like `as.data.frame(as.matrix(table(df1)))` – akrun Jun 20 '16 at 04:37