Pandas json_normalize and null values in JSON

user_1683626 0
Viewed 11000

I have this sample JSON

{
    "name":"John",
    "age":30,
    "cars": [
        { "name":"Ford", "models":[ "Fiesta", "Focus", "Mustang" ] },
        { "name":"BMW", "models":[ "320", "X3", "X5" ] },
        { "name":"Fiat", "models":[ "500", "Panda" ] }
    ]
 }

When I need to convert JSON to pandas DataFrame I use following code 

import json
from pandas.io.json import json_normalize
from pprint import pprint

with open('example.json', encoding="utf8") as data_file:
    data = json.load(data_file)
normalized = json_normalize(data['cars'])

This code works well but in the case of some empty cars (null values) I'm not possible to normalize_json.

Example of json

{
    "name":"John",
    "age":30,
    "cars": [
        { "name":"Ford", "models":[ "Fiesta", "Focus", "Mustang" ] },
        null,
        { "name":"Fiat", "models":[ "500", "Panda" ] }
    ]
 }

Error that was thrown

AttributeError: 'NoneType' object has no attribute 'keys'

I tried to ignore errors in json_normalize, but didn't help

normalized = json_normalize(data['cars'], errors='ignore')

How should I handle null values in JSON?

pandas python json
3 Answers

You can fill cars with empty dicts to prevent this error

data['cars'] = data['cars'].apply(lambda x: {} if pd.isna(x) else x)
user_62444640

I agree with vozman, and filling empty {} dictionaries will solve the problem. However, I had the same problem for my project and I made a package to work around with this kind of DataFrames. check out flat-table, it uses json_normalize but also expands rows and columns.

import flat_table
df = pd.DataFrame(data)
flat_table.normalize(df)

This will output the following. Lists expanded to different rows and dictionary keys expanded to different columns.

   index name_x  age name_y   models
0      0   John   30   Ford   Fiesta
1      0   John   30   Ford    Focus
2      0   John   30   Ford  Mustang
3      1   John   30    NaN      NaN
4      2   John   30   Fiat      500
5      2   John   30   Fiat    Panda
user_51595510

How is Another Answer?

data['cars'].fillna('{}')
user_181677550
Related