Pandas pd.read_excel() Function
Pandas Common Functionsread_excel()is a function in the pandas library used to read Excel files, supporting reading.xlsxand.xlsExcel files in the format.
Excel is the most commonly used file format in enterprise data analysis. It supports multiple worksheets, rich cell formatting, formulas, etc.read_excel()It can read data from Excel files and convert it into pandas DataFrame format, facilitating subsequent data processing and analysis.
Basic Syntax and Parameters
Syntax Format
pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None,
usecols=None, dtype=None, skiprows=None, nrows=None,
na_values=None, ...)
Parameter Description
| Parameter | Type | Description | Default Value |
|---|---|---|---|
| io | str, ExcelFile, path object, file-like object | Excel file path or ExcelFile object | Required |
| sheet_name | str, int, list, None | Worksheet name or index to read; None reads all worksheets | 0 |
| header | int, list of int | Row number to use as column names; 0 means the first row | 0 |
| names | list-like | Custom column name list | None |
| index_col | int, str | Column to use as row index | None |
| usecols | int, str, list | Read only the specified columns | None |
| dtype | dict | Data type for specified columns | None |
| skiprows | list-like, int | Skip the specified rows | None |
| nrows | int | Read only the first n rows | None |
Return Value
- Return Type：
pd.DataFrameordictof DataFrames - When
sheet_nameWhen it is a single worksheet, returns a DataFrame. - When
sheet_nameWhen it is None or contains multiple worksheets, returns a dictionary with worksheet names as keys and corresponding DataFrames as values.
Examples
Through the following examples, you will comprehensively masterread_excel()the various usages of.
Example 1: Reading a Local Excel File
First, create and read a simple Excel file.
Example
import pandas as pd
import openpyxl # Need to install: pip install openpyxl
# Create a DataFrame
data = {
'name': ['Tom', 'Jerry', 'Mike', 'Lucy'],
'age': [28, 35, 42, 26],
'city': ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen'],
'salary': [8000, 12000, 15000, 7000]
}
df_original = pd.DataFrame(data)
# Write the DataFrame to an Excel file
# Excel file path: employees.xlsx
df_original.to_excel('employees.xlsx', index=False, engine='openpyxl')
# Use read_excel to read the Excel file
# io: file path (required)
df = pd.read_excel('employees.xlsx')
# View the read result
print("Read DataFrame:")
print(df)
print("nData types:")
print(df.dtypes)
import openpyxl # Need to install: pip install openpyxl
# Create a DataFrame
data = {
'name': ['Tom', 'Jerry', 'Mike', 'Lucy'],
'age': [28, 35, 42, 26],
'city': ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen'],
'salary': [8000, 12000, 15000, 7000]
}
df_original = pd.DataFrame(data)
# Write the DataFrame to an Excel file
# Excel file path: employees.xlsx
df_original.to_excel('employees.xlsx', index=False, engine='openpyxl')
# Use read_excel to read the Excel file
# io: file path (required)
df = pd.read_excel('employees.xlsx')
# View the read result
print("Read DataFrame:")
print(df)
print("nData types:")
print(df.dtypes)
Expected output:
读取的 DataFrame:
name age city salary
0 Tom 28 Beijing 8000
1 Jerry 35 Shanghai 12000
2 Mike 42 Guangzhou 15000
3 Lucy 26 Shenzhen 7000
数据类型:
name object
age int64
city object
salary int64
Code explanation:
pd.read_excel('employees.xlsx')It is the most basic usage; simply pass in the file path.- By default, it reads the first worksheet (sheet_name=0), with the first row as column names.
- Need to install
openpyxllibrary to support reading and writing .xlsx format.
Example 2: Reading Multiple Worksheets
Excel files can contain multiple worksheets,read_excel()supporting flexible reading of specified worksheets or all worksheets.
h2 class="example">Example
import pandas as pd
# Create an Excel file containing multiple worksheets
# First create two DataFrames
df_sales = pd.DataFrame({
'product': ['A', 'B', 'C', 'D'],
'quantity': [100, 200, 150, 80],
'price': [50, 30, 40, 60]
})
df_inventory = pd.DataFrame({
'product': ['A', 'B', 'C', 'D'],
'stock': [500, 300, 400, 200],
'warehouse': ['WH1', 'WH2', 'WH1', 'WH3']
})
# Write multiple worksheets to one Excel file
with pd.ExcelWriter('multi_sheet.xlsx', engine='openpyxl') as writer:
df_sales.to_excel(writer, sheet_name='Sales', index=False)
df_inventory.to_excel(writer, sheet_name='Inventory', index=False)
# Example 2a: Read a specified worksheet (by name)
df_sales_read = pd.read_excel('multi_sheet.xlsx', sheet_name='Sales')
print("Read the Sales worksheet:")
print(df_sales_read)
print()
# Example 2b: Read a specified worksheet (by index)
df_inv_read = pd.read_excel('multi_sheet.xlsx', sheet_name=1)
print("Read the 2nd worksheet (index 1):")
print(df_inv_read)
print()
# Example 2c: Read all worksheets (returns a dictionary)
all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
print("All worksheet names:", list(all_sheets.keys()))
print("nIterate through all worksheets:")
for sheet_name, df_sheet in all_sheets.items():
print(f"n--- {sheet_name} ---")
print(df_sheet)
# Create an Excel file containing multiple worksheets
# First create two DataFrames
df_sales = pd.DataFrame({
'product': ['A', 'B', 'C', 'D'],
'quantity': [100, 200, 150, 80],
'price': [50, 30, 40, 60]
})
df_inventory = pd.DataFrame({
'product': ['A', 'B', 'C', 'D'],
'stock': [500, 300, 400, 200],
'warehouse': ['WH1', 'WH2', 'WH1', 'WH3']
})
# Write multiple worksheets to one Excel file
with pd.ExcelWriter('multi_sheet.xlsx', engine='openpyxl') as writer:
df_sales.to_excel(writer, sheet_name='Sales', index=False)
df_inventory.to_excel(writer, sheet_name='Inventory', index=False)
# Example 2a: Read a specified worksheet (by name)
df_sales_read = pd.read_excel('multi_sheet.xlsx', sheet_name='Sales')
print("Read the Sales worksheet:")
print(df_sales_read)
print()
# Example 2b: Read a specified worksheet (by index)
df_inv_read = pd.read_excel('multi_sheet.xlsx', sheet_name=1)
print("Read the 2nd worksheet (index 1):")
print(df_inv_read)
print()
# Example 2c: Read all worksheets (returns a dictionary)
all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
print("All worksheet names:", list(all_sheets.keys()))
print("nIterate through all worksheets:")
for sheet_name, df_sheet in all_sheets.items():
print(f"n--- {sheet_name} ---")
print(df_sheet)
Expected output:
读取 Sales 工作表: product quantity price 0 A 100 50 1 B 200 30 2 C 150 40 3 D 80 60 读取第2个工作表 (索引为1): product stock warehouse 0 A 500 WH1 1 B 300 WA2 2 C 400 WH1 3 D 200 WH3 所有工作表名称: ['Sales', 'Inventory'] 遍历所有工作表: --- Sales --- product quantity price ... --- Inventory --- product stock warehouse ...
Code explanation:
sheet_nameThe sheet_name parameter can accept worksheet names (strings) or indices (integers).- Setting
sheet_name=Nonewill read all worksheets and return a dictionary with worksheet names as keys and DataFrames as values. - Using
pd.ExcelWritermakes it easy to write multiple worksheets.
Example 3: Advanced Usage - Custom Columns and Skipping Rows
In real work, Excel files may have complex formats that require flexible handling.
Example
import pandas as pd
# Create an Excel file with a header row and empty rows
# The first few rows are metadata; actual data starts from row 4
data_with_header = """Company employee data
Creation date: 2024-01-01
Department: Technology Department
---
name,age,city,salary
Tom,28,Beijing,8000
Jerry,35,Shanghai,12000
"""
# First create a CSV then convert to Excel (simulating a real scenario)
import io
df_temp = pd.read_csv(io.StringIO(data_with_header.split('---')[1]))
df_temp.to_excel('complex_format.xlsx', index=False, engine='openpyxl')
# Example 3a: Skip the first few rows, use the Nth row as column names
df_skip = pd.read_excel('complex_format.xlsx', header=3)
print("Skip the first 3 rows, use row 4 as column names:")
print(df_skip)
print()
# Example 3b: Read only the specified columns
df_cols = pd.read_excel('complex_format.xlsx', usecols=['name', 'salary'])
print("Read only the name and salary columns:")
print(df_cols)
print()
# Example 3c: Read only the first few rows
df_head = pd.read_excel('complex_format.xlsx', nrows=2)
print("Read only the first 2 rows:")
print(df_head)
print()
# Example 3d: Custom column names
df_custom_names = pd.read_excel('complex_format.xlsx',
names=['Name', 'Age', 'City', 'Salary'],
header=0)
print("Custom column names:")
print(df_custom_names)
# Create an Excel file with a header row and empty rows
# The first few rows are metadata; actual data starts from row 4
data_with_header = """Company employee data
Creation date: 2024-01-01
Department: Technology Department
---
name,age,city,salary
Tom,28,Beijing,8000
Jerry,35,Shanghai,12000
"""
# First create a CSV then convert to Excel (simulating a real scenario)
import io
df_temp = pd.read_csv(io.StringIO(data_with_header.split('---')[1]))
df_temp.to_excel('complex_format.xlsx', index=False, engine='openpyxl')
# Example 3a: Skip the first few rows, use the Nth row as column names
df_skip = pd.read_excel('complex_format.xlsx', header=3)
print("Skip the first 3 rows, use row 4 as column names:")
print(df_skip)
print()
# Example 3b: Read only the specified columns
df_cols = pd.read_excel('complex_format.xlsx', usecols=['name', 'salary'])
print("Read only the name and salary columns:")
print(df_cols)
print()
# Example 3c: Read only the first few rows
df_head = pd.read_excel('complex_format.xlsx', nrows=2)
print("Read only the first 2 rows:")
print(df_head)
print()
# Example 3d: Custom column names
df_custom_names = pd.read_excel('complex_format.xlsx',
names=['Name', 'Age', 'City', 'Salary'],
header=0)
print("Custom column names:")
print(df_custom_names)
Expected output:
跳过前3行，使用第4行作为列名:
name age city salary
0 Tom 28 Beijing 8000
1 Jerry 35 Shanghai 12000
只读取 name 和 salary 列:
name salary
0 Tom 8000
1 Jerry 12000
只读取前2行:
name age city salary
0 Tom 28 Beijing 8000
自定义列名:
姓名 年龄 城市 薪资
0 Tom 28 Beijing 8000
1 Jerry 35 Shanghai 12000
Code explanation:
headerThe header parameter specifies which row to use as column names (counting from 0).usecolsusecols can specify which columns to read, supporting column name lists or column indices.nrowsnrows limits the number of rows read, suitable for partial reading of large files.namesThe names parameter can customize column names and will override the column names in the original file.
Notes
- Reading .xlsx format requires installing
openpyxl：pip install openpyxl。 - Reading .xls format requires installing
xlrd：pip install xlrd(Note: xlrd 2.0+ no longer supports .xls files). - Excel files have row and column limits (maximum 1,048,576 rows and 16,384 columns); exceeding these limits will result in data loss.
- When reading large files, consider using
usecolsthe usecols parameter to read only the needed columns and improve performance. sheet_nameThe sheet_name parameter supports mixing names and indices.
Summary
read_excel()It is the core function in pandas for reading Excel files and is very powerful. It supports reading single or multiple worksheets and can flexibly handle Excel files in various formats.
In practical data analysis work, Excel files are one of the most common data sources. Masteringread_excel()the various parameter usages allows you to efficiently handle all kinds of Excel data, preparing you for subsequent data cleaning and analysis. Readers are advised to practice more, especially reading multiple worksheets and configuring parameters.