pandas json normalize with some empty record_path

user_6850513 0
Viewed 848

I have been trying to normalize a nested json but am struggling with is how to deal with NAN and empty fields as i am getting a => KeyError: 'address' when i am trying to normalize with

pd.json_normalize(data ,record_path=["address"],record_prefix='add.')

The data that i am using

data = [{
    "id": "1",
    "name": "test",
    "last": "last"

}, {
    "id": "2",
    "name": "test1",
    "last": "last1",
    "address": []
},
{
    "id": "2",
    "name": "test2",
    "last": "last2",
    "address": [{
        "steet": "streed2",
        "no": 2
    }]
}]

the ouptput i want

 id | name  | last | add.street | add.no
 1  | test  | last | NAN        | NAN
 2  | test1 | last1 |           |  
 3  | test2 | last2 | street2   | 2
pandas dataframe python
1 Answers

You could use the following approach (I took the liberty to spell-check the json-string you provided):

So, you data is:

data = [{
    "id": "1",
    "name": "test",
    "last": "last"

}, {
    "id": "2",
    "name": "test1",
    "last": "last1",
    "address": []
},
{
    "id": "3",
    "name": "test2",
    "last": "last2",
    "address": [{
        "street": "street d2",
        "no": 2
    }]
}]

and the first step is to insert it in a dataframe:

df = pd.DataFrame(data)
print(df)

which returns:

 id   name   last                          address
0  1   test   last                              NaN
1  2  test1  last1                               []
2  3  test2  last2  [{'steet': 'streed2', 'no': 2}]

As you point out, the existence of nan is quite problematic. So, it is reasonable to make them "go away". First. explode the address column. That will expose the json strings and not lists of json strings:

df = df.explode('address')
print(df)

Which gives:

id   name   last                        address
0  1   test   last                            NaN
1  2  test1  last1                            NaN
2  3  test2  last2  {'steet': 'streed2', 'no': 2}

Then nan can be handled by replacing them with {} and this allows you to normalize you df.

df.address = df.address.fillna({i: {} for i in df.index})

# use json_normalize
df = df.join(pd.json_normalize(df.address)).drop(columns=['address'])
print(df)

which gives you:

  id   name   last     street   no
0  1   test   last        NaN  NaN
1  2  test1  last1        NaN  NaN
2  3  test2  last2  street d2  2.0
user_53636860
Related