Pandas: How to create a multi-indexed pivot

Question

I have a set of experiments defined by two variables: scenario and height. For each experiment, I take 3 measurements: result 1, 2 and 3. The dataframe that collects all the results looks like this:

import numpy as np
import pandas as pd

df = pd.DataFrame()
df['Scenario']= np.repeat(['Scenario a','Scenario b','Scenario c'],3)
df['height'] = np.tile([0,1,2],3)
df['Result 1'] = np.arange(1,10)
df['Result 2'] = np.arange(20,29)
df['Result 3'] = np.arange(30,39)

If I run the following:

mypiv = df.pivot('Scenario','height').transpose()
writer = pd.ExcelWriter('test_df_pivot.xlsx')
mypiv.to_excel(writer,'test df pivot')
writer.save()

I obtain a dataframe where columns are the scenarios, and the rows have a multi-index defined by result and height:

+----------+--------+------------+------------+------------+
|          | height | Scenario a | Scenario b | Scenario c |
+----------+--------+------------+------------+------------+
| Result 1 |      0 |          1 |          4 |          7 |
|          |      1 |          2 |          5 |          8 |
|          |      2 |          3 |          6 |          9 |
| Result 2 |      0 |         20 |         23 |         26 |
|          |      1 |         21 |         24 |         27 |
|          |      2 |         22 |         25 |         28 |
| Result 3 |      0 |         30 |         33 |         36 |
|          |      1 |         31 |         34 |         37 |
|          |      2 |         32 |         35 |         38 |
+----------+--------+------------+------------+------------+

How can I create a pivot where the indices are swapped, i.e. height first, then result?

I couldn't find a way to create it directly. I managed to get what I want swapping the levels and the re-sorting the results:

mypiv2 = mypiv.swaplevel(0,1 , axis=0).sortlevel(level=0,axis=0,sort_remaining=True)

but I was wondering if there is a more direct way.

jezrael · Accepted Answer · 2016-07-05 14:45:23Z

1

You can first set_index and then stack with unstack:

print (df.set_index(['height','Scenario']).stack().unstack(level=1))
Scenario         Scenario a  Scenario b  Scenario c
height                                             
0      Result 1           1           4           7
       Result 2          20          23          26
       Result 3          30          33          36
1      Result 1           2           5           8
       Result 2          21          24          27
       Result 3          31          34          37
2      Result 1           3           6           9
       Result 2          22          25          28
       Result 3          32          35          38

edited Jul 5, 2016 at 14:45

answered Jul 5, 2016 at 14:38

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Pandas: How to create a multi-indexed pivot

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related