CODEWITHSUNDEEP
pythonpython-3.xxlrd
user1651888
user1651888

Reputation: 463

python creating dictionary from excel

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}

Upvotes: 5

Views: 19176

Answers (2)

Marek
Marek

Reputation: 708

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

Upvotes: 7

rchang
rchang

Reputation: 5236

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).

Upvotes: 0

Related Questions