Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert list of dicts to CSV in Python 3

I got a list of dictionaries with different length and even different (key: values) pairs. For example:

[
    {'key1': 'value1', 'key3':'value3'},
    {'key1': 'someValue', 'key2':'value2', 'key3':'value3'},
    {'anotherKey': 'anotherValue', 'key1': 'value1', 'key2':'value2'},
    {'anotherKey': 'anotherValue', 'anotherKey1': 'anotherValue1', 'key1': 'value1', 'key2':'value2', 'key3':'value3'},
]

I need to create CSV file with all of keys as headers and values. If key not in the current dictionary then set the default value (for example '-'). The CSV from example should be looking like that:

enter image description here

I'm trying this code for my list of dictionaries, but it returns an error:

listOfDicts = [
    {'key1': 'value1', 'key3':'value3'},
    {'key1': 'someValue', 'key2':'value2', 'key3':'value3'},
    {'anotherKey': 'anotherValue', 'key1': 'value1', 'key2':'value2'},
    {'anotherKey': 'anotherValue', 'anotherKey1': 'anotherValue1', 'key1': 'value1', 'key2':'value2', 'key3':'value3'},
]

keys = listOfDicts[0].keys()
with open('test.csv', 'a') as output_file:
    dict_writer = csv.DictWriter(output_file, fieldnames=keys, delimiter='@')
    dict_writer.writeheader()
    dict_writer.writerows(listOfDicts)

ERROR:

ValueError: dict contains fields not in fieldnames: 'key2'

How I can add all unique keys as headers for CSV and fill it values by key?

like image 371
Konstantin Rusanov Avatar asked Nov 07 '18 14:11

Konstantin Rusanov


People also ask

How do I put a list of dictionaries in a CSV file?

To write a dictionary of list to CSV files, the necessary functions are csv. writer(), csv. writerow(). This method writes a single row at a time.

How do I convert a dictionary to csv?

Easiest way is to open a csv file in 'w' mode with the help of open() function and write key value pair in comma separated form. The csv module contains DictWriter method that requires name of csv file to write and a list object containing field names.

Can dict value be a list?

It definitely can have a list and any object as value but the dictionary cannot have a list as key because the list is mutable data structure and keys cannot be mutable else of what use are they.


2 Answers

Use DicitWritter() restval parameter,

The optional restval parameter specifies the value to be written if the dictionary is missing a key in fieldnames.

and for fieldnames parameter use a list of all available keys in a list of dictionaries.

import csv


listOfDicts = [
    {'key1': 'value1', 'key3':'value3'},
    {'key1': 'someValue', 'key2':'value2', 'key3':'value3'},
    {'anotherKey': 'anotherValue', 'key1': 'value1', 'key2':'value2'},
    {'anotherKey': 'anotherValue', 'anotherKey1': 'anotherValue1', 'key1': 'value1', 'key2':'value2', 'key3':'value3'},
]

keys = [i for s in [d.keys() for d in listOfDicts] for i in s]

with open('test.csv', 'a') as output_file:
    dict_writer = csv.DictWriter(output_file, restval="-", fieldnames=keys, delimiter='@')
    dict_writer.writeheader()
    dict_writer.writerows(listOfDicts)

output:

$ cat test.csv 
key3@key1@key2@anotherKey@anotherKey1
value3@value1@-@-@-
value3@someValue@value2@-@-
-@value1@value2@anotherValue@-
value3@value1@value2@anotherValue@anotherValue1

Reference: https://docs.python.org/2/library/csv.html#csv.DictWriter

like image 77
Raoslaw Szamszur Avatar answered Sep 19 '22 21:09

Raoslaw Szamszur


To overcome the error, you can collect all the keys before writing the file, like this:

keys = set()
for d in listOfDicts:
    keys.update(d.keys())

with open('test.csv', 'a') as output_file:
    dict_writer = csv.DictWriter(
        output_file, fieldnames=keys, restval='-', delimiter='@')
    dict_writer.writeheader()
    dict_writer.writerows(listOfDicts)

You can use the parameter DictWriter.restval to assign default values for missing keys.

like image 39
Ralf Avatar answered Sep 20 '22 21:09

Ralf