利用Python将Json数据转为表格并写入Excel
程序员文章站
2022-07-08 09:40:56
...
一、准备数据源
注意:每一行都有一个中括号[ ]。
[{"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变量是这样子的:
三、最后转换得到的excel表
下一篇: HDU 6370(并查集)
推荐阅读
-
利用python对Excel中的特定数据提取并写入新表的方法
-
Python将数据写入excel表格中保存
-
利用Python将Json数据转为表格并写入Excel
-
利用Python实现读取Word表格计算汇总并写入Excel
-
python 将数据写入json并解决乱码问题
-
利用python对Excel中的特定数据提取并写入新表的方法
-
Python读取Excel中符合特定条件的数据,并写入新的表格中
-
Python将数据写入excel表格中保存
-
利用Python 将csv数据以字典形式写入json 知识点【csv和json文件操作,有序字典】
-
利用Python实现读取Word表格计算汇总并写入Excel