How to combine rows of a dataframe after doing calculation in R

Question

How would I go about subtracting Read in line 1 from Read in line 5 and make that the value of Read in line 1. Then repeat so subtract Read in line 2 from line 6 and so on? Then adding up each group of 4 lines (the same time stamp) and making one line with that time stamp that is the sum of all four lines?

                  Time Meter     Read
1   2017-03-20 14:15:00     1 22582640
2   2017-03-20 14:15:00     2   571893
3   2017-03-20 14:15:00     3 45906886
4   2017-03-20 14:15:00     4  1890581
5   2017-03-20 14:20:00     1 22582640
6   2017-03-20 14:20:00     2   571893
7   2017-03-20 14:20:00     3 45906993
8   2017-03-20 14:20:00     4  1890581
9   2017-03-20 14:25:00     1 22582640
10  2017-03-20 14:25:00     2   571893
11  2017-03-20 14:25:00     3 45907206
12  2017-03-20 14:25:00     4  1890581
13  2017-03-20 14:30:00     1 22582640
14  2017-03-20 14:30:00     2   571893
15  2017-03-20 14:30:00     3 45907206
16  2017-03-20 14:30:00     4  1890581

dput of data is:

structure(list(Time = structure(c(1490037300, 1490037300, 1490037300, 
1490037300, 1490037600, 1490037600, 1490037600, 1490037600, 1490037900, 
1490037900, 1490037900, 1490037900, 1490038200, 1490038200, 1490038200, 
1490038200), class = c("POSIXct", "POSIXt"), tzone = ""), Meter = c(1, 
2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4), Read = c(22582640L, 
571893L, 45906886L, 1890581L, 22582640L, 571893L, 45906993L, 
1890581L, 22582640L, 571893L, 45907206L, 1890581L, 22582640L, 
571893L, 45907206L, 1890581L)), .Names = c("Time", "Meter", "Read"
), row.names = c(NA, 16L), class = "data.frame")

The result that I would want is this:

1   2017-03-20 14:15:00     1 107
2   2017-03-20 14:20:00     1 213

and so on.

I do not need to reference the Meter column if that makes it less complex. I could remove that column and just do some sort of offset as the formatting will always be the 4 entries together. I got the data formatted this far but I don't know where to begin to do what I am looking for as it means wiping out three lines and I'm not sure how that affects the offset for later calculations. I thought about adding a new column with the calculation but still can't figure out how to roll the sum up into one line.

I can not see how the result 107 is constructed (the values in the rows 1, 5, 9 and 13 are all the same). — jogo, Mar 21 '17 at 07:51
Right, it is the difference between 5 and 1, 6 and 2, 7 and 3, 8 and 4 added together to get one number for the first time stamp. The only difference in the first group of 4 and the second group of 4 is meter 3. that is where the 107 comes from. — Michael Ciesielczyk, Mar 21 '17 at 07:57
What do you want for the last time stamp? Lines 13-16 don't have anything to be subtracted from, so is the answer `NA`, omit, or something else? — Gregor Thomas, Mar 21 '17 at 08:17
`m <- matrix(d$Read, 4); diff(colSums(m))` or `diff(tapply(d$Read, d$Time, sum))` The last is better giving a named vector. — jogo, Mar 21 '17 at 08:17
http://stackoverflow.com/questions/3505701/r-grouping-functions-sapply-vs-lapply-vs-apply-vs-tapply-vs-by-vs-aggrega — jogo, Mar 21 '17 at 08:23

How to combine rows of a dataframe after doing calculation in R

0 Answers0