# Efficiently convert 60 GB JSON file to a csv file

### Description

Simply take a JSON file as input and convert the data in it into a CSV file. I won't describe the functionality in too much detail since I have reasonable docstrings for that. As you can see, my solution is not memory efficient since I'm reading all the file into memory.

I'd like to improve the performance of my solution as much as possible. (perhaps not load everything at once into memory -- even if it's gonna be slower).

The JSON file that I'm trying to convert is 60 GB and I have 64GB of RAM.

### Code

import csv
import json

CSV_PATH = 'file.csv'
JSON_PATH = 'file.json'

def flattenjson(json_data, delim):
"""
Flatten a simple JSON by prepending a delimiter to nested children.

Arguments:
json_data (dict): JSON object
e.g: {
"key1": "n1_value1",
"key2": "n1_value2",
"parent1": {
"child_key1": "n1_child_value1",
"child_key2": "n1_child_value2"
}
}
delim (str): Delimiter for nested children (e.g: '.')

Returns:
Flattened JSON object.
e.g: {
'key1': 'n1_value1',
'key2': 'n1_value2',
'parent1.child_key1': 'n1_child_value1',
'parent1.child_key2': 'n1_child_value2'
}
"""

flattened_json = {}
for i in json_data.keys():
if isinstance(json_data[i], dict):
get = flattenjson(json_data[i], delim)
for j in get.keys():
flattened_json[i + delim + j] = get[j]
else:
flattened_json[i] = json_data[i]

return flattened_json

def write_json_to_csv(flattened_json, csv_path):
"""
Write flattened json to a csv file. The keys of the json will be the header
of the csv and the values..well, the values ^_^.

Arguments:
flattened_json (dict): Flattened JSON object.
e.g: {
'key1': 'n1_value1',
'key2': 'n1_value2',
'parent1.child_key1': 'n1_child_value1',
'parent1.child_key2': 'n1_child_value2'
}
csv_path (str): path of the CSV file

Returns:
None
"""

with open(csv_path, 'w') as out_file:
w = csv.DictWriter(out_file, flattened_json.keys())
w.writerow(flattened_json)

def main():
"""
Main entry to our program.
"""

with open(JSON_PATH) as json_file:

flattened_json = flattenjson(json_data, '.')
write_json_to_csv(flattened_json, CSV_PATH)

if __name__ == '__main__':
main()


### More about input / output

• I don't know where the JSON file comes from so I have to stick with it and process it as is.
• I can't change the structure of the JSON file
• As far I saw, the JSON data will be at most 7 level-nested so we could have something like:
{
"a": "1",
"b": "2",
"c": {
"c_1": "3",
"c_2": "4"
},
"d": {
"d_1": {
"d_1_1": "5",
"d_1_2": "6"
},
"d_2": {
"d_2_1": "5",
"d_2_2": "6"
}
... and so on
}
}

• I have to write the data to the CSV file as described above.
• The CSV for the above JSON will look like this:

I'm specifically looking for a review orientated on memory optimizations which probably comes with a cost of a slower running time (that's fine) but any other overall improvements are welcome!

PS: I've done the above in Python 3.8.2 so I'd like you to focus on a version of Python >= 3.6

• In NodeJS we have something called "streaming" which is basically the same as what happens when you're watching a movie on Netflix or a video on YouTube. In short you take a source, "pipe it" and output it. Just like a water-hose. It loads chunks of the data in memory, processes it, outputs it and when it's ready to take more it'll load a new chunk. Isn't there something similar for Python? I know that NodeJS has it build in by default using the "readDataStream" method. Makes more sense than loading the whole 60GB in memory. – Melvin Idema Apr 30 '20 at 21:56
• Seems like Python core module I/O is what you'll need. docs.python.org/3/library/io.html – Melvin Idema Apr 30 '20 at 22:03
• Not quite. In this case you’d probably need to get all the keys beforehand so you can build the header of the csv and only then you might be able to iterate over chunked data and add it to the csv. But IDK if that’s the way to go hence this question here ^^ Thanks for the feedback! – Grajdeanu Alex Apr 30 '20 at 23:25
• Just check if there are any keys in the chunk that are not yet in the CSV header and if so add them. Streaming is meant exactly for use-cases like this. But I'm not proficient in Python at all. I'm curious to the solution! Goodluck – Melvin Idema Apr 30 '20 at 23:30
• Why don't aren't there 4 rows in the example? I would think there should be a new row for each top level key. Otherwise, you'll end up with one very long row. – RootTwo May 3 '20 at 1:40

It looks like the actual processing is quite simple, so I would recommend using a streaming JSON parser like jq --stream or (in Python) ijson.

Your script seems to create a one row csv file with each data element having a separate column. That didn't seem to make much sense, so here's a script that creates a new csv row for each top-level object in the json file. I suspect this still isn't what you want, because each unique data element gets its own column in the csv file. The script provides an outline; you can change the two passes to get what you want.

The script that does two passes over the json file. First pass is to get the column names. The second pass creates the csv file. I used StringIO for testing, you'll want to change StringIO to open (e.g., with open(...) as f). It uses the ijson library to incrementally read the json file. Also, the script only handles string data, because that's what is in the example data.

import csv
import ijson
import io

from collections import ChainMap

defaults = {}

#first pass through json data collect all collumn names
#they will be used for the field names in the csv file
# and for default values when writing the csv file
with io.StringIO(jsondata) as jsonfile:
for (prefix, event, value) in ijson.parse(jsonfile):
if event == "string":
defaults[prefix] = ''

# row.maps[0] will be updated as each new top level json objec
# is read from the json file.  row.maps[1] holds the default values
# for csv.DictWriter
row = ChainMap({}, defaults)

# StringIO is used for prototyping, you'll probably want to
# change them to open(filename, ...) or something
with io.StringIO(jsondata) as jsonfile, io.StringIO() as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=list(defaults.keys()))

for (prefix, event, value) in ijson.parse(jsonfile):
if event == "string":
row[prefix] = value

# if we're at the top-level key (prefix=='') and we are starting a new
# row (event=='map_key') or were all done (event=='end_map') and there is
# a row to write (row.maps[0] not empty), then write a row to the csvfile
# and clear the row for the next top level json object
elif prefix=='' and event in ('map_key', 'end_map') and row.maps[0]:
print(row)
writer.writerow(row)
row.maps[0].clear()

# this is to see what would be in the file.  It's here, inside the with
# because the csvfile gets deleted when the with statement ends
print(csvfile.getvalue())


A classic pattern is to set a ceiling for memory consumption and write a buffer function. Once you hit the buffer limit, dump everything to a partial file ("file_part1.csv") and begin writing to the next partial file. Once you're done writing everything, stitch the files together as a single csv.

Chapter 12 of the free Python reference "Python for Everybody" demonstrates the pattern. The chapter is written about networked programs, but the examples still apply.

• The format of the CSV output is not good for this. – Peilonrayz May 2 '20 at 12:53
• The original poster's stated purpose is to create a csv file. When I post here I always try to keep in mind what the OP is asking for! – Kyle Stone May 25 '20 at 16:48
• Yes but the order of the output is important. If the output were being added vertically then your solution would be viable. Since it's not you're still stuck with the same problem with merging multiple data sets horizontally. – Peilonrayz May 25 '20 at 16:51