Pandas ExcelWriter Explained with Examples

pandas ExcelWriter() class is used to save DataFrame to Excel sheet. This class is mainly used when you wanted to save multiple sheets and append data to an existing Excel sheet.

pandas ExcelWriter Key Points

  • By default, it uses xlsxwriter if it is installed otherwise it uses openpyxl
  • Supports saving multiple DataFrames to single sheet.
  • You can raise error when file already exists
  • Save multiple sheets, append existing sheet or file.

1. Quick Examples of pandas ExcelWriter()

Following are some examples of how to use pandas ExcelWriter() class with examples.


# Write excel file with default behaviour.
with pd.ExcelWriter("courses.xlsx") as writer:
    df.to_excel(writer) 

# Write to Multiple Sheets
with pd.ExcelWriter('Courses.xlsx') as writer:
    df.to_excel(writer, sheet_name='Technologies')
    df2.to_excel(writer, sheet_name='Schedule')

# Append DataFrame to existing excel file
with pd.ExcelWriter('Courses.xlsx',mode='a') as writer:  
    df.to_excel(writer, sheet_name='Technologies')

Let’s create a pandas DataFrame from list and use the examples specified above to run and explore the output.


import pandas as pd
import numpy as np
# Create DataFrame from multiple lists
technologies =  ['Spark','Pandas','Java','Python', 'PHP']
fee = [25000,20000,15000,15000,18000]
duration = ['5o Days','35 Days',np.nan,'30 Days', '30 Days']
discount = [2000,1000,800,500,800]
columns=['Courses','Fee','Duration','Discount']
df = pd.DataFrame(list(zip(technologies,fee,duration,discount)), columns=columns)
print(df)

# Outputs
#  Courses    Fee Duration  Discount
#0   Spark  25000  5o Days      2000
#1  Pandas  20000  35 Days      1000
#2    Java  15000      NaN       800
#3  Python  15000  30 Days       500
#4     PHP  18000  30 Days       800

2. ExcelWriter() Syntax

Following is the syntax of the ExcelWrite class. The object of this class is used as an argument to the DataFrame.to_excel() function to write DataFrame to excel sheet.


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

Following are some params.

  • path – Path where to save file.
  • if_sheet_exists – Takes values {‘error’, ‘new’, ‘replace’, ‘overlay’}, default ‘error’. Use this to raise error if sheets exists, replace existing contents with the new data. overlay contents at the end with out erasing contents.
  • mode{‘w’, ‘a’}, default ‘w’. W for write, a for append.
  • storage_options

3. ExcelWriter() Examples

By using pandas ExcelWrite() let’s see how to save a DataFrame to excel with default parameters.


# Write excel file with default behaviour.
with pd.ExcelWriter("courses.xlsx") as writer:
    df.to_excel(writer) 

This creates an excel file with the contents as below. By default, It exports column names, indexes, and data to a sheet named 'Sheet1'.

pandas ExcelWriter

4. Save to Multiple Sheets

The ExcelWriter class allows you to save or export multiple pandas DataFrames to separate sheets. First, you need to create an object for ExcelWriter.

The below example save data from df object to a sheet named Technologies and df2 object to a sheet named Schedule.


# Write to Multiple Sheets
df2 = df.clone()
with pd.ExcelWriter('Courses.xlsx') as writer:
    df.to_excel(writer, sheet_name='Technologies')
    df2.to_excel(writer, sheet_name='Schedule')

3. Append to Existing Excel File

ExcelWriter can be used to append DataFrame to an excel file. Use mode param with value 'a' to append. The code below opens an existing file and adds data from the DataFrame to the specified sheet.


# Append DataFrame to existing excel file
with pd.ExcelWriter('Courses.xlsx',mode='a') as writer:  
    df.to_excel(writer, sheet_name='Technologies')

Conclusion

In this article, you have learned the syntax of ExcelWrite class, its usage to save multiple DataFrames to excel file, append to existing sheet e.t.c.

You May Also Like

References

pandas ExcelWriter

NNK

SparkByExamples.com is a Big Data and Spark examples community page, all examples are simple and easy to understand and well tested in our development environment Read more ..

Leave a Reply

This Post Has One Comment

  1. Himanshu Punjabi

    Append Mode is not supported when using the Default Engine of ExcelWriter. We need to use engine=’openpyxl’.

You are currently viewing Pandas ExcelWriter Explained with Examples