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

Question

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.

Are you able to convert it to a csv file?

RoadRunner
– RoadRunner

2016-06-24 14:57:17 +00:00
Commented Jun 24, 2016 at 14:57 — RoadRunner
– RoadRunner, Commented Jun 24, 2016 at 14:57
Yes i could use either csv or xlsx file

Deuce525
– Deuce525

2016-06-24 15:01:10 +00:00
Commented Jun 24, 2016 at 15:01 — Deuce525
– Deuce525, Commented Jun 24, 2016 at 15:01

RoadRunner · Accepted Answer · 2016-06-25 03:37:55Z

1

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)

edited Jun 25, 2016 at 3:37

answered Jun 24, 2016 at 15:08

RoadRunner

26.4k6 gold badges46 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Deuce525 Over a year ago

i tried this quickly but i got the following error: defaultdict(<type 'list'>, {})

RoadRunner Over a year ago

hmmmm, do you think it would be okay if I could see your csv file? I also edited my answer to accommodate more values.

RoadRunner Over a year ago

I wil try using setdefault instead

RoadRunner Over a year ago

How is that @Deuce525?

Deuce525 Over a year ago

Hey i think that update with setdefault got me closer but it printed an empty dictionary {}

|

Jeff · Accepted Answer · 2016-06-24 14:47:35Z

1

Have you considered using Pandas? I'm not sure what your goal is with the data, but it seems like the most robust way to do what you want.

If you use pandas.read_excel(path, sheetname=None) then it will default to creating a dictionary where each key is a sheet and each value is a dataframe of that sheet. You can then iterate over your dictionary to merge them all together. It would be easier to get more specific with a pseudo-sample of your data.

answered Jun 24, 2016 at 14:47

Jeff

2,2381 gold badge19 silver badges30 bronze badges

2 Comments

Deuce525 Over a year ago

does this allow to have each row in the data be assigned both key and mulitple values? For example, B column are all keys and C, D, E are all values per row in the table.

Jeff Over a year ago

I'm having a hard time visualizing your data. Are these columns spread across multiple sheets that need to be related on a key, or are they just columns within the same sheet? Either way the answer is yes - a dataframe is essentially a relational structure that lets you work with data by rows and columns and perform all sorts of operations.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related