Excel文件操作

Excel 文件操作

小象实战讲义 · Python数据分析实战

在上一节，我们学习了如何使用 Pandas 处理 CSV 文件。CSV 是一种纯文本格式，而 Excel 文件则更为复杂，它不仅能存储数据，还能包含样式、图表以及多个工作表。本节我们将聚焦于数据分析的核心——数据本身，学习如何使用 Pandas 高效地读取和写入 Excel 文件，掌握处理多工作表数据的方法。

💡 核心导读

• Excel 与 CSV 的异同：理解两种格式的本质区别，明确数据分析的关注点在于纯数据。
• 读取 Excel 文件：使用 pd.read_excel() 读取单个或多个工作表，理解返回的数据结构。
• 写入 Excel 文件：使用 df.to_excel() 写入单个工作表，以及使用 pd.ExcelWriter 写入多个工作表。
• 实战演练：通过模拟“全球幸福指数”数据，完整演练 Excel 文件的读写操作。

Excel 文件简介

Excel 是微软开发的一款功能强大的电子表格软件。对于数据分析而言，我们只关心文件中的数据本身，而不关心其样式、图表等展示性元素。

与 CSV 文件相比，Excel 文件的主要区别在于：

• Excel：可以包含多个工作表（Sheet），每个工作表是一个独立的表格。文件内可以存储图表、单元格样式等非数据内容。
• CSV：是纯文本文件，每一行代表一条数据记录。它只能存储数据，无法存储任何样式或图表。

因此，当我们用 Pandas 读取 Excel 文件时，程序会自动忽略所有样式和图表，只将表格中的数据提取出来。

读取 Excel 文件

Pandas 提供了 read_excel() 函数来读取 Excel 文件，其核心参数是 sheet_name，用于指定要读取的工作表。

读取单个工作表

当 sheet_name 参数为字符串（工作表名称）或整数（工作表索引，从0开始）时，read_excel() 会返回一个 DataFrame 对象。

import pandas as pd

# 模拟原课程中的幸福指数数据（2016年部分数据）
data_2016 = {
    '国家': ['丹麦', '瑞士', '冰岛', '挪威', '芬兰'],
    '地区': ['Western Europe', 'Western Europe', 'Western Europe', 'Western Europe', 'Western Europe'],
    '幸福指数排名': [1, 2, 3, 4, 5],
    '幸福指数得分': [7.526, 7.509, 7.501, 7.498, 7.413],
    '家庭收入': [1.459, 1.562, 1.482, 1.582, 1.444]
}
df_2016 = pd.DataFrame(data_2016)

# 查看数据类型和预览数据
print("数据类型:", type(df_2016).__name__)
print("\n2016年幸福指数前5名国家:")
print(df_2016.head())

数据类型: DataFrame

2016年幸福指数前5名国家:
   国家              地区  幸福指数排名  幸福指数得分   家庭收入
0  丹麦  Western Europe       1   7.526  1.459
1  瑞士  Western Europe       2   7.509  1.562
2  冰岛  Western Europe       3   7.501  1.482
3  挪威  Western Europe       4   7.498  1.582
4  芬兰  Western Europe       5   7.413  1.444

读取多个工作表

当 sheet_name 参数为一个由工作表名称组成的列表时，read_excel() 会返回一个字典（dict；旧版 pandas 返回 collections.OrderedDict，新版已直接返回保持插入顺序的内置 dict），其键（key）是工作表名称，值（value）是对应的 DataFrame。

import pandas as pd

# 构造多个工作表的数据（模拟原课中的2015和2017年数据）
data_2015 = {
    '国家': ['瑞士', '冰岛', '丹麦', '挪威', '加拿大'],
    '幸福指数排名': [1, 2, 3, 4, 5],
    '幸福指数得分': [7.587, 7.561, 7.527, 7.522, 7.427]
}

data_2017 = {
    '国家': ['挪威', '丹麦', '冰岛', '瑞士', '芬兰'],
    '幸福指数排名': [1, 2, 3, 4, 5],
    '幸福指数得分': [7.537, 7.522, 7.504, 7.494, 7.469]
}

# 模拟读取多个工作表的结果：一个字典，键是工作表名，值是DataFrame
df_data = {
    '2015': pd.DataFrame(data_2015),
    '2017': pd.DataFrame(data_2017)
}

print("读取多个工作表后的数据类型:", type(df_data).__name__)

# 查看2015年的数据
print("\n2015年幸福指数前5名:")
print(df_data['2015'].head())

# 查看2017年的数据
print("\n2017年幸福指数前5名:")
print(df_data['2017'].head())

读取多个工作表后的数据类型: dict

2015年幸福指数前5名:
    国家  幸福指数排名  幸福指数得分
0   瑞士       1   7.587
1   冰岛       2   7.561
2   丹麦       3   7.527
3   挪威       4   7.522
4  加拿大       5   7.427

2017年幸福指数前5名:
   国家  幸福指数排名  幸福指数得分
0  挪威       1   7.537
1  丹麦       2   7.522
2  冰岛       3   7.504
3  瑞士       4   7.494
4  芬兰       5   7.469

写入 Excel 文件

与读取相对应，Pandas 也提供了将 DataFrame 写入 Excel 文件的方法。

写入单个工作表

使用 DataFrame 的 to_excel() 方法，并设置 index=False 可以避免将行索引写入文件，使数据更整洁。

import pandas as pd

# 构造要写入的数据
top5_2015_data = {
    '国家': ['瑞士', '冰岛', '丹麦', '挪威', '加拿大'],
    '幸福指数排名': [1, 2, 3, 4, 5],
    '幸福指数得分': [7.587, 7.561, 7.527, 7.522, 7.427]
}
top5_2015 = pd.DataFrame(top5_2015_data)

print("即将写入Excel的数据:")
print(top5_2015)
print("\n提示：在实际操作中，可使用 df.to_excel('2015_top5.xlsx', index=False) 将数据写入文件。")

即将写入Excel的数据:
    国家  幸福指数排名  幸福指数得分
0   瑞士       1   7.587
1   冰岛       2   7.561
2   丹麦       3   7.527
3   挪威       4   7.522
4  加拿大       5   7.427

提示：在实际操作中，可使用 df.to_excel('2015_top5.xlsx', index=False) 将数据写入文件。

写入多个工作表

要将多个 DataFrame 写入同一个 Excel 文件的不同工作表，需要使用 pd.ExcelWriter 对象作为中间媒介。

import pandas as pd

# 构造两个DataFrame
top5_2015_data = {
    '国家': ['瑞士', '冰岛', '丹麦', '挪威', '加拿大'],
    '排名': [1, 2, 3, 4, 5],
    '得分': [7.587, 7.561, 7.527, 7.522, 7.427]
}
df_2015 = pd.DataFrame(top5_2015_data)

top5_2017_data = {
    '国家': ['挪威', '丹麦', '冰岛', '瑞士', '芬兰'],
    '排名': [1, 2, 3, 4, 5],
    '得分': [7.537, 7.522, 7.504, 7.494, 7.469]
}
df_2017 = pd.DataFrame(top5_2017_data)

print("2015年数据:")
print(df_2015)
print("\n2017年数据:")
print(df_2017)
print("\n提示：写入多个工作表的代码流程如下：")
print("1. writer = pd.ExcelWriter('combined_top5.xlsx')")
print("2. df_2015.to_excel(writer, sheet_name='2015_top5', index=False)")
print("3. df_2017.to_excel(writer, sheet_name='2017_top5', index=False)")
print("4. writer.close()")

2015年数据:
    国家  排名     得分
0   瑞士   1  7.587
1   冰岛   2  7.561
2   丹麦   3  7.527
3   挪威   4  7.522
4  加拿大   5  7.427

2017年数据:
   国家  排名     得分
0  挪威   1  7.537
1  丹麦   2  7.522
2  冰岛   3  7.504
3  瑞士   4  7.494
4  芬兰   5  7.469

提示：写入多个工作表的代码流程如下：
1. writer = pd.ExcelWriter('combined_top5.xlsx')
2. df_2015.to_excel(writer, sheet_name='2015_top5', index=False)
3. df_2017.to_excel(writer, sheet_name='2017_top5', index=False)
4. writer.close()

版本提示：旧版 pandas 用 writer.save() 收尾，该方法已在 pandas 2.0 中移除——请改用 writer.close()；更推荐的写法是用 with pd.ExcelWriter('combined_top5.xlsx') as writer: 上下文管理器，代码块结束时自动保存并关闭。

本章小结

本节我们深入学习了如何使用 Pandas 处理 Excel 文件。关键在于理解 Excel 文件可以包含多个工作表以及非数据内容，而数据分析只提取其中的纯数据部分。通过 read_excel() 和 to_excel() 方法，我们可以灵活地进行数据的读取和写入操作。

📋 行动清单

• 尝试使用 pd.read_excel() 读取一个包含多个工作表的 Excel 文件（可使用自己已有的文件或在线示例），并分别查看不同工作表的数据。
• 将两个不同的 DataFrame 使用 pd.ExcelWriter 写入到同一个 Excel 文件的两个工作表中，并验证结果。
• 思考：如果 Excel 工作表中包含合并单元格或复杂格式，Pandas 读取后数据会变成什么样？动手验证你的猜想。

📝 动手练一练

1. 概念辨析：pd.read_excel(filename, sheet_name=0) 和 pd.read_excel(filename, sheet_name='Sheet1') 这两种写法在什么情况下结果相同，什么情况下可能不同？

2. 代码实践：假设你有一个字典 all_data，其键是年份（如 '2022', '2023'），值是包含当年销售数据的 DataFrame。请编写代码，将这些数据写入一个名为 'sales_report.xlsx' 的 Excel 文件中，每个 DataFrame 单独占一个以年份命名的工作表，并且不包含行索引。

import pandas as pd

# 假设的原始数据
all_data = {
    '2022': pd.DataFrame({'产品': ['A', 'B'], '销量': [100, 150]}),
    '2023': pd.DataFrame({'产品': ['A', 'B'], '销量': [120, 180]})
}

# 写入多个工作表
# 注意：此处仅为代码演示，实际执行不会真正创建文件
# with pd.ExcelWriter('sales_report.xlsx') as writer:
#     for year, df in all_data.items():
#         df.to_excel(writer, sheet_name=year, index=False)

print("代码逻辑已说明。核心步骤：")
print("1. 创建ExcelWriter对象。")
print("2. 遍历字典，将每个DataFrame写入指定名称的工作表。")
print("3. 关闭Writer对象（with 语句结束时自动完成）。")

代码逻辑已说明。核心步骤：
1. 创建ExcelWriter对象。
2. 遍历字典，将每个DataFrame写入指定名称的工作表。
3. 关闭Writer对象（with 语句结束时自动完成）。

—— 小象教研组