Extracting data from nested json arrays in python

Question

I am having trouble extracting data from nested json in python. I want to create a one column pandas dataframe of all the values of "bill", e.g.

bill
----
a1
a2
a3

Using the output from an API formatted like this:

{
  "status": "succeeded",
  "travels": [
    {
      "jobs": [
        {
          "bill": "a1"
        },
        {
          "bill": "a2"
        },
        {
          "bill": "a3"
        }
      ],
      "vehicle": {
        "plate": "xyz123"
      }
    }
  ]
}

Loading the json directly into pandas gives me only the first instance of 'bill'. I have tried json_normalize() on 'jobs', but it has a key error. Can anybody help me figure out how to grab just the 'bill'?

Thanks

Peter Leimbigler · Accepted Answer · 2019-03-08 02:53:06Z

1

I think you were on the right track with json_normalize. With your input as a python dictionary d:

from pandas.io.json import json_normalize
json_normalize(d, record_path=['travels', 'jobs'])
  bill
0   a1
1   a2
2   a3

answered Mar 8, 2019 at 2:53

Peter Leimbigler

11.1k1 gold badge27 silver badges39 bronze badges

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

2 Comments

catflaps Over a year ago

Thank you! That did the trick. I wasn't aware you didn't have to specify 'data' in json_normalize

Peter Leimbigler Over a year ago

Glad to help! Yeah, json_normalize can be finicky to get working sometimes.

Collectives™ on Stack Overflow

Extracting data from nested json arrays in python

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