Flatterning JSON to Pandas Dataframe

Question

I'm trying to flattern this json into a pandas dataframe, but it's getting the better of me.

[{
    'contact': {
        'id': 101,
        'email': '[email protected]',
    },
    'marketingPreference': [{
        'marketingId': 1093,
        'isOptedIn': True,
        'dateModifed': '2022-05-10T14:29:24Z'
    }]
},
{
    'contact': {
        'id': 102,
        'email': '[email protected]',
    },
    'marketingPreference': [{
        'marketingId': 1093,
        'isOptedIn': True,
        'dateModifed': '2022-05-10T14:29:24Z'
    }]
}
]

I am looking for the columns to be: Id, Email, MarketingId, IsOptedIn, DateModifed.

Even though marketingPreference is an array, there is only ever one json object inside.

Answer 1

You can use pd.json_normalize

df = pd.json_normalize(data, record_path='marketingPreference', meta=[['contact', 'id'], ['contact', 'email']])

print(df)

   marketingId  isOptedIn           dateModifed contact.id       contact.email
0         1093       True  2022-05-10T14:29:24Z        101  [email protected]
1         1093       True  2022-05-10T14:29:24Z        102  [email protected]