fathomson fathomson - 10 months ago 59
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:


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


Answer Source

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


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