logo标签列表

如何在Python中将JSON文件解析成CSV文件时获取横向输出

pythonjsoncsvparsing
3

我希望使用Python将我的JSON文件转换为CSV文件。我已经编写了一个脚本，但是无法正常工作。我的JSON模板如下：

{
 "-K6v8Ht6nXCjaO_ApNGr" : {
"key1" : 0,
"key2" : false,
"key3" : 0,
"key4" : 0,
"key5" : "01/01/2016 04:04",
"key" : "-K6v8Ht6nXCjaO_ApNGr",
"key6" : "01/01/2016 04:04",
"key7" : 23,
"key8" : 0,
"key9" : {
  "date" : 1,
  "day" : 5,
  "hours" : 4,
  "minutes" : 4,
  "month" : 0,
  "seconds" : 56,
  "time" : 1451617496647,
  "timezoneOffset" : -60,
  "year" : 116
},
"triedConnector" : {
  "OPENSL" : -1,
  "SAPA" : -1,
  "USB" : -1,
  "USB_7" : -1
},......

我的脚本（V3.x）：

import json,csv,sys
# -*- coding:utf-8 -*-

fileInput = sys.argv[1] 
fileOutput = sys.argv[2]

inputFile = open(fileInput,'r') #open json file
outputFile = open(fileOutput, 'w+') #load csv file

data = json.load(inputFile) #load json content

inputFile.close() #close the input file

csv_file = csv.writer(outputFile)

csv_file.writerow(["header 0", "header 1", "header 2", "header 3", "header 4"]) #headers

for value in data.values():
  for v in value.values():
    csv_file.writerow([unicode(v).encode('utf-8')])


for value in data.values():
  for v in value.values():
    for z in v.values():
        csv_file.writerow([unicode(z).encode('utf-8')])

outputFile.close()#close the output file

在这个脚本中，第一个for循环应该打印如下：
-K6v8Ht6nXCjaO_ApNGr
-K6v8Ht6nXCjaO_ApNGr
-K6v8Ht6nXCjaO_ApNGr

我的第一个循环运行得非常完美，但不幸的是，我的第二个循环把这个输出写入了我的CSV文件。

-KjR-JvTm3BKVkYWoyTV
-Kj5ZdfgX8lLLi-htqPU
-KkC4t0XQoJDViNqt18n

False

"{u'seconds': 18, u'year': 117, u'month': 4, u'hours': 8, u'time': 1494223818226L, u'date': 8, u'minutes': 10, u'day': 1, u'timezoneOffset': -120}"

0
08/05/2017 08:10
"{u'OPENSL': -1, u'USB_7': -1, u'SAPA': -1, u'USB': -1}"
08/05/2017 08:10
-KjafVrmb_wuwb36xNq0
0
23
0
0

我想要一个水平输出，同时也需要针对嵌套对象的第三个循环。但是当我尝试运行最后一个循环时，出现了以下错误：

AttributeError: 'bool' object has no attribute 'values'.

简而言之，我的问题在于我需要像这样的水平输出：

K6v8Ht6nXCjaO_ApNGr 0, false, 0, 0, 01/01/2016 04:04, -K6v8Ht6nXCjaO_ApNGr K6v8Ht6nXCjaO_ApNGr 0, false, 0, 0, 01/01/2016 04:04, -K6v8Ht6nXCjaO_ApNGr

所以请问有谁能帮助我理解并解决这些问题呢？谢谢。
输出应该是什么？ - Bill Bell
@BillBell 先生，像这样 K6v8Ht6nXCjaO_ApNGr 0, false, 0, 0, 01/01/2016 04:04, -K6v8Ht6nXCjaO_ApNGr K6v8Ht6nXCjaO_ApNGr 0, false, 0, 0, 01/01/2016 04:04, -K6v8Ht6nXCjaO_ApNGr。 - Mustafa Demir
3个回答
2

这段代码似乎可以满足您的需求，除了csv字段的顺序不同。通常这并不重要。我还尝试让代码解释它正在做什么。

json是您在问题中提供的三个副本，稍作修改以正确编译，并更改了主键。

json_txt = '''\
{
"-K6v8Ht6nXCjaO_ApNGr" : {
    "key1" : 0, "key2" : false, "key3" : 0, "key4" : 0, "key5" : "01/01/2016 04:04",
    "key" : "-K6v8Ht6nXCjaO_ApNGr", "key6" : "01/01/2016 04:04", "key7" : 23,
    "key8" : 0,
    "key9" : { "date" : 1, "day" : 5, "hours" : 4, "minutes" : 4, "month" : 0,
        "seconds" : 56, "time" : 1451617496647, "timezoneOffset" : -60, "year" : 116},
    "triedConnector" : { "OPENSL" : -1, "SAPA" : -1, "USB" : -1, "USB_7" : -1}
    },
"-K6v8Ht6nXCjaO_2" : {
    "key1" : 0, "key2" : false, "key3" : 0, "key4" : 0, "key5" : "01/01/2016 04:04",
    "key" : "-K6v8Ht6nXCjaO_ApNGr", "key6" : "01/01/2016 04:04", "key7" : 23,
    "key8" : 0,
    "key9" : { "date" : 1, "day" : 5, "hours" : 4, "minutes" : 4, "month" : 0,
        "seconds" : 56, "time" : 1451617496647, "timezoneOffset" : -60, "year" : 116},
    "triedConnector" : { "OPENSL" : -1, "SAPA" : -1, "USB" : -1, "USB_7" : -1}
    },
"-K6v8Ht6nXCjaO_3" : {
    "key1" : 0, "key2" : false, "key3" : 0, "key4" : 0, "key5" : "01/01/2016 04:04",
    "key" : "-K6v8Ht6nXCjaO_ApNGr", "key6" : "01/01/2016 04:04", "key7" : 23,
    "key8" : 0,
    "key9" : { "date" : 1, "day" : 5, "hours" : 4, "minutes" : 4, "month" : 0,
        "seconds" : 56, "time" : 1451617496647, "timezoneOffset" : -60, "year" : 116},
    "triedConnector" : { "OPENSL" : -1, "SAPA" : -1, "USB" : -1, "USB_7" : -1}
    }
}'''

import json
import csv

data = json.loads(json_txt)
print ('data keys -->', data.keys())
print ('one of these keys (arbitrary) -->', list(data.keys())[0])
print ('keys for one object in data -->', list(data[list(data.keys())[0]].keys()))
key9_fields = data[list(data.keys())[0]]['key9'].keys()
print ('keys in key9 -->', key9_fields)

print ('Now writing csv ...')

the_fieldnames = list(data[list(data.keys())[0]].keys())
# remove triedConnector and key9
the_fieldnames.remove('triedConnector')
the_fieldnames.remove('key9')
# add items from key9
the_fieldnames.extend(key9_fields)
print (the_fieldnames)

with open('tpbafk_csv.csv', 'w', newline='') as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=the_fieldnames)
    writer.writeheader()
    for key in data.keys():
        row = {}
        for field_key in the_fieldnames:
            if field_key in key9_fields:
                row[field_key] = data[key]['key9'][field_key]
            else:
                row[field_key] = data[key][field_key]
        writer.writerow(row)

标准输出的结果如下：

data keys --> dict_keys(['-K6v8Ht6nXCjaO_2', '-K6v8Ht6nXCjaO_3', '-K6v8Ht6nXCjaO_ApNGr'])
one of these keys (arbitrary) --> -K6v8Ht6nXCjaO_2
keys for one object in data --> ['key6', 'key8', 'triedConnector', 'key2', 'key7', 'key4', 'key', 'key5', 'key9', 'key3', 'key1']
keys in key9 --> dict_keys(['timezoneOffset', 'month', 'year', 'hours', 'time', 'day', 'seconds', 'date', 'minutes'])
Now writing csv ...
['key6', 'key8', 'key2', 'key7', 'key4', 'key', 'key5', 'key3', 'key1', 'timezoneOffset', 'month', 'year', 'hours', 'time', 'day', 'seconds', 'date', 'minutes']

CSV文件：

key6,key8,key2,key7,key4,key,key5,key3,key1,timezoneOffset,month,year,hours,time,day,seconds,date,minutes
01/01/2016 04:04,0,False,23,0,-K6v8Ht6nXCjaO_ApNGr,01/01/2016 04:04,0,0,-60,0,116,4,1451617496647,5,56,1,4
01/01/2016 04:04,0,False,23,0,-K6v8Ht6nXCjaO_ApNGr,01/01/2016 04:04,0,0,-60,0,116,4,1451617496647,5,56,1,4
01/01/2016 04:04,0,False,23,0,-K6v8Ht6nXCjaO_ApNGr,01/01/2016 04:04,0,0,-60,0,116,4,1451617496647,5,56,1,4
先生，这个工作得非常好。非常感谢您。您是最棒的！这是一个非常好的和简单的解决方案，再次感谢您。 - Mustafa Demir
0

如csv.writer文档中所述：

如果csvfile是文件对象，则应使用newline=''打开

只需修复此行：

outputFile = open(fileOutput, 'wb+', newline='') #load csv file
我尝试了但没有成功，我得到了这个错误：TypeError: 'newline'是该函数的无效关键字参数。 - Mustafa Demir
@tpbafk：有打错吗？newline是open的关键字。 - Skandix
@tpbafk：Python 版本是 2.x 吗？ - Skandix
open 方法没有参数。我使用 wb+ 修复了它。无论如何，谢谢。 - Mustafa Demir
@tpbafk：请随意分享您的完整解决方案或关闭此问题。 - Skandix
0

我假设你的JSON数据是这样的，所以我根据以下内容编写了脚本：

data = {
 "-K6v8Ht6nXCjaO_ApNGr" : {
"key1" : 0,
"key2" : False,
"key3" : 0,
"key4" : 0,
"key5" : "01/01/2016 04:04",
"key" : "-K6v8Ht6nXCjaO_ApNGr",
"key6" : "01/01/2016 04:04",
"key7" : 23,
"key8" : 0,
"key9" : {
  "date" : 1,
  "day" : 5,
  "hours" : 4,
  "minutes" : 4,
  "month" : 0,
  "seconds" : 56,
  "time" : 1451617496647,
  "timezoneOffset" : -60,
  "year" : 116
},
"triedConnector" : {
  "OPENSL" : -1,
  "SAPA" : -1,
  "USB" : -1,
  "USB_7" : -1
}}, "-K6v8Ht6nXCjaO_ApNGr1" : {
"key1" : 0,
"key2" : False,
"key3" : 0,
"key4" : 0,
"key5" : "01/01/2016 04:04",
"key" : "-K6v8Ht6nXCjaO_ApNGr",
"key6" : "01/01/2016 04:04",
"key7" : 23,
"key8" : 0,
"key9" : {
  "date" : 1,
  "day" : 5,
  "hours" : 4,
  "minutes" : 4,
  "month" : 0,
  "seconds" : 56,
  "time" : 1451617496647,
  "timezoneOffset" : -60,
  "year" : 116
},
"triedConnector" : {
  "OPENSL" : -1,
  "SAPA" : -1,
  "USB" : -1,
  "USB_7" : -1
}}}

代码：

all_keys = ['key']
all_keys.extend('key%d' % i for i in range(1,6))
csv_data = [[data[val][key] for key in all_keys] for val in data.keys()]
import csv
with open('horizontal.csv','w+') as outfile:
    write = csv.writer(outfile)
    write.writerows(csv_data)

输出：

-K6v8Ht6nXCjaO_ApNGr,False,0,0,01/01/2016 04:04
-K6v8Ht6nXCjaO_ApNGr,False,0,0,01/01/2016 04:04
我之前尝试过类似的解决方案，但它没有起作用。这几乎和我的旧解决方案相同。问题是：Traceback（最近的调用最先）：文件“C：\ Users \ MuhammedMustafa \ Desktop \ Python27 \ test.py”，第32行，在<module>中 csv_data = [[data[val][key] for key in all_keys] for val in data.keys()] NameError：名称'data'未定义 - Mustafa Demir
数据 = 您的JSON约束，否则请尝试使用我的JSON数据。 - Veera Balla Deva
我使用了您的JSON模板，但是出现了错误：“ValueError: No JSON object could be decoded”。非常抱歉，因为我是Python的新手，还不熟悉它的语法。我已将我的代码更改为这个。您能否检查我的代码是否有错误或其他问题？但我猜测解释器无法读取我的JSON文件，因为格式不正确，完整的错误信息在这里，我的新JSON在这里。 - Mustafa Demir
@tpbafk "connectorActivated" : false --> 在加载json文件之前，请将其设置为False。 - Veera Balla Deva
没有用，不起作用。我不明白为什么转换JSON文件这么难。 - Mustafa Demir
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接