Menu Close

How to Convert Excel to Dictionary in Python

How to Convert Excel to Dictionary in Python

In this article, you will see everything about how to convert excel to Dictionary in Python with the help of examples. Most of the time you will need to convert excel files to python dictionaries to perform some operations on the data.

Prerequisites

Before going further into this article you will need to install the following packages using the pip command, If you have already installed then you can ignore them.


pip install pandas
pip install openpyxl

Table of Contents

Convert excel file to dictionary in Python

Here we will know the total of two ways to convert excel files to dictionaries, As you know Python is one of the most popular programming languages for data science and Python has the capability of processing large excel files and manipulating their data as per requirements.

Most of the time you want to perform some operations on excel file data, then you have an option to convert the excel file into a Python dictionary.

So, let’s see the two best approaches to convert excel to the dictionary in Python.

Excel File

We have an excel file students.xlsx with the following data, as you can see in the below screenshot.

How to Convert Excel to Dictionary in Python

First method

Here we are going to use the pandas read_excel() method to read data from excel files and use the to_dict() method to convert data to dictionaries.

Example


import pandas as pd

# convert into dataframe
df = pd.read_excel("students.xlsx")

# convert into dictionary
dict = df.to_dict()
print(dict)

Output


{'Name': {0: 'Vishvajit Rao', 1: 'John', 2: 'Harshita', 3: 'Mohak'},
 'Occupation': {0: 'Developer',
                1: 'Front End Developer',
                2: 'Tester',
                3: 'Full Stack'},
 'Skills': {0: 'Python',
            1: 'Angular',
            2: 'Selenium',
            3: 'Python, React and MySQL'},
 'age': {0: 23, 1: 33, 2: 21, 3: 30}}

Second method

Again, Here we are going to use the pandas ExcelFile() method to read data from excel files and use the to_dict() method to convert data to Dictionary.

Example


excel = pd.ExcelFile("students.xlsx")
data = excel.parse(excel.sheet_names[0])
print(data.to_dict())

Output


{'Name': {0: 'Vishvajit Rao', 1: 'John', 2: 'Harshita', 3: 'Mohak'},
 'Occupation': {0: 'Developer',
                1: 'Front End Developer',
                2: 'Tester',
                3: 'Full Stack'},
 'Skills': {0: 'Python',
            1: 'Angular',
            2: 'Selenium',
            3: 'Python, React and MySQL'},
 'age': {0: 23, 1: 33, 2: 21, 3: 30}}

Conclusion

So, we have seen all about how to convert excel to dictionaries in Python with the help of examples. This is one of the most important concepts especially when you are want to convert your excel file data to a python dictionary to perform some operations in a Pythonic way. You can follow any approach from the above methods to convert excel to the dictionary in Python.

I hope this article will help you. if you like this article, please share and keep visiting for further interesting articles.

Thanks for your valuable time … 👏👏👏

Previous Article
Next Article

Related Posts