0
$\begingroup$

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.

Output Format

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

$\endgroup$
0
$\begingroup$

For this I would perform two steps, first transforming the data from a wide to long format using pandas.melt (i.e. transform the TP columns over the rows) and then use pandas.pivot to get the desired format.

result = (
    df
    .melt(id_vars=["POC_Code", "Trading_date"])
    .pivot(index=["Trading_date", "variable"], columns="POC_Code")
    .reset_index()
)

result

# Trading_date variable  value       
#                         ARA    BPE
#   1/04/2018      TP1  10120   3545
#   1/04/2018      TP2  10170   4329
#   1/04/2018      TP3  10200   3465
#   2/04/2018      TP1  11760  12749
#   2/04/2018      TP2  11790  13793
#   2/04/2018      TP3  11750  13943
#   3/04/2018      TP1  25930  11358
#   3/04/2018      TP2  25890  15448
#   3/04/2018      TP3  25860  16132

If you want to change the column names you can simply assign the new column names to df.columns:

result.columns = ["Trading_date", "TP", "ARA", "BPE"]
$\endgroup$
1
  • $\begingroup$ Thanks, that does exactly what I want, and I learned some new functions! $\endgroup$
    – CaptChilko
    Apr 19 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.