How to extract data from complex json with Python?

Question

I am trying to extract data from this json file in format like this:

    [{'from': '2019-11-22T12:45:00-05:00',
  'to': '2019-11-22T12:50:00-05:00',
  'value': [{'value': 0, 'label': 'fw'}, {'value': 0, 'label': 'bw'}]},
 {'from': '2019-11-22T12:50:00-05:00',
  'to': '2019-11-22T12:55:00-05:00',
  'value': [{'value': 0, 'label': 'fw'}, {'value': 1, 'label': 'bw'}]},
 {'from': '2019-11-22T12:55:00-05:00',
  'to': '2019-11-22T13:00:00-05:00',
  'value': [{'value': 0, 'label': 'fw'}, {'value': 0, 'label': 'bw'}]}]

The goal is to get the columns "from", "to", "value" and label" So I should have 6 row of data, 2 row for each instance of time, something like this:

I have tried using

pd.DataFrame function from pandas and i get output like this:

Any suggestion how do i go about this?

Serge Ballesta · Accepted Answer · 2020-07-09 13:06:47Z

1

You could explode the value column, and then extract the actual label and value fields. But IMHO it is simpler and faster to pre-process data

df = pd.DataFrame([[d['from'], d['to'], d1['value'], d1['label']]
                    for d in data for d1 in d['value']],
                   columns=['from', 'to', 'value', 'label'])

answered Jul 9, 2020 at 13:06

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

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

Collectives™ on Stack Overflow

How to extract data from complex json with Python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related