Converting nested JSON structures to Pandas DataFrames

Question

I've been struggling with the nested structure in json, how to convert to correct form

{
"id": "0c576f35-d704-4fa8-8cbb-311c6be36358",
"employee_id": null,
"creator_id": "16ca2db9-206c-4e18-891d-a00a5252dbd3",
"closed_by_id": null,
"request_number": 23,
"priority": "2",
"form_id": "urlaub-weitere-abwesenheiten",
"status": "opened",
"name": "Urlaub & weitere Abwesenheiten",
"read_by_employee": false,
"custom_status": {
    "id": 15793,
    "name": "In Bearbeitung HR"
},
"due_date": null,
"created_at": "2021-03-29T15:18:37.572040+02:00",
"updated_at": "2021-03-29T15:22:15.590156+02:00",
"closed_at": null,
"archived_at": null,
"attachment_count": 1,
"category": {
    "id": "payroll-time-management",
    "name": "Payroll, Time & Attendance"
},
"public_comment_count": 0,
"form_data": [
    {
        "field_id": "subcategory",
        "values": [
            "Time & Attendance - Manage monthly/year-end consolidation and report"
        ]
    },
    {
        "field_id": "separator-2",
        "values": [
            null
        ]
    },
    {
        "field_id": "art-der-massnahme",
        "values": [
            "Fortbildung"
        ]
    },
    {
        "field_id": "bezeichnung-der-schulung-kurses",
        "values": [
            "dfgzhujiko"
        ]
    },
    {
        "field_id": "startdatum",
        "values": [
            "2021-03-26"
        ]
    },
    {
        "field_id": "enddatum",
        "values": [
            "2021-03-27"
        ]
    },
    {
        "field_id": "freistellung",
        "values": [
            "nein"
        ]
    },
    {
        "field_id": "mit-bildungsurlaub",
        "values": [
            ""
        ]
    },
    {
        "field_id": "kommentarfeld_fortbildung",
        "values": [
            ""
        ]
    },
    {
        "field_id": "separator",
        "values": [
            null
        ]
    },
    {
        "field_id": "instructions",
        "values": [
            null
        ]
    },
    {
        "field_id": "entscheidung-hr-bp",
        "values": [
            "Zustimmen"
        ]
    },
    {
        "field_id": "kommentarfeld-hr-bp",
        "values": [
            "wsdfghjkmhnbgvfcdxsybvnm,"
        ]
    },
    {
        "field_id": "individuelle-abstimmung",
        "values": [
            ""
        ]
    }
],
"form_files": [
    {
        "id": 30129,
        "filename": "empty_background.png",
        "field_id": "anhang"
    }
],
"visible_by_employee": false,
"organization_ids": [],
"need_edit_by_employee": false,
"attachments": []

}

---

using a simple solution with pandas, dataframe

Request = pd.DataFrame.from_dict(pd.json_normalize(data), orient='columns')

it's displaying almost in its correct form:

how to split a dictionary from columns form_data i form_files, I've done a lot of research, but I'm still having a lot of trouble solving this problem, how to split form_data for columns, no rows for meta to ID

Answer 1

You can do something like this.

pass the dataframe and the column to the function as arguments

def explode_node(child_df, column_value):
    child_df = child_df.dropna(subset=[column_value])
    if isinstance(child_df[str(column_value)].iloc[0], str):
        child_df[column_value] = child_df[str(column_value)].apply(ast.literal_eval)
    expanded_child_df = (pd.concat({i: json_normalize(x) for i, x in child_df.pop(str(column_value)).items()}).reset_index(level=1,drop=True).join(child_df, how='right', lsuffix='_left', rsuffix='_right').reset_index(drop=True))
    expanded_child_df.columns = map(str.lower, expanded_child_df.columns)

    return expanded_child_df