[Python] How to convert a data frame to JSON file using a nested structure within a repeating...

Discussão em 'Python' iniciado por Stack, Março 12, 2025.

  1. Stack

    Stack Membro Participativo

    My team asked me to convert a data frame to a JSON file with a particular nested structure. My data frame is the following one:

    df = pd.DataFrame({
    'type': ['customer','customer','customer','customer','customer','customer','customer','customer','customer','customer','customer','customer','customer','customer','customer'],
    'customer_id': ['1-0000001','1-0000001','1-0000001','1-0000001','1-0000002','1-0000002','1-0000002','1-0000002','1-0000002','1-0000002','1-0000003','1-0000003','1-0000003','1-0000003','1-0000003'],
    'email': ['customer1@otenet.gr','customer1@otenet.gr','customer1@otenet.gr','customer1@otenet.gr','customer2@gmail.com','customer2@gmail.com','customer2@gmail.com','customer2@gmail.com','customer2@gmail.com','customer2@gmail.com','customer3@yahoo.com.au','customer3@yahoo.com.au','customer3@yahoo.com.au','customer3@yahoo.com.au','customer3@yahoo.com.au'],
    '# of policies':[4,4,4,4,6,6,6,6,6,6,5,5,5,5,5],
    'POLICY_NO': ['000000001','000000002','000000003','000000004','000000005','000000006','000000007','000000008','000000009','000000010','000000011','000000012','000000013','000000014','000000015'],
    'RECEIPT_NO': [420000001,'420000002','420000003','420000004','420000005','420000006','420000007','420000008','420000009','420000010','420000011','420000012','420000013','420000014','420000015'],
    'PAYMENT_CODE': ['RF35000000000000000000001','RF35000000000000000000002','RF35000000000000000000003','RF35000000000000000000004','RF35000000000000000000005','RF35000000000000000000006','null','RF35000000000000000000008','RF35000000000000000000009','null','RF35000000000000000000011','RF35000000000000000000012','null','RF35000000000000000000014','RF35000000000000000000015'],
    'KLADOS': ['Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου','Αυτοκινήτου']
    })


    What I would like to achieve is creating a JSON file grouped by the column 'type', 'customer_id' and a dictionary that contains the fields 'email' and '# of policies'. These three elements are used for grouping. The attribute dictionary must contain another nested dictionary called 'policy details' with POLICY_NO, RECEIPT_NO, PAYMENT_CODE and KLADOS. The 'policies details' dictionary must be repeated for each combinations 'type', 'customer_id' and 'attributes' as follow:

    "type": "customer",
    "customer_id": "1-0000001",
    "attributes": {
    "email": "customer1@otenet.gr",
    "# of policies": 4,
    "policies details": [
    {
    "POLICY_NO": 000000001,
    "RECEIPT_NO": 420000001,
    "PAYMENT_CODE": "RF35000000000000000000001"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000002,
    "RECEIPT_NO": 420000002,
    "PAYMENT_CODE": "RF35000000000000000000002"
    "KLADOS": "Αυτοκινήτου"
    ......


    To achieve this format I tried the following ways:


    • 1st Method

      # Create the new 'policy_details' field as dictionary
      df['policy_details'] = df[['POLICY_NO', 'RECEIPT_NO', 'PAYMENT_CODE', 'KLADOS']].to_dict(orient='records')

      # Create the new 'attributes' field as dictionary with the nested 'policy_details'
      df['attributes'] = df[['email', '# of policies', 'policy_details']].to_dict(orient='records')


      # JSON Section

      # Create the JSON structure
      j = df[['type', 'customer_id', 'attributes']].to_json(orient='records', force_ascii = False)

      print(j)


      but

      1. 'type','customer_id' and attributes are not grouped (they are repeated for each policy_details)


      2. the 'policy_details' brackets are curly and not square as shown below:

        [{
        "type": "customer",
        "customer_id": "1-0000001",
        "attributes": {
        "email": "customer1@otenet.gr",
        "# of policies": 4,
        "policy_details": {
        "POLICY_NO": "000000001",
        "RECEIPT_NO": 420000001,
        "PAYMENT_CODE": "RF35000000000000000000001",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000001",
        "attributes": {
        "email": "customer1@otenet.gr",
        "# of policies": 4,
        "policy_details": {
        "POLICY_NO": "000000002",
        "RECEIPT_NO": "420000002",
        "PAYMENT_CODE": "RF35000000000000000000002",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000001",
        "attributes": {
        "email": "customer1@otenet.gr",
        "# of policies": 4,
        "policy_details": {
        "POLICY_NO": "000000003",
        "RECEIPT_NO": "420000003",
        "PAYMENT_CODE": "RF35000000000000000000003",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000001",
        "attributes": {
        "email": "customer1@otenet.gr",
        "# of policies": 4,
        "policy_details": {
        "POLICY_NO": "000000004",
        "RECEIPT_NO": "420000004",
        "PAYMENT_CODE": "RF35000000000000000000004",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": {
        "email": "customer2@gmail.com",
        "# of policies": 6,
        "policy_details": {
        "POLICY_NO": "000000005",
        "RECEIPT_NO": "420000005",
        "PAYMENT_CODE": "RF35000000000000000000005",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": {
        "email": "customer2@gmail.com",
        "# of policies": 6,
        "policy_details": {
        "POLICY_NO": "000000006",
        "RECEIPT_NO": "420000006",
        "PAYMENT_CODE": "RF35000000000000000000006",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": {
        "email": "customer2@gmail.com",
        "# of policies": 6,
        "policy_details": {
        "POLICY_NO": "000000007",
        "RECEIPT_NO": "420000007",
        "PAYMENT_CODE": "null",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": {
        "email": "customer2@gmail.com",
        "# of policies": 6,
        "policy_details": {
        "POLICY_NO": "000000008",
        "RECEIPT_NO": "420000008",
        "PAYMENT_CODE": "RF35000000000000000000008",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": {
        "email": "customer2@gmail.com",
        "# of policies": 6,
        "policy_details": {
        "POLICY_NO": "000000009",
        "RECEIPT_NO": "420000009",
        "PAYMENT_CODE": "RF35000000000000000000009",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": {
        "email": "customer2@gmail.com",
        "# of policies": 6,
        "policy_details": {
        "POLICY_NO": "000000010",
        "RECEIPT_NO": "420000010",
        "PAYMENT_CODE": "null",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000003",
        "attributes": {
        "email": "customer3@yahoo.com.au",
        "# of policies": 5,
        "policy_details": {
        "POLICY_NO": "000000011",
        "RECEIPT_NO": "420000011",
        "PAYMENT_CODE": "RF35000000000000000000011",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000003",
        "attributes": {
        "email": "customer3@yahoo.com.au",
        "# of policies": 5,
        "policy_details": {
        "POLICY_NO": "000000012",
        "RECEIPT_NO": "420000012",
        "PAYMENT_CODE": "RF35000000000000000000012",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000003",
        "attributes": {
        "email": "customer3@yahoo.com.au",
        "# of policies": 5,
        "policy_details": {
        "POLICY_NO": "000000013",
        "RECEIPT_NO": "420000013",
        "PAYMENT_CODE": "null",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000003",
        "attributes": {
        "email": "customer3@yahoo.com.au",
        "# of policies": 5,
        "policy_details": {
        "POLICY_NO": "000000014",
        "RECEIPT_NO": "420000014",
        "PAYMENT_CODE": "RF35000000000000000000014",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }, {
        "type": "customer",
        "customer_id": "1-0000003",
        "attributes": {
        "email": "customer3@yahoo.com.au",
        "# of policies": 5,
        "policy_details": {
        "POLICY_NO": "000000015",
        "RECEIPT_NO": "420000015",
        "PAYMENT_CODE": "RF35000000000000000000015",
        "KLADOS": "Αυτοκινήτου"
        }
        }
        }
        ]

    • 2nd Method

      # Create the new 'attributes' field as dictionary
      df['attributes'] = df[['email', '# of policies']].to_dict(orient='records')

      # Convert the dictionary 'attributes' to a string
      df['attributes'] = df[['attributes']].astype(str)


      # JSON Section

      # Create the JSON structure
      j = (df.groupby(['type', 'customer_id', 'attributes'])
      .apply(lambda x: x[['POLICY_NO', 'RECEIPT_NO','PAYMENT_CODE', 'KLADOS']].to_dict('records'), include_groups=False)
      .reset_index()
      .rename(columns={0:'policies details'})
      .to_dict(orient='records')
      )

      # Convert list of dictionaries to JSON
      json_output = json.dumps(j, indent=4, ensure_ascii=False)

      print(json_output)


      This time:

      1. 'type', 'customer_id' and 'attributes' are finally grouped but 'attributes' is represented like a string and not like a dictionary (key:value pairs)


      2. the 'policy_details' has square brackets but it's not nested (it's at the same level of 'attributes' as shown below:

        [
        {
        "type": "customer",
        "customer_id": "1-0000001",
        "attributes": "{'email': 'customer1@otenet.gr', '# of policies': 4}",
        "policies details": [
        {
        "POLICY_NO": "000000001",
        "RECEIPT_NO": 420000001,
        "PAYMENT_CODE": "RF35000000000000000000001",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000002",
        "RECEIPT_NO": "420000002",
        "PAYMENT_CODE": "RF35000000000000000000002",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000003",
        "RECEIPT_NO": "420000003",
        "PAYMENT_CODE": "RF35000000000000000000003",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000004",
        "RECEIPT_NO": "420000004",
        "PAYMENT_CODE": "RF35000000000000000000004",
        "KLADOS": "Αυτοκινήτου"
        }
        ]
        },
        {
        "type": "customer",
        "customer_id": "1-0000002",
        "attributes": "{'email': 'customer2@gmail.com', '# of policies': 6}",
        "policies details": [
        {
        "POLICY_NO": "000000005",
        "RECEIPT_NO": "420000005",
        "PAYMENT_CODE": "RF35000000000000000000005",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000006",
        "RECEIPT_NO": "420000006",
        "PAYMENT_CODE": "RF35000000000000000000006",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000007",
        "RECEIPT_NO": "420000007",
        "PAYMENT_CODE": "null",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000008",
        "RECEIPT_NO": "420000008",
        "PAYMENT_CODE": "RF35000000000000000000008",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000009",
        "RECEIPT_NO": "420000009",
        "PAYMENT_CODE": "RF35000000000000000000009",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000010",
        "RECEIPT_NO": "420000010",
        "PAYMENT_CODE": "null",
        "KLADOS": "Αυτοκινήτου"
        }
        ]
        },
        {
        "type": "customer",
        "customer_id": "1-0000003",
        "attributes": "{'email': 'customer3@yahoo.com.au', '# of policies': 5}",
        "policies details": [
        {
        "POLICY_NO": "000000011",
        "RECEIPT_NO": "420000011",
        "PAYMENT_CODE": "RF35000000000000000000011",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000012",
        "RECEIPT_NO": "420000012",
        "PAYMENT_CODE": "RF35000000000000000000012",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000013",
        "RECEIPT_NO": "420000013",
        "PAYMENT_CODE": "null",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000014",
        "RECEIPT_NO": "420000014",
        "PAYMENT_CODE": "RF35000000000000000000014",
        "KLADOS": "Αυτοκινήτου"
        },
        {
        "POLICY_NO": "000000015",
        "RECEIPT_NO": "420000015",
        "PAYMENT_CODE": "RF35000000000000000000015",
        "KLADOS": "Αυτοκινήτου"
        }
        ]
        }
        ]

    Below the JSON structure I would like to achieve.

    [
    {
    "type": "customer",
    "customer_id": "1-0000001",
    "attributes": {
    "email": "customer1@otenet.gr",
    "# of policies": 4,
    "policies details": [
    {
    "POLICY_NO": 000000001,
    "RECEIPT_NO": 420000001,
    "PAYMENT_CODE": "RF35000000000000000000001"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000002,
    "RECEIPT_NO": 420000002,
    "PAYMENT_CODE": "RF35000000000000000000002"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000003,
    "RECEIPT_NO": 420000003,
    "PAYMENT_CODE": "RF35000000000000000000003"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000004,
    "RECEIPT_NO": 420000004,
    "PAYMENT_CODE": "RF35000000000000000000004"
    "KLADOS": "Αυτοκινήτου"
    }
    ]
    }
    },
    {
    "type": "customer",
    "customer_id": "1-0000002",
    "attributes": {
    "email": "customer2@gmail.com",
    "# of policies": 6,
    "policies details": [
    {
    "POLICY_NO": 000000005,
    "RECEIPT_NO": 420000005,
    "PAYMENT_CODE": "RF35000000000000000000005"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000006,
    "RECEIPT_NO": 420000006,
    "PAYMENT_CODE": "RF35000000000000000000006"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000007,
    "RECEIPT_NO": 420000007,
    "PAYMENT_CODE": "null"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000008,
    "RECEIPT_NO": 420000008,
    "PAYMENT_CODE": "RF35000000000000000000008"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000009,
    "RECEIPT_NO": 420000009,
    "PAYMENT_CODE": "RF35000000000000000000009"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000010,
    "RECEIPT_NO": 420000010,
    "PAYMENT_CODE": "null"
    "KLADOS": "Αυτοκινήτου"
    }
    ]
    }
    },
    {
    "type": "customer",
    "customer_id": "1-0000003",
    "attributes": {
    "email": "customer3@yahoo.com.au",
    "# of policies": 5,
    "policies details": [
    {
    "POLICY_NO": 000000011,
    "RECEIPT_NO": 420000011,
    "PAYMENT_CODE": "RF35000000000000000000011"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000012,
    "RECEIPT_NO": 420000012,
    "PAYMENT_CODE": "RF35000000000000000000012"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000013,
    "RECEIPT_NO": 420000013,
    "PAYMENT_CODE": "null"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000014,
    "RECEIPT_NO": 420000014,
    "PAYMENT_CODE": "RF35000000000000000000014"
    "KLADOS": "Αυτοκινήτου"
    },
    {
    "POLICY_NO": 000000015,
    "RECEIPT_NO": 420000015,
    "PAYMENT_CODE": "RF35000000000000000000015"
    "KLADOS": "Αυτοκινήτου"
    }
    ]
    }
    }
    ]

    Continue reading...
    Stack, Março 12, 2025
    #1

Compartilhe esta Página