CODEWITHSUNDEEP
pythonjsonparsingcsvimport
Petr Fedorov
Petr Fedorov

Reputation: 111

Import JSON data into CSV format

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'}

Upvotes: 1

Views: 1136

Answers (1)

mirthbottle
mirthbottle

Reputation: 732

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.

Upvotes: 1

Related Questions