How to map nested dictionaries to dataframe columns in python?-CodePudding

I have nested dictionaries like this:

X = {A:{col1:12,col-2:13},B:{col1:12,col-2:13},C:{col1:12,col-2:13},D:{col1:12,col-2:13}}
Y = {A:{col1:3,col-2:5},B:{col1:1,col-2:2},C:{col1:4,col-2:7},D:{col1:8,col-2:7}}
Z = {A:{col1:6,col-2:7},B:{col1:4,col-2:7},C:{col1:5,col-2:7},D:{col1:4,col-2:9}}

I also have a data frame with a single column like this:

Df: data frame([A,B,C,D],columns = ['Names'])

For every row in the data frame, I want to map the given dictionaries in this format:

Names Col-1_X  Col-1_Y  Col-1_Z  Col-2_X  Col-2_Y  Col-2_Z
A      12          3      6         13      5         7
B      12          1      4         13      2         7
C      12          4      5         13      7         7
D      12          8      4         13      7         9

Can anyone help me get the data in this format?

CodePudding user response：

Transpose and concat them:

dfX = pd.DataFrame(X).T.add_suffix('_X')
dfY = pd.DataFrame(Y).T.add_suffix('_Y')
dfZ = pd.DataFrame(Z).T.add_suffix('_Z')
output = pd.concat([dfX, dfY,dfZ], axis=1))

output :

   col1_X  col-2_X  col1_Y  col-2_Y  col1_Z  col-2_Z
A      12       13       3        5       6        7
B      12       13       1        2       4        7
C      12       13       4        7       5        7
D      12       13       8        7       4        9