Pandas Excel File Operations

Pandas provides rich Excel file manipulation features, helping us conveniently read and write.xlsand.xlsxfiles, supporting complex operations such as multiple sheets, indexing, and column selection. It is an essential tool in data analysis.

Operation	Method	Description
Read Excel file	`pd.read_excel()`	Read an Excel file and return a DataFrame
Write DataFrame to Excel	`DataFrame.to_excel()`	Write a DataFrame to an Excel file
Load Excel file	`pd.ExcelFile()`	Load an Excel file and access multiple sheets
Use ExcelWriter to write multiple sheets	`pd.ExcelWriter()`	Write multiple DataFrames to different sheets in the same Excel file

`pd.read_excel() - 读取 Excel 文件`

pd.read_excel()The method is used to read data from an Excel file and load it as a DataFrame. It supports reading.xlsand.xlsxformatted files.

The syntax is as follows:

pandas.read_excel(io, sheet_name=0, *, header=0, names=None, index_col=None, usecols=None, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, parse_dates=False, date_parser=<no_default>, date_format=None, thousands=None, decimal='.', comment=None, skipfooter=0, storage_options=None, dtype_backend=<no_default>, engine_kwargs=None)

Parameter description:

io: This is a required parameter, specifying the path or file object of the Excel file to read.
sheet_name=0: Specify the sheet name or index to read. The default is 0, i.e., the first sheet.
header=0: Specify the row to use as column names. The default is 0, i.e., the first row.
names=None: A list used to specify column names. If provided, it will override the column names in the file.
index_col=None: Specify the column to use as the row index. It can be a column name or a number.
usecols=None: Specify the columns to read. It can be a list of column names or a list of column indices.
dtype=None: Specify the data types of columns. It can be in dictionary format, with keys as column names and values as data types.
engine=None: Specify the parsing engine. The default isNone, pandas will automatically choose.
converters=None: A dictionary of functions used to convert data.
true_values=None: Specify values that should be treated as booleanTruevalues.
false_values=None: Specify values that should be treated as booleanFalsevalues.
skiprows=None: Specify the number of rows to skip or a list of rows to skip.
nrows=None: Specify the number of rows to read.
na_values=None: Specify values that should be treated as missing values.
keep_default_na=True: Specify whether to treat default missing values (such asNaN) parse asNA。
na_filter=True: Specify whether to convert data toNA。
verbose=False: Specify whether to output detailed progress information.
parse_dates=False: Specify whether to parse dates.
date_parser=<no_default>: A function used to parse dates.
date_format=None: Specify the format of dates.
thousands=None: Specify the thousands separator.
decimal='.': Specify the decimal point character.
comment=None: Specify the comment character.
skipfooter=0: Specify the number of rows to skip at the end of the file.
storage_options=None: A parameter dictionary for cloud storage.
dtype_backend=<no_default>: Specify the data type backend.
engine_kwargs=None: A dictionary of additional parameters to pass to the engine.

This article usesexample_pandas_data.xlsxas an example. You candownload example_pandas_data.xlsxto test.

Example

import pandas as pd

# Read the data.xlsx file
df = pd.read_excel('example_pandas_data.xlsx')

# Print the read DataFrame
print(df)

The output is:

      表格 1 Unnamed: 1   Unnamed: 2
0     Name        Age         City
1    Alice         25     New York
2      Bob         30  Los Angeles
3  Charlie         35      Chicago

read_excel reads the first sheet by default (sheet_name=0). Assuming the data.xlsx file has only one sheet, the read data is stored in a DataFrame.

If the data.xlsx file has multiple sheets, you can specify sheet_name to read data from a particular sheet, for examplepd.read_excel('data.xlsx', sheet_name='Sheet1')。

Example

import pandas as pd

# Read the default first sheet
df = pd.read_excel('data.xlsx')
print(df)

# Read the content of a specified sheet (by sheet name)
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df)

# Read multiple sheets, returning a dictionary
dfs = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])
print(dfs)

# Customize column names and skip the first two rows
df = pd.read_excel('data.xlsx', header=None, names=['A', 'B', 'C'], skiprows=2)
print(df)

