SJSU2013 SJSU2013 - 14 days ago 5
R Question

Subset data frame based on number of rows / observations per group

I wish to subset a data frame based on number of rows within each level of a variable, here 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.

review_count name longitude state stars latitude type categories1 categories2
X5 14 Hot Bagels & Deli -112.2003 AZ 3.5 33.71280 business <NA> Bagels
X10 10 Jersey Mike's Subs -111.8120 AZ 3.5 33.37884 business Sandwiches <NA>
X12 5 La Paloma Mexican Food -112.0814 AZ 4.0 33.48011 business Mexican <NA>
X14 55 Sauce -111.9263 AZ 4.0 33.61746 business Pizza <NA>
X18 23 Fuddruckers -112.1162 AZ 4.0 33.56699 business Burgers <NA>
X23 42 China Chan Restaurant -112.1205 AZ 3.5 33.58036 business Buffets Dim Sum


I wrote this code, but can't get it to work.

###### Remove Chain Restos #########
Chains <- as.data.frame(table(unique(expandedDataFrame)$name))
chainsfreq <- subset(expandedDataFrame, name>3)


Thanks in advance.

Answer
# create some data where some "name" occur more than 3 times
df <- data.frame(name = c("a", "a", "a", "b", "b", "c", "c", "c", "c"), x = sample(1:9))

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]), ]

Another base alternative, as suggested by @flodel:

df[ave(df$x, df$name, FUN = length) < 3, ]

An alternative with the data.table-package:

library(data.table)
setDT(df)[, if (.N < 3) .SD, by = name]

See also the related Q&A Count number of observations/rows per group and add result to data frame.

Comments