How To Convert A List Of Dictionaries To A Dataframe?

This is a list of dictionaries that I have which is to be converted to a dataframe. I tried using multi-index but couldn't convert the whole dataframe. response = [{ 'name': 'xyz',

Solution 1:

Use json_normalize with DataFrame.set_index:

df = pd.json_normalize(response,
                       record_path=['details','address'],
                       meta=['name','empId', ['address','country']]
                       )

df = df.set_index(['name','empId','address.country'])
print (df)
                               street     city
name empId address.country                    
xyz  007   xxz country       x street   x city
           xxz country      xx street  xx city
           yyz country       y street   y city
           yyz country      yy street  yy city

For older pandas versions use:

df = pd.io.json.json_normalize(response,
                               record_path=['details','address'],
                               meta=['name','empId', ['address','country']]
                       )

EDIT:

Tested with multiple values and working well:

response = [{
"name": "xyz",
"empId": "007",
"details": [{
        "address": [{
            "street": "x street",
            "city": "x city"
        }, {
            "street": "xx street",
            "city": "xx city"
        }],
        "country": "xxz country"
    },
    {
        "address": [{
            "street": "y street",
            "city": "y city"
        }, {
            "street": "yy street",
            "city": "yy city"
        }],
        "country": "yyz country"
    }
]
},
            {
"name": "xyz1",
"empId": "0071",
"details": [{
        "address": [{
            "street": "x street1",
            "city": "x city1"
        }, {
            "street": "xx stree1t",
            "city": "xx city1"
        }],
        "country": "xxz country"
    },
    {
        "address": [{
            "street": "y street",
            "city": "y city"
        }, {
            "street": "yy street",
            "city": "yy city"
        }],
        "country": "yyz country"
    }
]
}]

---

df = pd.json_normalize(response,
                       record_path=['details','address'],
                       meta=['name','empId', ['address','country']]
                       )

df = df.set_index(['name','empId','address.country'])

---

print (df)
                                street      city
name empId address.country                      
xyz  007   xxz country        x street    x city
           xxz country       xx street   xx city
           yyz country        y street    y city
           yyz country       yy street   yy city
xyz1 0071  xxz country       x street1   x city1
           xxz country      xx stree1t  xx city1
           yyz country        y street    y city
           yyz country       yy street   yy city

Solution 2:

As far as I know, there is no easy way to do it since your data contains multiple levels of lists. Although a bit convoluted, the following should work. The code will iteratively explode lists and convert dictionaries to columns with json_normalize.

df = pd.DataFrame.from_records(response)
df = df.explode('details', ignore_index=True)
df = pd.concat([df, pd.json_normalize(df['details'])], axis=1)
df = df.explode('address', ignore_index=True)
df = pd.concat([df, pd.json_normalize(df['address'])], axis=1)
df = df.drop(columns=['details', 'address'])

Result:

  name empId      country     street     city
0  xyz   007  xxz country   x street   x city
1  xyz   007  xxz country  xx street  xx city
2  xyz   007  yyz country   y street   y city
3  xyz   007  yyz country  yy street  yy city

Note: For pandas versions older than 1.1.0, explode do not have the ignore_index parameter. Instead, use reset_index(drop=True) after the explode.

In addition, in older pandas versions you need to use pd.io.json.json_normalize instead of pd.json_normalize.