[Tut] pd.to_excel() – An Unofficial Guide to Saving Data to Excel

[xSicKxBot]

pd.to_excel() – An Unofficial Guide to Saving Data to Excel

Microsoft Excel is a cross-platform and battle-tested spreadsheet software widely used for data analysis and visualization. It is a powerful and user-friendly tool indeed! But how can we bring it to the next level? 

We can combine Excel with Python to

• read data from Excel,
  
  
• save data to Excel with formats, formulas, and even charts, and
  
  
• automate Excel tasks in Python!
  

Please continue reading and stay tuned for my Excel in Python series if it sounds great!

This tutorial is all about saving data to Excel.

Concretely, I will first introduce Excel’s data structure and lingos.

Then, you will learn the difference between four popular Python ways to save data to excel, including

Code:

pandas

and

Code:

openpyxl

.

Finally, I will focus on the

Code:

pandas.DataFrame.to_excel()

method. I will guide you through four actual use cases in

Code:

pandas.DataFrame.to_excel()

, ranging from one Excel worksheet, multiple Excel worksheets, multiple Excel workbooks, and dealing with index cases. 

You can find all datasets and codes in this tutorial here. The data in our example datasets are not actual data and are only used for educational purposes.

Quick Introduction to Excel

Before jumping into reading data from Excel, let’s look at how data is stored in Excel and get ourselves familiar with some Excel lingos.

Simply put, data is stored in cells in Excel, and each cell can be identified with its unique row and column number pair. 

Columns in Excel are labeled in alphabets, starting from ‘

Code:

A

’, and rows in Excel are labeled in roman numbers, starting from ‘

Code:

1

’. For example, in the following Excel picture,

Code:

A1

is the cell in the intersection of the first column and first row, ‘ID’.



Yes, you got the idea! It is just like the DataFrame data structure in our friends, pandas.

And if you want to select an area to dump data into Excel, you can imagine drawing a rectangle data region based on the upper-left cell of the part.

On top of that, other commonly seen Excel lingos include worksheets and workbooks. 

• A worksheet means a single spreadsheet in an Excel file. 
  
  
• A workbook means a single Excel file with extensions like
  Code:

  .xlsx
  and
  Code:

  .xls
  . 
  

Tip: More file extensions supported by Excel can be found in the Microsoft official doc here.

Now, you are ready to know how to read data from Excel!

Popular Python ways to Save Data to Excel

Here is the summary of popular Python ways to save data to excel:



One of

Code:

pandas.DataFrame.to_excel()

’s advantages is engine compatibility. It uses multiple external engines for different Excel extensions, including

Code:

openpyxl

,

Code:

xlwt

, and

Code:

xlsxwriter

.

• “
  Code:

  xlwt
  ” supports old-style Microsoft Excel versions 95 to 2003 files (
  Code:

  .xls
  ).
  
  
• “
  Code:

  openpyxl
  ” supports newer Excel 2010 file formats (
  Code:

  .xlsx, .xlsm, .xltx, .xltm
  ).
  
  
• “
  Code:

  xlsxwriter
  ” supports Excel 2007+ file format (
  Code:

  .xlsx
  ).
  

In summary, you can use

Code:

openpyxl

and other libraries for specific Excel file formats and basic data processing. And please remember that

Code:

openpyxl

and

Code:

xlsxwriter

support newer Excel file formats. 

However, I recommend using

Code:

pandas.DataFrame.to_excel()

for data science and analytics applications because it supports most Excel file formats and accepts a DataFrame object with powerful methods.

Therefore, let’s see how to use

Code:

pandas.DataFrame.to_excel()

to save data to Excel!

Saving Data to Excel – pandas.DataFrame.to_excel()

Since

Code:

pandas.DataFrame.to_excel()

is the most powerful and inclusive way to save data from Excel files with different extensions, I will first introduce its syntax and walk you through three use cases with real Python codes below using

Code:

pandas.DataFrame.to_excel()

.

Meet pandas.ExcelWriter

Before heading over to the

Code:

pandas.DataFrame.to_excel

method, we need to know a new friend,

Code:

pandas.ExcelWriter

