How to convert python dataframe to nested dictionary based on column values?-CodePudding

I have 2 dataframes

df1 = pd.DataFrame(data={'ID': ['0','1'], 'col1': [0.73, 0.58], 'col2': [0.51, 0.93], 'Type': ['mean', 'mean'] })
df2 = pd.DataFrame(data={'ID': ['0','1'], 'col1': [0.44, 0.49], 'col2': [0.50, 0.24], 'Type': ['std', 'std'] })
print(df1)
print(df2)

I need to convert to nested dictionary like

mydict = {0: {'col1': {'mean': 0.73, 'std': 0.44}, 'col2': {'mean': 0.51, 'std': 0.5}},
 1: {'col1': {'mean': 0.58, 'std': 0.49}, 'col2': {'mean': 0.93, 'std': 0.24}}}

where 'ID' as key, column names as nested key and 'Type' as nested keys and column values as values

CodePudding user response：

Use concat with DataFrame.pivot for MultiIndex DataFrame and then convert to nested dict:

df = pd.concat([df1, df2]).pivot('Type', 'ID')

d = {level: df.xs(level, axis=1, level=1).to_dict() for level in df.columns.levels[1]}
print (d)
{'0': {'col1': {'mean': 0.73, 'std': 0.44}, 
       'col2': {'mean': 0.51, 'std': 0.5}}, 
 '1': {'col1': {'mean': 0.58, 'std': 0.49}, 
       'col2': {'mean': 0.93, 'std': 0.24}}}

CodePudding user response：

(df1.drop(columns = 'Type').melt('ID', value_name='mean')
    .merge(df2.drop(columns='Type').melt('ID', value_name='std'))
    .assign(c = lambda x:x[['mean', 'std']].to_dict('records'))
    .pivot('variable','ID', 'c').to_dict())

{'0': {'col1': {'mean': 0.73, 'std': 0.44},
  'col2': {'mean': 0.51, 'std': 0.5}},
 '1': {'col1': {'mean': 0.58, 'std': 0.49},
  'col2': {'mean': 0.93, 'std': 0.24}}}