Remove row with value x only if column contains both value x and y, by group
Below my clunky way to seek what I'm looking for, just to know if someone has a nicer, shorter method. Relatively concerned with performance. Ideally I'd try to avoid creating another object in the environment.
``` r
library(dplyr)
## making some dummy data
set.seed(42)
df <- esoph[sample(nrow(esoph), 50), ] %>%
group_by(agegp, alcgp) %>%
filter(n() > 1) %>%
slice(1:2) %>%
mutate(group_id = cur_group_id()) %>%
arrange(agegp, alcgp) %>%
head(14) %>%
ungroup() %>%
select(-agegp, -alcgp)
### to the actual problem
## only if group has both tobgp = "20-29" and "10-19", remove row "10-19".
df_im <- df %>%
group_by(group_id) %>%
filter(
any(tobgp == "20-29") & any(tobgp == "10-19"),
# remove then row "10-19"
tobgp != "10-19"
) %>%
ungroup()
## Now I'm removing all groups that I have found out to contain both values, just to add the data without the "10-19" rows back...
df %>%
anti_join(df_im, by = "group_id") %>%
bind_rows(df_im)
#> # A tibble: 13 × 4
#> tobgp ncases ncontrols group_id
#>
#> 1 20-29 0 6 1
#> 2 30+ 0 5 1
#> 3 0-9g/day 0 27 2
#> 4 30+ 0 7 2
#> 5 0-9g/day 0 35 3
#> 6 20-29 1 13 3
#> 7 10-19 0 6 4
#> 8 0-9g/day 0 11 4
#> 9 20-29 2 2 5
#> 10 0-9g/day 2 1 5
#> 11 20-29 1 4 7
#> 12 30+ 2 2 7
#> 13 20-29 5 10 6
## expected result: Group 6 has no "10-19"
Created on 2025-12-15 with reprex v2.1.1