Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Pandas Multi-index on 2 Levels

I have a json which is in the following format :

data = {
    'Low': {
        '2023.07.01': {"u": "mo", 'N': 1, 'O': 2, "PN": 22, "PO": 34},
        '2023.07.02': {"u": "no", 'N': 1, 'O': 2, "PN": 22, "PO": 34}
    },
    'Medium': {
        '2023.07.01': {"u": "no", 'N': 1, 'O': 2, "PN": 22, "PO": 34},
        '2023.07.02': {"u": "mo", 'N': 1, 'O': 2, "PN": 22, "PO": 34}
    },
    'High': {
        '2023.07.01': {"u": "no", 'N': 122, 'O': 2, "PN": 212, "PO": 334},
        '2023.07.02': {"u": "mo", 'N': 13, 'O': 2, "PN": 2, "PO": 342}
    }
}

How can i create a multi level dataframe with the following structure :

Date    U   Low              Medium            High         
            N   PN  O   PN   N  PN  O   PN     N    PN  O   PN

I have tried various ways like df.stack and df.pivot but I was not able to get the exact format I needed. Any help is appreciated.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Attaching an excel image for more clarity.
Thanks
Excel sheet sample

>Solution :

You can do:

(df.stack().apply(pd.Series)
   .set_index('u',append=True)
   .stack().unstack(level=(1,-1))
)

Output:

               Low                  Medium                  High                       Low
                 N    O    PN    PO      O    PN    PO  New    O     PN     PO    New  New
           u                                                                              
2023.07.01 mo  1.0  2.0  22.0  34.0    NaN   NaN   NaN  NaN  NaN    NaN    NaN    NaN  NaN
           no  NaN  NaN   NaN   NaN    2.0  22.0  34.0  1.0  2.0  212.0  334.0  122.0  NaN
2023.07.02 mo  NaN  NaN   NaN   NaN    2.0  22.0  34.0  1.0  2.0    2.0  342.0   13.0  NaN
           no  NaN  2.0  22.0  34.0    NaN   NaN   NaN  NaN  NaN    NaN    NaN    NaN  1.0
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading