Efficiently convert 60 GB JSON file to a csv file

Question

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.writeheader()
        w.writerow(flattened_json)


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

    with open(JSON_PATH) as json_file:
        json_data = json.load(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

Answer 1

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.

Answer 2

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

Answer 3

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.