Aggregate only rows based on values of another columns

Question

i have this dataset

  CASHPOINT_ID         DT     status   QT_REC
1   N053360330 2016-01-01 end_of_day      5
2   N053360330 2016-01-01 end_of_day      2
3   N053360330 2016-01-02 before          9
4   N053360330 2016-01-02 before         NA
5   N053360330 2016-01-03 end_of_day     16
6   N053360330 2016-01-03 end_of_day     NA

i want to aggregate only rows that don't have the column status marked as "before" and keep untouched the other. Resulting dataset should look like

 CASHPOINT_ID         DT     status       QT_REC
    1   N053360330 2016-01-01 end_of_day      7
    3   N053360330 2016-01-02 before          9
    4   N053360330 2016-01-02 before         NA
    5   N053360330 2016-01-03 end_of_day     16

Thanks.

Answer 1

Using data.table

Assuming your original data is called dt and has been setDT() then you could do:

df <- rbind(
  dt[status == "end_of_day", .(QT_REC = sum(QT_REC, na.rm = TRUE)), 
     by = .(CASHPOINT_ID, DT, status)],
  dt[status != "end_of_day"]
)[order(DT)]

print(df)
   CASHPOINT_ID         DT     status QT_REC
1:   N053360330 2016-01-01 end_of_day      7
2:   N053360330 2016-01-02     before      9
3:   N053360330 2016-01-02     before     NA
4:   N053360330 2016-01-03 end_of_day     16