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

Answer 2

This is a solution using dplyr.

    library(dplyr)
    df %>%  
          group_by(floor_date(DT, "day"),status) %>% 
          summarise(QT_REC = sum(QT_REC, na.rm = T))

Answer 3

Another plyr based solution:

ddply(.data = df,.variables = c('CASHPOINT_ID','DT','status'),
function(t){
    if(t$status[1]!='before'){
        unique(mutate(t,QT_REC=sum(QT_REC,na.rm=TRUE)))
    }else{
        t
    }
})

#  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