. It is a class for writing

Code:

pandas.DataFrame

objects into excel sheets.

When you are trying to write to multiple sheets, you need to create an

Code:

ExcelWriter

object and pass it to

Code:

pandas.DataFrame.to_excel()

as the first parameter.

To create an

Code:

ExcelWriter

object, we pass something to it according to its syntax:

Code:

# Syntax for pandas.ExcelWriter pandas.ExcelWriter(path[, engine=None[, date_format=None[, datetime_format=None[, mode='w'[, storage_options=None[, if_sheet_exists=None[, engine_kwargs=None[, **kwargs]]]]]])

Parameter |
Meta |
Description

Code:

path

|
required |
String or

Code:

typing.BinaryIO

to an Excel file.

Code:

engine

|
optional |
String specifying the engine to use for writing. Default is to use : 

Code:

xlwt

for

Code:

xls 

Code:

xlsxwriter

for

Code:

xlsx

if

Code:

xlsxwriter

is installed otherwise

Code:

openpyxl

Code:

odf

for

Code:

ods

Code:

date_format

|
optional |
Format string for dates written into Excel files (e.g.

Code:

'YYYY-MM-DD'

).

Code:

datetime_format

|
optional |
Format string for

Code:

datetime

objects written into Excel files. (e.g.

Code:

'YYYY-MM-DD HH:MM:SS'

).

Code:

mode

|
optional |
File mode to use (

Code:

'w'

for write;

Code:

'a'

for append). The default is to use

Code:

'w'

.

Code:

storage_options

|
optional |
A dictionary that makes sense for a particular storage connection, e.g. host, port, username, password, etc.

Code:

if_sheet_exists

|
optional |
What to do if a sheet exists under the append mode. Accepts

Code:

{'error', 'new', 'replace', 'overlay'}

. Default is

Code:

'error'

.

Code:

error

: raise a

Code:

ValueError

.

Code:

new

: create a new sheet, with a name determined by the engine.

Code:

replace

: delete the contents of the sheet before writing to it.

Code:

overlay

: write contents to the existing sheet without removing the old contents.

Code:

engine_kwargs

|
optional |
A dictionary containing keyword arguments passed into the engine. 

Tip: Generally, you only need to use the four parameters in bold format. The default engine is automatically chosen depending on the file extension.

A small note: For compatibility with CSV writers,

Code:

ExcelWriter

serializes lists and dicts to strings before writing.

Last, if you use the

Code:

with

statement to create an

Code:

ExcelWriter

, you do not need to worry about saving the change in the end! An example code to create an

Code:

ExcelWriter

in the

Code:

with

statement is:

Code:

import pandas as pd df = pd.DataFrame() excel_wb56_filepath = 'learn_excel_56.xlsx' # Syntax for create an ExcelWriter object in the with statement # To create a new empty Excel file, learn_excel_56.xlsx with two new sheets! with pd.ExcelWriter(excel_wb56_filepath) as writer: df.to_excel(writer, sheet_name='Class 5') df.to_excel(writer, sheet_name='Class 6')

After running the code, we create a new empty Excel file,

Code:

learn_excel_56.xlsx

with two new sheets, Class 5 and Class 6!



Meet pandas.DataFrame.to_excel

Hooray! Now, let’s look at the syntax and parameters of the

Code:

pandas.DataFrame.to_excel

method and get ourselves prepared for later examples!

Here is the syntax for

Code:

pandas.DataFrame.to_excel

:

Code:

# Syntax for pandas.DataFrame.to_excel DataFrame.to_excel(excel_writer[, sheet_name='Sheet1'[, na_rep=''[, float_format=None[, columns=None[, header=True[, index=True[, index_label=None[, startrow=0[, startcol=0[, engine=None[, merge_cells=True[, encoding=None[, inf_rep='inf'[, verbose=True[, freeze_panes=None[, storage_options=None]]]]]]]]]]]]]]]])

To write a single object (

Code:

pandas.DataFrame

) to an Excel

Code:

.xlsx

file, it is only necessary to specify a target file name. To write to multiple sheets, it is necessary to create an

Code:

ExcelWriter

object with a target file name.

