Problems trying to create a data frame from a nested json

Admin 1
Viewed 46

I'm trying to split a json response in pandas into different columns, so far I have this but I don't find an effective way how to do it, I already tried several solutions but I can't separate the object correctly. I try pd.json_normalize, but it doesn't work correctly on the object.

This is a sample of the data, the structure of the json is a bit strange because sometimes financeInfo comes and sometimes it doesn't, other times financeInfoAttributes comes but without financeInfo.

{
  "codeId": "fc-5599",
  "financeInfo": [
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    }, 
    {
      "date": "2022-02-28",
       "totalReturn": -0.0424735070051586,
       "financeInfoAttributes": [
       {
         "attributeId": "a-256",
         "value": "12.032791372796499"
       },
       {
       "attributeId": "a-257",
       "value": "9.975964795996589"
       },
       {
       "attributeId": "a-258",
       "value": "4.719852927810759"
       },
       {
       "attributeId": "a-259",
       "value": "4.18144793134823"
       },     
    ]
}

After processing the object I have a result like this.

output example

This is part of the final code

 normalizedfinanceInfo = pd.json_normalize(financeInfo[0], max_level=1)
        
 financeInfoAndfinanceInfoAttributes = pd.merge(normalizedfinanceInfo , financeInfoAttributes, left_index=True, right_index=True, how='outer')
        
 result = pd.merge(codeId, financeInfoAndfinanceInfoAttributes , left_index=True, right_index=True, how='left')
        
 final_result = pd.DataFrame(result)

Any idea how to work with this nested json in other less complex ways. And be able to normalize this nested field?

pandas nested python json
1 Answers

Reconstruct the json format, unifying the required fields and setting them to nan if you don't have them

import pandas as pd
items = {
  "codeId": "fc-5599",
  "financeInfo": [
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-01-30",
      "totalReturn": 0.022425456852
    },
    {
      "date": "2022-02-28",
       "totalReturn": -0.0424735070051586,
       "financeInfoAttributes": [
       {
         "attributeId": "a-256",
         "value": "12.032791372796499"
       },
       {
       "attributeId": "a-257",
       "value": "9.975964795996589"
       },
       {
       "attributeId": "a-258",
       "value": "4.719852927810759"
       },
       {
       "attributeId": "a-259",
       "value": "4.18144793134823"
       },
    ]
}
  ]
}
new_items = []
for item in items["financeInfo"]:
    if "financeInfoAttributes" not in item:
        new_items.append(
            {"date": item.get("date"), "totalReturn": item.get("totalReturn")}
        )
    else:
        for item2 in item["financeInfoAttributes"]:
            new_items.append(
                {"date": item.get("date"), "totalReturn": item.get("totalReturn"),
                 "attributeId": item2.get("attributeId"), "value": item2.get("value")}
            )
print(new_items)
print(pd.DataFrame(new_items))

Output:

         date  totalReturn attributeId               value
0  2022-01-30     0.022425         NaN                 NaN
1  2022-01-30     0.022425         NaN                 NaN
2  2022-01-30     0.022425         NaN                 NaN
3  2022-01-30     0.022425         NaN                 NaN
4  2022-01-30     0.022425         NaN                 NaN
5  2022-02-28    -0.042474       a-256  12.032791372796499
6  2022-02-28    -0.042474       a-257   9.975964795996589
7  2022-02-28    -0.042474       a-258   4.719852927810759
8  2022-02-28    -0.042474       a-259    4.18144793134823
user_168744660
Related