Json转excel

Json转excel

1.准备环境
pip install pandas

准备数据

下文为 name.json内容：

{"time": "20220808", "name": "why", "age": "20"}
{"time": "20220809", "name": "duet", "age": "18"}
{"time": "20220810", "name": "wxm", "age": "18"}
{"time": "20220811", "name": "dsb", "age": "19"}

以下为python 代码：

import json
import pandas as pd

df = pd.read_json(r'/root/duet/python/json/name.json', encoding ='UTF-8', lines =True)
df.to_excel(r'/root/duet/python/excel/name.xlsx', index=False)

2.内嵌的 JSON 数据文件
以下为内嵌json 文件name1.json

{
    "RequestId": "9602330-D5B7-5616-A077-121212F",
    "class": "class 1",
    "Data": {
        "students": [
            {
                "id": "001",
                "name": "yhy",
                "math": 20,
                "physics": 10,
                "chemistry": 30
            },
            {
                "id": "002",
                "name": "duet",
                "math": 50,
                "physics": 77,
                "chemistry": 87
            },
            {
                "id": "003",
                "name": "ww",
                "math": 100,
                "physics": 90,
                "chemistry": 88
            }]
    }
}

以下为python代码

import pandas as pd
import json

with open(r'/root/duet/python/json/name1.json',encoding='UTF-8') as f:
    data = json.loads(f.read())
df = pd.json_normalize(
    data,
    record_path =['Data','students']
)
df.to_excel("output.xlsx")