Parameter |
Meta |
Description

Code:

excel_writer

|
Required |
Target file name or

Code:

ExcelWriter

object.

Code:

sheet_name

|
Optional |
Name of sheet containing DataFrame. Default is

Code:

"Sheet 1"

.

Code:

na_rep

|
Optional |
Missing data representation.

Code:

Default = ""

Code:

float_format

|
Optional |
Format string for floating point numbers. For example

Code:

float_format="%.2f"

will format 0.1234 to 0.12.

Code:

column

|
Optional |
Columns (in the DataFrame) to write.

Code:

header

|
Optional |
Row to be considered as the header, excluding from the data part.

Code:

Default = True

, which means the first row. If

Code:

None

, no header.

Code:

index

|
Optional |
Write row names (index). Default is

Code:

True

, which means show index. If set to be

Code:

False

, it means no index in the output Excel worksheet.

Code:

index_label

|
Optional |
Column label for the index.

Code:

startrow

|
Optional |
Upper left cell row to dump data frame. Default is 0.

Code:

startcol

|
Optional |
Upper left cell column to dump data frame. Default is 0.

Code:

engine

|
Optional |
String specifying the engine to use for writing. Default is 

Code:

xlwt

for

Code:

xls

.

Code:

xlsxwriter

for

Code:

xlsx

if

Code:

xlsxwriter

is installed; otherwise

Code:

openpyxl

.

Code:

merge_cells

|
Optional |
Write

Code:

MultiIndex

and Hierarchical Rows as merged cells.

Code:

encoding

|
Optional |
Encoding of the resulting excel file. Only necessary for

Code:

xlwt

, other writers support Unicode natively.

Code:

inf_rep

|
Optional |
Representation for infinity (there is no native representation for infinity in Excel).

Code:

verbose

|
Optional |
Display more information in the error logs.

Code:

freeze_panes

|
Optional |
Specifies the one-based bottom-most row and right-most column that is to be frozen.

Code:

storage_options

|
Optional |
Extra options that make sense for a particular storage connection, e.g. host, port, username, password, etc.

Tip: Generally, you only need to use the four parameters in bold format. 

Please try to have an intuition for these parameters right now and I will cover the details for

Code:

sheet_name

,

Code:

index

, and

Code:

index_label

parameters in our next exciting examples!

Getting started

To use the

Code:

pandas.DataFrame.to_excel

method, you need to first installthe

Code:

pandas

package in your command line:

Code:

$ pip install pandas

Tip: you might need to use

Code:

pip3

instead of

Code:

pip

, depending on your environment.

Given the engine compatibility mentioned above, you also need to install respective engine libraries. For example, to use

Code:

openpyxl

, you need to install this package on your command line:

Code:

$ pip install openpyxl

Tip: you might need to use

Code:

pip3

instead of

Code:

pip

, depending on your environment.

By the way, if you have already installed Anaconda, you can skip this step

Dataset

In our examples, we will create Excel workbooks like the two Excel workbooks (

Code:

.xlsx

),

Code:

learn_excel_12

and

Code:

learn_excel_34

, in our first Excel in Python series.



These workbooks have the same data structures and column names in each worksheet. For example, the following is the data in the

Code:

Class_1

worksheet in the

Code:

learn_excel_12

workbook.



One row in each sheet represents a student in each class and the columns represent the student’s ID, Major, and Score respectively. 

Concretely,

Code:

learn_excel_12

has two worksheets, Class 1 and Class 2. And

Code:

learn_excel_34

has two worksheets, Class 3 and Class 4.

You can find all datasets and codes in this tutorial here. The data in our example datasets are not actual data and are only used for educational purposes.

Save Data to One Worksheet

So, how can we save data to a single excel sheet? We can pass a target file name or create an

Code:

ExcelWriter

object to do so!

For our example, we can create a new Excel file,

Code:

learn_excel_56

and write some student data into the Class 5 sheet, specifying through the parameter

Code:

sheet_name

.

Code:

import pandas as pd # For our writing mode example, # we can create a new Excel file, learn_excel_56 # and write some student data into it. excel_fp = 'learn_excel_56.xlsx' class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=False)

