Split elements of a dataframe into sub-columns

user_19899017 0
Viewed 54

I have a dataframe like so :


| -1W             | ME            | QE              | YE               |
|-----------------|---------------|-----------------|------------------|
| [119.06, 1.20]  | [120.27, 0.0] | [125.85, -5.58] | [80.53, 39.73]   |
| [207.00, 3.70]  | [210.71, 0.0] | [215.54, -4.83] | [119.74, 90.97]  |
| [313.32, 8.28]  | [321.61, 0.0] | [325.20, -3.59] | [203.13, 118.47] |
| [517.90, 10.87] | [528.77, 0.0] | [510.14, 18.63] | [395.14, 133.63] |

How could I separate the elements within this dataframe into two sub-column headers - one header for each element per column (kind of like a multi-indexed Dataframe)?

pandas python
2 Answers

Try this:

import pandas as pd
import numpy as np
   
d =[[ [119.06, 1.20]  , [120.27, 0.0] , [125.85, -5.58] , [80.53, 39.73] ],  
 [[207.00, 3.70]  , [210.71, 0.0] , [215.54, -4.83] , [119.74, 90.97]  ],
 [[313.32, 8.28]  , [321.61, 0.0] , [325.20, -3.59] , [203.13, 118.47] ],
 [[517.90, 10.87] , [528.77, 0.0] , [510.14, 18.63] , [395.14, 133.63]]]
  
df = pd.DataFrame(d, columns=['-1W','ME', 'QE', 'YE'])
dfe = df.explode(df.columns.tolist())
    
df_out = dfe.set_index(dfe.groupby(level=0).cumcount(), append=True).unstack()
print(df_out)

Output:

      -1W             ME           QE             YE        
        0      1       0    1       0      1       0       1
0  119.06    1.2  120.27  0.0  125.85  -5.58   80.53   39.73
1   207.0    3.7  210.71  0.0  215.54  -4.83  119.74   90.97
2  313.32   8.28  321.61  0.0   325.2  -3.59  203.13  118.47
3   517.9  10.87  528.77  0.0  510.14  18.63  395.14  133.63

To rename level=1 of pd.MultiIndex dataframe header,

df_out = dfe.set_index(dfe.groupby(level=0).cumcount().map({0:'high', 1:'low'}), 
                       append=True).unstack()

Output:

      -1W             ME           QE             YE        
     high    low    high  low    high    low    high     low
0  119.06    1.2  120.27  0.0  125.85  -5.58   80.53   39.73
1   207.0    3.7  210.71  0.0  215.54  -4.83  119.74   90.97
2  313.32   8.28  321.61  0.0   325.2  -3.59  203.13  118.47
3   517.9  10.87  528.77  0.0  510.14  18.63  395.14  133.63
user_63615310

Lets do stack and unstack to reshape dataframe:

s = df.stack()
pd.DataFrame([*s], s.index).stack().unstack([1, 2])

      -1W             ME           QE             YE        
        0      1       0    1       0      1       0       1
0  119.06   1.20  120.27  0.0  125.85  -5.58   80.53   39.73
1  207.00   3.70  210.71  0.0  215.54  -4.83  119.74   90.97
2  313.32   8.28  321.61  0.0  325.20  -3.59  203.13  118.47
3  517.90  10.87  528.77  0.0  510.14  18.63  395.14  133.63
user_128331660
Related