pandas 如何使用Python将此嵌套JSON转换为Excel或CSV文件

a0x5cqrl 于 2023-05-05 发布在 Python

关注(0)|答案(2)|浏览(786)

所以我有这个嵌套的JSON，我正在努力转换成表格形式，因为它的形状，它可能会成为一个.xslx或. csv。
我试着这样做，但我没有工作。

# 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)

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
            }
        }
    ]
}

理想情况下，输出应该如下所示：

有什么建议可以用Python来转换它吗？可以是pandas或任何其他模块。

pandas

来源：https://stackoverflow.com/questions/76173731/how-to-convert-this-nested-json-into-an-excel-or-csv-file-using-python

2条答案

按热度按时间

jyztefdp1#

扩展到Corralien所写的内容，尝试这样做：

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')

这是在假设service.services列下总是有一个列表的情况下工作的。
输出：

|    |   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                    |                          |

赞(0）回复(0）举报 2023-05-05

bvpmtnay2#

您可以迭代第一级记录以创建单个 Dataframe ，然后将它们连接起来以获得预期的输出：

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
   Price  category  service.id service.name service.description  ... creditTO.duration  creditTO.Type creditTO.Tax  creditTO.total  creditTO.promotion
A    200       620          15          KAL         Description  ...                 6       standard           51             400               False
B    200       620          15          BTX         Description  ...                 9       standard           51             400               False
C    600       620          15          FLS         Description  ...                12       standard           51             400               False
D    705       620          15          TRW         Description  ...                18       standard           67             245               False

赞(0）回复(0）举报 2023-05-05

我来回答

pandas 如何使用Python将此嵌套JSON转换为Excel或CSV文件

2条答案

相关问题

热门标签

最新问答