. Advertisement .
..3..
. Advertisement .
..4..
In Pandas, you can write a DataFrame to an excel with the pandas to_excel() function and extension xlsx. By default, it enables you to write single data to an excel sheet. Yet, you can also create various sheets. Let’s see how to write data to excel in Pandas.
DataFrame To Excel Sheets In Pandas Prerequisite
Before doing the tasks, you need to install the openpyxl module first. Run the pip install openpyx pip command to install.
How To Write Pandas DataFrame To Excel
Write DataFrame To Excel File By to_excel() Function
The to_excel() function allows you to write and export DataFrame to an excel sheet using the xslx extension. You can use it to write the files to the local file system.
There is no need to specify any parameter as the function writes to a single sheet by default. To_excel() takes various optional parameters to skip rows, columns, set column names, write index, or format.
Here is an example of a DataFrame:
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)
Output:
# a b c
# one 11 21 31
# two 12 22 32
# three 31 32 33
Your desired path can be specified as the first argument and this will become the sheet’s name. If omitted, the name will be Sheet1.
df.to_excel('pandas_to_excel.xlsx', sheet_name='new_sheet_name')
The argument index returns False if there is no row name and column name.
df.to_excel('pandas_to_excel_no_index_header.xlsx', index=False, header=False)
Write Multiple DataFrames To Excel Files
The ExcelWriter enables coders to use multiple pandas at once. This means the DataFrame can be exported into different separate sheets. To do this, you need to create an ExcelWrite object and follow the above steps. There is no need to call writer.close() and writer.save() within the blocks.
How To Save A DataFrame To Excel
Passing a path is the easiest method to save a DataFrame to an Excel sheet with the .to_excel() method. This method overwrites the previous file and saves the one at the path.
# Saving a Pandas DataFrame to an Excel File
import pandas as pd
df = pd.DataFrame.from_dict(
{'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]}
)
df.to_excel('filename.xlsx')
The code above saves the file, including default parameters. You can also specify the name with the sheet_name= parameter. If not, the default name will be ‘sheet1’.
How To Save MultipleDataFrames To Different Excel Sheets
Saving various DataFrames in different sheets can make Pandas less intuitive. The following command will overwrite the first one:
# The wrong way to save multiple DataFrames to the same workbook
import pandas as pd
df = pd.DataFrame.from_dict(
{'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]}
)
df.to_excel('filename.xlsx', sheet_name='Sheet1')
df.to_excel('filename.xlsx', sheet_name='Sheet2')
In this case, a Pandas Excel Writer is required to manage saving your workbook. Use the following context manager to do it:
# The Correct Way to Save Multiple DataFrames to the Same Workbook
import pandas as pd
df = pd.DataFrame.from_dict(
{'A': [1, 2, 3], 'B': [4, 5, 6], 'C': [7, 8, 9]}
)
with pd.ExcelWriter('filename.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1')
df.to_excel(writer, sheet_name='Sheet2')
The code creates various sheets existing in one workbook. They are arranged in the same order when you specify them.
Conclusion
With Excel in Pandas, you can write such data as numbers, lists, and strings to Excel easily. All you have to do is to convert the data into a DataFrame and write it to Excel.
Leave a comment