Retrieve values of excel as python dictionary

Question

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.

`todict()` makes a dictionary of lists, not a list of dictionaries. So `reader` is a list, and thus `reader['Name]` makes no sense. — Willem Van Onsem, Sep 23 '19 at 09:54
https://stackoverflow.com/questions/14196013/python-creating-dictionary-from-excel-data — SM Abu Taher Asif, Sep 23 '19 at 09:54

Willem Van Onsem · Accepted Answer · 2021-04-28T09:19:48.530

5

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'])

edited Apr 28 '21 at 09:19

answered Sep 23 '19 at 09:59

Willem Van Onsem

443,496
30
428
555

I think it should be `records` instead of `record` like specified here: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_dict.html – Antoine Delia Apr 28 '21 at 08:23
@AntoineDelia: for older versions it was `record`. If we do this with `record`, it prints a warning: `utureWarning: Using short name for 'orient' is deprecated. Only the options: ('dict', list, 'series', 'split', 'records', 'index') will be used in a future version.`. But I updated this to a more recent pandas version in the answer. Thanks! – Willem Van Onsem Apr 28 '21 at 09:19

score 0 · Answer 2 · answered Sep 23 '19 at 10:23

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)

Retrieve values of excel as python dictionary

2 Answers2