Pandas: JSON Normalize with brackets around the JSON?-CodePudding

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

CodePudding user response：

If your json objects are under the xd columns, you can exctract that json, which is a list of dictionaries. A list of dictionaries cand 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?

CodePudding user response：

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