Skip to content

Instantly share code, notes, and snippets.

@paulgb paulgb/convert.py
Last active Nov 28, 2018

Embed
What would you like to do?
Convert the Yelp Academic dataset from JSON to CSV files with Pandas.
'''
Convert Yelp Academic Dataset from JSON to CSV
Requires Pandas (https://pypi.python.org/pypi/pandas)
By Paul Butler, No Rights Reserved
'''
import json
import pandas as pd
from glob import glob
def convert(x):
''' Convert a json string to a flat python dictionary
which can be passed into Pandas. '''
ob = json.loads(x)
for k, v in ob.items():
if isinstance(v, list):
ob[k] = ','.join(v)
elif isinstance(v, dict):
for kk, vv in v.items():
ob['%s_%s' % (k, kk)] = vv
del ob[k]
return ob
for json_filename in glob('*.json'):
csv_filename = '%s.csv' % json_filename[:-5]
print 'Converting %s to %s' % (json_filename, csv_filename)
df = pd.DataFrame([convert(line) for line in file(json_filename)])
df.to_csv(csv_filename, encoding='utf-8', index=False)
@uttara-cmu

This comment has been minimized.

Copy link

commented Sep 30, 2014

Hi,

This worked great for reviews and businesses. Thanks a lot for the code. But for users it gives me an error

python convert.py
Converting yelp_academic_dataset_user.json to yelp_academic_dataset_user.csv
Traceback (most recent call last):
File "convert.py", line 29, in
df = pd.DataFrame([convert(line) for line in file(json_filename)])
File "convert.py", line 19, in convert
ob[k] = ','.join(v)
TypeError: sequence item 0: expected string, int found

What should I be doing?

@heng0136

This comment has been minimized.

Copy link

commented Oct 5, 2014

Hi uttara-cmu,

For converting of the user.json file, you might wanna try changing the following code
(Line 19) ob[k] = ','.join(v)

To this:
ob[k] = ','.join(str(v))

Hope it helps...

@tiagoovieira

This comment has been minimized.

Copy link

commented Jan 16, 2015

It converts to csv, but a lot of columns are blank :\

Does this happened before?

@juvingeorge

This comment has been minimized.

Copy link

commented Mar 7, 2016

Hi! I am new to python. I am trying to use this code but I received this error. I was wondering if anyone can help?

File "", line 28
print 'Converting %s to %s' % (json_filename, csv_filename)
^
SyntaxError: invalid syntax

@mis546x

This comment has been minimized.

Copy link

commented Apr 2, 2016

I am getting this error while converting the review dataset. Need help urgently.

Converting yelp_academic_dataset_review.json to yelp_academic_dataset_review.csv

Traceback (most recent call last):
File "C:\Python27\convert.py", line 27, in
df = pd.DataFrame([convert(line) for line in file(json_filename)])
File "C:\Python27\convert.py", line 14, in convert
ob = json.loads(x)
File "C:\Python27\lib\json__init__.py", line 339, in loads
return _default_decoder.decode(s)
File "C:\Python27\lib\json\decoder.py", line 365, in decode
end = _w(s, end).end()
MemoryError

@pkshah912

This comment has been minimized.

Copy link

commented Apr 29, 2016

Hi,
I am using Python 3.4. However, I am getting an error that
NameError: name 'file' is not defined on this line: df = pd.DataFrame([convert(line) for line in file(json_filename)])

What package needs to be installed on Python 3.4 in order to resolve this error.

@evashah

This comment has been minimized.

Copy link

commented Jun 1, 2016

Hi Heng0136,
Why does that user.json file need a different code ob[k] = ','.join(str(v)) vs. the other files?

Best,
E.

@sanketh8

This comment has been minimized.

Copy link

commented Nov 8, 2016

Where can we get the json data set file?

@Chokka

This comment has been minimized.

Copy link

commented Nov 10, 2016

I have downloaded the Dataset from Yelp Dataset Challenge. I am facing some difficulty on converting the 2.6 GB JSON file. Which specific Python version, i need to install on my Windows 10 OS?

@vihar007

This comment has been minimized.

Copy link

commented Apr 17, 2017

use open instead of file for python 3.x

@tothebeat

This comment has been minimized.

Copy link

commented May 18, 2017

@paulgb Thanks for this excellent start! I used it as a first step in my code to process the 2017 dataset from Yelp: https://github.com/tothebeat/Yelp-Challenge-Dataset

@24jancts

This comment has been minimized.

Copy link

commented Sep 23, 2017

Getting an error for Sep 2017

File "F:/Yelp/yelp_dataset/yelp_dataset/jsontocsv_business.py", line 10, in convert
for k, v in ob.items():

RuntimeError: dictionary changed size during iteration

@svknair

This comment has been minimized.

Copy link

commented Feb 12, 2018

I also got "RuntimeError: dictionary changed size during iteration", while using the above code to open the Round 11 business.json file. For the review.json file, I didn't get the error message but the code ran for almost an hour and nothing happened. Finally, I had to terminate the execution. Any advice to get around these issues is highly appreciated.

@emredjan

This comment has been minimized.

Copy link

commented Sep 5, 2018

I wrote another one that works with the 2018 version of the dataset. In theory it should work with any arbitrary dataset as long as they're structured as one json object per line:
https://gist.github.com/emredjan/6fffc4f696d2201d1e3697b783f9590b

Give it the directory where the json files reside from the command line, it should do the trick.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.