German Electricity Data :electric_plug:

• Data Source: SMARD Web Portal
• Task: forecasting (using the past to predict the future)
• Language: R

First steps:

• load the data (with appropriate column types) :heavy_check_mark:
• split the data into train/val/test sets :heavy_check_mark:
• plot the time series (at different temporal resolution) :heavy_check_mark:
• calculate the mean production per month :heavy_check_mark:
• is there a seasonal pattern (what kinds of seasonalities)
• make a prediction for the validation set
• evaluate the quality of the prediction

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.3     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.3     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.0
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

theme_set(theme_light())

set.seed(42)
"Oliver Stefan Markus" %>%  str_split(" ") %>% unlist %>% sample %>% str_c(collapse=" → ")

'Oliver → Markus → Stefan'

1. Data loading

• load the hourly data from the file Realisierte_Erzeugung_201501010000_202305301300_Stunde.csv as a table into R :heavy_check_mark:
• make sure, that data is interpreted and typed correctly :heavy_check_mark:
• clean header names :heavy_check_mark:
• create column Time containing the combined info from Datum and Anfang

data <- read_csv2(
    "../data_dojo_22/Realisierte_Erzeugung_201501010000_202305301300_Stunde.csv",
    col_types=cols(`Datum` = col_date("%d.%m.%Y")),
    na="-"
)

ℹ Using "','" as decimal and "'.'" as grouping mark. Use `read_delim()` for more control.

colnames(data) <- colnames(data) %>% str_remove(" \\[.*") %>% str_replace(" ","_")

2^nd session

• Create Time column
• Split into training/validation/test set
• Plot the training set with hourly resolution
• Aggregate by day/month
• Plot aggregated data

Bonus task

• did you observe something peculiar with dates and times?
• look at the hourly data of 2017-10-29 for an example
• what happened there?
• are there more days with a similar "problem"?
• is this a problem?
• how can we solve this?

data <- data %>%
    mutate(Time=Datum+hours(hour(Anfang)))

data

A tibble: 73716 × 16

Datum	Anfang	Ende	Biomasse	Wasserkraft	Wind_Offshore	Wind_Onshore	Photovoltaik	Sonstige_Erneuerbare	Kernenergie	Braunkohle	Steinkohle	Erdgas	Pumpspeicher	Sonstige_Konventionelle	Time
<date>	<time>	<time>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dttm>
2015-01-01	00:00:00	01:00:00	4024.25	1158.25	516.50	8128.00	0.00	133.00	10710.50	15687.25	3219.75	1226.25	1525.75	4909.25	2015-01-01 00:00:00
2015-01-01	01:00:00	02:00:00	3982.75	1188.00	516.25	8297.50	0.00	122.50	11086.25	15321.75	2351.25	870.75	1079.25	4932.75	2015-01-01 01:00:00
2015-01-01	02:00:00	03:00:00	4019.50	1139.25	514.00	8540.00	0.00	93.00	11026.25	14817.50	2227.00	809.50	787.00	5041.75	2015-01-01 02:00:00
2015-01-01	03:00:00	04:00:00	4040.75	1122.50	517.75	8552.00	0.00	86.50	11027.75	14075.00	2339.75	821.00	287.75	5084.00	2015-01-01 03:00:00
2015-01-01	04:00:00	05:00:00	4037.75	1112.00	519.75	8643.50	0.00	86.50	10962.25	14115.00	2461.50	831.25	346.75	5070.75	2015-01-01 04:00:00
2015-01-01	05:00:00	06:00:00	4028.25	1107.75	520.00	8711.75	0.00	86.75	10696.00	13474.25	2217.75	851.00	765.50	5096.75	2015-01-01 05:00:00
2015-01-01	06:00:00	07:00:00	4013.25	1111.75	521.50	9167.25	0.00	87.00	10299.50	12403.75	2373.25	868.25	414.50	5153.00	2015-01-01 06:00:00
2015-01-01	07:00:00	08:00:00	4012.75	1113.75	520.25	9811.00	0.00	87.00	10035.25	12062.50	2491.00	876.00	582.50	5161.00	2015-01-01 07:00:00
2015-01-01	08:00:00	09:00:00	3999.75	1107.50	525.25	9683.00	53.00	87.00	10245.75	12405.00	2530.25	888.25	750.50	5393.50	2015-01-01 08:00:00
2015-01-01	09:00:00	10:00:00	4016.25	1121.00	527.00	9501.75	773.25	85.75	10060.25	12798.75	2386.25	891.50	387.00	5884.00	2015-01-01 09:00:00
2015-01-01	10:00:00	11:00:00	4007.75	1122.00	525.00	10025.00	2116.75	82.00	10082.25	12728.50	2557.00	888.25	176.25	6064.00	2015-01-01 10:00:00
2015-01-01	11:00:00	12:00:00	4011.75	1146.25	527.25	10862.50	3364.25	82.00	9949.00	12451.00	2657.75	876.50	884.50	5914.75	2015-01-01 11:00:00
2015-01-01	12:00:00	13:00:00	4014.00	1139.75	527.75	11575.25	4198.25	82.00	10119.00	12464.50	2937.75	874.25	705.00	5778.25	2015-01-01 12:00:00
2015-01-01	13:00:00	14:00:00	4005.50	1128.00	527.75	11977.25	3500.50	84.50	10453.50	12417.75	2810.25	883.50	967.25	5808.75	2015-01-01 13:00:00
2015-01-01	14:00:00	15:00:00	4024.25	1154.75	524.75	11396.00	2278.75	87.00	10828.25	12621.50	2664.00	890.25	1176.25	5842.50	2015-01-01 14:00:00
2015-01-01	15:00:00	16:00:00	4028.75	1160.00	524.50	11326.50	745.50	86.00	11069.75	13387.25	2485.25	969.25	1663.00	6075.25	2015-01-01 15:00:00
2015-01-01	16:00:00	17:00:00	4030.75	1145.75	524.50	13058.00	50.00	82.00	11006.50	14679.00	2668.75	1085.25	1227.00	5946.00	2015-01-01 16:00:00
2015-01-01	17:00:00	18:00:00	4029.25	1171.50	524.50	14911.50	0.00	82.25	11057.50	14964.25	2808.75	1193.75	1597.75	5464.50	2015-01-01 17:00:00
2015-01-01	18:00:00	19:00:00	4027.50	1168.50	524.00	16344.00	0.00	86.00	10930.25	14286.00	2690.25	1135.50	1320.00	5604.00	2015-01-01 18:00:00
2015-01-01	19:00:00	20:00:00	4002.75	1157.75	523.50	17484.50	0.00	87.75	10861.75	13498.00	2490.75	1123.75	1535.50	5866.25	2015-01-01 19:00:00
2015-01-01	20:00:00	21:00:00	3991.50	1168.25	522.25	18659.00	0.00	88.00	10343.00	12366.00	2440.00	1209.25	1162.75	5861.25	2015-01-01 20:00:00
2015-01-01	21:00:00	22:00:00	3990.25	1186.50	521.00	19831.50	0.00	88.00	9840.00	11284.75	2409.00	1205.75	838.75	6090.75	2015-01-01 21:00:00
2015-01-01	22:00:00	23:00:00	4000.00	1170.25	519.50	20716.00	0.00	88.00	9749.00	10428.50	2298.75	1324.25	2066.75	6084.75	2015-01-01 22:00:00
2015-01-01	23:00:00	00:00:00	4002.00	1162.00	494.00	21589.00	0.00	88.00	9045.75	9861.25	2209.25	1212.75	1436.25	5989.25	2015-01-01 23:00:00
2015-01-02	00:00:00	01:00:00	3999.75	1122.00	305.00	22166.75	0.00	88.00	8164.75	9035.50	2225.50	1172.00	1301.00	5978.00	2015-01-02 00:00:00
2015-01-02	01:00:00	02:00:00	4003.25	1129.25	276.50	23158.00	0.00	87.75	7147.75	9013.50	2188.75	1127.75	893.50	6370.75	2015-01-02 01:00:00
2015-01-02	02:00:00	03:00:00	3997.25	1124.25	282.00	23966.00	0.00	87.75	6675.75	9018.50	1974.50	1123.25	608.50	5873.75	2015-01-02 02:00:00
2015-01-02	03:00:00	04:00:00	4000.00	1133.25	324.75	24708.75	0.00	87.75	6466.75	8765.00	1907.00	1120.25	203.50	5858.00	2015-01-02 03:00:00
2015-01-02	04:00:00	05:00:00	3992.00	1122.25	335.00	25386.00	0.00	88.00	6304.00	8398.25	2098.75	1129.75	712.50	6012.75	2015-01-02 04:00:00
2015-01-02	05:00:00	06:00:00	3981.50	1100.75	388.25	25783.75	0.00	88.00	6760.00	8593.00	2406.00	1157.50	729.25	6221.25	2015-01-02 05:00:00
⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮	⋮
2023-05-29	07:00:00	08:00:00	4489.00	2074.50	2402.00	6005.75	9430.50	117.00	0	3455.75	1583.25	2201.25	1292.75	1444.75	2023-05-29 07:00:00
2023-05-29	08:00:00	09:00:00	4523.25	2068.75	2560.00	4323.75	17827.00	117.00	0	3462.50	1812.00	2127.00	935.00	1448.00	2023-05-29 08:00:00
2023-05-29	09:00:00	10:00:00	4571.75	2071.50	2365.50	3468.75	25662.25	117.00	0	3450.25	1726.00	2052.25	476.00	1449.75	2023-05-29 09:00:00
2023-05-29	10:00:00	11:00:00	4579.00	2072.00	1576.25	3647.00	31928.50	117.00	0	3450.75	1691.75	2025.00	215.75	1448.00	2023-05-29 10:00:00
2023-05-29	11:00:00	12:00:00	4594.75	2032.25	1447.50	3519.50	35927.00	117.00	0	3424.25	1684.25	2041.00	225.00	1445.75	2023-05-29 11:00:00
2023-05-29	12:00:00	13:00:00	4584.25	1643.00	1128.25	3267.25	37681.50	117.00	0	3176.25	1464.75	2029.00	152.00	1433.75	2023-05-29 12:00:00
2023-05-29	13:00:00	14:00:00	4545.50	1598.75	855.00	3471.75	37628.50	117.00	0	3178.00	1290.50	2043.75	161.25	1439.25	2023-05-29 13:00:00
2023-05-29	14:00:00	15:00:00	4509.75	1574.50	697.00	3401.00	35503.25	117.00	0	3166.25	1273.00	2062.25	19.75	1419.00	2023-05-29 14:00:00
2023-05-29	15:00:00	16:00:00	4482.50	1518.25	1243.25	4111.75	31892.50	117.00	0	3169.50	1266.50	2077.50	2.50	1386.75	2023-05-29 15:00:00
2023-05-29	16:00:00	17:00:00	4458.00	1648.50	2406.50	7099.50	27421.75	117.25	0	3174.50	1282.50	2055.00	11.25	1408.25	2023-05-29 16:00:00
2023-05-29	17:00:00	18:00:00	4449.50	2067.25	2649.50	9514.00	20541.50	125.75	0	3390.75	1326.00	2118.50	109.75	1421.50	2023-05-29 17:00:00
2023-05-29	18:00:00	19:00:00	4465.00	2099.00	4183.50	12215.50	12323.25	126.00	0	3441.50	1375.00	2348.75	360.00	1405.75	2023-05-29 18:00:00
2023-05-29	19:00:00	20:00:00	4508.25	2099.50	4259.75	13248.00	5363.50	134.25	0	3643.25	1350.25	3261.25	815.00	1384.25	2023-05-29 19:00:00
2023-05-29	20:00:00	21:00:00	4562.00	2144.00	4221.00	13197.00	1635.00	133.25	0	3861.00	1323.00	3647.75	1620.50	1174.00	2023-05-29 20:00:00
2023-05-29	21:00:00	22:00:00	4600.50	2119.50	4300.25	14307.75	79.00	132.75	0	4030.00	1422.50	3970.75	1577.00	1170.50	2023-05-29 21:00:00
2023-05-29	22:00:00	23:00:00	4634.25	2117.50	4509.25	14627.50	0.00	123.00	0	3946.75	1466.00	3681.75	1807.75	1154.50	2023-05-29 22:00:00
2023-05-29	23:00:00	00:00:00	4651.25	2066.50	4647.25	14237.50	0.00	122.00	0	3713.00	1368.25	3729.75	900.25	1154.25	2023-05-29 23:00:00
2023-05-30	00:00:00	01:00:00	4629.75	1968.00	4278.75	13122.75	0.00	120.00	0	3735.25	1313.75	3710.00	625.25	1147.50	2023-05-30 00:00:00
2023-05-30	01:00:00	02:00:00	4581.50	2033.75	3891.75	11488.50	0.00	119.50	0	3523.50	1288.50	3984.75	769.25	1146.00	2023-05-30 01:00:00
2023-05-30	02:00:00	03:00:00	4551.00	1963.50	3725.50	10141.00	0.00	119.00	0	3484.25	1263.75	3592.00	604.25	1141.75	2023-05-30 02:00:00
2023-05-30	03:00:00	04:00:00	4503.25	2090.50	3635.25	9170.25	0.00	119.00	0	3490.00	1272.50	4030.50	578.75	1132.50	2023-05-30 03:00:00
2023-05-30	04:00:00	05:00:00	4454.25	2105.25	3635.75	8735.75	2.25	120.00	0	3678.25	1275.00	4895.50	433.25	1136.00	2023-05-30 04:00:00
2023-05-30	05:00:00	06:00:00	4406.75	2134.75	2940.00	8419.75	407.75	120.25	0	4087.50	1289.25	5723.75	404.25	1142.25	2023-05-30 05:00:00
2023-05-30	06:00:00	07:00:00	4413.25	2227.50	2272.50	7531.00	2888.75	128.25	0	5010.75	1362.00	6525.25	2803.75	1144.50	2023-05-30 06:00:00
2023-05-30	07:00:00	08:00:00	4468.00	2276.50	1916.25	5507.25	8343.25	137.00	0	5275.50	1173.75	6905.75	5712.75	1156.75	2023-05-30 07:00:00
2023-05-30	08:00:00	09:00:00	4503.50	2152.50	1643.25	3647.75	15893.25	138.00	0	5181.75	1196.25	6946.50	5079.50	1132.00	2023-05-30 08:00:00
2023-05-30	09:00:00	10:00:00	4544.50	2156.25	1285.00	2900.50	23612.50	135.00	0	4707.50	1114.75	6384.50	2122.75	1115.00	2023-05-30 09:00:00
2023-05-30	10:00:00	11:00:00	4568.25	2085.25	1241.25	2729.50	30341.75	121.25	0	3978.50	1095.50	4802.50	1279.00	1096.25	2023-05-30 10:00:00
2023-05-30	11:00:00	12:00:00	4618.75	2068.25	1053.25	3115.00	35196.25	119.00	0	3759.75	1151.50	3762.25	774.00	1096.50	2023-05-30 11:00:00
2023-05-30	12:00:00	13:00:00	4612.25	1967.25	781.25	3496.25	37030.00	119.00	0	3765.00	1296.75	3240.00	196.75	1099.75	2023-05-30 12:00:00

