How to add values rowwise in a grouped column

Question

I have some sensor data with 100 data entries per second. In the last column are milliseconds, which for now are all 10. How can I rowwise sum the milliseconds together, grouped by time and date.

testdata <- structure(list(local_date = c("26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017", "26-06-2017",  "26-06-2017", "26-06-2017"), 
                           local_time = c("13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23", "13:58:23",  "13:58:23", "13:58:23", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24",  "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24", "13:58:24" ), 
                           ms = c(10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10,  10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10)), 
                      .Names = c("local_date",  "local_time", "ms"), row.names = c(NA, -200L), class = c("data.table", "data.frame"))

The first 100 rows all share the same time (13:58:23) and date (26-06-2017), but they all have 10 milliseconds. The result should only have one an entry with 10 milliseconds per seconds and the following milliseconds are added to the previous ones.

This snippet would create the result with a sequence:

testdata$ms = rep(seq(from = 10, to = 1000, by = 10), 2)

But since the original data is not that clean, I have to group the data by date and time and then add the milliseconds together in a rowwise manner.

I would prefer a data.table solution, but dplyr would also work fine.

Answer 1

And to add a data.table version:

testdata[, ms := cumsum(ms), by = .(local_time, local_date)]

Answer 2

It sounds like you need a grouped cumsum:

library(dplyr) 

testdata$ms2 = rep(seq(from = 10, to = 1000, by = 10), 2)

testdata %>%
    group_by(local_date, local_time) %>%
    mutate(cumsum_ms = cumsum(ms))

   local_date local_time    ms   ms2 cumsum_ms
   <chr>      <chr>      <dbl> <dbl>     <dbl>
 1 26-06-2017 13:58:23      10    10        10
 2 26-06-2017 13:58:23      10    20        20
 3 26-06-2017 13:58:23      10    30        30
 4 26-06-2017 13:58:23      10    40        40
 5 26-06-2017 13:58:23      10    50        50