Write to an Excel file using Python Pandas

If you’re anything like me, you love finding new and interesting ways to work with data. And what could be more interesting than working with data in Excel?

Excel is a powerful tool for working with data, and Python Pandas makes it even easier. In this post, I’ll show you how to use Python Pandas to write data into an Excel file.

I’ll start by showing you how to install Pandas and create a DataFrame. Then I’ll show you how to write data to an Excel file using Pandas.

Also read: The xlrd Module – How To Handle Excel Files In Python?

What is an Excel file?

An Excel file is a spreadsheet file containing some cells in rows and columns (Tabular view) and can help in the arrangement, calculation, sorting, and managing of data.

The data in the spreadsheet may be numeric, text, formulas, hyperlinks, functions, etc. An XLS file stores data as binary streams. It can only be created by the most popular MS Excel or other spreadsheet programs. The file format .xlsx always indicates an excel file on its own.

The following image depicts an excel file created by the MS-excel program:

The pandas Module

It is an open-source library or module in Python providing some in-built data structures and data analysis tools. Some of the key features of the pandas Module are as follows.

It provides efficient and faster ways of managing data and accessing it.
It has the capability to handle the missing data integrated into its library.
It has some unique techniques for indexing its data.
It offers a large variety of tools that are in-built, which help us to read and write data.
It can support JSON, CSV, HDF5, and Excel files.
It is extremely responsible for merging various datasets.
It gives us access to other helpful libraries like MatPlotLib and NumPy.
It allows its users to implement all kinds of mathematical operations on the data sets.

Installing Python Pandas

Before getting started with this article, make sure you have installed this module on your computer. You can download this module by the command below on your command prompt.

pip install pandas

You can get your pandas module installed as follows.

We are going to use one another module in this article, Let’s understand that as well.

Installing the openpyxl Module

Openpyxl is a Python library or module used to read or write from an Excel file. This module needs to be installed to use certain methods like load_workbook(), otherwise, we can’t use those methods, It will throw error. Let’s install this module using our command prompt.

C:\Users\pc> pip install openpyxl

The above code snippet will install our openpyxl module.

Installing Openpyxl Module 1 — Installing Openpyxl Module

Writing a single spreadsheet to Excel file

We will first convert it into a Pandas Dataframe and then write the Dataframe to Excel. To export a Pandas DataFrame as an Excel file (i.e. .xlsx, .xls file), We will use the to_excel() method.

Follow the below code snippet:

>>> import pandas as pd
>>> import openpyxl

>>> df = pd.DataFrame([['Hi', 'Hello', 'Hey'], ['Nandini', 'Rupali', 'Malina'], ['Ram','Shyam', 'Babu']], index=['one', 'two', 'three'], columns=['a', 'b', 'c'])

If we print the above data frame, We can get the result as follows.

>>> print(df)
             a       b       c
one         Hi   Hello     Hey
two    Nandini  Rupali  Malina
three      Ram   Shyam    Babu
>>>

Now We will export the above data frame to an excel file using the below code snippet.

df.to_excel('new_excel.xlsx', sheet_name='new_sheet_name')

The above code snippet will create a .xlsx file in our PC folder automatically.

Writing multiple Spreadsheet to Excel file

Let us create another data frame.

df2 = pd.DataFrame([[11, 21, 31], [12, 22, 32], [31, 32, 33]], index=['one', 'two', 'three'], columns=['a', 'b', 'c'])

Now we will export both df and df2 data frames into our new_excel file. follow the code snippet below

with pd.ExcelWriter('new_excel.xlsx') as writer:
    df.to_excel(writer, sheet_name='sheet1')
    df2.to_excel(writer, sheet_name='sheet2')

It will create an excel file comprising two slides as below.

Summary

In this article, we covered 2 examples of writing data into our Excel file using Python. We discussed two popular modules for our appropriate output. Hope you enjoyed this quick demontration.