subset dataframe based on outlying counts
r, subset
Solution
Here's a `plyr` solution :
## df2$test is true if Count >= max(Count)*0.05 for this month
df2 <- ddply(df, .(Month), transform, test=Count>=(max(Count)*0.05))
## For each site, test$keep is true if at least one count is >= max(Count)*0.05 for this month
test <- ddply(df2, .(Site), summarise, keep=sum(test)>0)
## Subsetting
sites <- test$Site[test$keep]
df[df$Site %in% sites,]
Problem
I have a dataframe that looks like following: ``` df <- data.frame(Site=rep(paste0('site', 1:5), 50), Month=sample(1:12, 50, replace=T), Count=(sample(1:1000, 50, replace=T))) ``` I want to remove any sites where the count is always <5% of max monthly count across all sites. The max monthly counts across all sites are: ``` library(plyr) ddply(df, .(Month), summarise, Max.Count=max(Count)) ``` If a count of 1 is assigned to site5, then its counts are always <5% of max monthly counts across all sites. Therefore I would want site5 removed. ``` df$Count[df$Site=='site5'] <- 1 ``` However, after assigning new values to site2, some of its counts are <5% of max monthly counts, while others are >5%. Therefore I would not want site2 removed. ``` df$Count[df$Site=='site2'] <- ceiling(seq(1, 1000, length.out=20)) ``` How can I subset dataframe to remove any sites where counts are always <5% of max monthly count? Let me know if question unclear and I will amend.