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': ['[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]'],
'# 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": "[email protected]",
"# 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
'type','customer_id' and attributes are not grouped (they are repeated for each policy_details)
the 'policy_details' brackets are curly and not square as shown below:
[{ "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000001", "RECEIPT_NO": 420000001, "PAYMENT_CODE": "RF35000000000000000000001", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000002", "RECEIPT_NO": "420000002", "PAYMENT_CODE": "RF35000000000000000000002", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000003", "RECEIPT_NO": "420000003", "PAYMENT_CODE": "RF35000000000000000000003", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000004", "RECEIPT_NO": "420000004", "PAYMENT_CODE": "RF35000000000000000000004", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000005", "RECEIPT_NO": "420000005", "PAYMENT_CODE": "RF35000000000000000000005", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000006", "RECEIPT_NO": "420000006", "PAYMENT_CODE": "RF35000000000000000000006", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000007", "RECEIPT_NO": "420000007", "PAYMENT_CODE": "null", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000008", "RECEIPT_NO": "420000008", "PAYMENT_CODE": "RF35000000000000000000008", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000009", "RECEIPT_NO": "420000009", "PAYMENT_CODE": "RF35000000000000000000009", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000010", "RECEIPT_NO": "420000010", "PAYMENT_CODE": "null", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000011", "RECEIPT_NO": "420000011", "PAYMENT_CODE": "RF35000000000000000000011", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000012", "RECEIPT_NO": "420000012", "PAYMENT_CODE": "RF35000000000000000000012", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000013", "RECEIPT_NO": "420000013", "PAYMENT_CODE": "null", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000014", "RECEIPT_NO": "420000014", "PAYMENT_CODE": "RF35000000000000000000014", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# 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:
'type', 'customer_id' and 'attributes' are finally grouped but 'attributes' is represented like a string and not like a dictionary (key:value pairs)
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': '[email protected]', '# 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': '[email protected]', '# 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': '[email protected]', '# 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": "[email protected]",
"# 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": "[email protected]",
"# 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": "[email protected]",
"# 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": "Αυτοκινήτου"
}
]
}
}
]
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': ['[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]','[email protected]'],
'# 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": "[email protected]",
"# 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
'type','customer_id' and attributes are not grouped (they are repeated for each policy_details)
the 'policy_details' brackets are curly and not square as shown below:
[{ "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000001", "RECEIPT_NO": 420000001, "PAYMENT_CODE": "RF35000000000000000000001", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000002", "RECEIPT_NO": "420000002", "PAYMENT_CODE": "RF35000000000000000000002", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000003", "RECEIPT_NO": "420000003", "PAYMENT_CODE": "RF35000000000000000000003", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000001", "attributes": { "email": "[email protected]", "# of policies": 4, "policy_details": { "POLICY_NO": "000000004", "RECEIPT_NO": "420000004", "PAYMENT_CODE": "RF35000000000000000000004", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000005", "RECEIPT_NO": "420000005", "PAYMENT_CODE": "RF35000000000000000000005", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000006", "RECEIPT_NO": "420000006", "PAYMENT_CODE": "RF35000000000000000000006", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000007", "RECEIPT_NO": "420000007", "PAYMENT_CODE": "null", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000008", "RECEIPT_NO": "420000008", "PAYMENT_CODE": "RF35000000000000000000008", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000009", "RECEIPT_NO": "420000009", "PAYMENT_CODE": "RF35000000000000000000009", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000002", "attributes": { "email": "[email protected]", "# of policies": 6, "policy_details": { "POLICY_NO": "000000010", "RECEIPT_NO": "420000010", "PAYMENT_CODE": "null", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000011", "RECEIPT_NO": "420000011", "PAYMENT_CODE": "RF35000000000000000000011", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000012", "RECEIPT_NO": "420000012", "PAYMENT_CODE": "RF35000000000000000000012", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000013", "RECEIPT_NO": "420000013", "PAYMENT_CODE": "null", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# of policies": 5, "policy_details": { "POLICY_NO": "000000014", "RECEIPT_NO": "420000014", "PAYMENT_CODE": "RF35000000000000000000014", "KLADOS": "Αυτοκινήτου" } } }, { "type": "customer", "customer_id": "1-0000003", "attributes": { "email": "[email protected]", "# 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:
'type', 'customer_id' and 'attributes' are finally grouped but 'attributes' is represented like a string and not like a dictionary (key:value pairs)
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': '[email protected]', '# 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': '[email protected]', '# 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': '[email protected]', '# 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": "[email protected]",
"# 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": "[email protected]",
"# 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": "[email protected]",
"# 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": "Αυτοκινήτου"
}
]
}
}
]
1 Answer
Reset to default 0Here is an approach you can try for the required format using pandas groupby
Hope this helps, Let me know if it worked for you.
import json
list_of_cust= []
// Group By on require columns
for x, y in df.groupby(by=["type", "customer_id",'email', '# of policies']):
// selecting required columns on group By
policy = y[['POLICY_NO', 'RECEIPT_NO', 'PAYMENT_CODE','KLADOS']]
obj = (x, policy)
list_of_cust.append(obj)
jsonstr = "["
for x in list_of_cust:
str = f''' {{ "type": "{x[0][0]}",
"customer_id": "{x[0][1]}",
"attributes": {{
"email": "{x[0][2]}",
"# of policies": {x[0][3]},
"policies details": {json.dumps(x[1].to_dict(orient="records"), indent=4, ensure_ascii=False)}
}}
}},'''
jsonstr= jsonstr + str
jsonstr= jsonstr[0:len(jsonstr)-1] + "]"