我无法使用python将JSON转换为CSV

Question

我有一个JSON文件，我正试图转换成CSV来获取数据。然而，我得到的结果远远不正确。你知道吗

到目前为止，我已经：

import csv
import json
infile = open("top40nl.json", "r")
outfile = open("top40nl.csv", "w")

writer = csv.writer(outfile)

for row in json.loads(infile.read()):
writer.writerow(row)

使用这种类型的JSON数据（例如）：

{
    "info": "SQLite Pro Result Set",
    "data": [
        {
            "top40_SK": "118899",
            "song_title": "Sorry",
            "artist": "Justin Bieber",
            "year_released": "2015",
            "year": "2016",
            "week": "1",
            "position": "1",
            "prev_position": "1",
            "weeks_in_top40_v1": "10",
            "weeks_in_top40_v2": "10",
            "highest_reached_position": "1",
            "total_points": "775",
            "top40url": "https://www.top40.nl/top40/2016/week-1"
        },
        {
            "top40_SK": "118900",
            "song_title": "Love yourself",
            "artist": "Justin Bieber",
            "year_released": "2015",
            "year": "2016",
            "week": "1",
            "position": "2",
            "prev_position": "2",
            "weeks_in_top40_v1": "6",
            "weeks_in_top40_v2": "6",
            "highest_reached_position": "1",
            "total_points": "764",
            "top40url": "https://www.top40.nl/top40/2016/week-1"
        }
    ]
}

到目前为止，上述所有方法都有效，但这是我的成果： i,n,f,od,a,t,a

你知道怎么解决这个问题吗？你知道吗

Answer 1

这里是使用熊猫库的最简单的一个

import pandas as pd
import json

with open('top40nl.json') as fi:
    data = json.load(fi)
    df = pd.DataFrame(data=data)
    df.to_csv('top40nl.csv', index=False)

Answer 2

我不明白为什么你想要一个CSV文件而不是JSON，但是下面是如何从每个数据列表中提取dict并将它们写入CSV。为了简化示例，我只将输出写入sys.stdout，而不是磁盘文件。你知道吗

import json
import csv
import sys

JSON = '''\
{
    "info": "SQLite Pro Result Set",
    "data": [
        {
            "top40_SK": "118899",
            "song_title": "Sorry",
            "artist": "Justin Bieber",
            "year_released": "2015",
            "year": "2016",
            "week": "1",
            "position": "1",
            "prev_position": "1",
            "weeks_in_top40_v1": "10",
            "weeks_in_top40_v2": "10",
            "highest_reached_position": "1",
            "total_points": "775",
            "top40url": "https://www.top40.nl/top40/2016/week-1"
        },
        {
            "top40_SK": "118900",
            "song_title": "Love yourself",
            "artist": "Justin Bieber",
            "year_released": "2015",
            "year": "2016",
            "week": "1",
            "position": "2",
            "prev_position": "2",
            "weeks_in_top40_v1": "6",
            "weeks_in_top40_v2": "6",
            "highest_reached_position": "1",
            "total_points": "764",
            "top40url": "https://www.top40.nl/top40/2016/week-1"
        }
    ]
}
'''

data = json.loads(JSON)

keys = data["data"][0].keys()
writer = csv.DictWriter(sys.stdout, fieldnames=keys)
writer.writerow(dict(zip(keys, keys)))
for d in data["data"]:
    writer.writerow(d)

输出

top40_SK,song_title,artist,year_released,year,week,position,prev_position,weeks_in_top40_v1,weeks_in_top40_v2,highest_reached_position,total_points,top40url
118899,Sorry,Justin Bieber,2015,2016,1,1,1,10,10,1,775,https://www.top40.nl/top40/2016/week-1
118900,Love yourself,Justin Bieber,2015,2016,1,2,2,6,6,1,764,https://www.top40.nl/top40/2016/week-1