JSON到CSV Python和cStringI

Question

我正在尝试将JSON文件转换为CSV格式（在内存中），以便将其传递给Mulesoft中的另一个转换器。以下是JSON的一个片段：

[
{
"observationid": 1,
"fkey_observation": 1,
"value": 1,
"participantid": null,
"uom": "ppb",
"finishtime": 1008585047000,
"starttime": 1008581447000,
"observedproperty": "NO2",
"measuretime": 1008581567000,
"measurementid": 1,
"longitude": 3.1415,
"identifier": "Test-1",
"latitude": 10
},
{
"observationid": 1,
"fkey_observation": 1,
"value": 12,
"participantid": null,
"uom": "ppb",
"finishtime": 1008585047000,
"starttime": 1008581447000,
"observedproperty": "SO2",
"measuretime": 1008582047000,
"measurementid": 2,
"longitude": 5,
"identifier": "Test-1",
"latitude": 11
}
]

本质上，这应该创建一个包含2行的CSV（内存中），如下所示：

^{pr2}$

目前，输出结果是这样的，这是错误的：

[1  1   1    None    u'ppb'  1008585047000L  1008581447000L  u'NO2'  1008581567000L 1   3.1415   u'Test-1'   10]
[1  1   12   None    u'ppb'  1008585047000L  1008581447000L  u'SO2'  1008582047000L 2   5    u'Test-1'   11]

我相信'u'位表示Unicode，但我不知道如何更改编码。 任何帮助将不胜感激！在

以下是我目前掌握的Python代码：

import json
import cStringIO

f = open('test.json')
data = json.load(f) 
f.close()

output = cStringIO.StringIO()
for item in data:
    output.write(str([item['observationid'], item['fkey_observation'],     item['value'], item['participantid'], item['uom'], item['finishtime'], item['starttime'], item['observedproperty'], item['measuretime'],item['measurementid'], item['longitude'], item['identifier'], item['latitude']]) + '\n')

contents = output.getvalue()
print contents`

编辑

嗨，伙计们，计划有点改变。 实际上，我有一个String对象，但实际上它的结构类似于JSON文件：

"[{observationid=1, fkey_observation=1, value=1, participantid=null,   uom=ppb, finishtime=2001-12-17 10:30:47.0, starttime=2001-12-17 09:30:47.0, observedproperty=NO2, measuretime=2001-12-17 09:32:47.0, measurementid=1, longitude=3.1415, identifier=CITISENSE-Test-00000001, latitude=10}, {observationid=1, fkey_observation=1, value=12, participantid=null, uom=ppb, finishtime=2001-12-17 10:30:47.0, starttime=2001-12-17 09:30:47.0, observedproperty=SO2, measuretime=2001-12-17 09:40:47.0, measurementid=2, longitude=5, identifier=CITISENSE-Test-00000001, latitude=11}, {observationid=1, fkey_observation=1, value=7000, participantid=null, uom=ppb, finishtime=2001-12-17 10:30:47.0, starttime=2001-12-17 09:30:47.0, observedproperty=NO2, measuretime=2001-12-17 09:52:47.0, measurementid=3, longitude=6, identifier=CITISENSE-Test-00000001, latitude=9}, {observationid=2, fkey_observation=2, value=5, participantid=null, uom=ppb, finishtime=2001-12-18 10:30:47.0, starttime=2001-12-18 09:30:47.0, observedproperty=SO2, measuretime=2001-12-18 09:32:47.0, measurementid=4, longitude=7, identifier=CITISENSE-Test-00000001, latitude=8}, {observationid=2, fkey_observation=2, value=6, participantid=null, uom=ppb, finishtime=2001-12-18 10:30:47.0, starttime=2001-12-18 09:30:47.0, observedproperty=PM10, measuretime=2001-12-18 09:34:47.0, measurementid=5, longitude=8, identifier=CITISENSE-Test-00000001, latitude=10}, {observationid=3, fkey_observation=3, value=10000, participantid=null, uom=ppb, finishtime=2001-12-19 10:30:47.0, starttime=2001-12-19 09:30:47.0, observedproperty=SO2, measuretime=2001-12-19 09:38:47.0, measurementid=6, longitude=9,  identifier=CITISENSE-Test-00000001, latitude=11.2}]"

如何将其转换为CSV？我不能使用json模块，因为它不是json文件。在

Answer 1

我的方法是：使用^{}来处理从字典到一行CSV数据的转换：

import csv
import json
from cStringIO import StringIO

with open('test.json') as f:
    my_data = json.load(f)
    headers = [
        'observationid', 'fkey_observation', 'value',
        'participantid', 'uom', 'finishtime', 'starttime',
        'observedproperty', 'measuretime', 'measurementid',
        'longitude', 'identifier', 'latitude']

    buffer = StringIO()
    writer = csv.DictWriter(buffer, headers)

    for row in my_data:
        writer.writerow(row)
    print buffer.getvalue()

Answer 2

这是我写的一个小片段，我认为它应该处理您的场景，并给您一个列表列表。不过，埃雷利用这个模块做了些什么，也许会让你的生活更轻松。但同时也许这会有所帮助。在

import json
myFile =  open('myJson.json','r+')
myData = json.load(myFile)
myFile.close()

myList = []
for x in range(0,len(myData)):
    myList.append([])
    for key in myData[x].keys():
        value = myData[x][key]
        if isinstance(value,(str,unicode)):
            value = value.encode('ascii','ignore')
        myList[x].append(value)

print myList

Answer 3

您可能应该考虑使用csvwriter。它将为您处理转义和分隔符设置。在

参见Python3的示例：

import csv 
with open('output.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile, delimiter=',')
    for line in data:
        writer.writerow(line)

它也可以与cStringIO一起使用。在