Convert JSON to CSV using Python (Idle)

问题:

I have a JSON file of Latitude/Longitude that I want to covert to a CSV file. I want to do this using Python. I have read/tried all other stackoverflow and google search results suggestions. I've managed to get as far as creating the CSV and including headers, but beyond that, goofy stuff starts to happen. Here is the working part of my code so far:

import json, csv

x="""[
    {"longitude":"-73.689070","latitude":"40.718000"},
    {"longitude":"-73.688400","latitude":"40.715990"},
    {"longitude":"-73.688340","latitude":"40.715790"},
    {"longitude":"-73.688370","latitude":"40.715500"},
    {"longitude":"-73.688490","latitude":"40.715030"},
    {"longitude":"-73.688810","latitude":"40.714370"},
    {"longitude":"-73.688980","latitude":"40.714080"},
    {"longitude":"-73.689350","latitude":"40.713390"},
    {"longitude":"-73.689530","latitude":"40.712800"},
    {"longitude":"-73.689740","latitude":"40.712050"},
    {"longitude":"-73.689820","latitude":"40.711810"},
    {"longitude":"-73.689930","latitude":"40.711380"},
    {"longitude":"-73.690110","latitude":"40.710710"}
]"""

x = json.loads(x)

f = csv.writer(open("test.csv", "wb+"))

f.writerow(["longitude", "latitude"])

And here's where it falls apart ("?'s" mean I'm not sure what to put there. I've tried all sorts of combinations of things that I've found in my search for answers):

for ? in ?:
    f.writerow([?[?],?[?]])

I got the above from answers to this question by little_fish. I can see that our JSON examples are slightly different, and I am assuming that has something to do with why I can't get it to work...

Any help would be greatly appreciated, and I am happy to provide clarification if need be. FYI, I am new to Python, so if you're going to use jargon, please explain it as clearly as possible. Thanks! (P.S. Not sure if it matters, but I am using IDLE).

回答1:

I would use a csv.DictWriter, since you're dealing with dicts, which is exactly the case DictWriter is there for.

rows = json.loads(x)
with open('test.csv', 'wb+') as f:
    dict_writer = csv.DictWriter(f, fieldnames=['longitude', 'latitude'])
    dict_writer.writeheader()
    dict_writer.writerows(rows)

Edit:
Since the .writeheader() method was only added in 2.7, you can use something like this on older versions:

rows = json.loads(x)
fieldnames = ['longitude', 'latitude']
with open('test.csv', 'wb+') as f:
    dict_writer = csv.DictWriter(f, fieldnames=fieldnames)
    dict_writer.writerow(dict(zip(fieldnames, fieldnames)))
    dict_writer.writerows(rows)

回答2:

Loop over the list one by one, and write out the field names explicitly is perhaps the simplest for this case.

for row in x:
    f.writerow( [row['longitude'], row['latitude']] )

回答3:

This will help you iterate each item and write it to your csv file:

import json, csv

x = """[
    {"longitude":"-73.689070","latitide":"40.718000"},
    {"longitude":"-73.688400","latitide":"40.715990"},
    {"longitude":"-73.688340","latitide":"40.715790"},
    {"longitude":"-73.688370","latitide":"40.715500"},
    {"longitude":"-73.688490","latitide":"40.715030"},
    {"longitude":"-73.688810","latitide":"40.714370"},
    {"longitude":"-73.688980","latitide":"40.714080"},
    {"longitude":"-73.689350","latitide":"40.713390"},
    {"longitude":"-73.689530","latitide":"40.712800"},
    {"longitude":"-73.689740","latitide":"40.712050"},
    {"longitude":"-73.689820","latitide":"40.711810"},
    {"longitude":"-73.689930","latitide":"40.711380"},
    {"longitude":"-73.690110","latitide":"40.710710"}
]"""

jsoned = json.loads(x)

with open("test.csv", "wb+") as csv_file:
    csv_writer = csv.writer(csv_file)
    for i in jsoned:
        csv_writer.writerow([i[u'longitude'],
                             i[u'latitide']])

Note, you're misspelling latitude (latitide) in your original post.

回答4:

Based on some of the answer responses, this works like a charm!:

import json, csv

    x="""[
        {"longitude":"-73.689070","latitude":"40.718000"},
        {"longitude":"-73.688400","latitude":"40.715990"},
        {"longitude":"-73.688340","latitude":"40.715790"},
        {"longitude":"-73.688370","latitude":"40.715500"},
        {"longitude":"-73.688490","latitude":"40.715030"},
        {"longitude":"-73.688810","latitude":"40.714370"},
        {"longitude":"-73.688980","latitude":"40.714080"},
        {"longitude":"-73.689350","latitude":"40.713390"},
        {"longitude":"-73.689530","latitude":"40.712800"},
        {"longitude":"-73.689740","latitude":"40.712050"},
        {"longitude":"-73.689820","latitude":"40.711810"},
        {"longitude":"-73.689930","latitude":"40.711380"},
        {"longitude":"-73.690110","latitude":"40.710710"}
    ]"""

    x = json.loads(x)

    f = csv.writer(open("test.csv", "wb+"))

    f.writerow(["longitude", "latitude"])

    for row in x:
        f.writerow( [row['longitude'], row['latitude']] )