admin管理员组文章数量:1394077
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] + "]"
本文标签：
版权声明：本文标题：python - How to convert a data frame to JSON file using a nested structure within a repeating group - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1744748350a2623023.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论