0

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: enter image description here

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

Georg Heiler
  • 16,916
  • 36
  • 162
  • 292

1 Answers1

0

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)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252