pandas normalize simple JSON

Question

I have a simple JSON data like:

[{
    "load": 1,
    "results": {
        "key": "A",
        "timing": 1.1
    }
}, {
    "load": 2,
    "results": {
        "key": "B",
        "timing": 2.2
    }
}]

When trying to load it to pandas:

pd.read_json('res.json')

The result looks like:

But Instead of having key, value as separate columns they are still nested. How can these be normalized?

Answer 1

Use json.json_normalize:

data = [{
    "load": 1,
    "results": {
        "key": "A",
        "timing": 1.1
    }
}, {
    "load": 2,
    "results": {
        "key": "B",
        "timing": 2.2
    }
}]

from pandas.io.json import json_normalize
df = json_normalize(data)
print (df)
   load results.key  results.timing
0     1           A             1.1
1     2           B             2.2

If need data from file:

from pandas.io.json import json_normalize
import json

with open('sample.json') as f:    
    data = json.load(f)  

df = json_normalize(data)