一、需求
1.需求
想要从JAON文件转换成csv文件,进行数据处理。注意此json文件为多个json的合集。
2.测试json文件数据
{"t": "2017-12-20T10:53:51.582000+08:00", "a": 0, "b": 0, "c": 7, "d": 42, "e": 1, "g": 23, "h": 1}
{"t": "2017-12-20T10:53:51.582000+08:00", "a": 0, "b": 0, "c": 7, "d": 42, "e": 1}
{"t": "2017-12-20T10:53:51.582000+08:00", "a": 0, "b": 0, "c": 7, "d": 42, "e": 1}
{"t": "2017-12-20T10:53:51.582000+08:00", "a": 0, "b": 0, "c": 7, "d": 42, "e": 1, "g": 23, "h": 1}
二、代码一
import csv
import json
import sys
import collections # 有序字典
def trans(path):
jsonData=open(path+'.json')
#csvfile = open(path+'.csv', 'w')#此处这样写会导致写出来的文件会有空行
csvfile = open(path+'.csv', 'wb')#python2下
# csvfile = open(path+'.csv', 'w',newline='')#python3下
for line in jsonData:#获取属性列表
dic=json.loads(line[0:])
keys=dic.keys()
break
writer = csv.writer(csvfile)
writer.writerow(keys)#将属性列表写入csv中
for dic in jsonData:#读取json数据的每一行,将values数据一次一行的写入csv中
dic=json.loads(dic[0:])
writer.writerow(dic.values())
jsonData.close()
csvfile.close()
trans("./dic_test")
1.结果:
a c b e dg h t
0 7 01 42 2017-12-20T10:53:51.582000+08:00
0 7 01 42 2017-12-20T10:53:51.582000+08:00
0 7 01 42 23 1 2017-12-20T10:53:51.582000+08:00
数据错位了?什么情况?问题出现在哪里?
2.分析:
多个json由于每个json的key值不统一(有多有少),json是无序的,导致插入时候的value值无序,所以导致最后写入csv的值错乱。
三、代码二
import csv
import json
import sys
import collections # 有序字典
def trans(path):
jsonData=open(path+'.json')
#csvfile = open(path+'.csv', 'w')#此处这样写会导致写出来的文件会有空行
csvfile = open(path+'.csv', 'wb')#python2下
data = {}
keys_write = True
writer = csv.writer(csvfile)
for line in jsonData:#获取属性列表
dic=json.loads(line[0:-1])
keys=dic.keys()
break
for dic in jsonData:#读取json数据的每一行,将values数据一次一行的写入csv中
print(dic)
dic=json.loads(dic[0:])
for key in keys:
if dic.has_key(key):
data[key] = dic[key]
else:
data[key] = ""
print(data)
if keys_write == True:
writer.writerow(data.keys())
writer.writerow(data.values())
keys_write = False
jsonData.close()
csvfile.close()
trans("./dic_test")
结果:
a c b e dg h t
0 7 01 42 2017-12-20T10:53:51.582000+08:00
0 7 01 42 2017-12-20T10:53:51.582000+08:00
0 7 01 42 23 1 2017-12-20T10:53:51.582000+08:00
参考:
http://blog.csdn.net/qq_23926575/article/details/72788485