Deuce525 Deuce525 - 5 months ago 33
Python Question

Loop from excel data into a dictionary with multiple values per key

I am fairly new to Python and I think this question is fairly easy but I can't figure it out...

I have a data table in excel in which I have B column strings and C through I columns as values. I want to create a dictionary in which for each key value in
B column, I assign values of the columns C through I. I figured out how to do it per row, one at at time but I'm looking for a

for
loop syntax to do it
throughout the entire excel data table.

Here's my code:

NYSE = {}
NYSE.setdefault(sheet['B5'].value, []).append(sheet['C5'].value)
NYSE.setdefault(sheet['B5'].value, []).append(sheet['D5'].value)
NYSE.setdefault(sheet['B6'].value, []).append(sheet['C6'].value)
NYSE.setdefault(sheet['B6'].value, []).append(sheet['D6'].value)
print NYSE


I can keep manually adding to this...B7 C7, B7 D7, etc, but there must be a way to loop this in a function and output the dictionary.

Answer

You could try something like this:

from collections import defaultdict

d = defaultdict(list)
for line in open("pyex.csv").readlines():
    line = line.strip()
    line = line.split(",")
    key, value = line[0], line[1:]
    d[key] += value
print(d)

So if you have a csv file that looks like this. Where the first column are strings, and every column second and after are the values:

crow    19    13
bird    16    32

this code would output:

defaultdict(<class 'list'>, {'crow ': ['19', '13'], 'bird': ['16', '32']})

[Finished in 0.1s]

This allows you to have multiple values for each key, since the values are contained in a list.

UPDATE:

Using setdefault instead:

d = {}
for line in open("pyex.csv").readlines():
    line = line.strip()
    line = line.split(",")
    key = line[0]
    for value in line[1:]:
        d.setdefault(key, []).append(value)
print(d)

Output:

{'crow': ['19', '13'], 'bird': ['16', '32']}

Or even with the csv library

import csv

csv_file = open("pyex.csv")
csv_reader = csv.reader(csv_file)

d = {}
for line in csv_reader:
    key = line[0]
    for value in line[1:]:
        d.setdefault(key, []).append(value)
print(d)

As @martineu said, you don't need defaultdicts or setdefaults:

import csv

csv_file = open("Book1.csv")
csv_reader = csv.reader(csv_file)

d = {}
for line in csv_reader:
    key = line[0]
    d[key] = line[1:]
print(d)