fathomson fathomson - 1 month ago 4
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(address in unique(data$Address)){
subaddr <- subset(data, data$Address== address)
for(meter in unique(subaddr$Meter)){
submeter <- subset(subaddr, subaddr$Meter == meter)
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]

Answer

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)

data <- data %>% group_by(Address, Meter) %>% 
  mutate(delta = order_by(UCPTlogTime, UCPTvalue - lag(UCPTvalue))) %>%
  mutate(delta = ifelse(is.na(delta), 0, delta))