Problem
I am trying to shape a dataset which includes stacked dates/data as rows and half-hourly data as columns (Trading periods, eg. TP1, TP2), into a chronological half-hourly order with the stacked data becoming columns (see image below for the output format) and the column headers (TP1, TP2, etc) becoming indices along with the dates.
I am new to Python and Pandas (C and MATLAB programming experience), initially I thought to loop through the data and append the relevant parts of each row into a new data-frame. It seems to me however that either the df.pivot()
or pd.pivot_table()
would be a more suitable way to achieve this as it would allow some aggregation at the same time.
Sample dataset:
df = pd.DataFrame({'POC_Code': ['ARA', 'ARA', 'ARA', 'BPE', 'BPE', 'BPE'],
'Trading_date': ['1/04/2018','2/04/2018','3/04/2018','1/04/2018','2/04/2018','3/04/2018'],
'TP1':[10120,11760,25930,3545,12749,11358],
'TP2':[10170,11790,25890,4329,13793,15448],
'TP3':[10200,11750,25860,3465,13943,16132]})
POC_Code Trading_date TP1 TP2 TP3
0 ARA 1/04/2018 10120 10170 10200
1 ARA 2/04/2018 11760 11790 11750
2 ARA 3/04/2018 25930 25890 25860
3 BPE 1/04/2018 3545 4329 3465
4 BPE 2/04/2018 12749 13793 13943
5 BPE 3/04/2018 11358 15448 16132
My Attempt
Using the following I get close what I want (the image below), but I am still not sure how to turn the TP1, TP2, into indices:
piv = df.pivot_table(values=['TP1','TP2','TP3'], index=['Trading_date'], columns='POC_Code')
TP1 TP2 TP3
POC_Code ARA BPE ARA BPE ARA BPE
Trading_date
1/04/2018 10120 3545 10170 4329 10200 3465
2/04/2018 11760 12749 11790 13793 11750 13943
3/04/2018 25930 11358 25890 15448 25860 16132
I tried to access the column headers like this, but it's not valid which I get: piv = df.pivot_table(values=['TP1','TP2','TP3'], index=['Trading_date', df.columns.values[2:5]], columns='POC_Code')
Desired Output
I need the output to be a chronological format (by date & trading period) such as the multi-index way below, so that I can combine it into a single index of the form 2018-04-01-01
for export to another program. If this combination of the indices can be done in the same step, even better.
Is this possible with pivot
/pivot_table
, or would something like this (https://stackoverflow.com/questions/37430940/python-pandas-converting-column-headers-into-index) be a better approach (or a combination of both)? The full dataset includes 65 POC_Codes and 50 Trading periods, so I would like to use a pivot table as I also plan to sum these into various categories.
Full dataset can be found at: https://www.emi.ea.govt.nz/Wholesale/Datasets/Generation/Generation_MD/201804_Generation_MD.csv