How to iterate through JSON objects in Python and save to a CSV file?

Question

I have Python script which retrieves data from an API in JSON format (at least I think its JSON format). I want to loop through each 'object' and write to a CSV file.

Here is the data returned from the api:

[
{
    "bids_won": "7",
    "partner_revenue": "$0.01",
    "profit": "$0.00",
    "campaign_id": "test 1", 
    "post_click_conversions": "0", 
    "total_cost": "$0.01",
    "total_media_cost": "$0.01",
    "post_view_conversions": "2", 
    "adjusted_partner_cost": "$0.01", 
    "clicks": "0",
    "day": "August 21, 2011"
},
{
    "bids_won": "30,209", 
    "partner_revenue": "$38.67", 
    "profit": "$8.92", 
    "campaign_id": "test 2", 
    "post_click_conversions": "0", 
    "total_cost": "$29.75", 
    "total_media_cost": "$29.75", 
    "post_view_conversions": "0", 
    "adjusted_partner_cost  ": "$25.26", 
    "clicks": "10", 
    "day": "August 21, 2011"
}
 ]

How can I loop through these 'objects' and write them to a CSV file? My current attempt to loop through it results in the script interating through each letter..

Appreciate the help.

ps I'm using python 2.7

Alec Munro · Accepted Answer · 2011-09-09 16:37:52Z

4

To convert this to a proper csv file, with keys as a header row, something like this should do it, where "data" is the string containing your json:

import json
from csv import DictWriter
dicts = json.loads(data)
the_file = open("sample.csv", "w")
writer = DictWriter(the_file, dicts[0].keys())
writer.writeheader()
writer.writerows(dicts)
the_file.close()

Note that as of Python 2.6, simplejson has been included in the standard library as "json".

answered Sep 9, 2011 at 16:37

Alec Munro

2231 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mr B Over a year ago

I attempted this but I got the following error: Traceback (most recent call last): File "test.py", line 45, in <module> writer = DictWriter(report_file, dicts[0].keys()) AttributeError: 'unicode' object has no attribute 'keys'

Alec Munro Over a year ago

Ok, so it seems like your data isn't quite in the same format we were expecting. I suppose you should print it, or a subset of it, to see why it isn't parsing to a dictionary.

Mr B Over a year ago

I tried to print out each object by looping through the data but it prints out each character instead of the data for one object...

Alec Munro Over a year ago

That suggests that maybe it is being interpreted as a list containing a single string. That's certainly not too unusual to encounter when working with JSON. Before passing to loads(), data should look something like this: "[{'a':1, 'b':2,...}...]"

Mr B Over a year ago

Th data looks like this: pastebin.com/XMt6e7Ht is it being interpreted as a list containing a single string because of all those backslashes?

|

Gary Chambers · Accepted Answer · 2011-09-09 16:28:01Z

0

Try using simplejson to convert the JSON string to a native Python object. You should then be able to loop through the objects using a standard for loop.

http://pypi.python.org/pypi/simplejson/

data = simplejson.loads(JSON_STRING)

answered Sep 9, 2011 at 16:28

Gary Chambers

25.9k5 gold badges37 silver badges31 bronze badges

1 Comment

Chinmay Kanchi Over a year ago

Note that in Python 2.6+, simplejson included in the standard library, and is simply called json.

amoebe · Accepted Answer · 2011-09-09 16:30:56Z

0

This will do it:

import json
for i in json.loads(json_string):
    print ','.join(i)

See json module docs

answered Sep 9, 2011 at 16:30

amoebe

5,0415 gold badges40 silver badges44 bronze badges

Comments

Jordan Messina · Accepted Answer · 2011-09-15 01:34:33Z

You should use simplejson: http://pypi.python.org/pypi/simplejson/

With simplejson you can simply do:

import simplejson as json

info = """[
{
    "bids_won": "7",
    "partner_revenue": "$0.01",
    "profit": "$0.00",
    "campaign_id": "test 1", 
    "post_click_conversions": "0", 
    "total_cost": "$0.01",
    "total_media_cost": "$0.01",
    "post_view_conversions": "2", 
    "adjusted_partner_cost": "$0.01", 
    "clicks": "0",
    "day": "August 21, 2011"
},
{
    "bids_won": "30,209", 
    "partner_revenue": "$38.67", 
    "profit": "$8.92", 
    "campaign_id": "test 2", 
    "post_click_conversions": "0", 
    "total_cost": "$29.75", 
    "total_media_cost": "$29.75", 
    "post_view_conversions": "0", 
    "adjusted_partner_cost  ": "$25.26", 
    "clicks": "10", 
    "day": "August 21, 2011"
}
 ]"""

data = json.loads(info)

And data will be a python list that you can iterate through.

Mohan · Accepted Answer · 2013-05-09 14:01:13Z

0

import json
import csv
temp = json.load(open('filename.json','r'))
output =[]
for each in temp:
     row = {}
     row['field1'] =each['field1']
     row['field2'] = each['field2']
     output.append(row)
file = open( "filename_destination.csv", "w")

fileWriter = csv.writer(file , delimiter=",",quotechar='"', quoting=csv.QUOTE_MINIMAL)

Header = ['field1','field2']

fileWriter.writerow(Header)

for x in output:
te = [x['field1'],x['field2']]
fileWriter.writerow(te)
file.close()

answered May 9, 2013 at 14:01

Mohan

97 bronze badges

Comments

Mr B · Accepted Answer · 2011-09-19 14:06:41Z

-1

In the end I was given a slightly different URL for the request which returned raw CSV. I dumped the content into a CSV file:

f = open('report.csv', 'wb')
f.write(response_content)
f.close

Its not an ideal solution because I still cannot loop through the CSV objects (goes through each character) but I'm able to create a resonably well formatted csv file so its ok for now.

answered Sep 19, 2011 at 14:06

Mr B

4,1809 gold badges54 silver badges78 bronze badges

2 Comments

RayLuo Over a year ago

I wish I have enough reputation to downvote this worse question and worst answer! First, json IS a built-in module of python 2.7 so question owner should be able to loop his json.load(...) result. And second, the chosen "answer" does NOT solve the problem (whatever it is) at all! Reading that question and answer is a totally waste of reader's time. (PS: People who agree with this comment please upvote my comment to allow me more reputation to ...)

Tim Over a year ago

This answer doesn't help anyone who comes to the page looking for an answer to the original question.

Collectives™ on Stack Overflow

How to iterate through JSON objects in Python and save to a CSV file?

6 Answers 6

8 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

8 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related