select id that appears at least (n) times in each group in r

user_13945480 0
Viewed 193

i am working with a huge dataset that have ids and time. Let's say I want to select ids that appear at least two times at month 1 and 2 (in actual data I want to select ids if they appear at least 15 times in specified month). How can I accomplish this in R. Here is the dataset

df <- data.frame(id = c(1,1,1,1,1,2,2,2,2,3,3,3),
                 month = c(1,1,1,2,2,1,1,2,2,2,3,3))

here is what i want

df_1 <- data.frame(id = c(1,1,1,1,1,2,2,2,2),
                   month = c(1,1,1,2,2,1,1,2,2))

Thanks in advance!

r
3 Answers

Tidyverse:

library(dplyr)
df %>%
  group_by(id, month) %>%
  filter(month %in% 1:2, n() >= 2) %>%
  ungroup()
# # A tibble: 9 x 2
#      id month
#   <dbl> <dbl>
# 1     1     1
# 2     1     1
# 3     1     1
# 4     1     2
# 5     1     2
# 6     2     1
# 7     2     1
# 8     2     2
# 9     2     2

data.table:

library(data.table)
DT <- as.data.table(df)
DT[, .SD[month < 3 & .N >= 2,], by = .(month, id)]
#    month id n
# 1:     1  1 3
# 2:     1  1 3
# 3:     1  1 3
# 4:     2  1 2
# 5:     2  1 2
# 6:     1  2 2
# 7:     1  2 2
# 8:     2  2 2
# 9:     2  2 2
user_33582720

A quick and dirty base R solution. Can surely be optimised

df <- data.frame(id = c(1,1,1,1,1,2,2,2,2,3,3,3),
                 month = c(1,1,1,2,2,1,1,2,2,2,3,3))

# select months 1 and 2 only 
sel.months <- c(1, 2)
df2 <- df[df$month %in% sel.months,]

# count ids
tb <- as.matrix(table(df2))

# get table rows that are > 2
ids <- as.integer(apply(tb, 2, function(x) {names(which(x >= 2)) })) 
# remove duplicates 
ids <- unique(ids)
# filter data 
df.filtered <- df[df$id %in% ids,]

df.filtered

A prettier solution using dplyr

library (dplyr) 

df <- data.frame(id = c(1,1,1,1,1,2,2,2,2,3,3,3),
                 month = c(1,1,1,2,2,1,1,2,2,2,3,3))

sel.months <- c(1, 2)

df.filtered <- df %>%
 filter (month %in% sel.months) %>%
 group_by (id, month) %>%
 mutate (count = table(id)) %>%
 filter (count >= 2)
user_1769230

If you want to select id's that occur atleast two times in the month 1 and 2 :

library(dplyr)
df %>% group_by(id) %>% filter(sum(month %in% 1:2) >= 2)

#    id month
#  <dbl> <dbl>
#1     1     1
#2     1     1
#3     1     1
#4     1     2
#5     1     2
#6     2     1
#7     2     1
#8     2     2
#9     2     2

The equivalent data.table :

library(data.table)
setDT(df)[, .SD[sum(month %in% 1:2) >= 2], id]

and base R solution :

subset(df, ave(month %in% 1:2, id, FUN = sum) >= 2)
user_39629140
Related