Python - Extract Json file into headers

Question

I have a json file that I imported into pandas. The first column is filled with cells that are in json format. Below is the first cell of 10K cells or so...

df = pd.read_json("test_file.json") # import data
print (df['test_column'].iloc[0]) # print first cell

{'data': [{'time': '2016-03-25', 'id': '54', 'stop': {'length': 38, 'fun_time': False, 'before': '2015-03-24', 'id': '10xd9'}}], 'dataType': 'life', 'weird': '2013-06-15', '_id': 'dirt', '_type': 'what', 'trace': '32', 'timestamp': 1418193255, 'teller': 'jeff', 'work': '1', 'eventCategory': 'so_true', 'eventType': 'complete', 'city': 'CHI', 'type': 'some_type', 'value': '32', 'data': 'river' }}}

The code above is an approximation of the real data in each cell

Is there a quick way to extract all the key values in the json data, append them as a header to new columns in a pandas, and then add the value to the appropriate row?

Thanks

piRSquared · Accepted Answer · 2017-03-01 07:46:32Z

1

Try

pd.io.json.json_normalize(df.test_column.apply(pd.io.json.loads))

answered Mar 1, 2017 at 7:46

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

1 Comment

Jeff Saltfist Over a year ago

Got this error: TypeError: Expected String or Unicode

Collectives™ on Stack Overflow

Python - Extract Json file into headers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related