Subset data frame based on number of rows per group
dataframe, r, r-faq, subset
Solution
First, two `base` alternatives. One relies on `table`, and the other on `ave` and `length`. Then, two `data.table` ways.
1. `table`
tt <- table(df$name)
df2 <- subset(df, name %in% names(tt[tt < 3]))
# or
df2 <- df[df$name %in% names(tt[tt < 3]), ]
If you want to walk it through step by step:
# count each 'name', assign result to an object 'tt'
tt <- table(df$name)
# which 'name' in 'tt' occur more than three times?
# Result is a logical vector that can be used to subset the table 'tt'
tt < 3
# from the table, select 'name' that occur < 3 times
tt[tt < 3]
# ...their names
names(tt[tt < 3])
# rows of 'name' in the data frame that matches "the < 3 names"
# the result is a logical vector that can be used to subset the data frame 'df'
df$name %in% names(tt[tt < 3])
# subset data frame by a logical vector
# 'TRUE' rows are kept, 'FALSE' rows are removed.
# assign the result to a data frame with a new name
df2 <- subset(df, name %in% names(tt[tt < 3]))
# or
df2 <- df[df$name %in% names(tt[tt < 3]), ]
2. `ave` and `length`
As suggested by @flodel:
df[ave(df$x, df$name, FUN = length) < 3, ]
3. `data.table`: `.N` and `.SD`:
library(data.table)
setDT(df)[, if (.N < 3) .SD, by = name]
4. `data.table`: `.N` and `.I`:
setDT(df)
df[df[, .I[.N < 3], name]$V1]
See also the related Q&A Count number of observations/rows per group and add result to data frame.
Problem
I have data like this, where some "name" occurs more than three times: ``` df <- data.frame(name = c("a", "a", "a", "b", "b", "c", "c", "c", "c"), x = 1:9) name x 1 a 1 2 a 2 3 a 3 4 b 4 5 b 5 6 c 6 7 c 7 8 c 8 9 c 9 ``` I wish to subset (filter) the data based on number of rows (observations) within each level of the `name` variable. If a certain level of `name` occurs more than say 3 times, I want to remove all rows belonging to that level. So in this example, we would drop observations where `name == c`, since there are `> 3` rows in that group: ``` name x 1 a 1 2 a 2 3 a 3 4 b 4 5 b 5 ``` I wrote this code, but can't get it to work. ``` as.data.frame(table(unique(df)$name)) subset(df, name > 3) ```