CSV header based on JSON dictionary keys

Question

I have a JSON like this for example:

[
    {
        'name':'test1',
        'random1':'random_1_value'
    },
    {
        'name':'test2',
        'random2':'random_2_value'
        'random4':'random_4_value'
    },
    {
        'name':'test3',
        'random3':'random_3_value'
    },
]

I want to convert this JSON and construct CSV header based on dictionary keys. Then fill each row respectively. Expected output:

name, random1, random2, random4, random3
test1, random_1_value
test2, ,random_2_value, random_4_value, ,
test3, , , , random_3_value

This is my code so far:

data = json.loads(open('output_data.json').read())
csvwriter = csv.writer(open("output.csv", "w"))
count = 0
for emp in data:
    if count == 0:
        header = emp.keys()
        csvwriter.writerow(header)
        count += 1
    csvwriter.writerow(emp.values())

Are you interested in a very very short and easy command line solution? — aborruso, Sep 11 '19 at 08:09

score 1 · Answer 1 · answered Sep 12 '19 at 07:33

You can use a combination of the collections and csv modules in the standard library to do this.

We can use a collections.OrderedDict to get the column names in the order in which they appear.

We use an OrderedDict so that the code will work with versions of Python earlier than 3.7. From Python 3.7 onwards the language guarantees that ordinary dictionaries remember the order in which their keys were inserted. If your code will only be run by Python 3.7+ you can use the dict builtin instead of an OrderedDict.

headers = collections.OrderedDict((key, None) for dict_ in data for key in dict_)

Now that the column names have been generated, use a csv.DictWriter to write each dictionary in the data to the output file. DictWriter automatically handles missing keys, ensuring that each data item is written to the right column.

with open('output.csv', 'w', newlines='') as f:
    writer = csv.DictWriter(f, fieldnames=headers)
    # Write the column names to file.
    writer.writeheader()
    # Write the data to file.
    writer.writerows(data)

score 0 · Answer 2 · answered Sep 11 '19 at 06:57

You should use the with operator when reading and writing files. See here

# Open the JSON file
>>> with open('csv.json') as f:
...     data = json.load(f)

# Get the column names
>>> col = [k for d in data for k in d]

# Create a matrix of the data
>>> matrix = [[d['name']] + [d.get(c, '') for c in col] for d in data]

>>> csv_data = [['name'] + col] + matrix

>>> with open('json.csv', 'w') as f:
...     writer = csv.writer(f)
...     writer.writerows(csv_data)

CSV header based on JSON dictionary keys

2 Answers2