如何用dplyr优化按组筛选：组内最高日值>3时取全组行

Hey there! Let's tackle this problem neatly and efficiently. You want to keep all rows from groups (grouped by id) where the value corresponding to the maximum day in that group is greater than 3. Here are some clean, optimized solutions:

dplyr Solution (Concise & Efficient) #

This version cuts out unnecessary helper columns and does the check directly in the filter step—no extra steps needed:

library(dplyr)

filtered_data <- my_data %>%
  group_by(id) %>%
  filter(value[which.max(day)] > 3) %>%
  ungroup()

Breakdown: #

• group_by(id): Groups the data by each unique id so we can evaluate conditions per group.
• filter(value[which.max(day)] > 3): For each group, which.max(day) finds the position of the highest day value, then we grab the corresponding value and check if it's greater than 3. If yes, all rows in that group are kept.
• ungroup(): Resets the grouping to prevent unexpected behavior in later operations.

If you prefer to see the helper value explicitly (for debugging or clarity), you can use this slightly more verbose version:

filtered_data <- my_data %>%
  group_by(id) %>%
  mutate(max_day_value = value[which.max(day)]) %>%
  filter(max_day_value > 3) %>%
  select(-max_day_value) %>% # Clean up the helper column
  ungroup()

data.table Solution (Great for Large Datasets) #

If you're working with big data, data.table offers faster performance with a clean syntax:

library(data.table)

setDT(my_data) # Convert data frame to data.table
filtered_data <- my_data[, if (value[which.max(day)] > 3) .SD, by = id]

Breakdown: #

• setDT(my_data): Converts your data frame to a data.table for optimized operations.
• [, if (value[which.max(day)] > 3) .SD, by = id]: For each group (by = id), we check if the value at the maximum day is >3. .SD stands for "Subset of Data"—so if the condition is met, we return all rows in that group.

Sample Output #

Using your test data, the filtered result will include all rows for id = "a" and id = "c" (since their max day values are 4, which is >3):

id day value
1:  a   1     2
2:  a   2     3
3:  a   3     4
4:  c   1     1
5:  c   2     2
6:  c   3     4

内容的提问来源于stack exchange，提问作者tjebo