Convert the Yelp Academic dataset from JSON to CSV files with Pandas.

convert.py

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

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?

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

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

Does this happened before?

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

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

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.

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

Best,
E.

Where can we get the json data set file?

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?

use open instead of file for python 3.x

@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

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

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.

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.