pandas json_normalize KeyError

Question

I have a nested json file that doesn't have a unified structure, like the following sample:

[{ "name": "Jon", "last": "Jonny"}, 
 {"name": "Jimmy", "last": "johnson", "kids":[{"kidName":"johnson_junior","kidAge": "1"}, {"kidName":"johnson_junior2", "kidAge": "4"}]}]

See that in the second item there is list name "kids" that doesn't exists in the first item.

When i tries to flat the json with pandas json_normalize it Throws out error message : "KeyError: 'kids'"

This is the json_normalize command:

flat_json = json_normalize(json_file, record_path= 'kids',  errors='ignore')

it seems that json_normalize doesn't support nested json that doesn't have unified structure.

Has someone experienced the same issue? Do you have an idea on how to get through it?

Answer 1

If it is not much trouble, I would add 'kids':[{'kidName':None,'kidAge':None}] whenever that key is not present.

errors='ignore' is used for keys listed in meta (see docu) whereas what you are specifying with kids is a record path.

I don't know if you were asking for general advice as in "what happens if the record path key sometimes is not available?", but just in case the data example you provide is your current problem, that's the quick fix I would propose.

Something like this works:

data = {"name": "Jimmy", "last": "johnson", "kids":[{"kidName":"johnson_junior","kidAge": "1"}, {"kidName":"johnson_junior2", "kidAge": "4"}]}]

# then you inform with empty kids if looping doesn't alter your desired flow that much
[elem.update({'kids':[{'kidName':None,'kidAge':None}]}) for elem in data if 'kids' not in elem.keys()]

# finally you normalize
flat_json = json_normalize(data,'kids', ['name','last'])

The output:

kidAge          kidName   name     last
0   None             None    Jon    Jonny
1      1   johnson_junior  Jimmy  johnson
2      4  johnson_junior2  Jimmy  johnson