CODEWITHSUNDEEP
pythonjsonpandas
isic5
isic5

Reputation: 191

Converting nested dict from json into dataframe with values as columns

I have a dict from json that where I aggregate survey results via a loop for each individual person that answered via an API and would like to convert it into a dataframe that uses the 'title' values as columns and then has all the answers of one person in one row.

This is what the raw results look like for one person that took part in the survey:

 {'0': {'title': 'What department do you work?',
    'results': {'0': '', '1': '', '2': '', '3': 'Unit D'}}, 
    '1': {'title': 'I can focus on clear targets?',
    'results': {'0': 'Yes', '1': '', '2': ''}}

This is how the dataframe should look like: 

Result ID  |  What department do you work in? | I can focus on clear targets
    1      |  Unit D                          | Yes

I tried a few different options in pandas, but couldnt get the desired results.

Upvotes: 1

Views: 117

Answers (1)

jezrael
jezrael

Reputation: 862661

If there is one non empty value per person use nested dictionary comprehension with filtering:

d =  {'0': {'title': 'What department do you work?',
    'results': {'0': '', '1': '', '2': '', '3': 'Unit D'}}, 
    '1': {'title': 'I can focus on clear targets?',
    'results': {'0': 'Yes', '1': '', '2': ''}}}

df = pd.DataFrame({v['title']: [v1 for k1, v1 in v['results'].items() if v1] 
                   for k, v in d.items()})
print (df)
  What department do you work? I can focus on clear targets?
0                       Unit D                           Yes

If possible multiple values:

d =  {'0': {'title': 'What department do you work?',
    'results': {'0': '', '1': '', '2': '', '3': 'Unit D'}}, 
    '1': {'title': 'I can focus on clear targets?',
    'results': {'0': 'Yes', '1': 'No', '2': ''}}}


df = pd.DataFrame({v['title']: pd.Series([v1 for k1, v1 in v['results'].items() if v1])
                   for k, v in d.items()})
print (df)
  What department do you work? I can focus on clear targets?
0                       Unit D                           Yes
1                          NaN                            No

Upvotes: 1

Related Questions