CODEWITHSUNDEEP
rdplyr
iskandarblue
iskandarblue

Reputation: 7526

Create matrix of counts using two variables

I have two columns - a unique id column id and the day of travel day. My objective is to create a matrix of counts per id per day (and to include all days even if the count is zero)

> test
   id day
1   3   3
2   4   4
3   1   4
4   2   3
5   2   5
6   2   4
7   1   1
8   5   4
9   1   1
10  3   2
11  2   2
12  4   2
13  2   4
14  2   5
15  4   5
16  3   4
17  5   3
18  3   2
19  5   5
20  3   4
21  1   3
22  2   3
23  2   5
24  5   2
25  3   2

The output should be the following, where rows represent id and columns represent day:

> output
  1 2 3 4 5
1 2 0 1 1 0
2 0 1 2 2 3
3 0 3 1 2 0
4 0 1 0 1 1
5 0 1 1 1 1

I have tried the following with the reshape package

output <- reshape2::dcast(test, day ~ id, sum)

but it throws the following error:

Error in unique.default(x) : unique() applies only to vectors

Why does this happen and what would the right solution be in dplyr or using base R? Any tips would be appreciated.

Here is the data:

> dput(test)
structure(list(id = c(3, 4, 1, 2, 2, 2, 1, 5, 1, 3, 2, 4, 2, 
2, 4, 3, 5, 3, 5, 3, 1, 2, 2, 5, 3), day = c(3, 4, 4, 3, 5, 4, 
1, 4, 1, 2, 2, 2, 4, 5, 5, 4, 3, 2, 5, 4, 3, 3, 5, 2, 2)), .Names = c("id", 
"day"), row.names = c(NA, -25L), class = "data.frame")

Upvotes: 1

Views: 5816

Answers (2)

pyll
pyll

Reputation: 1764

Easier to see whats going on with character variables

id <- c('a', 'a', 'b', 'f', 'b', 'a')
day <- c('x', 'x', 'x', 'y', 'z', 'x')

test <- data.frame(id, day)



output <- as.data.frame.matrix(table(test))

This is the simplest way to do it...use the table() function then convert to data.frame

Upvotes: 2

Shahab Einabadi
Shahab Einabadi

Reputation: 332

 ans <- tapply(test$id, test$day, 
               function(x) {
                 y <- table(x)
                 z <- rep(0, 5)
                 z[as.numeric(names(y))] <- y
                 z
               } )
 do.call("cbind", ans)
     1 2 3 4 5
[1,] 2 0 1 1 0
[2,] 0 1 2 2 3
[3,] 0 3 1 2 0
[4,] 0 1 0 1 1
[5,] 0 1 1 1 1

Upvotes: 0

Related Questions