I have the below time series, showing 15minutes stamps of electricity Load for the whole year of 2017:
-Datum & Zeit` kWh
Sun Jan-01-2017 01:45 374.420
Sun Jan-01-2017 02:00 355.040
Sun Jan-01-2017 02:15 359.995
Sun Jan-01-2017 02:30 375.715
Sun Jan-01-2017 02:45 371.520
Sun Jan-01-2017 03:00 355.100
Sun Jan-01-2017 03:15 411.780
Sun Jan-01-2017 03:30 417.330
Sun Jan-01-2017 03:45 401.555
Sun Jan-01-2017 04:00 362.180
Sun Jan-01-2017 04:15 361.605
Sun Jan-01-2017 04:30 366.155
Sun Jan-01-2017 04:45 363.785
....
...
Sun Dec-31-2017 23:45 363.785
I would like now to convert it to a matrix, which only selects the time stamps for a specific workday (here sunday) and converts it into columns so I can compare the load for a specific day over the year, this would end up in 52 columns.
Sun Jan-01-2017 Sun Jan-08-2017 .... Sun Dec-31-2017 23:45
01:45 374.420 ... ....
02:00 355.040 ... ....
02:15 359.995 ... ....
02:30 375.715 ... ....
02:45 371.520 ... ....
03:00 355.100 ... ....
03:15 411.780 ... ....
03:30 417.330 ... ....
03:45 401.555 ... ....
04:00 362.180 ... ....
04:15 361.605 ... ....
04:30 366.155 ... ....
04:45 363.785 ... ....
05:00 335.880 ... ....
How could I do this?
How about a tidyverse solution like this:
library(tidyverse)
long_data <- data.frame(Datum_and_Zeit = c("Sun Jan-01-2017 01:45", "Sun Jan-01-2017 02:00", "Sun Jan-01-2017 02:15", "Mon Jan-02-2017 01:45", "Mon Jan-02-2017 02:00", "Mon Jan-02-2017 02:15"), kWh = c(374.420, 355.040, 359.995, 375.715, 371.520, 355.100), stringsAsFactors = FALSE)
I'm using a small subset of data that looks like yours to illustrate.
wide_data <- long_data %>%
separate(Datum_and_Zeit, into = c("Day", "Date", "Time"), sep = " ") %>%
filter(Day == "Sun") %>%
spread(Date, kWh) %>%
select(-Day)