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.
Attaching an excel image for more clarity.
Thanks

>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