openpyxl Part 5 – Converting Spreadsheet Data into a Dictionary

In the previous part of the openpyxl series we were iterating over the data in the spreadsheet. In this part we’ll have a look at how to convert spreadsheet data into a dictionary.

This time we’ll be using a spreadsheet that will contain some more useful data. It’s saved as wb2.xlsx. I just entered the data (which is fictional, by the way) by hand and saved the workbook. Here it is:

As you can see, there are 10 rows (1-10) and 5 columns (A-E). The first row contains the headers. As we are going to work with this workbook now, let’s import it:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

In the previous part we saw that the data was returned as tuples. Suppose we want to retrieve all the rows, including the headers row:

>>> for value in sheet.iter_rows(values_only=True):
...     print(value)
... 
('company', 'city', 'category', 'income', 'number_of_employees')
('Roses and Pansies', 'Toronto', 'gardening', 456845, 26)
("Reader's Paradise", 'Sydney', 'publisher', 854782, 44)
('Dogs and Cats', 'Madrid', 'pets', 458784, 36)
('Enjoy Your Meal', 'New Orleans', 'restaurant', 447884, 18)
('Bloom Store', 'London', 'gardening', 1254654, 84)
('Tastes of the South', 'Miami', 'restaurant', 587454, 42)
('Pet For You', 'Athens', 'pets', 987887, 21)
("Eat 'Em Up", 'Helsinki', 'restaurant', 1254477, 53)
('Mr. Bunny', 'Vienna', 'pets', 254478, 12) 

But sometimes tuples is not what you need. Luckily, you can use all the Python techniques to convert the data to any other format. The dictionary is a format that often comes in handy, so let’s convert the data to a dictionary:

>>> companies = {}
>>> for row in sheet.iter_rows(min_row=2, values_only=True):
...     company_name = row[0]
...     company_data = {
...         'city': row[1],
...         'category': row[2],
...         'income': row[3],
...         'number of employers': row[4]
...         }
...     companies[company_name] = company_data

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

As you can see, we’re using the names of the companies as keys and the other company data is presented as a dictionary too.

Now we can retrieve the data like so:

>>> companies['Enjoy Your Meal']
{'city': 'New Orleans', 'category': 'restaurant', 'income': 447884, 'number of employers': 18}
>>> companies['Pet For You']['income']
987887

Another data type we may often want to convert our spreadsheet data into is the class. In the next part we’ll see how to do that.

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.