fathomson - 1 year ago 100
R Question

# Calculate delta's between different rows in same table

I've got a table which contains a lot of measurements from different meters. Each measurement is stored in a new row and has the actual meter value. I need to have the difference between each successive measurement per meter.

Simplified imput:

`````` [2016-11-03,MeterA,45]
[2016-11-03,MeterB,45]
[2016-11-04,MeterA,47]
[2016-11-04,MeterB,54]
``````

Currently I am doing this with several for loops but this takes long and there probably is a more efficient way. Code currently

``````data\$diff <- 0;
for (i in 1:nrow(submeter)){
if(i > 1){
prow = submeter[i-1,]
row = submeter[i,]
data[which(data\$Address ==  address & data\$Meter == meter &    data\$UCPTlogTime == row\$UCPTlogTime),]\$diff <- row\$UCPTvalue - prow\$UCPTvalue
}
}
}
}
``````

Desired output

`````` [2016-11-03,MeterA,0]
[2016-11-03,MeterB,0]
[2016-11-04,MeterA,2]
[2016-11-04,MeterB,9]
``````

This is a breeze with dplyr using the `lag` function. Assuming the columns in your dataframe are named `UCPTlogTime`, `Address`, `Meter`, and `UCPTvalue`:
``````library(dplyr)