How To Transpose Column Values To Headers, While Getting Values From Another Column?

I am currently struggling with extracting/flatten data from hugely nested dictionary: Flattening a nested dictionary with unique keys for each dictionary? . I received a somewhat a

Solution 1:

The problem is that your dictionaries have a different number of levels. 'birthArea' and 'passportArea' contain dictionaries while the other keys simply contain values. You can use pd.json_normalize() to flatten the keys of the innermost dictionary as described in Flatten nested dictionaries, compressing keys.

In [37]: pd.DataFrame(responses).stack().apply(lambda x: pd.json_normalize(x, sep='_').to_dict(orient='records')[0]).apply(pd.Series).stack().reset_index()                                                
Out[37]: 
   level_0 level_1                  level_2        0
0    coach    7454                     wyId   562711
1    coach    7454                shortName    Name1
2    coach    7454                firstName       N1
3    coach    7454               middleName         
4    coach    7454                 lastName       N2
..     ...     ...                      ...      ...
28   coach    7453           birthArea_name  Denmark
29   coach    7453          passportArea_id      208
30   coach    7453  passportArea_alpha2code       DK
31   coach    7453  passportArea_alpha3code      DNK
32   coach    7453        passportArea_name  Denmark