Calculate mean change in values one day later

I have a dataframe with values and their timestamps. The data looks like this:

library(lubridate)
df <- data.frame(date1= dmy_hms(c("01.08.2019 12:11:32", "01.05.2019 10:01:17")),
                 value1= c(60, 70),
                 date2= dmy_hms(c("01.08.2019 12:41:38", "01.05.2019 11:51:17")),
                 value2= c(80, 60),
                 date3= dmy_hms(c("02.08.2019 12:01:09", "02.05.2019 10:11:34")),
                 value3= c(10, 40),
                 date4= dmy_hms(c("02.08.2019 12:41:38", "02.05.2019 11:51:17")),
                 value4= c(30, 80))
df
                date1 value1               date2 value2               date3 value3               date4 value4
1 2019-08-01 12:11:32     60 2019-08-01 12:41:38     80 2019-08-02 12:01:09     10 2019-08-02 12:41:38     30
2 2019-05-01 10:01:17     70 2019-05-01 11:51:17     60 2019-05-02 10:11:34     40 2019-05-02 11:51:17     80

I need to find out how the values changed after one day, caring only about the hour.

First row from data above: value1 (60) and value2 (80) are both recorded at 12:XX:XX o'clock at the same day, so the mean of 12:XX:XX o'clock for this day is 70. The mean of 12:XX:XX o'clock of the next day is 20. This means a change of -50 for the first row.

Second row: Here value1 is 70 at 10:XX:XX o'clock and one day later at 10:XX:XX o'clock the value is 40, so the change is -30. The change for 11:XX:XX o'clock from one day to the next is +20. So the mean change is (-30 + 20)/2 = -5.

Thus, my expected output is

mean_change <- matrix(c(-50, -5), ncol= 1)
mean_change
     [,1]
[1,]  -50
[2,]   -5

Solution

I would change the data structure into long. At least for me, this makes the task more intuitive. Is this what you are looking for?

library(lubridate)
library(dplyr)

df <- data.frame(date1= dmy_hms(c("01.08.2019 12:11:32", "01.05.2019 10:01:17")),
                 value1= c(60, 70),
                 date2= dmy_hms(c("01.08.2019 12:41:38", "01.05.2019 11:51:17")),
                 value2= c(80, 60),
                 date3= dmy_hms(c("02.08.2019 12:01:09", "02.05.2019 10:11:34")),
                 value3= c(10, 40),
                 date4= dmy_hms(c("02.08.2019 12:41:38", "02.05.2019 11:51:17")),
                 value4= c(30, 80))
df

df.long <- as.data.frame(matrix(t(df), ncol=2, byrow=T))
df.long$Date <- as.Date(df.long$V1)
df.long$Time <-  format(as.POSIXct(df.long$V1) ,format = "%H") 
df.long <- df.long[c(2:4)]
df.long$V2 <- as.numeric(as.character(df.long$V2))
daychange <- df.long %>% group_by(Date, Time) %>% summarise(d.h.mean = mean(V2))
daychange$Date <- strftime(daychange$Date,format = "%Y-%m")

daychange %>% group_by(Date, Time)  %>% mutate(change = d.h.mean-lag(d.h.mean))

# A tibble: 6 x 4
# Groups:   Date, Time [3]
Date    Time  d.h.mean change
<chr>   <chr>    <dbl>  <dbl>
  1 2019-05 10          70     NA
2 2019-05 11          60     NA
3 2019-05 10          40    -30
4 2019-05 11          80     20
5 2019-08 12          70     NA
6 2019-08 12          20    -50