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?

can you post your desired data set?

MaxU - stand with Ukraine
– MaxU - stand with Ukraine

2018-03-11 09:32:02 +00:00
Commented Mar 11, 2018 at 9:32 — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Mar 11, 2018 at 9:32

Guiem Bosch · Accepted Answer · 2018-03-11 11:12:35Z

3

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

answered Mar 11, 2018 at 11:12

Guiem Bosch

2,7781 gold badge22 silver badges37 bronze badges

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

2 Comments

Shay Eyal Over a year ago

Thank you for your answer, it would work if i had a simple json, but i have multi nested json file with a lot of items and i don't know the structure of all the nested items inside.

Guiem Bosch Over a year ago

Cool, I was afraid that would be the case. Then I don't really know if that's possible. Let's see if anybody else comes up a better solution! :)

Collectives™ on Stack Overflow

pandas json_normalize KeyError

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related