How can parse through JSON Data that has multiple dictionaries and some same names?

Question

This is my Normalized_Json =

coding
[{'code': 'MC', 'display': "Patient's Medicare number", 'extension': [{'url': 'https://bluebutton.cms.gov/resources/codesystem/identifier-currency', 'valueCoding': {'code': 'current', 'display': 'Current', 'system': 'https://bluebutton.cms.gov/resources/codesystem/identifier-currency'}}], 'system': 'http://terminology.hl7.org/CodeSystem/v2-0203'}]

I am trying to convert the following table to:

Code	display	extension.url	extension.valueCoding.code	extension.valueCoding.display
MC	Patient's Medicare number	'https://bluebutton.cms.gov/resources/codesystem/identifier-currency'	current	Current

And so on I am having trouble normalizing all of these columns.

So far I've tried this:

t = df['type'].explode()
out = df.join(pd.DataFrame(t.tolist(), index=t.index).explode('code')).drop(columns='type')

&

type_norm = pd.json_normalize(df['type'])
all_data = []
for k, v in type_norm.items():
    texts, values, pairs = v['code'], v['display'], v['extension'], v['valueCoding'], v['system']
    for t, val, p in zip(texts, values, pairs):
        all_data.append({
            'record': k,
            'text': ' '.join(t),
            'pairs': p,
            **{'val_{}'.format(i): val_ for i, val_ in enumerate(val, 1)}
        })

However, this isn't working either.

It would be awesome if someone could show me an example of converting json with multiple dictionaries. I know my data is kinda confusing and hard to read, but a simple example of multiple dictionaries would get me on the correct path.

Answer 1

Have you tried this:

pd.json_normalize(
    json_data,
    record_path=["extension"],
    meta=["code", "display", "system"],
    record_prefix="extension.",
)

record_path is the deepest level that you want to extract. It should points to an array where each item in the array becomes a single row. meta is what extra data you want to grab when travelling down to the record_path.