Pandas: JSON Normalize with brackets around the JSON?

Question

How might I use json_normalize to breakdown a nested JSON object that is contained in a list?

Consider this example:

df = pd.DataFrame({'xd': [
    [{
        "status": "pass",
        "desc": "desc",
        "actionable": False,
        "err_code": "None",
        "err_msg": "None"
    }],
    [{
        "status": "fail",
        "desc": "desc",
        "actionable": True,
        "err_code": "None",
        "err_msg": "None"
    }] ]})


pd.json_normalize(df['xd'])  # not expected

Expected output:

  status  desc  actionable err_code err_msg
0   pass  desc       False     None    None
1   fail  desc        True     None    None

how about this: `pd.DataFrame(data['xd'][0])` where `data` is the dictionary whose key is "xd"? — , Mar 11 '22 at 20:22

Alexandru Placinta · Accepted Answer · 2022-03-11T23:34:20.577

2

If your json objects are under the xd columns, you can exctract that json, which is a list of dictionaries. A list of dictionaries can be used to create a dataframe object, from here.

list_of_dicts = list_of_dicts=list(map(lambda l: l[0], df['xd'].to_list()))
expected = pd.Dataframe(list_of_dicts)

Does this answer your question?

edited Mar 11 '22 at 23:34

answered Mar 11 '22 at 20:24

Alexandru Placinta

662
3
11

No, consider the case where there are multiple rows; I have updated the example. – John Stud Mar 11 '22 at 20:41
1

Ok, then the list of dict needs to be computed as follows ```list_of_dicts=list(map(lambda l: l[0], df['xd'].to_list()))``` – Alexandru Placinta Mar 11 '22 at 20:45
``pd.DataFrame([ent for ent in df.xd.str[0]])`` also works – sammywemmy Mar 11 '22 at 22:22

score 1 · Answer 2 · answered Mar 11 '22 at 20:56

You could explode + tolist + DataFrame constructor:

out = pd.DataFrame(df['xd'].explode().tolist())

Output:

  status  desc  actionable err_code err_msg
0   pass  desc       False     None    None
1   fail  desc        True     None    None

Pandas: JSON Normalize with brackets around the JSON?

2 Answers2