DataFrame.to_excel() - Write DataFrame to Excel File

to_excel()The method is used to write a DataFrame to an Excel file, supporting.xlsand.xlsxformats.

The syntax is as follows:

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, inf_rep='inf', freeze_panes=None, storage_options=None, engine_kwargs=None)

Parameter description:

excel_writer: This is a required parameter, specifying the Excel file path or file object to write to.
sheet_name='Sheet1': Specify the worksheet name to write to, defaulting to'Sheet1'。
na_rep='': Specify the string representing missing values (NaN) in the Excel file. The default is an empty string.
float_format=None: Specify the format of floating-point numbers. If it isNone, Excel's default format is used.
columns=None: Specify the columns to write. If it isNone, all columns are written.
header=True: Specify whether to write column names as the first row. If it isFalse, column names are not written.
index=True: Specify whether to write the index as the first column. If it isFalse, the index is not written.
index_label=None: Specify the label for the index column. If it isNone, the index label is not written.
startrow=0: Specify the row number to start writing from. The default is row 0.
startcol=0: Specify the column number to start writing from. The default is column 0.
engine=None: Specify the engine used when writing to the Excel file. The default isNone, pandas will automatically choose.
merge_cells=True: Specify whether to merge cells. If it isTrue, cells with the same value are merged.
inf_rep='inf': Specify the string representing infinite values in the Excel file. The default is'inf'。
freeze_panes=None: Specify the position of frozen panes. If it isNone, panes are not frozen.
storage_options=None: A parameter dictionary for cloud storage.
engine_kwargs=None: A dictionary of additional parameters to pass to the engine.

Example

import pandas as pd

# Create a simple DataFrame
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'Los Angeles', 'Chicago']
})

# Write the DataFrame to an Excel file, into the 'Sheet1' sheet
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)

