Pandas Multi-index on 2 Levels

Question

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 :

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

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

Answer 1

You can do:

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

Output:

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