Rearranging/Restructuring Data Table

General
1

Good day everyone!

I am playing around with the Generator Output-Capability Month Report data from the Independent Electricity System Operator (IESO). It is a publicly available data that shows the capability and output of each major electricity generator in Ontario, Canada. More specifically, I want to see the difference between the capability and the outputs, and how it changes over time.

Unfortunately, I do not like the way that the data is formatted, so I wanted to rearrange/restructure the data frame. But I don't know how to do this, so I was wondering if anyone here could help me with this. I reckon that this isn't going to be easy, but any help would be much appreciated!

If you wish to play around with the actual dataset instead of the simplified dataset that I'll be providing below, feel free to go to IESO Generator Output Capability Month Report and download PUB_GenOutputCapabilityMonth_202001.csv dataset. I obviously prefer that you try this with the actual dataset, but it's your call.

That being said, below is the simplified version of the dataset that only shows the first two generators (i.e., Abkenora Hydro unit & Adelaide Wind unit) in the PUB_GenOutputCapabilityMonth_202001.csv file. Note that, under the "Measurement" column, "Available Capacity" for the wind unit is essentially the same as the "Capability" for other types of generators.

Table 1: Simplified Data

Delivery Date Generator Fuel Type Measurement Hour 1 Hour 2 Hour 3 Hour 4 Hour 5 Hour 6 Hour 7 Hour 8 Hour 9 Hour 10 Hour 11 Hour 12 Hour 13 Hour 14 Hour 15 Hour 16 Hour 17 Hour 18 Hour 19 Hour 20 Hour 21 Hour 22 Hour 23 Hour 24
2020-01-01 ABKENORA HYDRO Capability 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13
2020-01-01 ABKENORA HYDRO Output 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13
2020-01-01 ADELAIDE WIND Available Capacity 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60
2020-01-01 ADELAIDE WIND Forecast 28 35 32 24 24 20 32 32 26 30 25 21 26 26 37 40 37 39 47 55 49 56 57 57
2020-01-01 ADELAIDE WIND Output 1 0 0 0 0 0 0 0 0 0 0 18 25 29 38 43 34 43 49 57 51 59 58 57

Table 2 below is even more simplified version (fewer number of hours) of the table above that you can copy and paste into R.

Table 2: Slightly More Simplified Version of Table 1 (fewer hours)

JanuaryData = data.frame(`Delivery Date`= c('2020-01-01', '2020-01-01', '2020-01-01', '2020-01-01', '2020-01-01'),
                         `Generator` = c('ABKENORA', 'ABKENORA', 'ADELAIDE', 'ADELAIDE', 'ADELAIDE'),
                         `Fuel Type` = c('Hydro', 'Hydro', 'Wind', 'Wind', 'Wind'),
                         `Measurement` = c('Capability', 'Output', 'Available Capacity', 'Forecast', 'Output'),
                         `Hour 1` = c('13', '13', '60', '28', '1'),
                         `Hour 7` = c('13', '13', '60', '32', '0'),
                         `Hour 13` = c('13', '13', '60', '26', '25'),
                         `Hour 18` = c('13', '13', '60', '39', '43'),
                         `Hour 24` = c('13', '13', '60', '57', '57')
                         )

View(JanuaryData)

Now I want to rearrange/restructure the data and make it look something like Table 3 below. Note that Table 3 is based on Table 2 (i.e., only shows two generators and fewer number of hours). Again, the actual dataset contains dozens of generators and covers all 24 hours in a day by hourly interval (i.e., hour 1, 2, 3, ... , 23, 24). Also, I want to rename wind generator's "Available Capacity" and merge it to "Capability" so I can represent all of them under one "Capability" column instead of needlessly having two separate columns (i.e., "Available Capacity" column for wind generators & "Capability" column for other types of generators) describing essentially the same thing.

Table 3: Simplified Final Product - Based on Table 2 data

Delivery Date Generator Fuel Type Hour Capability Output Forecast
2020-01-01 ABKENORA HYDRO 1 13 13 n/a
2020-01-01 ABKENORA HYDRO 7 13 13 n/a
2020-01-01 ABKENORA HYDRO 13 13 13 n/a
2020-01-01 ABKENORA HYDRO 18 13 13 n/a
2020-01-01 ABKENORA HYDRO 24 13 13 n/a
2020-01-01 ADELAIDE WIND 1 60 1 28
2020-01-01 ADELAIDE WIND 7 60 0 32
2020-01-01 ADELAIDE WIND 13 60 25 26
2020-01-01 ADELAIDE WIND 18 60 43 39
2020-01-01 ADELAIDE WIND 24 60 57 57

Thank you for reading this long post! It would be great if your explanation can be as detailed as possible.

2

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.