利用Python将Json数据转为表格并写入Excel

小鱼儿 2021-07-04 20:53 827阅读 0赞

## 一、准备数据源 ##

注意：每一行都有一个中括号\[ \]。

[{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783825903,"scanDateStr":"2019-03-28 14:37:05","value":111.1,"interval":10}]
    [{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783836097,"scanDateStr":"2019-03-28 14:37:16","value":107.4,"interval":10}]
    [{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783846322,"scanDateStr":"2019-03-28 14:37:26","value":107.9,"interval":10},{ "name":"风速(act.)","id":"WS","unit":"m/s","device":"weather","timestamp":1553783846322,"scanDateStr":"2019-03-28 14:37:26","value":2.2,"interval":10}]
    [{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783856534,"scanDateStr":"2019-03-28 14:37:36","value":94.1,"interval":10},{ "name":"风速(act.)","id":"WS","unit":"m/s","device":"weather","timestamp":1553783856534,"scanDateStr":"2019-03-28 14:37:36","value":1.1,"interval":10}]
    [{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783876925,"scanDateStr":"2019-03-28 14:37:56","value":122.6,"interval":10},{ "name":"风速(act.)","id":"WS","unit":"m/s","device":"weather","timestamp":1553783876925,"scanDateStr":"2019-03-28 14:37:56","value":1.4,"interval":10}]
    [{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783887103,"scanDateStr":"2019-03-28 14:38:07","value":132.8,"interval":10},{ "name":"风速(act.)","id":"WS","unit":"m/s","device":"weather","timestamp":1553783887103,"scanDateStr":"2019-03-28 14:38:07","value":1.4,"interval":20}]
    [{ "name":"风向(act.)","id":"WD","device":"weather","timestamp":1553783897281,"scanDateStr":"2019-03-28 14:38:17","value":122.2,"interval":10},{ "name":"风速(act.)","id":"WS","unit":"m/s","device":"weather","timestamp":1553783897281,"scanDateStr":"2019-03-28 14:38:17","value":2.7,"interval":31}]

## 二、编写Python脚本 ##

# -*- coding: utf-8 -*-
    
    import json
    import pandas as pd
    
    data = [] # 用于存储每一行的Json数据
    with open('./data/temp2.txt','r', encoding = 'UTF-8') as fr:
        for line in fr:
            j = json.loads(line)
            data.append(j)
    
    df = pd.DataFrame() # 最后转换得到的结果
    for line in data:
        for i in line:
            df1 = pd.DataFrame([i])
            df = df.append(df1)
    
    # 在excel表格的第1列写入, 不写入index
    df.to_excel('data.xlsx', sheet_name='Data', startcol=0, index=False)

运行结果中data变量是这样子的：  
![在这里插入图片描述][20201009182801323.png]

## 三、最后转换得到的excel表 ##

![在这里插入图片描述][20201009182842311.png]

--------------------

# python实现`json`转`excel` #

import json
    
    import xlwt
    
    a = json.load(open("haha.json", encoding='utf8'))
    title = list(set([j for i in a for j in i]))
    book = xlwt.Workbook()
    sheet = book.add_sheet('Sheet1', cell_overwrite_ok=True)  # 添加一个sheet页
    for i in range(len(title)):  # 循环列
        sheet.write(0, i, title[i])  # 将title数组中的字段写入到0行i列中
    for i, it in enumerate(a):
        for j, k in enumerate(title):
            sheet.write(1 + i, j, it[k])
    book.save('haha.xls')

[20201009182801323.png]: /images/20210704/1c9c8e2610804f0a9a1c7219811dc8b6.png
[20201009182842311.png]: /images/20210704/4c7d652e1dd54492a712dc26a8024a96.png