pandas json_normalize flatten nested dictionaries

Question

I am trying to flatten nested dictionaries by using json_normalize. My data is like this:

data = [
    {'gra': [
        {
            'A': 1,
            'B': 9,
            'C': {'D': '1', 'E': '1'},
            'date': '2019-06-27'
        }
    ]},
    {'gra': [
        {
            'A': 2,
            'B': 1,
            'C': {'D': '1', 'E': '2'},
            'date': '2019-06-27'
        }
    ]},
    {'gra': [
        {
            'A': 6,
            'B': 1,
            'C': {'D': '1', 'E': '3'},
            'date': '2019-06-27'
        }
    ]}
]

I want to get a dataframe like this:

A   B    C.D   C.E       date
1   9     1     1     2019-06-27
2   1     1     2     2019-06-27
6   1     1     3     2019-06-27

I tried record_path and meta in the json_normalize, but it keeps giving me an error.

How do you achieve this?

Answer 1

json_normalize does a pretty good job of flatting the object into a pandas dataframe:

from pandas.io.json import json_normalize
json_normalize(sample_object)

from pandas.io.json import json_normalize
data_ = [item['gra'][0] for item in data] # [{'A': 1, 'B': 9, 'C': {'D': '1', 'E': '1'}, 'date': '2019-06-27'}, {'A': 2, 'B': 1, 'C': {'D': '1', 'E': '2'}, 'date': '2019-06-27'}, {'A': 6, 'B': 1, 'C': {'D': '1', 'E': '3'}, 'date': '2019-06-27'}]

print (json_normalize(data_))

output:

   A  B C.D C.E        date
0  1  9   1   1  2019-06-27
1  2  1   1   2  2019-06-27
2  6  1   1   3  2019-06-27