Shaping columns (and column headers) into multi-index rows using pivot/pivot_table

Question

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

Answer 1

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"]

Answer 2

from values in image to some output like below: [1]: https://i.stack.imgur.com/VLwv8.png [Desired output]: [2]: https://i.stack.imgur.com/X6hTl.png