How do you write to an excel file in python using pandas?

Write Excel with Python Pandas. You can write any data (lists, strings, numbers etc) to Excel, by first converting it into a Pandas DataFrame and then writing the DataFrame to Excel.

To export a Pandas DataFrame as an Excel file (extension: .xlsx, .xls), use the to_excel() method.

Related course: Data Analysis with Python Pandas

installxlwt, openpyxl

to_excel() uses a library called xlwt and openpyxl internally.

• xlwt is used to write .xls files (formats up to Excel2003)
• openpyxl is used to write .xlsx (Excel2007 or later formats).

Both can be installed with pip. (pip3 depending on the environment)

1
2

$ pip install xlwt
$ pip install openpyxl

Write Excel

Write DataFrame to Excel file

Importing openpyxl is required if you want to append it to an existing Excel file described at the end.
A dataframe is defined below:

1
2
3
4
5
6
7
8
9
10
11

import pandas as pd
import openpyxl

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

print(df)

You can specify a path as the first argument of the to_excel() method.

Note: that the data in the original file is deleted when overwriting.

The argument new_sheet_name is the name of the sheet. If omitted, it will be named Sheet1.

1

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

Related course: Data Analysis with Python Pandas

If you do not need to write index (row name), columns (column name), the argument index, columns is False.

1

df.to_excel('pandas_to_excel_no_index_header.xlsx', index=False, header=False)

Write multiple DataFrames to Excel files

The ExcelWriter object allows you to use multiple pandas. DataFrame objects can be exported to separate sheets.

As an example, pandas. Prepare another DataFrame object.

1
2
3
4
5
6

df2 = df[['a', 'c']]
print(df2)

Then use the ExcelWriter() function like this:

1
2
3

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

You don’t need to call writer.save(), writer.close() within the blocks.

Append to an existing Excel file

You can append a DataFrame to an existing Excel file. The code below opens an existing file, then adds two sheets with the data of the dataframes.

Note: Because it is processed using openpyxl, only .xlsx files are included.

1
2
3
4
5
6

path = 'pandas_to_excel.xlsx'

with pd.ExcelWriter(path) as writer:
    writer.book = openpyxl.load_workbook(path)
    df.to_excel(writer, sheet_name='new_sheet1')
    df2.to_excel(writer, sheet_name='new_sheet2')

Related course: Data Analysis with Python Pandas