CODEWITHSUNDEEP
pythoncsv
Kevin Le
Kevin Le

Reputation: 49

How to write list values from a dictionary under the key header in a CSV as columns

I'm trying to create a csv file from a dictionary where each value is a list of numbers.

example = {"A" : [1,2,3], "B" : [1], "C": [4,5,6,7,8,9]}

I want the csv to be like:

A,B,C
1,1,4
2, ,5
3, ,6
 , ,7
 , ,8
 , ,9

I tried doing something like:

with open("example.csv", "w", newline='') as f:
    headers = example.keys()
    writer = csv.DictWriter(f, fieldnames=headers)
    writer.writeheader()
    writer.writerow(example)

but it ended up with with something like this in excel:

A,B,C
[1,2,3], [1], [4,5,6,7,8,9]

Some possible things I could think about doing is append an empty space to the smaller list values to have the same length as the longest list value, zip based on the index value then have each tuple be a row. If i do it this way tho, would there be an efficient way of finding the biggest list size and appending empty spaces without a lot of for loops and appending? The list size can be as big as 65000. Also the number of entries in the dictionary is variable, meaning its never a set constant.

Upvotes: 0

Views: 1270

Answers (3)

buran
buran

Reputation: 14233

with just standard library (assume python 3.7+ - for order-preserving feature of dict necessary for the correct order of the keys in the header)

import csv
from itertools import zip_longest

example = {"A" : [1,2,3], "B" : [1], "C": [4,5,6,7,8,9]}
keys, values = zip(*example.items())
data = (dict(zip(keys, row)) for row in zip_longest(*values, fillvalue=''))
with open('output.csv', 'w') as f:
    wrtr = csv.DictWriter(f, fieldnames=keys)
    wrtr.writeheader()
    wrtr.writerows(data)

EDIT: For python version before 3.7* you can not rely on order being preserved, so you need to explicitly provide the header in the desired order (i.e. it cannot come from the dict.keys())

import csv
from itertools import zip_longest

header = ['A', 'B', 'C']
example = {"A" : [1,2,3], "B" : [1], "C": [4,5,6,7,8,9]}

values = (example.get(key, []) for key in header)
data = (dict(zip(header, row)) for row in zip_longest(*values, fillvalue=''))
with open('output.csv', 'w') as f:
    wrtr = csv.DictWriter(f, fieldnames=header)
    wrtr.writeheader()
    wrtr.writerows(data)
  • in 3.6 order-preserving feature is considered implementation detail and should not be relied upon.

Upvotes: 2

r_batra
r_batra

Reputation: 410

One of the solution can be to convert the dictionary into dataframe and then use that for csv file, but keep in mind, still there is no way to assign non equal length array with blank values, it will be NaN.

import pandas as pd 
df = pd.DataFrame.from_dict(example, orient='index') 
df = df.T           # Transpose dataframe to get same orientation as required

enter image description here

Upvotes: 0

Vink
Vink

Reputation: 589

If you can use the pandas library try the following, 

import pandas as pd

example = {"A" : [1,2,3], "B" : [1], "C": [4,5,6,7,8,9]}
df = pd.DataFrame({ key:pd.Series(value) for key, value in example.items() })
df.to_csv('example_dict.csv', index=False)

Upvotes: 0

Related Questions