Convert Pandas Dataframe to nested dictionary

Question

I am trying to convert a dataframe to a nested dictionary but no success so far.

Dataframe: clean_data['Model', 'Problem', 'Size']

Here's how my data looks like:

 Model                Problem             Size
 lenovo a6020         screen broken         1
 lenovo a6020a40      battery              60
                      bluetooth            60
                      buttons              60
 lenovo k4            wi-fi                 3
                      bluetooth             3

My desired output:

{
  "name": "Brand",
  "children": [
     {
         "name": "Lenovo",
         "children": [
             {
              "name": "lenovo a6020",
              "children": {
                  "name": "screen broken",
                  "size": 1
               }
             },
             {
              "name": "lenovo a6020a40",
              "children": [
                 {
                   "name": "battery",
                   "size": 60
                 },
                 {
                   "name": "bluetooth",
                   "size": 60
                 },
                 {
                   "name": "buttons",
                   "size": 60
                 }
               ]
             },
             {
              "name": "lenovo k4",
              "children": [
                {
                  "name": "wi-fi",
                  "size": 3
                },
                {
                  "name": "bluetooth",
                  "size": 3
                }
               ]
            }
         ]
      }
   ]
 }

I have tried pandas.DataFrame.to_dict method But it is returning a simple dictionary but I want it like the one mentioned above.

Could you please add code that generates your example dataframe? — Xukrao
– Xukrao, Commented Sep 27, 2018 at 12:06
Dataframe: clean_data['Model', 'Problem', 'Size'] I updated it, please see the edits — Rohit Swami
– Rohit Swami, Commented Sep 27, 2018 at 12:17

jezrael · Accepted Answer · 2018-09-27 12:20:57Z

6

Use:

print (df)
             Model        Problem  size
0     lenovo a6020  screen broken     1
1  lenovo a6020a40        battery    60
2              NaN      bluetooth    60
3              NaN        buttons    60
4        lenovo k4          wi-fi     3
5              NaN      bluetooth     3

 #repalce missing values by forward filling
df = df.ffill()
#split Model column by first whitesapces to 2 columns 
df[['a','b']] = df['Model'].str.split(n=1, expand=True)

#each level convert to list of dictionaries
#for correct keys use rename
L = (df.rename(columns={'Problem':'name'})
        .groupby(['a','b'])['name','size']
        .apply(lambda x: x.to_dict('r'))
        .rename('children')
        .reset_index()
        .rename(columns={'b':'name'})
        .groupby('a')['name','children']
        .apply(lambda x: x.to_dict('r'))
        .rename('children')
        .reset_index()
        .rename(columns={'a':'name'})
        .to_dict('r')
        )
#print (L)

#create outer level by contructor
d = { "name": "Brand", "children": L}

print (d)

{
    'name': 'Brand',
    'children': [{
        'name': 'lenovo',
        'children': [{
            'name': 'a6020',
            'children': [{
                'name': 'screen broken',
                'size': 1
            }]
        }, {
            'name': 'a6020a40',
            'children': [{
                'name': 'battery',
                'size': 60
            }, {
                'name': 'bluetooth',
                'size': 60
            }, {
                'name': 'buttons',
                'size': 60
            }]
        }, {
            'name': 'k4',
            'children': [{
                'name': 'wi-fi',
                'size': 3
            }, {
                'name': 'bluetooth',
                'size': 3
            }]
        }]
    }]
}

answered Sep 27, 2018 at 12:20

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rohit Swami Over a year ago

is it necessary to fill missing values? In actual, they are not missing values but nested.

jezrael Over a year ago

@RohitSwami - Yes, if MultiIndex change df = df.ffill() to df = df.reset_index()

Rohit Swami Over a year ago

Exactly what I was looking for!! Thanks for help <3

Collectives™ on Stack Overflow

Convert Pandas Dataframe to nested dictionary

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related