data %>% count(year(Time))

A tibble: 9 × 2

year(Time)	n
<dbl>	<int>
2015	8760
2016	8784
2017	8760
2018	8760
2019	8760
2020	8784
2021	8760
2022	8760
2023	3588

train <- data %>% filter(year(Time)<2021)

val <- data %>% filter(year(Time)==2021)

test <- data %>% filter(year(Time)>2021)

dim(train)

1. 52608
2. 16

options(repr.plot.res = 200)

tidydf <- train %>%
    pivot_longer(names_to='Type', values_to='MWh', Biomasse:Sonstige_Konventionelle) %>%
    select(-c(Datum, Anfang, Ende))

ggplot(tidydf)+
    geom_line(aes(Time, MWh, color = Type)) + facet_wrap(Type~.)

tidydf %>% 
            group_by(date = floor_date(Time, unit = "day"),Type) %>% 
            summarize(MWh = mean(MWh)) %>%
            ggplot() + geom_line(aes(date,MWh,color = Type)) + facet_wrap(~Type)

`summarise()` has grouped output by 'date'. You can override using the
`.groups` argument.

tidydf %>% 
            group_by(date = floor_date(Time, unit = "month"),Type) %>%
            summarize(MWh = mean(MWh)) %>%
            ggplot() + geom_line(aes(date,MWh,color = Type)) + facet_wrap(~Type)
            #identity

