Filter grouped data with conditional statement in R

Question

The data frame below is grouped by id:

id<- c(1,1,1,2,3,3,4,4,4,5,5,6)
x <- c(0,1,0,0,1,1,0,0,1,0,0,1)
df <- data.frame(id, x)

I am looking for a way to filter the data in R based a condition. The condition is; if an id includes 1 in column x delete the preceding rows containing 0 for that id while maintaining the other structure of the data. The expected output is

I tried to subset the data using the filter function in the dplyr package as in the code below:

df <- df %>%
   group_by(id) %>%
   filter(first(x)==1 | x == 0)

but I am not getting the expected and I am reaching out for help. I greatly appreciate any help.

If x is binary as in your example, you can do df |> filter(cumsum(x) > 0 | sum(x) == 0, .by = id). — iroha
– iroha, Commented Jun 3 at 1:40

Edward · Accepted Answer · 2025-06-03 09:04:36Z

1

A rather awkward-looking base R way may be:

do.call(rbind, by(df, id, FUN=\(x) subset(x, cumsum(x)>0 | all(x==0, na.rm=T))))

The rownames look ugly, but they do provide useful information. However, the answer given by lroha using dplyr::filter in the comments looks like the preferred method.

answered Jun 3 at 9:04

Edward

22.2k3 gold badges18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

s_baldur · Accepted Answer · 2025-06-04 21:32:18Z

1

Using which.max()

stopifnot(all(df$x %in% c(0, 1))) # Assumes 0/1 only.

df |> filter(row_number() >= which.max(x), .by = id)
# or
df |> slice(which.max(x):n(), .by = id)

#   id x
# 1  1 1
# 2  1 0
# 3  2 0
# 4  3 1
# 5  3 1
# 6  4 1
# 7  5 0
# 8  5 0
# 9  6 1

Why it works?

Two cases to consider:

If there is a 1 in x then which.max(x) is the index of the first instance and we remove everything before it.
Otherwise (all 0) which.max(x) == 1 so we don't remove anything.

edited Jun 4 at 21:32

answered Jun 4 at 21:11

s_baldur

34.5k4 gold badges43 silver badges80 bronze badges

Collectives™ on Stack Overflow

Filter grouped data with conditional statement in R

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related