Python 利用 Pandas 将 json 文件转为 excel

 

这是针对一行一条json的数据，并且json数据格式都一样的情况

import json
import pandas as pd


def trans():
    df_data = pd.read_json("clean_data.txt", lines=True)
    # with pd.ExcelWriter('value_app_result.xlsx', engine='xlsxwriter', options={'strings_to_urls': False}) as writer:
    with pd.ExcelWriter('value_app_result.xlsx', engine='xlsxwriter', engine_kwargs={'options': {'encoding': 'utf-8'}}) as writer:
        df_data.to_excel(writer, index=False)

 

 如果整体数据就只有一条json数据，这个该怎么转化呢

{
    "all": {
        "2022": 141175,
        "2021": 141260,
        "2020": 141212
    },
    "man": {
        "2022": 72206,
        "2021": 72311,
        "2020": 72357
    },
    "women": {
        "2022": 68969,
        "2021": 68949,
        "2020": 68855
    },
    "town": {
        "2022": 92071,
        "2021": 91425,
        "2020": 90220
    },
    "village": {
        "2022": 49104,
        "2021": 49835,
        "2020": 5099
    }
}

代码：

import pandas as pd

df = pd.read_json("result.json.json", orient='records')
print(df)
df.to_excel('pandas处理ceshi-json.xlsx', index=True, columns=["all", "man","women","town","village"])

 写成csv同样的道理

import pandas as pd

df = pd.read_json("result.json", orient='records')
print(df)
df.to_csv('pandas处理ceshi-json.csv', index=True, columns=["all", "man", "women", "town", "village"])

 

------------------------------------------------------分割线---------------------------------------------------------------------------------------------------------- 

把excel转成json数据

    import pandas as pd
    import numpy

    df = pd.read_excel("14-16号推送数据.xlsx")

    # orient有index，colunm，records三种模式
    json_data = df.to_json(orient="records")

    result = json.loads(json_data)
    print(result)
    with open("www", "w", encoding="utf-8") as writer:
        for i in result:
            writer.write(json.dumps(i, ensure_ascii=False) + "\n")