Pivot pandas dataframe to long format with multiple layers

user_12642161 0
Viewed 79
|            |          Var1                 Var2                 
|------------|------|------|-----|------|------|-----|
|            | SPY  | AAPL | MSFT| SPY  | AAPL | MSFT 
|       Date |      |      |     |      |      |     |         
| 2011-01-03 | 30   | 30  | 30   | 30   | 30  | 30   | 
| 2011-01-04 | 30   | 30  | 30   | 21   | 30  | 30   | 
| 2011-01-05 | 30   | 30  | 30   | 30   | 30  | 30   |

How could I pivot the dataframe above, which has multiple layers, into long format like below? Expected Output is shown below:

|            | firm | Var1 | Var2 |
|------------|------|------|------|
| Date       |      |      |      |    
| 2011-01-03 | AAPL |   30 |   30 | 
| 2011-01-04 | SPY  |   30 |   30 |
| 2011-01-05 | MSFT |   30 |   30 |

sample data :

df = pd.DataFrame([{('Var1', 'SPY'): 30.0,
      ('Var1', 'AAPL'): 30.0,
      ('Var1', 'MSFT'): 30.0,
      ('Var2', 'SPY'): 30.0,
      ('Var2', 'AAPL'): 30.0,
      ('Var2', 'MSFT'): 30.0},
     {('Var1', 'SPY'): 30.0,
      ('Var1', 'AAPL'): 30.0,
      ('Var1', 'MSFT'): 30.0,
      ('Var2', 'SPY'): 21.0,
      ('Var2', 'AAPL'): 30.0,
      ('Var2', 'MSFT'): 30.0},
     {('Var1', 'SPY'): 30.0,
      ('Var1', 'AAPL'): 30.0,
      ('Var1', 'MSFT'): 30.0,
      ('Var2', 'SPY'): 30.0,
      ('Var2', 'AAPL'): 30.0,
      ('Var2', 'MSFT'): 30.0}]
pandas pivot-table dataframe python-3.x python
1 Answers

let's reproduce the dataframe 1st.

A:

            SPL AAPL MSFT
2011-01-03  30  30  30
2011-01-04  30  30  30
2011-01-05  30  30  30

B:

            SPL AAPL MSFT
2011-01-03  30  30  30
2011-01-04  21  30  30
2011-01-05  30  30  30

A.columns = pd.MultiIndex.from_product([['Var1'], A.columns])
B.columns = pd.MultiIndex.from_product([['Var2'], B.columns])
df = pd.concat([A, B], axis = 1)

Your current Dataframe df:

                Var1           Var2
            SPL AAPL MSFT   SPL AAPL MSFT
2011-01-03  30  30  30      30  30  30
2011-01-04  30  30  30      21  30  30
2011-01-05  30  30  30      30  30  30

Code:

df = df.stack().reset_index().rename(columns={'level_0':'Date', 'level_1': 'firm'})
df.set_index(['Date'], inplace=True)

Result df:

            firm    Var1    Var2
Date            
2011-01-03  AAPL    30      30
2011-01-03  MSFT    30      30
2011-01-03  SPL     30      30
2011-01-04  AAPL    30      30
2011-01-04  MSFT    30      30
2011-01-04  SPL     30      21
2011-01-05  AAPL    30      30
2011-01-05  MSFT    30      30
2011-01-05  SPL     30      30
user_66603730
Related