Remove non-last rows with certain condition per group

Question

I have the following dataframe called df (dput below):

  group indicator value
1     A     FALSE     2
2     A     FALSE     1
3     A     FALSE     2
4     A      TRUE     4
5     B     FALSE     5
6     B     FALSE     1
7     B      TRUE     3

I would like to remove the non-last rows with indicator == FALSE per group. This means that in df the rows: 1,2 and 5 should be removed because they are not the last rows with FALSE per group. Here is the desired output:

  group indicator value
1     A     FALSE     2
2     A      TRUE     4
3     B     FALSE     1
4     B      TRUE     3

So I was wondering if anyone knows how to remove non-last rows with certain condition per group in R?

dput of df:

df <- structure(list(group = c("A", "A", "A", "A", "B", "B", "B"), 
    indicator = c(FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE
    ), value = c(2, 1, 2, 4, 5, 1, 3)), class = "data.frame", row.names = c(NA, 
-7L))

@AnoushiravanR, because row 3 is the last row with FALSE. I would like to keep all last row FALSE per group. That's why row 3 and 6 are not removed. — Quinten, Nov 29 '22 at 12:14
And is the last row indicator always TRUE or could be also FALSE? — Anoushiravan R, Nov 29 '22 at 12:21
@AnoushiravanR, The rows with TRUE should happen nothing. So they need to stay. — Quinten, Nov 29 '22 at 12:22
I know but I would like to know whether last rows are always TRUE or not? — Anoushiravan R, Nov 29 '22 at 12:23
@zx8754, The number of F before T could be different, but the T is in this case always last per group. Is that what you mean? Thank you! — Quinten, Nov 29 '22 at 12:45
So we want last 2 rows per group? Maybe duplicate of https://stackoverflow.com/q/53994497/680068 ? — zx8754, Nov 29 '22 at 13:03
@zx8754, If there is no F per group then it still should return the T. — Quinten, Nov 29 '22 at 13:04
@Quinten it is in a way a duplicate as it seems what you'd like could be rephrased as last value per groups (whereby both `group` and `indicator` are grouping values).. — arg0naut91, Nov 29 '22 at 13:42
Hi @arg0naut91, I would like to keep all the T rows which means that the filtering is on a specific condition per group instead of always keeping the last value. Do you understand what I mean? Thank you! — Quinten, Nov 29 '22 at 13:44

zephryl · Accepted Answer · 2022-11-29T15:11:08.317

4

Filter using last(which()) to find the row number of the last FALSE row per group:

library(dplyr)

df %>%
  group_by(group) %>%
  filter(indicator | row_number() == last(which(!indicator))) %>%
  ungroup()

# A tibble: 4 × 3
  group indicator value
  <chr> <lgl>     <dbl>
1 A     FALSE         2
2 A     TRUE          4
3 B     FALSE         1
4 B     TRUE          3

edited Nov 29 '22 at 15:11

answered Nov 29 '22 at 12:38

zephryl

14,633
3
11
30

MarBlo · Answer 2 · 2022-11-29T12:52:36.240

You can do this with lead and check if the following indicator is TRUE.

library(tidyverse)
df <- structure(list(group = c("A", "A", "A", "A", "B", "B", "B"), 
                     indicator = c(FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE
                     ), value = c(2, 1, 2, 4, 5, 1, 3)), class = "data.frame", row.names = c(NA, 
                                                                                             -7L))
df |> 
  group_by(group) |> 
  mutate(slicer = if_else(lead(indicator) ==F, 1, 0)) |> 
  mutate(slicer = if_else(is.na(slicer), 0 , slicer)) |> 
  filter(slicer == 0) |> 
  select(-slicer)
#> # A tibble: 4 × 3
#> # Groups:   group [2]
#>   group indicator value
#>   <chr> <lgl>     <dbl>
#> 1 A     FALSE         2
#> 2 A     TRUE          4
#> 3 B     FALSE         1
#> 4 B     TRUE          3

arg0naut91 · Answer 3 · 2022-11-29T13:52:57.440

Another approach:

library(dplyr)

df %>%
  group_by(group) %>%
  slice_max(cumsum(!indicator))

Note: While this approach covers the example shown and OP's clarification that T always comes last, it will not work in sequences such as T, F, F, T in which you'd like to keep both Ts and not just the one following F.

Output:

# A tibble: 4 x 3
# Groups:   group [2]
  group indicator value
  <chr> <lgl>     <dbl>
1 A     FALSE         2
2 A     TRUE          4
3 B     FALSE         1
4 B     TRUE          3

score 1 · Answer 4 · answered Nov 29 '22 at 14:36

Some alternatives one could come up with:

"Dumb" solution

should_be_kept <- logical(nrow(df))
for(row in 1:nrow(df)) {
  if(df[row,"Indicator"]) {
    should_be_kept[row] <- TRUE
  } else if(row == max(which(!df[, "Indicator"] & df$Group == df[row, "Group"]))) {
    should_be_kept[row] <- TRUE
  } else {
    should_be_kept[row] = FALSE
  }
}
df[should_be_kept, ]

Solution using a custom function to find the last FALSE indicators from each group

rows_to_keep <- logical(nrow(df)) #We create a TRUE/FALSE vector with one entry for each row of df
rows_to_keep[df$Indicator] <- TRUE #If Indicator is TRUE, we mark that row as "selectable"

get_last_false_in_group <- function(df, group) {
  return(max(which(df$Group == group & !df$Indicator))) #Gets the last time the condition inside of which() is met
}

#The following chunk does a group-by-group search of the last false indicator. There's probably some apply magic that simplifies this but I'm too dumb to come up with it.
groups <- levels(factor(df$Group))
for(current_group in groups) {
  rows_to_keep[get_last_false_in_group(df, current_group)] <- TRUE
}

#Now that our rows_to_keep vector is ready, we can filter the corresponding rows and get the intended result:
df[rows_to_keep,]

With the data.table package, it's possible to replace the calls to max(which(...)) with calls to just the last function

Remove non-last rows with certain condition per group

4 Answers4