我有一个JSON文件,我想转换为CSV文件。我如何用Python做到这一点?
我试着:
import json
import csv
f = open('data.json')
data = json.load(f)
f.close()
f = open('data.csv')
csv_file = csv.writer(f)
for item in data:
csv_file.writerow(item)
f.close()
然而,这并没有起作用。我正在使用Django和我收到的错误是:
`file' object has no attribute 'writerow'`
然后我尝试了以下方法:
import json
import csv
f = open('data.json')
data = json.load(f)
f.close()
f = open('data.csv')
csv_file = csv.writer(f)
for item in data:
f.writerow(item) # ← changed
f.close()
然后得到错误:
`sequence expected`
样本json文件:
[{
"pk": 22,
"model": "auth.permission",
"fields": {
"codename": "add_logentry",
"name": "Can add log entry",
"content_type": 8
}
}, {
"pk": 23,
"model": "auth.permission",
"fields": {
"codename": "change_logentry",
"name": "Can change log entry",
"content_type": 8
}
}, {
"pk": 24,
"model": "auth.permission",
"fields": {
"codename": "delete_logentry",
"name": "Can delete log entry",
"content_type": 8
}
}, {
"pk": 4,
"model": "auth.permission",
"fields": {
"codename": "add_group",
"name": "Can add group",
"content_type": 2
}
}, {
"pk": 10,
"model": "auth.permission",
"fields": {
"codename": "add_message",
"name": "Can add message",
"content_type": 4
}
}
]
首先,JSON包含嵌套对象,因此通常不能直接转换为CSV。你需要把它改成这样:
{
"pk": 22,
"model": "auth.permission",
"codename": "add_logentry",
"content_type": 8,
"name": "Can add log entry"
},
......]
下面是我的代码来生成CSV:
import csv
import json
x = """[
{
"pk": 22,
"model": "auth.permission",
"fields": {
"codename": "add_logentry",
"name": "Can add log entry",
"content_type": 8
}
},
{
"pk": 23,
"model": "auth.permission",
"fields": {
"codename": "change_logentry",
"name": "Can change log entry",
"content_type": 8
}
},
{
"pk": 24,
"model": "auth.permission",
"fields": {
"codename": "delete_logentry",
"name": "Can delete log entry",
"content_type": 8
}
}
]"""
x = json.loads(x)
f = csv.writer(open("test.csv", "wb+"))
# Write CSV Header, If you dont need that, remove this line
f.writerow(["pk", "model", "codename", "name", "content_type"])
for x in x:
f.writerow([x["pk"],
x["model"],
x["fields"]["codename"],
x["fields"]["name"],
x["fields"]["content_type"]])
你会得到如下输出:
pk,model,codename,name,content_type
22,auth.permission,add_logentry,Can add log entry,8
23,auth.permission,change_logentry,Can change log entry,8
24,auth.permission,delete_logentry,Can delete log entry,8
我对丹提出的解决方案感到困惑,但这对我来说很管用:
import json
import csv
f = open('test.json')
data = json.load(f)
f.close()
f=csv.writer(open('test.csv','wb+'))
for item in data:
f.writerow([item['pk'], item['model']] + item['fields'].values())
“测试的地方。Json”包含以下内容:
[
{"pk": 22, "model": "auth.permission", "fields":
{"codename": "add_logentry", "name": "Can add log entry", "content_type": 8 } },
{"pk": 23, "model": "auth.permission", "fields":
{"codename": "change_logentry", "name": "Can change log entry", "content_type": 8 } }, {"pk": 24, "model": "auth.permission", "fields":
{"codename": "delete_logentry", "name": "Can delete log entry", "content_type": 8 } }
]
如果我们考虑下面的例子,将json格式的文件转换为csv格式的文件。
{
"item_data" : [
{
"item": "10023456",
"class": "100",
"subclass": "123"
}
]
}
下面的代码将转换json文件(data3. xml)。Json)转换为CSV文件(data3.csv)。
import json
import csv
with open("/Users/Desktop/json/data3.json") as file:
data = json.load(file)
file.close()
print(data)
fname = "/Users/Desktop/json/data3.csv"
with open(fname, "w", newline='') as file:
csv_file = csv.writer(file)
csv_file.writerow(['dept',
'class',
'subclass'])
for item in data["item_data"]:
csv_file.writerow([item.get('item_data').get('dept'),
item.get('item_data').get('class'),
item.get('item_data').get('subclass')])
上面提到的代码已经在本地安装的pycharm中执行,它已经成功地将json文件转换为csv文件。希望这有助于转换文件。
我假设您的JSON文件将解码为字典列表。首先,我们需要一个将JSON对象扁平化的函数:
def flattenjson(b, delim):
val = {}
for i in b.keys():
if isinstance(b[i], dict):
get = flattenjson(b[i], delim)
for j in get.keys():
val[i + delim + j] = get[j]
else:
val[i] = b[i]
return val
在JSON对象上运行这段代码的结果:
flattenjson({
"pk": 22,
"model": "auth.permission",
"fields": {
"codename": "add_message",
"name": "Can add message",
"content_type": 8
}
}, "__")
is
{
"pk": 22,
"model": "auth.permission",
"fields__codename": "add_message",
"fields__name": "Can add message",
"fields__content_type": 8
}
对JSON对象输入数组中的每个dict应用此函数后:
input = map(lambda x: flattenjson( x, "__" ), input)
并查找相关的列名:
columns = [x for row in input for x in row.keys()]
columns = list(set(columns))
在CSV模块中运行这个并不难:
with open(fname, 'wb') as out_file:
csv_w = csv.writer(out_file)
csv_w.writerow(columns)
for i_r in input:
csv_w.writerow(map(lambda x: i_r.get(x, ""), columns))