CODEWITHSUNDEEP
pythondjangopandas
Sharath Nayak
Sharath Nayak

Reputation: 217

Retrieve values of excel as python dictionary

Sr. No   Name
1         a
2         b
3         c

Imagine this is my excel file.

And

To get the header:

dic = pandas.read_excel(excelfile).columns

convert excel file into dict:

readers = pandas.read_excel(excelfile).to_dict()

To retrieve values:

for reader in readers:
    reader['Name']

So if I Retrieve values like that, its showing "list indices must be int, not str". How to resolve this issue.

Upvotes: 2

Views: 3610

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476659

A .to_dict() will create a dictionary where the keys are the names of the columns, and the values lists that contain the values.

Indeed, for the given dataframe, we get:

>>> df.to_dict()
{'Sr. No': {0: 1, 1: 2, 2: 3}, 'Name': {0: 'a', 1: 'b', 2: 'c'}}

You probably want to convert it to a list of dictionaries. For older versions of pandas, you can do that by specifying 'records', for older versions, this is record:

>>> df.to_dict('records')
[{'Sr. No': 1, 'Name': 'a'}, {'Sr. No': 2, 'Name': 'b'}, {'Sr. No': 3, 'Name': 'c'}]

for reader in pandas.read_excel(csvfile).to_dict('records'):
    print(reader['Name'])

Upvotes: 5

Rohit Gawas
Rohit Gawas

Reputation: 277

You can try using openpyxl. It is quite easy in openpyxl.Refer following code(Kindly update the code for exact row and column values as per your excel file if required) :

from openpyxl import load_workbook

wb = load_workbook("Path to excel file")
sheet1 = wb.worksheets[0]
list_dict=[]
dict={}
for row_value in range(1,sheet1.max_row+1):
    dict.update({sheet1.cell(row=row_value,column=1).value:sheet1.cell(row=row_value, column=2).value})
    list_dict.append(dict)

print(list_dict)

Upvotes: 0

Related Questions