-2

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?

Solar
  • 1
  • 2
  • 3
    You should give it a try and then you'll have a more specific question to work with. –  Oct 26 '18 at 19:07

3 Answers3

0

This isn't a reproducible example but what I would do before formatting this data set as a time series object, I would create a column using lubridate's wday() function and then subset with a filter() for the days you would like to exclude. Oh also, You need to make sure you have the date time variable in a POSIXct as well.

df <- mutate(df, wday= wday(TimeVar))
df.bizdays <- filter(df, wdat!= 1 & wday!=7)

Then I would simply recreate the Time series object from that subset. This will also make your seasonalities a little more complex so I would also consider an msts() object instead of a ts() especially if you want to do analysis at the sub hour mark.

Hope that helps!

CrayCrayTown
  • 61
  • 7
0

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)
david
  • 16
  • 3
0

I had to take a bit different approach.

Somehow when I used @david approach. There was "Error: var must evaluate to a single number or a column name, not a list"

Further it gave me the following output (2x53)

 Sun Jan-01-2017 01:45", "Sun Jan-01-2017 02:00", "Sun Jan-01-2017 02:15", ...
 SUN    74.420,             355.040,                359.995,               ....

I created another dataframe, where I split up weekday, time, date and kwh

 Weekday   Time     Date         kWh
 Sun       01:45    Jan-01-2017  74.420
 Sun       02:45    Jan-01-2017  355.040
 ....
 ..


dailys<- data.frame(Time,Weekday,Date,Load)
dailys

wide_data <- dailys %>% 
   filter(Weekday == "sun") %>% 
   spread(Time, kWh) %>% 
   select(-Date)

where I get the following output, which comes pretty close. 

Weekday   Jan-01-2017   Jan-08-2017  Jan-015-2017 
Sun         4.420,        455.040,     789.995,
Sun          ...          ...         ...
Sun          ...          ...          ..
Sun
..
..

Now I exchanged the Weekday_Column against timestamps column (0:00; 00:15, 00:30,..., 23:45), which is probably not the smoothest way of doing it?

Solar
  • 1
  • 2