Pandas Write to Excel with Examples

Spread the love

Use pandas to_excel() function to write a DataFrame to an excel sheet with extension .xlsx. By default it writes a single DataFrame to an excel file, you can also write multiple sheets by using an ExcelWriter object with a target file name, and sheet name to write to.

Note that creating an ExcelWriter object with a file name that already exists will result in the contents of the existing file being erased.

Related: pandas read Excel Sheet

pandas to Excel key Points

  • By default, it uses xlsxwriter if it is installed otherwise it uses openpyxl
  • Supports saving multiple DataFrames to single sheet.
  • Save multiple sheets, append existing sheet or file.
  • Use ExcelWriter()

Let’s create a pandas DataFrame from list and explore usingto_excel() function by using multiple parameters.


import pandas as pd
import numpy as np

# Create 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']

# Create DataFrame from multiple lists
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

1. pandas DataFrame to Excel

Use to_excel() function to write or export pandas DataFrame to excel sheet with extension xslx. Using this you can write excel files to the local file system, S3 e.t.c. Not specifying any parameter it default writes to a single sheet.

to_excel() takes several optional params that can be used skip columns, skip rows, not to write index, set column names, formatting, and many more.


# Write DataFrame to Excel file
df.to_excel('Courses.xlsx')

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

You can change the name of the sheet from Sheet1 to something that makes sense to your data by using sheet_name param. The below example exports it to the sheet named ‘Technologies‘.


# Write DataFrame to Excel file with sheet name
df.to_excel('Courses.xlsx', sheet_name='Technologies')

2. Write to Multiple Sheets

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

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


# 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')

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')

4. Save Selected Columns

use param columns to save selected columns from DataFrame to excel file. The below example only saves columns Fee, Duration to excel file.


# Save Selected Columns to Excel File
df.to_excel('Courses.xlsx', columns = ['Fee','Duration'])

Use header param with a list of values if you wanted to write with different column names.

5. Skip Index

To skip Index from writing use index=False param. By default, it is set to True meaning write numerical Index to excel sheet.


# Skip Index
df.to_excel('Courses.xlsx', index = False)

Conclusion

In this article, you have learned how to write pandas DataFrame to excel file by using to_excel(). Also explored how to write to specific sheets, multiple sheets, and append to existing excel file.

References

Naveen (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

You are currently viewing Pandas Write to Excel with Examples