Pandas – to_excel() – Write a dataframe to an excel file.

Spread the love

In this post, you will learn –

1 . How to write a pandas dataframe to a excel file.

2 . How to write specific columns to a excel file.

3. How to write a pandas dataframes to multiple excel sheets using for loop.

1. How to write a pandas dataframe to a excel file.

Let’s read the data to work with. If you want to follow along then download this file – click to download sales data.

And before we do anything, you have to also install the openpyxl library to read and write excel file in pandas. 

# to install
pip install openpyxl

We will read three data from the sales excel file to work with.

sales = pd.read_excel('sales_data.xlsx', sheet_name='Sales')
purchase = pd.read_excel('sales_data.xlsx', sheet_name='Purchase Orders 1')
select = pd.read_excel('sales_data.xlsx', sheet_name='Select')

sales –

purchase –

select –

To write a pandas dataframe to an excel file, we use the DataFrame.to_excel() method in pandas.

Let’s say that you want to write the sales data to a new excel file. To do that, you will write.

sales.to_excel('sales_output.xlsx')

By default, the data will be named as sheet1, sheet2 etc. If you want, you can also name the sheet using the sheet_name parameter.

sales.to_excel('sales_output2.xlsx', sheet_name='Sales')

And if you look at the output, you will see that pandas have added the index of the dataframe to the excel file along with the data. If you want to get rid of this index, you need to set the index parameter to index=False.

sales.to_excel('sales_output3.xlsx', sheet_name='Sales', index=False)

2 . How to write specific columns to a excel file.

Sometimes, you may want to write only specific columns and ignore all the other columns. To do that you can use the columns parameter.

Let’s say you only want to include all the columns except the Date column in the sales data.

cols = ['Product ID', 'Branch ID', 'Price', 'Quantity']

sales.to_excel('sales_output4.xlsx', sheet_name='Sales', 
               index=False, columns=cols)

3. How to write a pandas dataframes to multiple excel sheets using for loop.

Let’s say that you want to write all the 3 dataframe into a single excel file. To do that you will write – 

with pd.ExcelWriter('sales_new.xlsx') as writer:
    sales.to_excel(writer, sheet_name='sales')
    purchase.to_excel(writer, sheet_name='purchase')
    select.to_excel(writer, sheet_name='select')

1 . Pandas – read_excel() – How to read Excel file in python

2 . Pandas read_csv() – read a csv file in Python

3 . Pandas to_csv – write a dataframe to a csv file

Leave a Reply