I have a dataset where multiple machines (A, B, C) are consuming power at different rates over specific time periods. The data includes start and end times of each machine's power usage and the power consumption rate. I want to compute the total power consumption at each time interval where there is a change, and track the start and end times of those periods.
How can I achieve this in R, while ensuring I correctly handle start and end times?
Example data:
data <- data.frame(
MachineID = c(1, 1, 2, 2),
PowerA_Start = c("2022-01-01 00:00:00", "2022-01-01 02:00:00", "2022-01-02 00:00:00", "2022-01-02 02:00:00"),
PowerA_End = c("2022-01-01 01:00:00", "2022-01-01 03:00:00", "2022-01-02 01:00:00", "2022-01-02 03:00:00"),
PowerA_Consumption = c(5, 10, 7, 12),
PowerB_Start = c("2022-01-01 00:30:00", "2022-01-01 02:30:00", "2022-01-02 00:30:00", "2022-01-02 02:30:00"),
PowerB_End = c("2022-01-01 01:30:00", "2022-01-01 03:30:00", "2022-01-02 01:30:00", "2022-01-02 03:30:00"),
PowerB_Consumption = c(3, 6, 5, 8),
PowerC_Start = c("2022-01-01 01:00:00", "2022-01-01 03:00:00", "2022-01-02 01:00:00", "2022-01-02 03:00:00"),
PowerC_End = c("2022-01-01 02:00:00", "2022-01-01 04:00:00", "2022-01-02 02:00:00", "2022-01-02 04:00:00"),
PowerC_Consumption = c(4, 8, 6, 10)
)
MachineID Start_Time End_Time Total_Consumption
<dbl> <dttm> <dttm> <dbl>
1 1 2022-01-01 00:00:00 2022-01-01 00:30:00 5
2 1 2022-01-01 00:30:00 2022-01-01 01:00:00 8
.
.
There are shorter ways to do this, but I think it's clearest to follow if we reshape longer twice, first to put the various "PowerA/PowerB/etc." observations into their own rows, and then to separate Starts and Ends. That's helpful since Start represents an increase in consumption, while End is a reduction.
Then we can track cumulative changes in consumption per machine, keep the last observation per machine-time, and just keep the columns we want in the end.
library(tidyverse)
data |>
# pivot PowerA/PowerB/etc. into rows, then into Start/End
pivot_longer(-MachineID,
names_to = c("phase", ".value"),
names_pattern = "(.*)_(.*)") |>
pivot_longer(Start:End, values_to = "dt") |>
# arrange by time, track cumulative changes, and keep last row per machine/time
arrange(MachineID, dt) |>
mutate(Total_Consumption = cumsum(Consumption * ifelse(name == "End", -1, 1)),
.by = MachineID) |>
slice_max(name, by = c(MachineID, dt)) |>
# final presentation
mutate(MachineID, Start_time = dt, End_time = lead(dt), Total_Consumption,
.keep = "none", .by = MachineID) |>
filter(!is.na(End_time))
Result
# A tibble: 16 × 4
MachineID Total_Consumption Start_time End_time
<dbl> <dbl> <chr> <chr>
1 1 5 2022-01-01 00:00:00 2022-01-01 00:30:00
2 1 8 2022-01-01 00:30:00 2022-01-01 01:00:00
3 1 7 2022-01-01 01:00:00 2022-01-01 01:30:00
4 1 4 2022-01-01 01:30:00 2022-01-01 02:00:00
5 1 10 2022-01-01 02:00:00 2022-01-01 02:30:00
6 1 16 2022-01-01 02:30:00 2022-01-01 03:00:00
7 1 14 2022-01-01 03:00:00 2022-01-01 03:30:00
8 1 8 2022-01-01 03:30:00 2022-01-01 04:00:00
9 2 7 2022-01-02 00:00:00 2022-01-02 00:30:00
10 2 12 2022-01-02 00:30:00 2022-01-02 01:00:00
11 2 11 2022-01-02 01:00:00 2022-01-02 01:30:00
12 2 6 2022-01-02 01:30:00 2022-01-02 02:00:00
13 2 12 2022-01-02 02:00:00 2022-01-02 02:30:00
14 2 20 2022-01-02 02:30:00 2022-01-02 03:00:00
15 2 18 2022-01-02 03:00:00 2022-01-02 03:30:00
16 2 10 2022-01-02 03:30:00 2022-01-02 04:00:00