import nested data into pandas from a json file

Question

I have a generated file as follows:

[{"intervals": [{"overwrites": 35588.4, "latency": 479.52}, {"overwrites": 150375.0, "latency": 441.1485001192274}], "uid": "23"}]

I simplified the file a bit for space reasons (there are more columns besides for the "overwrites" and "latency" ). I would like to import the data into a dataframe so I can later on draw the latency. I tried the following:

with open(os.path.join(path, "my_file.json")) as json_file:
   curr_list=json.load(json_file)   
    df=pd.Series(curr_list[0]['intervals'])

print df

which returned:

0 {u'overwrites': 35588.4, u'latency...

1 {u'overwrites': 150375.0, u'latency...

However I couldn't get to store df in a data structure that allows me to access the latency field as follows:

graph = df[['latency']]
graph.plot(title="latency")

Any ideas? Thanks for the help!

jezrael · Accepted Answer · 2016-04-19 10:55:00Z

1

I think you can use json_normalize:

import pandas as pd
from pandas.io.json import json_normalize

data = [{"intervals": [{"overwrites": 35588.4, "latency": 479.52}, 
                       {"overwrites": 150375.0, "latency": 441.1485001192274}], 
         "uid": "23"}]


result = json_normalize(data, 'intervals', ['uid'])
print result
    latency  overwrites uid
0  479.5200     35588.4  23
1  441.1485    150375.0  23

answered Apr 19, 2016 at 10:55

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

import nested data into pandas from a json file

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