After running the code,  we can get the output Excel file:



Save Data to Multiple Worksheets

Likewise, we can save data to multiple worksheets by calling

Code:

pandas.DataFrame.to_excel()

method multiple times.

For our example, we can create a new Excel file,

Code:

learn_excel_56

and write some student data into the Class 5 sheet and Class 6 sheet, specifying through the parameter

Code:

sheet_name

.

Code:

import pandas as pd # For our writing mode example, # we can create a new Excel file, learn_excel_56 # and write some student data into it. excel_fp = 'learn_excel_56.xlsx' class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]}) class6_df = pd.DataFrame( {'ID': [61, 62], 'Major': ['History', 'Math'], 'Score': [78, 96]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=False) class6_df.to_excel(writer, sheet_name='Class 6', index=False)

After running the code,  we can get the output Excel file:





Save Data to Multiple Workbooks

To get multiple workbooks, we can just create multiple

Code:

pandas.ExcelWriter

objects.



To give a quick example, let’s create two workbooks,

Code:

learn_excel_78

and

Code:

learn_excel_910

.

Code:

import pandas as pd # let’s create two workbooks, learn_excel_78 and learn_excel_910. wb78_fp = 'learn_excel_78.xlsx' wb910_fp = 'learn_excel_910.xlsx' df = pd.DataFrame() with pd.ExcelWriter(wb78_fp, mode='w') as writer_78, pd.ExcelWriter(wb910_fp, mode='w') as writer_910: df.to_excel(writer_78, sheet_name='Class 7', index=False) df.to_excel(writer_78, sheet_name='Class 8', index=False) df.to_excel(writer_910, sheet_name='Class 9', index=False) df.to_excel(writer_910, sheet_name='Class 10', index=False)

After running the code,  we can get the output workbooks:



In the

Code:

learn_excel_78

file, we can see that we have created two empty worksheets:



So far, we have understood the basic writing operations. Let’s move forward and deal with the most common issues—index and date

Deal with Index

You can set up the index and index’ column label when calling the

Code:

pandas.DataFrame.to_excel()

method.

Previously, our example codes set the index to be

Code:

False

, which means no index column in the output Excel file. Let’s see what will happen if we set the index to be

Code:

True

in the multiple worksheets scenario.

Code:

import pandas as pd # For our writing mode example, # we can create a new Excel file, learn_excel_56 # and write some student data into it. excel_fp = 'learn_excel_56.xlsx' class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]}) class6_df = pd.DataFrame( {'ID': [61, 62], 'Major': ['History', 'Math'], 'Score': [78, 96]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=True) class6_df.to_excel(writer, sheet_name='Class 6', index=True)

After running the code, we can see that we now have an index column, counting from zero.



On top of that, we can give a column name to the index column by specifying the parameter,

Code:

index_label

.

Code:

import pandas as pd # For our writing mode example, # we can create a new Excel file, learn_excel_56 # and write some student data into it. excel_fp = 'learn_excel_56.xlsx' class5_df = pd.DataFrame( {'ID': [51, 52], 'Major': ['English', 'Math'], 'Score': [98, 89]}) class6_df = pd.DataFrame( {'ID': [61, 62], 'Major': ['History', 'Math'], 'Score': [78, 96]}) with pd.ExcelWriter(excel_fp, mode='w') as writer: class5_df.to_excel(writer, sheet_name='Class 5', index=True, index_label='No.') class6_df.to_excel(writer, sheet_name='Class 6', index=True, index_label='No.')

After running the code, we can see that we now have an index column with a name, “No.”!



Conclusion

That’s how to save data to Excel. This is the second article in our Excel in Python series.

We learned about Excel’s data structure and commonly used lingos and four popular ways to save data to Excel in Python, including

Code:

pandas

and

Code:

openpyxl

.

Finally, we looked at four actual use cases in

Code:

pandas.DataFrame.to_excel

, ranging from one Excel worksheet, multiple Excel worksheets, multiple Excel workbooks, and dealing with index cases.

I hope you enjoy all this, and stay tuned for our following Excel in Python article on saving data to Excel! Happy coding!