List of list of dictionaries to CSV

Question

I know I can write a list of dictionaries directly to a CSV file. Similarly, is there a direct way to write a list of list of dictionaries to a csv file in python without iterating through each line manually?

Sample data

[[{'e': 46, 'p': 100, 'n': 0, 'a': 100, ...}, 
  {'e': 29, 'p': 40, 'n': 1, 'a': 40, ...}, ...],
 [{...}, ...]

Expected format

e,p,n,a,....
46,100,0,100,....
29,40,1,40,...
.......

Note this is not a list of dictionaries, but a list of list of dictionaries

Do the dictionaries all have the same format regardless of whether they're in a sublist (i.e., are the keys still the same)? — coltonoscopy, Dec 03 '18 at 00:43
@coltonoscopy yes, all the data instances contains the same set of fields — Thusitha Thilina Dayaratne, Dec 03 '18 at 00:50

slider · Accepted Answer · 2018-12-03T01:03:42.750

6

Without Pandas, you can use itertools.chain to get a flattened list of all dictionaries and then write that to your CSV file with csv.DictWriter:

import csv
from itertools import chain

data = [
    [{'e': 46, 'p': 100, 'n': 0, 'a': 100}, 
     {'e': 29, 'p': 40, 'n': 1, 'a': 40}],
    [{'e': 56, 'p': 200, 'n': 23, 'a': 10}, 
     {'e': 22, 'p': 41, 'n': 11, 'a': 420}]]

fieldnames = ['e', 'p', 'n', 'a']
with open('mydata.csv', 'w') as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(chain.from_iterable(data))

Output (mydata.csv)

e,p,n,a
46,100,0,100
29,40,1,40
56,200,23,10
22,41,11,420

edited Dec 03 '18 at 01:03

answered Dec 03 '18 at 00:53

slider

11,349
1
22
37

This works like charm :) However, I get empty lines in between each row. I can manually remove them. But is there any parameter to get rid of that? – Thusitha Thilina Dayaratne Dec 03 '18 at 00:58
@ThusithaThilinaDayaratne I didn't get empty lines with example. That is strange. – slider Dec 03 '18 at 01:00
1

I could get rid of empty lines using 'wb' instead of 'w' (y) Seems like a system line termination config – Thusitha Thilina Dayaratne Dec 03 '18 at 01:09

DYZ · Answer 2 · 2018-12-03T00:53:07.877

2

There must be a way to accomplish your task using just core Python, but I would go for Pandas:

import pandas as pd
d = yourListOfListsOfDictionaries
df = pd.concat(map(pd.DataFrame, d), sort=True)
#     a   e  n    p
#0  100  46  0  100
#0   40  29  1   40
df.to_csv(index=False)
#'a,e,n,p\n100,46,0,100\n40,29,1,40\n'

edited Dec 03 '18 at 00:53

answered Dec 03 '18 at 00:43

DYZ

45,526
9
47
76

score 2 · Answer 3 · answered Dec 03 '18 at 00:56

If you want the set of keys to be the union of all of the dictionaries in the list of list of dicts, then you can do something like this:

import csv
x = \
[[{'e': 46, 'p': 100, 'n': 0, 'a': 100},
  {'e': 29, 'p': 40, 'n': 1, 'a': 40}],
 [{'e': 19, 'p': 10, 'n': 1, 'a': 10, 'b':8}]]

key_dict = {}
for l in x:
    for d in l:
        for k in d:
            key_dict[k] = None

with open('file.csv', 'w') as csvfile:
    writer = csv.DictWriter(csvfile, key_dict.keys())
    writer.writeheader()
    for l in x:
        for d in l:
            writer.writerow(d)

Result:

a,p,b,e,n
100,100,,46,0
40,40,,29,1
10,10,8,19,1

score 2 · Answer 4 · answered Dec 03 '18 at 00:56

If the dictionaries have the same format, just flatten the list like so (assuming it's indeed a list of lists, two-dimensional):

data = [
    [{'a': 10, 'b': 20, 'c': 30}],
    [{'a': 20, 'b': 30, 'c': 40},
     {'a': 30, 'b': 40, 'c': 50}]
]
rows = [item for sublist in data for item in sublist]

Then just write your rows to the CSV:

with open('my_data.csv', 'wb') as output_file:
    dict_writer = csv.DictWriter(output_file, rows[0].keys())
    dict_writer.writeheader()
    dict_writer.writerows(rows)

A combination of the two following posts:

How to make a flat list out of list of lists?

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

List of list of dictionaries to CSV

4 Answers4