Dataframe from nested JSON Python

Alex Calder 0
Viewed 24

Looking for help with pd.json_normalize for this JSON. I am unable to figure it out. I am struggling with the "itemsToMake.itemReference" and the "salesOrderLineItemReference"

Here is what I have so far.

df1 = pd.json_normalize(data['data']['jobs']['items']
    ,['itemsToMake','operations']
    ,['createdUtc','number'
        ,['itemsToMake','quantityToMake']
        ,['itemsToMake','itemReference']
        ,'salesOrderLineItemReference'])

Here is the JSON

    {
  "data": {
    "jobs": {
      "items": [
        {
          "createdUtc": "2021-07-01T00:03:34.520Z",
          "number": 11229,
          "itemsToMake": [
            {
              "operations": [
                {
                  "estimatedSetupTimeInSeconds": 600,
                  "estimatedDurationTimeInSeconds": 0,
                  "operation": {
                    "name": "Pull Material"
                  }
                },
                {
                  "estimatedSetupTimeInSeconds": 900,
                  "estimatedDurationTimeInSeconds": 720,
                  "operation": {
                    "name": "Cut Material any Shear (2 person operation)"
                  }
                },
                {
                  "estimatedSetupTimeInSeconds": 900,
                  "estimatedDurationTimeInSeconds": 810,
                  "operation": {
                    "name": "Folding"
                  }
                }
              ],
              "quantityToMake": 18,
              "itemReference": {
                "name": "ANGLE-TRIM|2op|8\"Max",
                "id": "5f496b3bcb66432ca0c471f6",
                "description": "Angle trim with 2 operations - up to 8\" Max SO | 10' sections (6 Sections per Sheet)\n032, 040, 050, 063, 26ga, 24ga, 22ga, 20ga"
              }
            }
          ],
          "salesOrderLineItemReference": {
            "sONumber": 5308
          }
        }
      ]
    }
  }
}

Here is what I get so far.

    estimatedSetupTimeInSeconds  estimatedDurationTimeInSeconds                               operation.name                createdUtc number itemsToMake.quantityToMake                          itemsToMake.itemReference salesOrderLineItemReference
0                          600                               0                                Pull Material  2021-07-01T00:03:34.520Z  11229                         18  {'name': 'ANGLE-TRIM|2op|8"Max', 'id': '5f496b...          {'sONumber': 5308}
1                          900                             720  Cut Material any Shear (2 person operation)  2021-07-01T00:03:34.520Z  11229                         18  {'name': 'ANGLE-TRIM|2op|8"Max', 'id': '5f496b...          {'sONumber': 5308}
2                          900                             810                                      Folding  2021-07-01T00:03:34.520Z  11229                         18  {'name': 'ANGLE-TRIM|2op|8"Max', 'id': '5f496b...          {'sONumber': 5308}
normalization dataframe pandas json
1 Answers

json_normalize has its limitations. It seems to me that it isn't possible to convert it all in once to a flattened df. You could just add this line to achieve it:

# your code
df = pd.json_normalize(data=data['data']['jobs']['items'],
                        record_path=['itemsToMake','operations'],
                        meta=[
                            'createdUtc',
                            'number',
                            ['itemsToMake','quantityToMake'],
                            ['itemsToMake','itemReference'],
                            'salesOrderLineItemReference'
                        ]
                       )

# handle the 2 columns
res = df.join(df.pop(x).apply(pd.Series) for x in ['itemsToMake.itemReference', 'salesOrderLineItemReference'])

Output:

   estimatedSetupTimeInSeconds  estimatedDurationTimeInSeconds                               operation.name                createdUtc number itemsToMake.quantityToMake                  name                        id                                                                                                                       description  sONumber
0                          600                               0                                Pull Material  2021-07-01T00:03:34.520Z  11229                         18  ANGLE-TRIM|2op|8"Max  5f496b3bcb66432ca0c471f6  Angle trim with 2 operations - up to 8" Max SO | 10' sections (6 Sections per Sheet)\n032, 040, 050, 063, 26ga, 24ga, 22ga, 20ga      5308
1                          900                             720  Cut Material any Shear (2 person operation)  2021-07-01T00:03:34.520Z  11229                         18  ANGLE-TRIM|2op|8"Max  5f496b3bcb66432ca0c471f6  Angle trim with 2 operations - up to 8" Max SO | 10' sections (6 Sections per Sheet)\n032, 040, 050, 063, 26ga, 24ga, 22ga, 20ga      5308
2                          900                             810                                      Folding  2021-07-01T00:03:34.520Z  11229                         18  ANGLE-TRIM|2op|8"Max  5f496b3bcb66432ca0c471f6  Angle trim with 2 operations - up to 8" Max SO | 10' sections (6 Sections per Sheet)\n032, 040, 050, 063, 26ga, 24ga, 22ga, 20ga      5308
Rabinzel0
Related