Creating a table of counts by some factor level in R

Question

I have some data of foods eaten on different days.

day <- c(1,1,1,1,2,2,2,3,3,3,3,3)
food <- c('pizza','pizza','taco','snake','snake','taco','taco','pizza','taco','pizza','taco','snake')
all <- data.frame(day, food)

I'd like to create a table with food on the x axis, day on the y axis, and the number of foods per day in the matrices such that

food.list <- unique(all$food)
 day.list <- unique(all$day )
output <- data.frame(c(2,1,1),c(0,2,1),c(2,2,1))
output
      1 2 3
pizza 2 0 2
taco  1 2 2
snake 1 1 1

What is a straightforward way to solve this problem?

d.b · Accepted Answer · 2017-03-29T21:21:28.630

3

library(reshape2)
dcast(data = all, formula = food~day, fun.aggregate = length, value.var = "food")
   food 1 2 3
1 pizza 2 0 2
2 snake 1 1 1
3  taco 1 2 2

OR

table(all$food, all$day)

        1 2 3
  pizza 2 0 2
  snake 1 1 1
  taco  1 2 2

edited Mar 29 '17 at 21:21

answered Mar 29 '17 at 21:17

d.b

32,245
6
36
77

no need to transpose, just pass the variables in reverse order, either name them or `table(all[2:1])` – user20650 Mar 29 '17 at 21:20

neilfws · Answer 2 · 2017-03-29T21:39:42.673

It would be "tidier" to leave day, food and count in different columns. Note: I added stringsAsFactors = FALSE when creating the data frame, it avoids issues later.

library(dplyr)
all %>%
  group_by(day, food) %>% 
  tally()

But you can always spread if you really want days as columns:

library(tidyr)
all %>% 
  group_by(day, food) %>% 
  tally() %>% 
  spread(day, n)

# A tibble: 3 × 4
    food   `1`   `2`   `3`
* <chr> <int> <int> <int>
1  pizza     2    NA     2
2  snake     1     1     1
3   taco     1     2     2

and if you want NA to be 0, add:

%>% 
  mutate_if(is.integer, funs(replace(., is.na(.), 0)))

`count` does `group_by(...) %>% tally()` in one go: `all %>% count(day, food)` — alistaire, Mar 29 '17 at 21:37

score 2 · Answer 3 · answered Mar 29 '17 at 21:42

2

Sorry but what's wrong with table?

day = c(1,1,1,1,2,2,2,3,3,3,3,3)
food = c('pizza','pizza','taco','snake','snake','taco','taco','pizza','taco','pizza','taco','snake')
table(food, day)

      day
food    1 2 3
  pizza 2 0 2
  snake 1 1 1
  taco  1 2 2

answered Mar 29 '17 at 21:42

Fernando

7,785
6
49
81

yup, *the* answer really is `table` . d.b. does give a table solution, but lower in precedence so +1 – user20650 Mar 29 '17 at 22:00
That's why I didn't saw it I guess. – Fernando Mar 30 '17 at 13:32
jusr re-read my comment... it wasnt meant to suggest that you shouldnt of answered as it had already been given.. it was meant to say that table is the answer ie just like this answer – user20650 Mar 30 '17 at 13:40
1

Of course, I know that =) – Fernando Mar 30 '17 at 14:07

989 · Answer 4 · 2017-03-29T21:29:45.317

1

You could also do this using xtabs:

xtabs(col~food+day, cbind(all, col=1))

# OR

xtabs(col~food+day, transform(all, col=1))

# OR

xtabs(~food+day, all) # thanks to user20650

#      day
#food    1 2 3
#  pizza 2 0 2
#  snake 1 1 1
#  taco  1 2 2

edited Mar 29 '17 at 21:29

answered Mar 29 '17 at 21:27

989

12,579
5
31
53

Creating a table of counts by some factor level in R

4 Answers4