How can I rearrange a DataFrame with Pandas?

Question

I have a DataFrame:

                 Amount                           dwy                                         bmd
Portfolio   EUR GBP JPY USD                 EUR GBP JPY USD                          EUR    GBP JPY USD
date
2016-05-13  100 200 300 400                -0.5 0.5 0   0.8                          3.8    3   0   3

and I would like to transfer it to this:

date        ccy             amt       dwy   bmd
2016-05-13  EUR             100      -0.5   3.8
2016-05-13  GBP             200       0.5   3
2016-05-13  JPY             300       0     0
2016-05-13  USD             400       0.8   3

I have tried df.stack() and df.unstack and also df.T in a variety of ways. Other than pull this apart piece by piece and reassemble is is there a more optimal way?

Answer 1

pd.DataFrame(df.stack("Currency").to_records()) Should do the trick.

Here's an explanation of the steps:

1. Reproducing your dataframe:

arrays = [['Amount', 'Amount', 'Amount', 'Amount', 'dwy', 'dwy', 'dwy', 'dwy', 'bmd', 'bmd', 'bmd', 'bmd'],
          ['EUR', 'GBP', 'JPY', 'USD', 'EUR', 'GBP', 'JPY', 'USD', 'EUR', 'GBP', 'JPY', 'USD']]

tuples = list(zip(*arrays))

index = pd.MultiIndex.from_tuples(tuples, names=['Portfolio', 'Currency'])

data = [100, 200, 300, 400, -0.5, 0.5, 0, 0.8, 3.8, 3, 0, 3]

df = pd.DataFrame(data).T
df.columns = index
df.index = ['2016-05-13']

Here's the "Input" DataFrame:

Portfolio  Amount                 dwy                bmd            
Currency      EUR  GBP  JPY  USD  EUR  GBP JPY  USD  EUR GBP JPY USD
2016-05-13    100  200  300  400 -0.5  0.5   0  0.8  3.8   3   0   3

2. Calling df.stack("Currency") will stack on the Currency column:

Portfolio            Amount  bmd  dwy
           Currency                  
2016-05-13 EUR          100  3.8 -0.5
           GBP          200  3.0  0.5
           JPY          300  0.0  0.0
           USD          400  3.0  0.8

3. Close but not quite. We just need to flatten the index of the Dataframe. To do this, we can call to_records():

3. df.stack("Currency").to_records()

# Result:
# => rec.array([('2016-05-13', 'EUR', 100.0, 3.8, -0.5),
 ('2016-05-13', 'GBP', 200.0, 3.0, 0.5),
 ('2016-05-13', 'JPY', 300.0, 0.0, 0.0),
 ('2016-05-13', 'USD', 400.0, 3.0, 0.8)], 
          dtype=[('level_0', 'S10'), ('Currency', 'S3'), ('Amount', '<f8'), ('bmd', '<f8'), ('dwy', '<f8')])

4. Then we can recreate the dataframe with a new index:

pd.DataFrame(df.stack("Currency").to_records())

      level_0 Currency  Amount  bmd  dwy
0  2016-05-13      EUR     100  3.8 -0.5
1  2016-05-13      GBP     200  3.0  0.5
2  2016-05-13      JPY     300  0.0  0.0
3  2016-05-13      USD     400  3.0  0.8

From here, you can can simply rename and reorder your columns if necessary.