12. Pandas :使用pandas读Excel文件的常用方法
一 read_excel 函数
其他参数根据实际需要进行查找。
1.接受一个工作表
在 11 案例用到的 Excel 工作簿中,数据是从第一张工作表的 A1 单元格开始的。但在实际场景中, Excel 文件可能并没有这么规整。所以 panda 提供了一些参数来优化读取过程。
比如 store.xlsx 文件的三个工作表都是这样:
通过 sheet_name、skiprows 和 usecols 这些参数,可以告诉 pandas 关于我们想要读取的列的详细信息。
sheet_name="2019"
:指定了要读取的工作表名称。sheet_name 接受工作表切片。闭区间,包括起始和结束的工作表。
- 单个工作表:
sheet_name="Sheet1"
- 多个工作表(列表):
sheet_name=["Sheet1", "Sheet2"]
- 工作表切片:
sheet_name="Sheet1:Sheet3"
或sheet_name=0:3
(使用索引)
skiprows=1
:用于跳过文件开头指定数量的行。在这个例子中,skiprows=1
表示跳过文件的第一行,从第二行开始读取数据。
usecols="B:F"
:用于指定要读取的列范围。读取从列 B 到列 F 的数据(包括 B 和 F 列)。
补充:Flagship 的数据类型应该是 bool 而不是 object。要修正这一问题,需要提供一个转换函数来处理某列中发生冲突的单元格。(编写 fix_missing 函数或者写一个lambda 表达式)
将
Flagship
列中的非布尔值处理为False
,而将有效的布尔值保留为True
或False
。
Flagship
列的数据类型是object
,而不是bool
。是因为该列中包含了一些非布尔值(如MISSING
和NaN
),导致 pandas 无法自动将其转换为布尔类型。1.编写函数
import pandas as pd import numpy as np # 定义 fix_missing 函数 def fix_missing(value): if isinstance(value, bool): return value else: return False # 读取数据 df = pd.read_excel("xl/stores.xlsx", sheet_name="2019", skiprows=1, usecols="B:F") # 应用 fix_missing 函数到 Flagship 列 df['Flagship'] = df['Flagship'].apply(fix_missing) # 打印修正后的 DataFrame print(df)
2.使用 lambda 表达式
import pandas as pd # 读取数据 df = pd.read_excel("xl/stores.xlsx", sheet_name="2019", skiprows=1, usecols="B:F") # 使用 lambda 表达式将 Flagship 列中非布尔值转换为 False df['Flagship'] = df['Flagship'].apply(lambda x: x if isinstance(x, bool) else False) # 打印修正后的 DataFrame print(df)
apply()
方法用于对 DataFrame 的每一列应用一个函数。
lambda x: x if isinstance(x, bool) else False
是一个匿名函数,它检查每个元素x
。如果
x
是布尔类型(True
或False
),则保留原值。如果x
不是布尔类型(例如,MISSING
或NaN
),则将其转换为False
。
isinstance(x, bool)
是一个内置函数,用于检查变量x
是否是布尔类型 (bool
)。3.结合使用
converters
参数用于指定列的自定义转换函数。这允许你在数据读取过程中,对特定列的数据进行预处理或转换。converters
是一个字典,其中键是列名(或列索引),值是应用于该列的转换函数。当pandas.read_excel
读取数据时,会使用这些转换函数来处理指定列的数据。def fix_missing(x): return False if x in ["", "MISSING"] else x df = pd.read_excel( "xl/stores.xlsx", sheet_name="2019", skiprows=1, usecols="B:F", converters={"Flagship": fix_missing})
fix_missing
函数:这个函数用于检查传入的值x
。如果
x
是一个空字符串""
或字符串"MISSING"
,则返回False
。否则,返回原始值x
。
converters={"Flagship": fix_missing}
:这里指定了一个转换字典,其中键是
"Flagship"
,表示要对Flagship
列应用转换。值是fix_missing
函数,这意味着在读取Flagship
列的数据时,每个单元格的值都会通过fix_missing
函数进行处理。
2.接受一个工作表名称列表
在 pandas
库中,read_excel
函数可以读取 Excel 文件中的一个或多个工作表。当你想要读取多个工作表时,可以通过传递一个工作表名称的列表或者使用 sheet_name=None
来读取所有的工作表。这将返回一个字典,其中键是工作表名称,值是对应的 DataFrame
。
假设你有一个 Excel 文件 data.xlsx
,其中包含以下三个工作表:
Sheet1: 包含学生基本信息。
Sheet2: 包含学生的成绩。
Sheet3: 包含学生的出勤记录。
import pandas as pd
# 读取 Excel 文件中的所有工作表
sheets = pd.read_excel("data.xlsx", sheet_name=None)
# `sheets` 是一个字典,键是工作表名称,值是对应的 DataFrame
print(sheets.keys()) # 输出: dict_keys(['Sheet1', 'Sheet2', 'Sheet3'])
# 访问特定工作表的 DataFrame
students_info = sheets['Sheet1']
students_grades = sheets['Sheet2']
students_attendance = sheets['Sheet3']
# 打印 Sheet1 的数据
print(students_info)
补充:usecols 参数。
用于指定要读取的列名,从而只导入你感兴趣的数据,忽略其他列。
import pandas as pd # 读取 Excel 文件中的两个工作表 "2019" 和 "2020" sheets = pd.read_excel( "xl/stores.xlsx", sheet_name=["2019", "2020"], skiprows=1, usecols=["Store", "Employees"]) # 打印 2019 年工作表的前两行 print(sheets["2019"].head(2))
.head(2)
方法打印该DataFrame
的前两行。
3.设置列名
源文件没有列标题,可以设置参数 header=None 并通过 names 参数提供对应的列名。
import pandas as pd
df = pd.read_excel(
"xl/stores.xlsx",
sheet_name=0,
skiprows=2,
skipfooter=3,
usecols="B:C,F",
header=None,
names=["Branch", "Employee_Count", "Is_Flagship"]
)
sheet_name=0:
指定读取 Excel 文件的第一个工作表。
skiprows=2:
跳过文件开头的 2 行。
skipfooter=3:
跳过文件末尾的 3 行。
usecols="B:C,F":
指定要读取的列。"B:C"
表示读取从 B 列到 C 列的所有列(包括 B 和 C 列)。"F"
表示读取 F 列。
header=None:
指定 Excel 文件中没有列标题行。规定不要将文件中的任何一行用作列名。
names=["Branch", "Employee_Count", "Is_Flagship"]
:由于 header=None
,我们需要通过 names
参数提供自定义的列名。
这里为读取的列指定了名称:"Branch"
、"Employee_Count"
和 "Is_Flagship"
。
4.处理缺失值
使用 na_values
和 keep_default_na 参数。
import pandas as pd
df = pd.read_excel(
"xl/stores.xlsx",
sheet_name="2019",
skiprows=1,
usecols="B,C,F",
skipfooter=2,
na_values="MISSING",
keep_default_na=False
)
na_values="MISSING"
:指定将 "MISSING"
视为缺失值(NaN
)。任何在数据中出现的 "MISSING"
都会被转换为 NaN
。
keep_default_na=False
:指定不保留 pandas
默认的缺失值标识符。默认情况下,pandas
会将空字符串 ""
、'nan'
、'null'
、'NULL'
等识别为缺失值并转换为 NaN
。设置为 False
后,只有 na_values
中指定的值(如 "MISSING"
) 会被视为缺失值。
二 ExcelFile 类
1.什么是 ExcelFile 类
使用 ExcelFile
类,你可以一次性加载整个 Excel 文件,然后根据需要选择性地解析各个工作表,从而提高数据处理的效率。
2.为什么有 ExcelFile 类
当需要从同一个 Excel 文件中读取多个工作表时,直接使用 read_excel
函数可能会多次打开和读取整个文件,导致性能下降。而 ExcelFile
类允许你一次性打开文件,并在内存中保持打开状态,从而避免了重复的文件读取操作。
ExcelFile
类可以被用作上下文管理器(使用 with
语句),这意味着文件会在使用后被自动关闭,从而避免了资源泄漏的问题。当使用 with
语句来打开 ExcelFile
时,pandas
会自动处理文件的打开和关闭。
3.如何使用 ExcelFile 类
1.导入 pandas 库
import pandas as pd
2.创建 ExcelFile 对象
传入 Excel 文件的路径。
补充:通过 ExcelFile 访问所有工作表的名称
3.读取工作表
使用 pd.read_excel
函数读取 ExcelFile
对象中的特定工作表。
4.(可选)使用上下文管理器
为了确保文件在使用后被正确关闭,可以使用 with
语句来管理 ExcelFile
对象。
补充:上下文管理器和 with 语句
Python 中的 with 语句被用于管理文件或数据连接之类的资源。如果你想加载最新的销售数据以便对其进行分析,就必须打开一个文件或者建立一个数据库连接。在数据读取完成后,最好尽快关闭文件或者数据库连接。否则,你可能会无法打开其他文件或者建立新的数据库连接。
手动打开和关闭文本文件的代码:
执行这段代码会在笔记本的工作目录中创建一个叫作 output.txt 的文件,并将“Some text”
写入文件。要读取一个文件,需要使用 r 模式而不是 w 模式;要在文件末尾追加内容,则需要使用 a 模式。
文件的打开和关闭是一种相当常见的操作,因此 Python 提供了 with 语句来简化这类代码:
当代码的执行过程离开 with 语句的主体时,无论是否发生异常,文件都会被自动关闭。支持 with 语句的对象被称作上下文管理器。ExcelFile 对象和 ExcelWriter 对象,数据库连接对象都是上下文管理器。
5.示例
pd.ExcelFile("xl/stores.xls")
: 使用 pandas
的 ExcelFile
类来打开和加载名为 "xl/stores.xls"
的 Excel 文件。
as f
: 将创建的 ExcelFile
对象赋值给变量 f
。这样,在 with
语句的作用域内,你可以通过 f
来引用这个 ExcelFile
对象。