pythonjsonpandasexport-to-csv
How to convert this nested JSON into an Excel or CSV file using Python
So I have this nested json and I'm struggling with the conversion into a tabular form because of how it's shaped, it could become a .xslx or .csv.
I tried doing it like this but I didn't work.
# Load the JSON data into a pandas DataFrame
df = pd.read_json(response_data, typ='series')
# Convert the DataFrame to a flattened dictionary
flat_dict = pd.json_normalize(df.to_dict())
# Export the flattened dictionary to an Excel file
flat_dict.to_excel('output.xlsx', index=False)
The json:
{
"A": [
{
"Price": 200,
"category": 620,
"service": {
"id": 15,
"name": "KAL",
"description": "Description",
"Validity": null,
"order": 0,
"services": [
{
"id": 100,
"financeable": true,
"benefit": {
"id": 235,
"name": "ZSX",
"Priced": null
},
"Execution": null,
"serviceId": 112,
"label": "Colab"
}
],
"selection": false
},
"creditTO": {
"id": 0,
"duration": 6,
"Type": "standard",
"Tax": 51,
"total": 400,
"promotion": false
}
}
],
"B": [
{
"Price": 200,
"category": 620,
"service": {
"id": 15,
"name": "BTX",
"description": "Description",
"Validity": null,
"order": 0,
"services": [
{
"id": 100,
"financeable": true,
"benefit": {
"id": 235,
"name": "ZSX",
"Priced": null
},
"Execution": null,
"serviceId": 112,
"label": "Colab"
}
],
"selection": false
},
"creditTO": {
"id": 0,
"duration": 9,
"Type": "standard",
"Tax": 51,
"total": 400,
"promotion": false
}
}
],
"C": [
{
"Price": 600,
"category": 620,
"service": {
"id": 15,
"name": "FLS",
"description": "Description",
"Validity": null,
"order": 0,
"services": [
{
"id": 100,
"financeable": true,
"benefit": {
"id": 235,
"name": "ZSX",
"Priced": null
},
"Execution": null,
"serviceId": 112,
"label": "Colab"
}
],
"selection": false
},
"creditTO": {
"id": 0,
"duration": 12,
"Type": "standard",
"Tax": 51,
"total": 400,
"promotion": false
}
}
],
"D": [
{
"Price": 705,
"category": 620,
"service": {
"id": 15,
"name": "TRW",
"description": "Description",
"Validity": null,
"order": 0,
"services": [
{
"id": 100,
"financeable": true,
"benefit": {
"id": 235,
"name": "ZSX",
"Priced": null
},
"Execution": null,
"serviceId": 112,
"label": "Colab"
}
],
"selection": false
},
"creditTO": {
"id": 0,
"duration": 18,
"Type": "standard",
"Tax": 67,
"total": 245,
"promotion": false
}
}
]
}
Ideally, the output should look like this:
Any suggestion on how to transform it using Python ? Could be pandas or any other module.
Solution
Expanding to what Corralien wrote, try something like this:
import json
import pandas as pd
data = json.loads(response_json)
df = pd.concat({k: pd.json_normalize(v) for k, v in data.items()}).droplevel(1)
df = pd.concat((df, pd.concat({k: pd.json_normalize(v) for k, v in df['service.services'].items()}).droplevel(1).add_prefix('service.')), axis=1).drop(columns='service.services')
This works under the assumption you will always have a list under the service.services column.
Output:
| | Price | category | service.id | service.name | service.description | service.Validity | service.order | service.selection | creditTO.id | creditTO.duration | creditTO.Type | creditTO.Tax | creditTO.total | creditTO.promotion | service.id | service.financeable | service.Execution | service.serviceId | service.label | service.benefit.id | service.benefit.name | service.benefit.Priced |
|:---|--------:|-----------:|-------------:|:---------------|:----------------------|:-------------------|----------------:|:--------------------|--------------:|--------------------:|:----------------|---------------:|-----------------:|:---------------------|-------------:|:----------------------|:--------------------|--------------------:|:----------------|---------------------:|:-----------------------|:-------------------------|
| A | 200 | 620 | 15 | KAL | Description | | 0 | False | 0 | 6 | standard | 51 | 400 | False | 100 | True | | 112 | Colab | 235 | ZSX | |
| B | 200 | 620 | 15 | BTX | Description | | 0 | False | 0 | 9 | standard | 51 | 400 | False | 100 | True | | 112 | Colab | 235 | ZSX | |
| C | 600 | 620 | 15 | FLS | Description | | 0 | False | 0 | 12 | standard | 51 | 400 | False | 100 | True | | 112 | Colab | 235 | ZSX | |
| D | 705 | 620 | 15 | TRW | Description | | 0 | False | 0 | 18 | standard | 67 | 245 | False | 100 | True | | 112 | Colab | 235 | ZSX | |