Write a Dictionary With Multiple Values to Store Data in Columns in the CSV File

Write a dictionary with multiple values to store data in columns in the CSV file

I've modified your function so it doesn't use a global variable which generally should be avoid whenever possible. The "trick" here is to use the built-in zip() function to transpose the values in the dictionary so they're in column-major instead of row-major order.

To understand better what's going on in the code below, first consider that the dictionary.values() call is producing the following sequence from the sample data shown in the dictionary in your question:

[1, 2, 3], [4, 5, 6], [6, 7, 8]

All of these are passed to the zip() function all-at-once by using the following shorthand Python notation (note the * prefix):

zip(*dictionary.values())

Which is equivalent to calling it like this:

zip([1, 2, 3], [4, 5, 6], [6, 7, 8])

So the zip() function rearranges them resulting it it producing this sequence:

(1, 4, 6), (2, 5, 7), (3, 6, 8)

Another way to to think about is to consider the values as a 2D matrix:

1, 2, 3
4, 5, 6
6, 7, 8

in which case zip() is doing to them:

1, 4, 6
2, 5, 7
3, 6, 8

In other words, it's exchanging the rows and columns of the matrix, which is called "transposing" the matrix. As I said, it's being done here to put them into the order needed to write the column values row-by-row into csv file.

Anyway, here's the code:

import csv

def save_output(dictionary, output_file_name):
    with open(output_file_name, 'w', newline='') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow(dictionary)  # First row (the keys of the dictionary).
        for values in zip(*dictionary.values()):
            writer.writerow(values)

mydict = {'k1': [1, 2, 3], 'k2': [4, 5, 6], 'k3': [6, 7, 8]}

save_output(mydict, 'test_output.csv')

Contents of the test_output.csv file produced:

k1,k2,k3
1,4,6
2,5,7
3,6,8

writing data to csv from dictionaries with multiple values per key

For a much simpler answer, you just need to add one line of code to what you have:

row = [row[0]] + row[1]

so:

for employee, company in izip_longest(e.items(), c.items()):
        row = list(employee)
        row = [row[0]] + row[1]
        row += list(company) if company is not None else ['', '']  # Write empty fields if no company

How to create a dictionary with one key and multiple values from a CSV file?

csv.DictReader by default takes the first row as the keys of the dictionary, so that won't work here since you want the first column as the keys.

So you can read the csv file using csv.reader and then iterate over the rows and create your dictionary using dictionary comprehension

import csv

mydict = {}

#Open the file in read mode
with open('input_experiment.csv', mode='r') as infile:
    #Open a reader to the csv, the delimiter is a single space
    reader = csv.reader(infile, delimiter=' ', skipinitialspace=True)

    #Read into the dictionary using dictionary comprehension, key is the first column and row are rest of the columns
    mydict = { key: row for key, *row in reader }

print(mydict)

So if the input file is

EOLB-98 2 4 3 1 4 4 CCPZ-81 CTCB-18 VBOX-39
LKHN-41 3 3 1 1 4 3 BYAP-21 QENC-92 JSZQ-42
NWVF-51 5 3 2 4 3 5 YWVL-18 KPCC-99 FIMD-24
XVGP-15 1 4 1 1 4 1 DZCP-35 WMBB-45 XTCH-99

The output will be

{'EOLB-98': ['2', '4', '3', '1', '4', '4', 'CCPZ-81', 'CTCB-18', 'VBOX-39'], 
'LKHN-41': ['3', '3', '1', '1', '4', '3', 'BYAP-21', 'QENC-92', 'JSZQ-42'], 
'NWVF-51': ['5', '3', '2', '4', '3', '5', 'YWVL-18', 'KPCC-99', 'FIMD-24'], 
'XVGP-15': ['1', '4', '1', '1', '4', '1', 'DZCP-35', 'WMBB-45', 'XTCH-99']}

How to write dictionary with multiple values to a CSV?

Loop through the dictionary keys/values, then loop through the values:

import csv

D = { 1: ['apple', 'orange'],
      2: ['fennel', 'basil', 'bay leaves'],
      3: ['almonds', 'walnuts']}

# with open('out.csv','w',newline='') as f:  # Python 3
with open('out.csv','wb') as f:              # Python 2
    w = csv.writer(f)
    w.writerow(['list_id','list_items'])
    for key,items in D.items():
        for item in items:
            w.writerow([key,item])

Converting a csv to dict with multiple values

You can skip Pandas and deal with the file directly. Since you actually have a two character delimiter ', ' it is easier to skip csv too:

di={}
with open('/tmp/fruit.csv') as f:
    for x,y in (line.rstrip().split(', ') for line in f):
        di.setdefault(y, []).append(x)

>>> di
{'meat': ['chicken', 'veal'], 'carbs': ['rice', 'potato'], 'veggies': ['carrot', 'mushroom'], 'fruits': ['apples']}

Or use pandas:

df=pd.read_csv('/tmp/fruit.csv',  header=None, sep=', ', engine='python').groupby([1])[0].agg(list).to_dict()

>>> df
{'carbs': ['rice', 'potato'], 'fruits': ['apples'], 'meat': ['chicken', 'veal'], 'veggies': ['carrot', 'mushroom']}

How do I write a csv file from a dictionary with multiple values per key?

You can use csv.DictWriter. From the docs:

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'})
    writer.writerow({'first_name': 'Lovely', 'last_name': 'Spam'})
    writer.writerow({'first_name': 'Wonderful', 'last_name': 'Spam'})

---

Related Topics