Convert JSON to CSV using Python (Idle)

Question

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).

Englert

Answer 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)

Dillard

hmm... when I try that I get the following error: DictWriter instance has no attribute 'writeheader' – Englert

Yeah, it seems the writeheader() method was added in 2.7, so the earlier versions won't have it. You can write the header as a normal row though. – Dillard

Answer 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']] )

Fabulous

Works! Thank you. (Works of course AFTER I corrected the misspelling mentioned below). – Englert

Answer 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.

Deeprooted

Wow. Incredible. I can't BELIEVE I didn't catch that. Oy. Thank you. – Englert

Answer 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']] )

Englert

using with open() as x syntax is typically recommended as it allows the open filehandler to be closed automatically after you're finished with it. – Deeprooted

OK, since I've been playing around with this, I can see that now. Thanks! – Englert