Count number of rows per group and add result to original data frame

Uri Laserson picture Uri Laserson · Sep 16, 2011 · Viewed 104k times · Source

Say I have a data.frame object:

df <- data.frame(name=c('black','black','black','red','red'),
                 type=c('chair','chair','sofa','sofa','plate'),
                 num=c(4,5,12,4,3))

Now I want to count the number of rows (observations) of for each combination of name and type. This can be done like so:

table(df[ , c("name","type")])

or possibly also with plyr, (though I am not sure how).

However, how do I get the results incorporated into the original data frame? So that the results will look like this:

df
#    name  type num count
# 1 black chair   4     2
# 2 black chair   5     2
# 3 black  sofa  12     1
# 4   red  sofa   4     1
# 5   red plate   3     1

where count now stores the results from the aggregation.

A solution with plyr could be interesting to learn as well, though I would like to see how this is done with base R.

Answer

Ramnath picture Ramnath · Sep 16, 2011

Using data.table:

library(data.table)
dt = as.data.table(df)

# or coerce to data.table by reference:
# setDT(df)

dt[ , count := .N, by = .(name, type)]

For pre-data.table 1.8.2 alternative, see edit history.


Using dplyr:

library(dplyr)
df %>%
  group_by(name, type) %>%
  mutate(count = n())

Or simply:

add_count(df, name, type)

Using plyr:

plyr::ddply(df, .(name, type), transform, count = length(num))