I have a number of JSON files to combine and output as a single CSV (to load into R), with each JSON file at about 1.5GB. While doing a trial on 4-5 JSON files at 250MB each, the code works when I only use 2-3 files but chokes when the total file sizes get larger.

I'm running Python version 2.7.6 (default, Nov 10 2013, 19:24:24) [MSC v.1500 64 bit (AMD64)] on 8GB RAM and Windows 7 Professional 64 bit.

I'm a Python novice and have little experience with writing optimized code. I would appreciate guidance on optimizing my script below.

Python MemoryError

Traceback (most recent call last):
  File "C:\Users\...\tweetjson_to_csv.py", line 52, in <module>
    for line in file:
[Finished in 29.5s]

JSON to CSV conversion script

# csv file that you want to save to
out = open("output.csv", "ab")

filenames = ["8may.json", "9may.json", "10may.json", "11may.json", "12may.json"]
open_files = map(open, filenames)

# change argument to the file you want to open
for file in open_files:
    for line in file:
        # only keep tweets and not the empty lines
        if line.rstrip():

for tweet in tweets:
... ...

print >> out, "ids,text,time_created,retweet_counts,in_reply_to,geos,coordinates,places,country,language,screen_name,followers,friends,statuses,locations"
rows = zip(ids,texts,time_created,retweet_counts,in_reply_to_screen_name,geos,coordinates,places,places_country,lang,user_screen_names,user_followers_count,user_friends_count,user_statuses_count,user_locations)

csv = writer(out)

for row in rows:
    values = [(value.encode('utf8') if hasattr(value, 'encode') else value) for value in row]

  • \$\begingroup\$ Hi there, welcome to Code Review. We do review of code that has to work. If this code works for smaller JSON files please change title and body in how to improve to larger files. \$\endgroup\$ – chillworld May 15 '14 at 5:23
  • \$\begingroup\$ In my opinion, this question is on-topic, since it is said to work for more reasonably sized inputs. It's just like the numerous programming-challenge questions with "Time Limit Exceeded" problems. \$\endgroup\$ – 200_success May 15 '14 at 6:39

You're storing lots of results in lists, which could be streamed instead. Fortunately, using generators, you can make this ‘streaming’ relatively easy without changing too much of the structure of your code. Essentially, put each ‘step’ into a function, and then rather than appending to a list, yield each value. Then you’d have something that might look like this:

def load_json():
    for file in open_files:
        for line in file:
            # only keep tweets and not the empty lines
            if line.rstrip():
                    datum = json.loads(line)
                    yield datum

Then you could replace

for tweet in tweets:


for tweet in load_json():

But this will still store the IDs into ids, texts into texts, etc. You could use a bunch of generators and zip them together with itertools.izip, but the better solution would be to extract the columns from the tweet when writing each line. Then (omitting the UTF-8 encoding piece, which you'd want to rewrite to work on dictionaries) you’d have

for tweet in load_json():
    csv.writerow((tweet['id'], tweet['text'], ...))

Lastly, since this is Code Review, you might consider putting all the keys you want to pull out into a list:

columns = ['id', 'text', ...]

Then your row-writing code can be simplified to

csv.writerow([tweet[key] for key in columns])

Rewriting it this way, you can then UTF-8 encode rather easily (using a helper function):

def encode_if_possible(value, codec):
    if hasattr(value, 'encode'):
        return value.encode(codec)
        return value

csv.writerow([encode_if_possible(tweet[key], 'utf-8') for key in columns])
  • \$\begingroup\$ thanks icktoofay. I'm in the process of implementing your suggestion now but am not sure how to include the UTF-8 encoding piece. Would you be able to help please? \$\endgroup\$ – Eugene Yan May 15 '14 at 6:30
  • \$\begingroup\$ I've done something like this but my csv has each character in each column: csv.writerow(str((tweet["id_str"], tweet["text"], tweet["created_at"], tweet["retweet_count"], tweet["in_reply_to_screen_name"], tweet["geo"], tweet["coordinates"], tweet["place"], tweet["lang"], tweet["user"]["screen_name"], tweet["user"]["followers_count"], tweet["user"]["friends_count"], tweet["user"]["statuses_count"], tweet["user"]["statuses_count"])).encode("utf-8")) \$\endgroup\$ – Eugene Yan May 15 '14 at 6:40
  • \$\begingroup\$ @eugeneyan: It does that because you're putting everything into a tuple, converting that tuple into a string, and then encoding the string representation of the tuple; that encoded string then goes to writerow, and writerow treats each character as a column. Rather than calling str and then encode on the whole thing, do it piece-by-piece. I've updated my answer to show how to do that after the last refactoring I suggested has been applied. \$\endgroup\$ – icktoofay May 16 '14 at 1:21
  • \$\begingroup\$ thanks @icktoofay, it works like a charm! may I enquire why you had the datum = json.loads(line) & yield datum in a try-else structure? it works just fine without try-else \$\endgroup\$ – Eugene Yan May 16 '14 at 2:09
  • \$\begingroup\$ @eugeneyan: Calling load_json returns a generator object, from which successive values yielded can be retrieved. However, generators are bidirectional: certain uses of the generator can make yield, if used as an expression, itself return a result or raise an exception. If someone explicitly tries to raise an exception inside the generator, we probably want to leave it uncaught and let it abort the generator, rather than letting it pass silently and continue generating tweets. \$\endgroup\$ – icktoofay May 16 '14 at 2:12

This code looks suspiciously familiar! You can actually just use this previous answer with hardly any changes. (Is this program your original work? Are you both basing your code on a bad example?)

In summary, your code shares the same problems as the other program:

  • File descriptor leaks

  • You read all the tweets into an array of JSON objects, then slice the data "vertically" by attribute, then re-aggregate the data "horizontally". That's inefficient in terms of memory usage (you load the entire data set in memory, twice, simultaneously, as tweets and rows) as well as cache locality.

  • Unless you have a good reason, just transform one line of input at a time. (A good reason might be that you want to produce no output file at all if an error occurs while processing any line.)

  • \$\begingroup\$ indeed I had started based on the same code the user had shared; however, after a couple of iterations it has transformed to be quite different \$\endgroup\$ – Eugene Yan May 15 '14 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.