Remove rows after a group by based on a column value

Question

I have a data set of different IDs, Date, Quantities. All IDs have the same exact start date, however some have starting quantities of 0 so basically the start date should be later.

I am trying to filter the rows such that each ID

df <- data.frame(ID = c("1", "1", "1", "1", "1", "1",
                        "2", "2", "2", "2", "2", "2",
                        "3", "3", "3", "3", "3", "3"),
                 Date = c(seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6)),
                 QTY = c(0, 0, 0, 40, 0, 60, 50, 55, 0, 70, 88, 64, 0, 43, 43, 0, 99, 99))

So basically in the df example above, I want ID number 1 to start from the fourth QTY value, and for ID number 3 to start from the second QTY value.

I tried using group by and to mutate a new column with an ifelse but I had some error.

My expected df should be a transformation from the first one to the one below, so just to remove the first rows with zeroes for each ID until QTY starts to be more than 0

df <- data.frame(ID = c("1", "1", "1",
                        "2", "2", "2", "2", "2", "2",
                        "3", "3", "3", "3", "3"),
                 Date = c(seq(as.Date("2000/4/1"), by = "month", length.out = 3),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/2/1"), by = "month", length.out = 5)),
                 QTY = c(40, 0, 60, 50, 55, 0, 70, 88, 64, 43, 43, 0, 99, 99))

What is your expected output? Do you just need to select all rows that are not 0 in `QTY`? Then likely there's already answer somewhere else on SO. — arg0naut91, Mar 31 '20 at 14:21
@arg0naut91 actually not just remove the 0 quantities, because a 0 may come up in the middle after the quantity of each given ID has started. apologies I should have explained. I will modify my question — principe, Mar 31 '20 at 14:27
@arg0naut91 i modified the question and the dateframe and the desired output for clarification — principe, Mar 31 '20 at 14:33
I see, to avoid misunderstandings you may also want to include as explanation or example whether you expect any `NA`s to be either at the beginning or anywhere in the data, or if you expect to have negative values — arg0naut91, Mar 31 '20 at 15:06

score 3 · Accepted Answer · answered Mar 31 '20 at 20:29

If the data is as in your example (i.e. no NA or negative values), just this base solution would suffice:

df[with(df, ave(QTY, ID, FUN = cumsum)) > 0, ]

Or subset(df, ave(QTY, ID, FUN = cumsum) > 0), but likely a tiny bit slower.

Output:

   ID       Date QTY
4   1 2000-04-01  40
5   1 2000-05-01   0
6   1 2000-06-01  60
7   2 2000-01-01  50
8   2 2000-02-01  55
9   2 2000-03-01   0
10  2 2000-04-01  70
11  2 2000-05-01  88
12  2 2000-06-01  64
14  3 2000-02-01  43
15  3 2000-03-01  43
16  3 2000-04-01   0
17  3 2000-05-01  99
18  3 2000-06-01  99

This is the same as in dplyr:

library(dplyr)

df %>%
  group_by(ID) %>%
  filter(cumsum(QTY) > 0)

score 2 · Answer 2 · answered Mar 31 '20 at 18:04

Try this. Basic idea is compute the cumsum of non-zero QTY for each ID over time. Then filter out obs with cumsum == 0:

library(dplyr)

df <- data.frame(ID = c("1", "1", "1", "1", "1", "1",
                        "2", "2", "2", "2", "2", "2",
                        "3", "3", "3", "3", "3", "3"),
                 Date = c(seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6)),
                 QTY = c(0, 0, 0, 40, 0, 60, 50, 55, 0, 70, 88, 64, 0, 43, 43, 0, 99, 99))

df %>% 
  arrange(ID, Date) %>% 
  group_by(ID) %>% 
  mutate(cum_non_zero = cumsum(QTY > 0)) %>% 
  filter(cum_non_zero > 0)
#> # A tibble: 14 x 4
#> # Groups:   ID [3]
#>    ID    Date         QTY cum_non_zero
#>    <fct> <date>     <dbl>        <int>
#>  1 1     2000-04-01    40            1
#>  2 1     2000-05-01     0            1
#>  3 1     2000-06-01    60            2
#>  4 2     2000-01-01    50            1
#>  5 2     2000-02-01    55            2
#>  6 2     2000-03-01     0            2
#>  7 2     2000-04-01    70            3
#>  8 2     2000-05-01    88            4
#>  9 2     2000-06-01    64            5
#> 10 3     2000-02-01    43            1
#> 11 3     2000-03-01    43            2
#> 12 3     2000-04-01     0            2
#> 13 3     2000-05-01    99            3
#> 14 3     2000-06-01    99            4

^{Created on 2020-03-31 by the reprex package (v0.3.0)}

Remove rows after a group by based on a column value

2 Answers2