如何在Python中将JSON转换为CSV

Question

以下是我的JSON文件输入。

{"userID": "679d3bad-155e-4b39-9ff7-7d564f408942", "Is salary credited before 5th": "Yes", "Avg Salary of last 3 months": 15453.33, "Avg Salary of last 6 months": 15290.5, "Avg Balance before salary of last 3 months": 113.15, "Avg Balance before salary of last 6 months": 105.22}

代码

    with open('/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.json', "r") as f:
        BankData = json.loads(f.read())
    x = json.loads(json.dumps(BankData))
    f = csv.writer(open("/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.csv", "w"))
    f.writerow(["userID", "Is salary credited before 5th", "Avg Salary of last 3 months", "Avg Salary of last 6 months", "Avg Balance before salary of last 3 months", "Avg Balance before salary of last 6 months"])

    for y in x:
        f.writerow([x["userID"], x["Is salary credited before 5th"],
                    x["Avg Salary of last 3 months"],
                    x["Avg Salary of last 6 months"],
                    x["Avg Balance before salary of last 3 months"],
                    x["Avg Balance before salary of last 6 months"]])

输出

userID,Is salary credited before 5th,Avg Salary of last 3 months,Avg Salary of last 6 months,Avg Balance before salary of last 3 months,Avg Balance before salary of last 6 months
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22

所以，我得到了答案，但是它打印了七次而不是一次。我该如何解决这个问题。

- vrinda

---

你为什么要这样做？x = json.loads(json.dumps(BankData))？无论如何，x是一个dict对象。你遍历它，for y in x: 这将遍历它的键。但你甚至没有使用y，只是重复写了同一行7次（键的数量）。 - juanpa.arrivillaga

@Chris：你建议我做什么？ - vrinda

Answer 1

您也可以使用pandas来处理数据框，

dct = {"userID": "679d3bad-155e-4b39-9ff7-7d564f408942", "Is salary credited before 5th": "Yes", "Avg Salary of last 3 months": 15453.33,
       "Avg Salary of last 6 months": 15290.5, "Avg Balance before salary of last 3 months": 113.15, "Avg Balance before salary of last 6 months": 105.22}

import pandas as pd

df = pd.DataFrame.from_records(dct, index=[0])

df.to_csv('outputfile.csv')

- Menglong Li

Answer 2

GeekSambhu的解决方案对我有用，只需进行了一些小修改。由于像Vinsent一样，我遇到了KeyError，所以我进行了一些修改。如果JSON结构具有包含数据行数组的顶级对象（这被认为是JSON最佳实践），则可能会出现KeyError。假设有一个名为“data”的顶级对象，则只需更改GeekSambhu解决方案的两行代码。

writer.writerow(_json['data'][0].keys()) #标题行
for row in _json['data']:

- MrIonic

Answer 3

BankData 是一个字典，您无需迭代它。您可以直接使用键访问值。

例如：

import csv
import json

with open('/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.json') as infile:
    BankData = json.loads(infile.read())

with open("/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.csv", "w") as outfile:
    f = csv.writer(outfile)
    f.writerow(["userID", "Is salary credited before 5th", "Avg Salary of last 3 months", "Avg Salary of last 6 months", "Avg Balance before salary of last 3 months", "Avg Balance before salary of last 6 months"])
    f.writerow([BankData["userID"], BankData["Is salary credited before 5th"],
                BankData["Avg Salary of last 3 months"],
                BankData["Avg Salary of last 6 months"],
                BankData["Avg Balance before salary of last 3 months"],
                BankData["Avg Balance before salary of last 6 months"]])

- Rakesh

---

json 库还可以直接读取文件对象，而不需要先获取字符串表示形式：json.load(infile)。 - Night Train

Answer 4

你可以这样做：使用导入json和csv模块，读取你的JSON并将其写入CSV文件。

import json, csv
from collections import OrderedDict #To maintain key value pair order
_json=json.loads(open('data.json', 'r').read(), object_pairs_hook=OrderedDict) 
out=open('converted.csv', 'w')
writer = csv.writer(out)               #create a csv.write
writer.writerow(_json[0].keys())      # header row
for row in _json:
    writer.writerow(row.values())

- ShivaGaire

---

你的代码将会生成CSV格式的输出，其中包含JSON值及其语法。 - Bhaskar Das

是的，@BhaskarDas，它将为提供的 csv 生成 JSON。 - ShivaGaire

writer.writerow(_json[0].keys()) # 表头行KeyError: 0 出现上述错误 - vinsent paramanantham