python creating dictionary from excel

Question

I have an excel sheet with 2 columns. Column 1 is name, and Column 2 is age. I want to create a dictionary where name is key and age is value. Here is the code, but it is creating a dictionary incorrectly.

keyValues = [x.value for x in worksheet.col(0)]
data = dict((x, []) for x in keyValues)
while curr_row < num_rows:
        curr_row += 1
        for row_index in range(1, worksheet.nrows):  data[keyValues[row_index]].append(worksheet.cell_value(curr_row, 1))

I want to have a dictionary like below coming from 2 columns of excel sheet.

{'Ann': 12, 'Maria': 3, 'Robin': 4, 'NameN':N}

Marek · Accepted Answer · 2015-02-10 06:35:15Z

7

That's quite simple with pandas:

import pandas as pd
my_dic = pd.read_excel('names.xlsx', index_col=0).to_dict()

my_dic is now:

{'Robin': 4, 'Maria': 3, 'Ann': 12}

index_col=0 if 'name' is in the first column of your excel file

edited Feb 10, 2015 at 6:35

answered Feb 10, 2015 at 6:23

Marek

7184 silver badges15 bronze badges

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

1 Comment

piah Over a year ago

Hej! I would get {12: {'Maria': 3, 'Robin': 4}}. Do you know what my problem could be? @Marek

rchang · Accepted Answer · 2015-02-09 20:35:00Z

0

If your columns don't contain any data other than the names and ages and you're pretty confident about the data quality (no non-numeric data where ages should be, etc.) then you could go about it something like this:

names = (name.value for name in worksheet.col(0))
ages = (int(age.value) for age in worksheet.col(1))
data = dict(zip(names, ages))

If you want to guarantee order, you could use a collections.OrderedDict object for data instead of a vanilla dictionary.

If you need to handle bad data (i.e. someone entered a string instead of an integer age in the sheet), you may have to come up with custom implementations of the names and ages generator objects (the ones above assume smooth sailing).

answered Feb 9, 2015 at 20:35

rchang

5,2441 gold badge18 silver badges26 bronze badges

Collectives™ on Stack Overflow

python creating dictionary from excel

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related