Import JSON data into CSV format

Question

My ultimate goal is to parse JSON data from an API call into a CSV file. I'm using sample code for the Yelp API http://www.yelp.com/developers/documentation/v2/examples. The best I've gotten is to write the data into a CSV file, but it's not very pretty. I believe I'm missing putting the data into a Python dictionary first, but I researched and tried a few approaches that led me nowhere.

try:
    CSVfile = open('C:\Users\Petr Fedorov\Desktop\YelpAPI.csv', "wb")
    response = json.dump(conn.read(),CSVfile,sort_keys=True)
    #response = json.loads(conn.read()) #this is the original code under try:

finally:
    conn.close()
    CSVfile.close()

return response

I've looked through the following posts, but couldn't adopt their approach to this.

Parsing values from a JSON file using Python? How do I write JSON data to a file in Python? Creating a JSON response using Django and Python

Your time and effort in providing the code or guiding me in the right directions are greatly appreciated.

Here is the sample output with the original code... response = json.loads(conn.read())

Result for business "affordable-interior-design-new-york" found:
{ u'categories': [[u'Interior Design', u'interiordesign']],
  u'display_phone': u'+1-917-686-1319',
  u'id': u'affordable-interior-design-new-york',
  u'image_url': u'http://s3-media1.fl.yelpcdn.com/bphoto/taBV7OM7mNyS_pQD7ifSVw/ms.jpg',
  u'is_claimed': True,
  u'is_closed': False,
  u'location': { u'address': [],
                 u'city': u'New York',
                 u'coordinate': { u'latitude': 40.7376543584561,
                                  u'longitude': -74.0053272269531},
                 u'country_code': u'US',
                 u'display_address': [u'West Village', u'New York, NY 10014'],
                 u'geo_accuracy': 9.5,
                 u'neighborhoods': [u'West Village'],
                 u'postal_code': u'10014',
                 u'state_code': u'NY'},
  u'mobile_url': u'http://m.yelp.com/biz/affordable-interior-design-new-york',
  u'name': u'Affordable Interior Design',
  u'phone': u'9176861319',
  u'rating': 5.0,
  u'rating_img_url': u'http://s3-media1.fl.yelpcdn.com/assets/2/www/img/f1def11e4e79/ico/stars/v1/stars_5.png',
  u'rating_img_url_large': u'http://s3-media3.fl.yelpcdn.com/assets/2/www/img/22affc4e6c38/ico/stars/v1/stars_large_5.png',
  u'rating_img_url_small': u'http://s3-media1.fl.yelpcdn.com/assets/2/www/img/c7623205d5cd/ico/stars/v1/stars_small_5.png',
  u'review_count': 10,
  u'reviews': [ { u'excerpt': u'Classes are structured in a very practical manner. It sets up a basic layout and gives you a foundation about what to take care of step-by-step.\nAnd yes,...',
                  u'id': u'Vu5-xvjUII0wCV3Q-1vz8w',
                  u'rating': 4,
                  u'rating_image_large_url': u'http://s3-media2.fl.yelpcdn.com/assets/2/www/img/ccf2b76faa2c/ico/stars/v1/stars_large_4.png',
                  u'rating_image_small_url': u'http://s3-media4.fl.yelpcdn.com/assets/2/www/img/f62a5be2f902/ico/stars/v1/stars_small_4.png',
                  u'rating_image_url': u'http://s3-media4.fl.yelpcdn.com/assets/2/www/img/c2f3dd9799a5/ico/stars/v1/stars_4.png',
                  u'time_created': 1324256900,
                  u'user': { u'id': u'r9ecgI5mnHgPo4W0fPRqPA',
                             u'image_url': u'http://s3-media4.fl.yelpcdn.com/photo/kcsQCRHtTmZcwPtE7_aeAQ/ms.jpg',
                             u'name': u'Wendy G.'}}],
  u'snippet_image_url': u'http://s3-media4.fl.yelpcdn.com/assets/2/www/img/cc4afe21892e/default_avatars/user_medium_square.png',
  u'snippet_text': u'So much more than I could have hoped for. Betsy is fun, funny, and REALLY good at Interior Design.\n\nThe 3 hour plan was perfect.',
  u'url': u'http://www.yelp.com/biz/affordable-interior-design-new-york'}

My first recommendation would be *not* to serialize to CSV, if you have a choice. JSON is a perfectly cromulent serialization format. Barring that, you'll need to post sample input and desired output; there is no universal mapping from JSON->CSV since JSON supports arbitrarily nested structures and CSV does not. — roippi, Nov 01 '14 at 21:58
@roippi I'd like to output to CSV because that'll be the final destination of the data. I added the output for the original code. Any guidance is appreciated. Like I mentioned, I'm a complete newb to Python. I code some things in VBA, but that's about it, so that's why I'm utterly lost in things like JSON, dictionaries, conversions, etc. Thanks again for any guidance. — Petr Fedorov, Nov 01 '14 at 22:43
json is a nested data structure, and csv files are generally flat - (unless you want repeated columns) - are you sure you need csv? — Burhan Khalid, Nov 02 '14 at 19:57

score 1 · Answer 1 · answered Mar 03 '16 at 21:04

Actually, your response is already a python dictionary. One thing you can do is to use get put the data in a pandas DataFrame object. Then output to csv from there.

You can do that directly from the json string

import pandas as pd
df = pd.read_json(conn.read())

Or you can do that from the python dictionary.

df = pd.from_dict(response)

Then you can output it to csv

df.to_csv('file.csv')

One concern is that the response you have has more than 1 level. In other words, there are nested dictionaries. You need to have something in this format

r = {'a': [list], 'b': [list] , 'c': value, 'd':value}

In other words, the values can't be dictionaries. That means you'll still have to manipulate the dictionary data to get it into the right form first like by selecting some parts of the data.

Import JSON data into CSV format

1 Answers1