# Write multiple sheets using ExcelWriter
with pd.ExcelWriter('output.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
df.to_excel(writer, sheet_name='Sheet2', index=False)

ExcelFile - Load Excel File

ExcelFileIt is a class for reading Excel files. It can handle multiple sheets and access the data within without reopening the file.

The syntax is as follows:

excel_file = pd.ExcelFile('data.xlsx')

Common methods:

Method	Function description
`sheet_names`	Return a list of names of all sheets in the file
`parse(sheet_name)`	Parse the specified sheet and return a DataFrame
`close()`	Close the file to release resources

Example

import pandas as pd

# Use ExcelFile to load an Excel file
excel_file = pd.ExcelFile('data.xlsx')

# View the names of all sheets
print(excel_file.sheet_names)

# Read the specified sheet
df = excel_file.parse('Sheet1')
print(df)

# Close the file
excel_file.close()

ExcelWriter - Write Excel File

ExcelWriter is a class provided by pandas, used to write DataFrame or Series objects to Excel files. With ExcelWriter, you can write multiple worksheets to one Excel file and have more flexible control over the writing process.

The syntax is as follows:

pandas.ExcelWriter(path, engine=None, date_format=None, datetime_format=None, mode='w', storage_options=None, if_sheet_exists=None, engine_kwargs=None)

Parameter description:

path: This is a required parameter, specifying the path, URL, or file object of the Excel file to write. It can be a local file path, a remote storage path (such as S3), a URL link, or an already open file object.。
engine: This is an optional parameter used to specify the engine for writing to the Excel file. If it isNone, pandas will automatically select an available engine (by default, it prefersopenpyxl, and if unavailable, it selects another available engine). Common engines include'openpyxl'(for.xlsxfiles),'xlsxwriter'(provides advanced formatting and chart features),'odf'(for OpenDocument formats such as.ods) etc.。
date_format: This is an optional parameter, specifying the format string for dates written to the Excel file, for example"YYYY-MM-DD"。
datetime_formatThis is an optional parameter that specifies the format string for datetime objects written to the Excel file, for example"YYYY-MM-DD HH:MM:SS"。
modeThis is an optional parameter, defaulting to'w', indicating the write mode. If set to'a', it means append mode, adding data to an existing file (only supported by some engines, such asopenpyxl）。
storage_optionsThis is an optional parameter used to specify additional options for connecting to the storage backend, such as authentication information, access permissions, etc., applicable to writing to remote storage (such as S3, GCS)。
if_sheet_existsThis is an optional parameter, defaulting to'error', specifying the behavior when a worksheet already exists. Options include'error'(raise an error),'new'(create a new worksheet),'replace'(replace the content of the existing worksheet),'overlay'(overwrite on the existing worksheet)。
engine_kwargsThis is an optional parameter used to pass other keyword arguments to the engine. These arguments are passed to the functions of the corresponding engine, for examplexlsxwriter.Workbook(file, **engine_kwargs)oropenpyxl.Workbook(**engine_kwargs)wait。

Creating an ExcelWriter object:

Example

with ExcelWriter('output.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1')

Here, ExcelWriter is used as a context manager to ensure that the file is properly closed after the operation is completed.

Writing to multiple worksheets:

You can use the same ExcelWriter object to write different DataFrames to different worksheets in the same Excel file.

Example

df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
with pd.ExcelWriter("path_to_file.xlsx") as writer:
df1.to_excel(writer, sheet_name="Sheet1")
df2.to_excel(writer, sheet_name="Sheet2")

Setting date format or datetime format:

Example

from datetime import date, datetime
df = pd.DataFrame(
[
[date(2014, 1, 31), date(1999, 9, 24)],
[datetime(1998, 5, 26, 23, 33, 4), datetime(2014, 2, 28, 13, 5, 13)],
],
index=["Date", "Datetime"],
columns=["X", "Y"],
)
with pd.ExcelWriter(
"path_to_file.xlsx",
date_format="YYYY-MM-DD",
datetime_format="YYYY-MM-DD HH:MM:SS"
) as writer:
df.to_excel(writer)

Appending content to an existing Excel file:

Example

with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
df.to_excel(writer, sheet_name="Sheet3")

Using the if_sheet_exists parameter to replace an existing worksheet:

Example

with ExcelWriter(
"path_to_file.xlsx",
mode="a",
engine="openpyxl",
if_sheet_exists="replace",
) as writer:
df.to_excel(writer, sheet_name="Sheet1")

Writing multiple DataFrames to the same worksheet, note that the if_sheet_exists parameter needs to be set to overlay:

Example

with ExcelWriter("path_to_file.xlsx",
mode="a",
engine="openpyxl",
if_sheet_exists="overlay",
) as writer:
df1.to_excel(writer, sheet_name="Sheet1")
df2.to_excel(writer, sheet_name="Sheet1", startcol=3)

Storing the Excel file in memory:

Example

import io
df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
buffer = io.BytesIO()
with pd.ExcelWriter(buffer) as writer:
df.to_excel(writer)

Packaging the Excel file into a zip compressed file:

Example

import zipfile
df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
with zipfile.ZipFile("path_to_file.zip", "w") as zf:
with zf.open("filename.xlsx", "w") as buffer:
with pd.ExcelWriter(buffer) as writer:
df.to_excel(writer)

Passing additional arguments to the underlying engine:

Example

with pd.ExcelWriter(
"path_to_file.xlsx",
engine="xlsxwriter",
engine_kwargs={"options": {"nan_inf_to_errors": True}}
) as writer:
df.to_excel(writer)

In append mode, engine_kwargs will be passed to openpyxl's load_workbook:

Example

with pd.ExcelWriter(
"path_to_file.xlsx",
engine="openpyxl",
mode="a",
engine_kwargs={"keep_vba": True}
) as writer:
df.to_excel(writer, sheet_name="Sheet2")

Other extensions

Data Reading and Writing

Data Processing Core

Reference Manual

Statistics and Cases

Pandas Excel File Operations

`pd.read_excel() - 读取 Excel 文件`

Example

Example

DataFrame.to_excel() - Write DataFrame to Excel File

Example

ExcelFile - Load Excel File

Example

ExcelWriter - Write Excel File

Example

Example

Example

Example

Example

Example

Example

Example

Example

Example