Pivot with multi index in Pandas data frame

Question

I'm working on a report, and I need to create a pivot table.

Some context:

The data has two date columns:
- The origination date
- The observation date
Each row contains multiple values:
- Payments
- Balance
- ...

So, my original dataframe looks something like this (a little sample):

obs_date     orig_date   payments   balance
2017-09-12   2019-09-12    200.00   1700.00
2019-09-19   2019-09-12      0.00   1750.00
2019-09-26   2019-09-12    100.00   1650.00
2019-09-19   2019-09-19      0.00   1235.00
2019-09-26   2019-09-19    300.00    950.00
2019-09-26   2019-09-26     50.00   3435.00

I tried using Pandas .pivot() function on the dataframe, but what I get is something like this:

              payments                               balance
orig_date     2017-09-12   2017-09-19   2017-09-26   2017-09-12   2017-09-19   2017-09-26
obs_date
2017-09-12        200.00         0.00       100.00      1700.00      1750.00      1650.00
2019-09-19                       0.00       300.00                   1235.00       950.00
2019-09-26                                   50.00                                3435.00

What I'd like to get is something like this:

 obs_date            2017-09-12   2017-09-19   2017-09-26
orig_date
2017-09-12 payments      200.00         0.00       100.00
           balance      1700.00      1750.00      1650.00
2017-09-19 payments                     0.00       300.00      
           balance                   1235.00       950.00
2017-09-26 payments                                 50.00
           balance                                3435.00

So, is there a way to get this?

Sample code:

import pandas as pd

df = pd.DataFrame({
    'obs_date':['2017-09-12', '2017-09-19', '2017-09-26', '2017-09-19', '2017-09-26', '2017-09-26'],
    'orig_date': ['2017-09-12', '2017-09-12', '2017-09-12', '2017-09-19', '2017-09-19', '2017-09-26'],
    'payments': [200.00, 0.00, 100.00, 0.00, 300.00, 50.00],
    'balance': [1700.00,1750.00,1650.00,1235.00,950.00,3435.00]
})

# My unsuccesful attempt:
cols = [c for c in df.columns if c not in ['obs_date', 'orig_date']]
df_pivot = df.pivot(index='orig_date', columns='obs_date', values=cols)

BENY · Accepted Answer · 2019-03-07 00:11:21Z

3

You are very close fix your code by stack

df_pivot.stack(level=0)
Out[682]: 
obs_date             2017-09-12  2019-09-19  2019-09-26
orig_date                                              
2019-09-12 balance       1700.0      1750.0      1650.0
           payments       200.0         0.0       100.0
2019-09-19 balance          NaN      1235.0       950.0
           payments         NaN         0.0       300.0
2019-09-26 balance          NaN         NaN      3435.0
           payments         NaN         NaN        50.0

answered Mar 7, 2019 at 0:11

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

Pivot with multi index in Pandas data frame

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