How do I convert this list of dictionaries to a csv file?

Question

I have a list of dictionaries that looks something like this:

toCSV = [{'name':'bob','age':25,'weight':200},{'name':'jim','age':31,'weight':180}]

What should I do to convert this to a csv file that looks something like this:

name,age,weight
bob,25,200
jim,31,180

Demobilize

possible duplicate of writing header in csv python with DictWriter – Phenoxide

Answer 1

import csv

to_csv = [
    {'name': 'bob', 'age': 25, 'weight': 200},
    {'name': 'jim', 'age': 31, 'weight': 180},
]

keys = to_csv[0].keys()

with open('people.csv', 'w', newline='') as output_file:
    dict_writer = csv.DictWriter(output_file, keys)
    dict_writer.writeheader()
    dict_writer.writerows(to_csv)

Subaxillary

Is there a way of doing this when the data is embedded? for example if it is {{"first":"John", "last": "Doe"}, uri} for each entry in the array but you want the csv to contain only data for first and last? – Salchunas

How can I write file like this to CSV format if I have Cyrillic symbols in dictionary's values? I tried .encode('utf-8') but unfortunately in CSV file values do not shows correctly. – Ornis

this program write output in reverse order like weightage name weight 25 bob 200 31 jim 180 – Sedgewick

Does not work if first list item does not contain all keys – Phalange

set().union(*(d.keys() for d in mylist)) to get all the keys in the list (if you have some which don't have all the keys.) – Jephthah

The data does not seem to split into columns for me using this answer, but instead is written into in a single column seperated by comma. Is this normal behavior? – Bayreuth

@Bayreuth Yes, this is normal. A CSV file is just a text file. You are probably using a "table calculation tool" to view your CSV. You have to import the data from the CSV (while specifying a delimiter character and a quoting character) into your software/tool to be able to view different columns. – Transferase

Answer 2

In python 3 things are a little different, but way simpler and less error prone. It's a good idea to tell the CSV your file should be opened with utf8 encoding, as it makes that data more portable to others (assuming you aren't using a more restrictive encoding, like latin1)

import csv
toCSV = [{'name':'bob','age':25,'weight':200},
         {'name':'jim','age':31,'weight':180}]
with open('people.csv', 'w', encoding='utf8', newline='') as output_file:
    fc = csv.DictWriter(output_file, 
                        fieldnames=toCSV[0].keys(),

                       )
    fc.writeheader()
    fc.writerows(toCSV)

• Note that csv in python 3 needs the newline='' parameter, otherwise you get blank lines in your CSV when opening in excel/opencalc.

Alternatively: I prefer use to the csv handler in the pandas module. I find it is more tolerant of encoding issues, and pandas will automatically convert string numbers in CSVs into the correct type (int,float,etc) when loading the file.

import pandas
dataframe = pandas.read_csv(filepath)
list_of_dictionaries = dataframe.to_dict('records')
dataframe.to_csv(filepath)

Note:

• pandas will take care of opening the file for you if you give it a path, and will default to utf8 in python3, and figure out headers too.
• a dataframe is not the same structure as what CSV gives you, so you add one line upon loading to get the same thing: dataframe.to_dict('records')
• pandas also makes it much easier to control the order of columns in your csv file. By default, they're alphabetical, but you can specify the column order. With vanilla csv module, you need to feed it an OrderedDict or they'll appear in a random order (if working in python < 3.5). See: Preserving column order in Python Pandas DataFrame for more.

Sideway

How is list_of_dictionaries written to CSV? I can't make sense of the second code example. – Bailable

@IainSamuelMcLeanElder .to_dict returns your dataframe in one of several formats, depending what you specify. ('records') returns a list of dictionaries where each column is a dictionary, and .to_dict('index') returns a dictionary of dictionaries, with top-level keys being the index values, and the nested dictionary being column:value pairs. Depending on how you export your csv, you choose the structure the CSV function expects. – Sideway

Your second code example doesn't seem to answer the OP's question. Shouldn't it be using from_dict somwhere? I had the same problem and that's what worked for me. That's good to know about to_dict, but it seems more relevant for reading, not writing. – Bailable

Answer 3

this is when you have one dictionary list:

import csv
with open('names.csv', 'w') as csvfile:
    fieldnames = ['first_name', 'last_name']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerow({'first_name': 'Baked', 'last_name': 'Beans'})

Pussy

Answer 4

a short solution with Pandas

import pandas as pd

list_of_dicts = [
    {'name': 'bob', 'age': 25, 'weight': 200},
    {'name': 'jim', 'age': 31, 'weight': 180},
]

df = pd.DataFrame(list_of_dicts) 
df.to_csv("names.csv", index=False)

Brodsky

Answer 5

Because @User and @BiXiC asked for help with UTF-8 here a variation of the solution by @Matthew. (I'm not allowed to comment, so I'm answering.)

import unicodecsv as csv
toCSV = [{'name':'bob','age':25,'weight':200},
         {'name':'jim','age':31,'weight':180}]
keys = toCSV[0].keys()
with open('people.csv', 'wb') as output_file:
    dict_writer = csv.DictWriter(output_file, keys)
    dict_writer.writeheader()
    dict_writer.writerows(toCSV)

Goat

Answer 6

Here is another, more general solution assuming you don't have a list of rows (maybe they don't fit in memory) or a copy of the headers (maybe the write_csv function is generic):

def gen_rows():
    yield OrderedDict(name='bob', age=25, weight=200)
    yield OrderedDict(name='jim', age=31, weight=180)

def write_csv():
    it = genrows()
    first_row = it.next()  # __next__ in py3
    with open("people.csv", "w") as outfile:
        wr = csv.DictWriter(outfile, fieldnames=list(first_row))
        wr.writeheader()
        wr.writerow(first_row)
        wr.writerows(it)

Note: the OrderedDict constructor used here only preserves order in python >3.4. If order is important, use the OrderedDict([('name', 'bob'),('age',25)]) form.

Longevous

never saw anyone store data in a generator before - interesting approach. – Sideway

Answer 7

import csv

with open('file_name.csv', 'w') as csv_file:
    writer = csv.writer(csv_file)
    writer.writerow(('colum1', 'colum2', 'colum3'))
    for key, value in dictionary.items():
        writer.writerow([key, value[0], value[1]])

This would be the simplest way to write data to .csv file

Orly

Answer 8

import csv
toCSV = [{'name':'bob','age':25,'weight':200},
         {'name':'jim','age':31,'weight':180}]
header=['name','age','weight']     
try:
   with open('output'+str(date.today())+'.csv',mode='w',encoding='utf8',newline='') as output_to_csv:
       dict_csv_writer = csv.DictWriter(output_to_csv, fieldnames=header,dialect='excel')
       dict_csv_writer.writeheader()
       dict_csv_writer.writerows(toCSV)
   print('\nData exported to csv succesfully and sample data')
except IOError as io:
    print('\n',io)

Sergias