pandas-read_excel函数介绍

下面来介绍关于pandas的一个常用函数，作为财务，每天就是和各种数据打交道，数据的载体大多就是excel，每个财务人不是几乎，每天都会使用excel。

Excel是非常全能且易用的，现在办公已经离不开Excel，但是Excel也有很明显的短板，就是不擅长数据处理与分析，在面对大数据时，Excel就显得力不从心了，所以以后我们会介绍新工具，pandas，也是给博主做一个备忘录，我在学到各种新方法时，在熟练后，都会更新到博客，以便自查，同时也想吧新方法交给大家。

好了话不多说，介绍pandas的read_excel函数：

可点击此链接，阅读博主博客原文章

基本用法

import pandas as pd

# 最简单的调用方式，只需要提供Excel文件的路径
df = pd.read_excel('path_to_file.xlsx')

参数

read_excel 提供了许多参数来定制读取过程，以下是一些常用的参数：

io: 文件路径或文件句柄。
sheet_name: 指定要读取的工作表名称或索引，默认是第一个工作表（0 或 None）。也可以传递列表以同时读取多个工作表。
header: 指定哪一行作为列名，默认是第0行（即第一行）。如果数据没有列标题，则设置为None。
names: 如果没有头部信息或者想要覆盖头部信息，可以使用这个参数提供自定义列名。
index_col: 将指定列设置为索引列。
usecols: 可选参数，用于指定只读取哪些列，可以提高性能。
dtype: 强制转换某些列的数据类型。
skiprows: 跳过指定数量的行或指定跳过的行号。
nrows: 限制读取的行数。
na_values: 指定额外的缺失值标记。

下面是read_excel的简单示例，从excel中创建df:

# 读取特定的工作表并命名列
df = pd.read_excel('data.xlsx', sheet_name='Sheet2', header=None, names=['A', 'B', 'C'])

# 读取多个工作表，并将它们放入字典中
dfs = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet3'])

# 读取特定列，并跳过前两行
df = pd.read_excel('data.xlsx', usecols=[0, 2], skiprows=2)

# 强制某些列的数据类型
df = pd.read_excel('data.xlsx', dtype={'Column1': str, 'Column2': float})

参数示例

我们注意讲解各个参数并附上简单示例

指定工作表

# 读取特定的工作表 'Sheet2'
df_sheet2 = pd.read_excel('employees.xlsx', sheet_name='Sheet2')
print(df_sheet2.head())

pandas在读取excel时，可以指定工作表，此时参数sheet_name可以被传入整数或字符串，如果传入整数，则为工作表的索引（注意索引从0开始）；如果传入字符串，则为工作表的名称。

注意：

如果超过索引或者没有相应工作表的名称则会报错；

如果一个工作表名为‘1’，则你应传入参数sheet_name=”1″，而不是sheet_name=1。

读取多个工作表

pandas支持一次性读取多个工作表并返回一个df字典

# 读取多个工作表，并将它们放入字典中
dfs = pd.read_excel('employees.xlsx', sheet_name=['Sheet1', 'Sheet3'])
for sheet, data in dfs.items():
    print(f"Sheet: {sheet}")
    print(data.head())

读取Sheet1和Sheet3，并返回一个df字典，并通过字典的items方法遍历输出各个表的df

如果想一次性读取所有表，则只需要传入sheet_name=None，即：

import pandas as pd

# 读取所有工作表，并返回一个字典
dfs = pd.read_excel('employees.xlsx', sheet_name=None)

# 遍历字典以查看每个工作表的前几行数据
for sheet_name, df in dfs.items():
    print(f"Sheet Name: {sheet_name}")
    print(df.head())
    print("\n")  # 打印空行用于分隔不同工作表的输出

返回的dfs是一个包含所有表的df字典，可以通过调用字典的一个方法，来分别输出各个表格的df。

如果想输出第1个到第3个表格，只知道索引，不知道名称，也不想读取所有表，则可以通过列表推导式，传入一个整数列表，例如：

sheet_name=[i for i in range(0,3) ]

