How to Write Pandas Dataframe to Excel Spreadsheets

Often Python developers need to export data to spreadsheets. This is a common requirement in data analysis and reporting. You can easily do this using Pandas library. It allows you to easily export one or more dataframes to excel workbooks. In this article, we will learn how to write Pandas dataframe to Excel spreadsheets.

How to Write Pandas Dataframe to Excel Spreadsheets

Every Pandas dataframe supports to_excel() function that allows you to export the dataframe to a spreadsheet. It supports numerous arguments that allow you to customize how you export your data. Here is its syntax.

dataframe.to_excel( excel_writer, sheet_name, **kwargs )

In the above command, you can invoke to_excel() function from any dataframe. You need to provide the file path to excel spreadsheet at the minimum. Other arguments are optional.

In this article, we will learn about the common use cases faced by Python developers. Let us say you have the following dataframe.

import pandas as pd

data = {'Name': ['John', 'Jane', 'Joe'],
        'Age': [28, 22, 22],
        'City': ['New York', 'Paris', 'London']}

df = pd.DataFrame(data)
print(df)

## output

   Name  Age      City
0  John   28  New York
1  Jane   22     Paris
2   Joe   22    London

1. Export to Single Spreadsheet

You can export a single dataframe to a single spreadsheet by simply calling the to_excel() function on the dataframe. Here is an example to export the above dataframe to spreadsheet /home/ubuntu/test.xlsx.

df.to_excel('/home/ubuntu/test.xlsx')

Please ensure that you provide full file path to the spreadsheet. Otherwise, it will create the file in present working directory.

Here is the output you will see after exporting of data.

Excel file '/home/ubuntu/test.xlsx' created successfully.

2. Export to Multiple Spreadsheets

to_excel() function can also be used to export data to multiple spreadsheets. If you need to import multiple dataframes, you can do so as shown below. For this purpose, you need to use ExcelWriter class that is available in Pandas library.

import pandas as pd

data = {'Name': ['John', 'Jane', 'Joe'],
        'Age': [28, 22, 22],
        'City': ['New York', 'Paris', 'London']}

data2 = {'Name': ['John', 'Jane', 'Joe'],
        'Age': [28, 22, 22],
        'City': ['New York', 'Paris', 'London']}

df = pd.DataFrame(data)
df2 = pd.DataFrame(data2)

Here is how to export the above 2 dataframes to separate spreadsheets of same workbook.

with pd.ExcelWriter('/home/ubuntu/test.xlsx') as writer:
    df1.to_excel(writer, sheet_name='data', index=False)
    df2.to_excel(writer, sheet_name='data2', index=False)

In the above code, we use excel writer object to write individual dataframes to separate spreadsheets.

3. Customize Data formatting during export

MS Excel is well known for its formatting capabilities. You will be happy to know that you can format your spreadsheets using Pandas, right from within Python. Here is an example to make the header row (row #1) bold format after data export. First, we export the data to spreadsheet.

import pandas as pd

data = {'Name': ['John', 'Jane', 'Joe'],
        'Age': [28, 22, 22],
        'City': ['New York', 'Paris', 'London']}

df = pd.DataFrame(data)

with pd.ExcelWriter('/home/ubuntu/test.xlsx') as writer:
    df.to_excel(writer, sheet_name='data', index=False)

Here is the code to format the header row.

workbook = writer.book
worksheet = writer.sheets['data']

header_format = workbook.add_format({
    'bold': True
})

for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

In the above code, we first create an object for workbook. Then we access its ‘data’ spreadsheet. Then we define the header format using add_format() function. Then we loop through the header row one by one, updating their format, one at a time.

4. Using Conditional Formatting

Often, Python developers need to apply some sort of conditional formatting too to their workbooks. You can easily do this using conditional_format() function available to worksheet object.

After you have exported the data to spreadsheet as shown below.

import pandas as pd

data = {'Name': ['John', 'Jane', 'Joe'],
        'Age': [28, 22, 22],
        'City': ['New York', 'Paris', 'London']}

df = pd.DataFrame(data)

with pd.ExcelWriter('/home/ubuntu/test.xlsx') as writer:
    df.to_excel(writer, sheet_name='data', index=False)

We create an object for our spreadsheet, for conditional formatting.

workbook = writer.book
worksheet = writer.sheets['data']

Then, we define conditional formatting for age>25 and age<=25, using add_format() function

# green color if age>=25
positive_format = workbook.add_format({'bg_color': '#C6EFCE'})

# red color if age<25
negative_format = workbook.add_format({'bg_color': '#FFC7CE'})

Then we apply this formatting, along with defining the condition.

# apply conditional formatting to all data cells
worksheet.conditional_format('A2:C4', {
        'type': 'cell',
        'criteria': '>=',
        'value': 25,
        'format': positive_format
    })

worksheet.conditional_format('A2:C4', {
        'type': 'cell',
        'criteria': '<',
        'value': 25,
        'format': negative_format
    })

Conclusion

In this article, we have learnt several simple ways to easily export Python data to Excel spreadsheets, using Pandas library. We learnt how to export a single dataframe to a spreadsheet. We also learnt how to export multiple dataframes to multiple sheets in a single workbook. We learnt how to format Excel data from Python itself, using Pandas library. For all these purposes, using to_excel() function along with ExcelWriter class is the key.

Also read:

How to Find Duplicates in Python Pandas Dataframe
How to Merge & Join Pandas Dataframe
How to Create Pivot Tables in Python Pandas