`summarise()` has grouped output by 'date'. You can override using the
`.groups` argument.

Todos 3^rd Session

• Select one of the types (e.g. Photovoltaik, Erdgas or Wind_Onshore)
• What kinds of seasonality are detectable?
• Is there a general trend on top of the seasonal pattern(s)?
• Can you use this information to manually craft a prediction model?

Bonus:

• How accurately does your manually created model predict the values in the validation set?

tidydf %>%
    filter(Type == "Photovoltaik") %>%
    ggplot(aes(x=Time, y=MWh)) +
        geom_line()

Inspect yearly pattern

tidydf %>%
    filter(Type == "Photovoltaik") %>%
    mutate(year = year(Time), date_only = format(Time, format="%m-%d")) %>%
    ggplot(aes(x=date_only, y=MWh, color=year)) +
        geom_line(alpha=.4)

Inspect daily pattern

tidydf %>%
    filter(Type == "Photovoltaik") %>%
    mutate(year = year(Time), month = as.factor(month(Time)), day = day(Time), hour = hour(Time)) %>%
    filter(year == 2015, day == 1) %>%
    ggplot(aes(x=hour, y=MWh, color=month)) +
        geom_line()

tidydf %>%
    filter(Type == "Photovoltaik") %>%
    mutate(year = year(Time), month = as.factor(month(Time)), day = day(Time), hour = hour(Time)) %>%
    mutate(summertime = month(Time) > 3 & month(Time) < 11) %>%
    filter(year == 2015, day == 1) %>%
    ggplot(aes(x=hour, y=MWh, color=month)) +
        geom_line(aes(lty = summertime))

timeseries <-
tidydf %>%
    filter(Type == "Photovoltaik") %>%
    group_by(year(Time), month(Time)) %>%
    summarize(mean_year = mean(MWh)) %>%
    select(mean_year)
#timeseries
    timeseries <- ts(timeseries$mean_year, start = 2015, frequency = 12)
    timeseries_df <- decompose(timeseries)
#plot(timeseries)

plot(timeseries_df)

`summarise()` has grouped output by 'year(Time)'. You can override using the
`.groups` argument.
Adding missing grouping variables: `year(Time)`

timeseries <-
tidydf %>%
    filter(Type == "Photovoltaik") %>%
    group_by(year(Time), month(Time), day(Time)) %>%
    summarize(mean_day = mean(MWh)) %>%
    select(mean_day)
#timeseries
    timeseries <- ts(timeseries$mean_day, start = 2015, frequency = 365)
    timeseries_df <- decompose(timeseries)
#plot(timeseries)

plot(timeseries_df)

`summarise()` has grouped output by 'year(Time)', 'month(Time)'. You can
override using the `.groups` argument.
Adding missing grouping variables: `year(Time)`, `month(Time)`