这样就可以读取特定位置的表格了。

自定义列名

如果你的excel在写入df时，没有列名或者你想自定义列名，可以传入names参数，例如：

# 跳过第一行作为标题，使用自定义列名
df_custom_header = pd.read_excel('employees.xlsx', header=None, names=['ID', 'Name', 'Department', 'Salary'])
print(df_custom_header.head())

在这段代码中， header=None为没有标题行，而后names传入一个列表，作为标题行。

如果不传入header=None，df会自动覆盖第一行数据，并且如果你传入的names列表元素数量与读取到df宽度不匹配还会报错：

ValueError: Number of passed names did not match number of header fields in the file (sheet: 0)

读取特定列

在使用pandas读取excel时，如果我们希望只读取excel的特定列，就需要传入usecols参数，例如：

# 只读取 'Name' 和 'Salary' 列
df_specific_columns = pd.read_excel('employees.xlsx', usecols=['Name', 'Salary'])
print(df_specific_columns.head())

df_specific_columns 智慧输出列名为Name和Salary的数据；除了传入代表列名的字符串列表，usecols还支持传入整数列表，代表着列的索引，例如，要读取第一列到第二十列，则：

# 只读取表格的第一列到第二十列
df_specific_columns = pd.read_excel('employees.xlsx', usecols=[x for x in range(0,21) ])
print(df_specific_columns.head())

我们传入usecols一个列表推导式即可，但注意，如果传入的字符串列表中的元素，没有相应列名，或者传入的整数列表超过了实际列数范围，则会报错。

设置索引列

read_excel提供了一个参数index_col来设置索引，参数的默认值是None，代表着没有指定索引，pandas将不会使用Excel文件中的任何一列作为DataFrame的索引，而是会为每一行生成一个默认的整数索引，从0开始，依次递增。

单列设置为单级索引：

# 使用第1列（即索引为0的列）作为索引
df = pd.read_excel('data.xlsx', index_col=0)

# 或者使用列名为 'ID' 的列作为索引
df = pd.read_excel('data.xlsx', index_col='ID')

多个列设置多级索引：

# 使用前两列作为多级索引
df = pd.read_excel('data.xlsx', index_col=[0, 1])

# 或者使用列名为 'Category' 和 'Subcategory' 的列作为多级索引
df = pd.read_excel('data.xlsx', index_col=['Category', 'Subcategory'])

注意，你设置的索引最好是唯一的，或者多级索引起来应该是唯一的，虽然这不是强制性的，但非唯一的索引可能会导致某些操作（如查找或排序）效率低下，或是结果不符合预期。

如果你的索引中有缺失值，那么这一行就被丢弃，如果不想被pandas丢弃缺失索引值的行，你可以设置keep_default_na=False，这时，缺失值会被都读取为空字符串。

import pandas as pd

# 使用 'ID' 列作为索引读取Excel文件，假设ID有缺失值，则需要设置keep_default_na=False
df = pd.read_excel('employees.xlsx', index_col='ID',keep_default_na=False)

# 打印前几行查看结果
print(df.head())

强制转换数据类型

在读取excel时，经常会遇到读取出的值的数据类型被pandas自动转换，例如，财务数据中的银行卡号，身份证号等，会被pandas处理为浮点数（财务人留下了悔恨的泪水），我们需要指定数据结构，利用dtype参数，就可以制定列的数据结构，例如：

# 强制转换 'Salary' 列为整数类型
df_dtype = pd.read_excel('employees.xlsx', dtype={'BankCardNumber': str,'IdNumber': str})
print(df_dtype.head())

跳过指定行读取

参数skiprows可以时在读取时跳过特定行，参数可以传入整数、列表和函数，例如：

df = pd.read_excel('data.xlsx', skiprows=2)

这会使pandas读取excel跳过前两行；如果想跳过跳过第1行和第3行，则可以传入：

df = pd.read_excel('data.xlsx', skiprows=[0, 2])

注意行号从0开始，当然，如果有更复杂的跳过逻辑，可以传入函数：

