Write a list of dictionaries in Python CSV

Question

Suppose I have a list of data dictionaries, for example,

data_set = [ {'Active rate': [0.98, 0.97, 0.96]}, {'Operating Expense': [3.104, 3.102, 3.101]} ] 

I need to iterate over the list of dictionaries and put the keys in the column headings and their values ​​as strings and write them to a CSV file.

 Active rate Operating Expense 0.98 3.104 0.97 3.102 0.96 3.101 

Here is what I tried

 data_set = [ {'Active rate': [0.98, 0.931588, 0.941192]}, {'Operating Expense': [3.104, 2.352, 2.304]} ] import csv with open('names.csv', 'w') as csvfile: fieldnames = ['Active rate', 'Operating Expense'] writer = csv.DictWriter(csvfile, fieldnames=fieldnames) writer.writeheader() writer.writerow({'Active rate': 0.98, 'Operating Expense': 3.102}) writer.writerow({'Active rate': 0.97, 'Operating Expense': 3.11}) writer.writerow({'Active rate': 0.96, 'Operating Expense': 3.109}) 

For brevity, I reduced the keys to 2 and the list of values ​​to 3.

How to approach this problem?

thanks

Answer 1

 data_set = [ {'Active rate': [0.98, 0.97, 0.96]}, {'Operating Expense': [3.104, 3.102, 3.101]} ] 

First, just a quick comment, your original data structure does not necessarily make sense as it is. You use a list of dicts, but each dict seems to use only one key, which seems to be pursuing the goal.

Other data structures that might make more sense would be like this (where each dict structure is used, as it is currently, for one label / value pair, but at least a dict is used to indicate the label and value ):

 data_set = [ {'label': 'Active rate', 'values': [0.98, 0.97, 0.96]}, {'label': 'Operating Expense', 'values': [3.104, 3.102, 3.101]} ] 

or perhaps better, OrderedDict , which gives you both the order of your original dataset and the benefits of key / value mapping:

 from collections import OrderedDict data_set = OrderedDict() data_set['Active rate'] = [0.98, 0.97, 0.96] data_set['Operating Expense'] = [3.104, 3.102, 3.101] 

Of course, we do not always choose the data structures that we receive, so suppose you cannot change them. Then your question will become a problem for replacing the roles of rows and columns from the original dataset. In fact, you want to iterate over multiple lists at once, and zip is very useful for this.

 import csv fieldnames = [] val_lists = [] for d in data_set: # Find the only used key. # This is a bit awkward because of the initial data structure. k = d.keys()[0] fieldnames.append(k) val_lists.append(d[k]) with open('names.csv', 'w') as csvfile: writer = csv.writer(csvfile) writer.writerow(fieldnames) for row in zip(*val_lists): # This picks one item from each list and builds a list. # The first row will be [0.98, 3.104] # The second row will be [0.97, 3.102] # ... writer.writerow(row) 

Note that there is no need to use DictWriter when you are using zip , as that means you need to rebuild the dict without any real benefit.

Answer 2

For the data structure you gave, the following approach should work:

 import csv data_set = [ {'Active rate': [0.98, 0.97, 0.96]}, {'Operating Expense': [3.104, 3.102, 3.101]} ] fieldnames = ['Active rate', 'Operating Expense'] rows = [] for field in fieldnames: for data in data_set: try: rows.append(data[field]) break except KeyError, e: pass with open('names.csv', 'wb') as f_output: csv_output = csv.writer(f_output) csv_output.writerow(fieldnames) csv_output.writerows(zip(*rows)) 

Providing you with the following CSV output file:

 Active rate,Operating Expense 0.98,3.104 0.97,3.102 0.96,3.101 

Answer 3

 d1 = {'Active rate': [0.98, 0.931588, 0.941192]} d2 = {'Operating Expense': [3.104, 2.352, 2.304]} with open('names.csv', 'w') as csvfile: fieldnames = zip(d1, d2)[0] writer = csv.DictWriter(csvfile, fieldnames=fieldnames) writer.writeheader() for row in zip(d1['Active rate'], d2['Operating Expense']): writer.writerow(dict(zip(fieldnames, row))) 

For performance, you can use itertools.izip over zip depending on the length of the lists.

Answer 4

(This answer has the disadvantage of using an external library, but)

pandas already provides unusually powerful and simple tools for working with csv files. You can use to_csv .

Note that the data structure is awkwardly structured, so we will first convert it to a more intuitive structure.

 data_set2 = { x.keys()[0] : x.values()[0] for x in data_set } import pandas as pd df = pd.DataFrame(data_set2) df.to_csv('names.csv', index = False) 

Answer 5

This code will help you without binding to a specific number of dicts inside data_set

I added another dict with the "Losses" key to check

 import csv data_set = [ {'Active rate': [0.98, 0.97, 0.96]}, {'Operating Expense': [3.104, 3.102, 3.101]}, {'Losses': [1.14, 2.28, 3.42]} ] headers = [d.keys()[0] for d in data_set] with open('names.csv', 'w') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=headers) writer.writeheader() for item in zip(*[x.values()[0] for x in data_set]): more_results = list() more_results.append(headers) more_results.append(item) writer.writerow(dict(zip(*more_results))) 

Output: