Here’s an approach with Python script using pandas and json to transform your data frame into the required JSON structure.
import pandas as pd
import json
# Sample DataFrame
df = pd.DataFrame({
'type': ['customer'] * 15,
'customer_id': ['1-0000001'] * 4 + ['1-0000002'] * 6 + ['1-0000003'] * 5,
'email': ['[email protected]'] * 4 + ['[email protected]'] * 6 + ['[email protected]'] * 5,
'# of policies': [4] * 4 + [6] * 6 + [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': ['Αυτοκινήτου'] * 15
})
# Group by 'type' and 'customer_id'
grouped_data = []
for (cust_type, cust_id), group in df.groupby(['type', 'customer_id']):
attributes = {
"email": group['email'].iloc[0],
"# of policies": int(group['# of policies'].iloc[0]), # Convert to int
"policies details": group[['POLICY_NO', 'RECEIPT_NO', 'PAYMENT_CODE', 'KLADOS']].to_dict(orient='records')
}
grouped_data.append({
"type": cust_type,
"customer_id": cust_id,
"attributes": attributes
})
# Convert to JSON and save to file
json_output = json.dumps(grouped_data, indent=4, ensure_ascii=False)
# Print the output
print(json_output)
Group by type and customer_id → Ensures customers are uniquely identified.
Extract email and # of policies from the first row since these values are consistent within each group.
Convert policy details to a list of dictionaries using .to_dict(orient='records').
Store the structured data in a list.
Dump the JSON with indent=4 for readability and ensure_ascii=False to retain Greek characters.
[
{
"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": "Αυτοκινήτου"
}
]
}
}
]
I hope this information is helpful. Please let me know if it works for you or if you need any further clarification.