I have a data frame with two columns, date and id
> test
date id
1 2018-01-01 a
2 2018-01-02 b
3 2018-01-03 c
4 2018-01-04 d
5 2018-01-01 e
6 2018-01-02 f
7 2018-01-03 a
8 2018-01-04 b
9 2018-01-01 c
10 2018-01-01 d
11 2018-01-01 d
My goal is to shape this into a table that counts the number of each occurrence of each distinct id (in the dataset) per date, while preserving 0s if there is no occurrence of an id on a particular date using dplyr or data.table
The desired result output would look something like this:
> output
date a b c d e f
1 2018-01-01 1 0 1 2 1 0
2 2018-01-02 0 1 0 0 0 1
3 2018-01-03 1 0 1 0 0 0
4 2018-01-04 0 1 0 1 0 0
This code does not produce the desired result:
test %>%
group_by(date, id) %>%
summarise_all(funs(sum))
What is the correct way to produce the desired output using dplyr or data.table?
Below is a copy of the sample data:
> dput(test)
structure(list(date = structure(c(17532, 17533, 17534, 17535,
17532, 17533, 17534, 17535, 17532, 17532, 17532), class = "Date"),
id = c("a", "b", "c", "d", "e", "f", "a", "b", "c", "d",
"d")), .Names = c("date", "id"), row.names = c(NA, 11L), class = "data.frame")
