Python – Pandas – Converting XLSX to Dictionary (key and values)

To totally unlock this section you need to Log-in

During data import process in a Jupyter Notebook, using Python and Pandas module for data science, we would need to manipulate or import directly an Excel file into a notebook and transfor all the data into a dictionary, so in this article we will focus on this particular need.

Let’s say we have an Excel file with four columns, City, Country, Population and Area: now that we have this example file in the folder we are working with, we can get this data into Python code in a few lines.

The code above relies on using an outside library called pandas, as it’s good at reading Excel files. A library is just a set of reusable functions. The pandas library is available for free online. We tell our current Jupyter notebook that we are about to use it with the line import pandas.

And that gives us an object, like a dictionary, which has a method in it called read_excel. Similar to how we can call {‘foo’: ‘bar’}.keys(). That’s the benefit of a library, we can get methods that do not come out of the box with Python.

So we use the read_excel data to read our excel file, by providing the name of the file, cities.xlsx, and the preceding ./ just indicates that the file is found in the current folder. Finally with the line travel_df.to_dict(‘records’) we return a list of our dictionaries representing our data. You can see that when we access the first element of this list, it returns our first dictionary.

Look at that. Our variable cities is full of cities from the spreadsheet.

Now that we have the data in Python, we can use a couple of other of functions to quickly explore our data. For example, let’s say that we want to remind ourselves of all of the attributes associated with a single city. If we just look at the first element we see both the keys and values.

As you can see, by using the keyword dict, we can pass through the name of the key followed by the equal sign. Notice that the key name is provided as a string, but Python still knows how to handle it.

Alternative Method

Another easy way to convert a spreadsheet to Python dictionary is to use parse method, always from Pandas library, to get Excel data to a dataframe and finally pass it to the to_dict method to transform the dataframe into a dictionary. You can use these like in the following code: