Python Pandas Dataframe Pivot Table Column and Values Order

Question

long time XLS user still new to Python/Pandas.I'm tying to automate a report for some price curves and I can't quite manage to get the Pivot in the format I'm looking for. Thanks in advance for your help!

I have a dataframe in this format:

 data = [['AAA',1,11,1,],['AAA',2,12,2],['AAA',3,13,3],
          ['BBB',1,21,5],['BBB',2,22,6],['BBB',3,23,7],
         ['CCC',1,31,9],['CCC',2,32,10],['CCC',3,33,11]]
            
    df = pd.DataFrame(data, columns = ['Curve', 'Tenor','Price','Change'])
        
    print(df)
    
      Curve  Tenor  Price  Change
    0   AAA      1     11       1
    1   AAA      2     12       2
    2   AAA      3     13       3
    3   BBB      1     21       5
    4   BBB      2     22       6
    5   BBB      3     23       7
    6   CCC      1     31       9
    7   CCC      2     32      10
    8   CCC      3     33      11

I pivoted the df as follows and the result looks like this:

df2 = pd.pivot_table(df, values=['Price','Change'], index='Tenor',columns='Curve', aggfunc=np.mean)

          Change         Price        
    Curve    AAA BBB CCC   AAA BBB CCC
    Tenor                             
    1          1   5   9    11  21  31
    2          2   6  10    12  22  32
    3          3   7  11    13  23  33

I'd like it to change the order of the column grouping such that it looked like

        AAA             BBB             CCC 
Tenor   Price   Change  Price   Change  Price   Change
1        11     1       5       21      31      9
2        12     2       6       22      32      10
3        13     3       7       23      33      11

In XLS you'd just move the fields around physically in the Pivot. I'm sure its just as trivial here too :) but no matter how I google I can't seem to find a solution. Thanks again for your help! Any and all assistance appreciated. Cheers.

Henry Ecker · Accepted Answer · 2021-10-29 02:28:48Z

4

We can swaplevel, sort_index and reindex to reorganize the headers:

df2 = (
    df.pivot_table(values=['Price', 'Change'], index='Tenor', columns='Curve')
        .swaplevel(axis=1)
        .sort_index(level=0, axis=1)
        .reindex(['Price', 'Change'], level=1, axis=1)
        .rename_axis(columns=[None, None])
)

df2:

        AAA          BBB          CCC       
      Price Change Price Change Price Change
Tenor                                       
1        11      1    21      5    31      9
2        12      2    22      6    32     10
3        13      3    23      7    33     11

swaplevel switches the values in level 0 and level 1 (so AAA, BBB, CCC become top level index)
sort_index will lexicographically sort the new top level AAA BBB CCC (we could have alternatively done .reindex(['AAA', 'BBB', 'CCC'], level=0, axis=1) to supply a specific order for that level.)
reindex is needed to put "Price" before "Change" since sorting will not work since "Change" comes alphabetically before "Price"
rename_axis get's rid of the axis name "Curve" which is created when pivoting.

edited Oct 29, 2021 at 2:28

answered Oct 29, 2021 at 2:22

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dev1602 Over a year ago

Thanks for your help. This worked great! I have a slightly more complex pivot to create a report out of which i'm having a similar problem with. stackoverflow.com/questions/69951324/…

Collectives™ on Stack Overflow

Python Pandas Dataframe Pivot Table Column and Values Order

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related