# 定义一个函数，跳过行号能被3整除的行
def should_skip(row_num):
    return row_num % 3 == 0

df = pd.read_excel('data.xlsx', skiprows=should_skip)

注意，跳过指定行后的表头设置，当使用 skiprows 参数时，pandas 在确定列名时会考虑已经跳过的行。例如，如果此时设置了 skiprows=2 并且 header=0，那么实际用作标题的将是原始文件中的第3行（因为前两行已经被跳过了），很合理对吧，我也这么觉得。

限制读取的行数

在处理大型文件，或者想要预处理文件excel时，我们可以设置限制读取的最大行数，以便进行预处理操作。设置参数nrows：

df = pd.read_excel('data.xlsx', nrows=10)

这样就可以读取data.xlsx的前十行。

nrows与skiprows可以指定读取具体的行数，例如

# 跳过前5行，然后读取接下来的10行数据
df = pd.read_excel('data.xlsx', skiprows=5, nrows=10)

这样就可以直接读取第6到15行;

在处理超大型数据时，通常可以使用chunksize参数，指定每次读取的行数，直到结尾；

import pandas as pd

# 创建一个迭代器，每次读取100行数据
chunk_iterator = pd.read_excel('large_dataset.xlsx', chunksize=100)

# 遍历每个数据块
for chunk in chunk_iterator:
    # 在这里处理每个数据块
    print(chunk.head())  # 打印每个数据块的前几行以检查内容

chunksize 接受一个正整数作为参数，表示每次读取的行数。read_excel 将返回一个 TextFileReader 对象（实际上是 ExcelFile 对象的一个迭代器），你可以使用这个对象来逐块读取数据。每次迭代都会返回一个新的DataFrame，包含 chunksize 行的数据。

这里简要讲解chunksize参数，作为nrows参数的补充拓展，实际处理超大型数据时要复杂得多。

缺失值指定

在读取excel时，我们可以用na_values参数来设置关于缺失值的判定，例如：

df = pd.read_excel('data.xlsx', na_values='NA')

在pandas读取excel时，文件中的’NA’会被处理成为缺失值，在excel中，经常会因为函数引发错误，我们可以设置多个缺失值的判定，传入一个缺失值列表给na_values即可：

df = pd.read_excel('data.xlsx', na_values=['#NAME?', '#REF!', '#DIV/0!','#N/A'])

这样，在读取时，pandas就会讲这些错误转化为缺失值，方便我们后续的数据清洗。

当对不同的列指定不同的缺失值符号，可以传入一个字典，其中键是列名，值是要识别为缺失值的列表，例如：

df = pd.read_excel('data.xlsx', na_values={'Age': ['NA', 'unknown'], 'Salary': ['-', 'none']})

这其中，对Age的[‘NA’, ‘unknown’]会被认定为缺失值，而在Salary列[‘-‘, ‘none’]会认定为缺失值。

注意，在默认情况下，pandas已经定义了一些常见的缺失值标识符，例如：

• ‘-1.#IND’
• ‘1.#QNAN’, ‘1.#IND’
• ‘#N/A’, ‘#N/A N/A’, ‘#NA’
• ‘NULL’, ‘NaN’, ‘nan’, ‘NA’
• ‘NULL’, ‘nan’, ‘N/A’
• 空白字符串 ”

这意味着即使你不显式地设置 na_values 参数，pandas 也会将这些常见的缺失值标识符转换为 NaN。

其中excel常见的因为函数引发的错误：#NAME?，#REF!，#DIV/0!，#N/A均被包含其中，所以你不需要特殊说明pandas会自动转化excel函数错误为缺失值NaN。

当然如果不想使用默认的缺失值转换，可以直接设置 keep_default_na=False。

结尾

可点击此链接，阅读博主博客原文章

到这里pandas读取Excel就结束了，你现在几乎可以完美读取一个excel了，上述的参数可以应对90%以上的Excel文件的读取了。

当Excel的数据传入pandas的df中，你就可以应用df的方法来处理Excel了。