使用 Python pandas操作 Excel 文件
使用 Python pandas 操作 Excel 文件
flyfish
pandas 是 Python 中一个强大的数据处理和分析库，它提供了丰富的数据结构（如 Series 和 DataFrame）和数据操作方法，能够方便地处理各种数据格式，包括 Excel 文件。
安装 pandas
在使用 pandas 之前，需要先安装它。可以使用 pip 进行安装：
pip install pandas
如果需要处理 Excel 文件，还需要安装 openpyxl 库（用于处理 .xlsx 文件）：
pip install openpyxl
数据结构
Series：一维的带标签数组，类似于一维数组或列表。DataFrame：二维的表格型数据结构，类似于 Excel 表格，由行和列组成。
例子
1. 读取 Excel 文件
import pandas as pd
# 读取 Excel 文件
file_path = 'example.xlsx'
df = pd.read_excel(file_path)
# 查看数据的基本信息
print('数据基本信息：')
df.info()
# 查看数据集行数和列数
rows, columns = df.shape
if rows:
# 数据行数大于 0 时查看数据前几行信息
print('数据前几行信息：')
print(df.head().to_csv(sep='\t', na_rep='nan'))
else:
print('数据为空')
代码解释：
pd.read_excel用于读取 Excel 文件，返回一个DataFrame对象。df.info()用于查看数据的基本信息，如列名、数据类型等。df.head()用于查看数据集行数和列数。df.head()用于查看数据的前几行。
2. 写入 Excel 文件
import pandas as pd
# 创建示例数据
data = {
'姓名': ['张三', '李四', '王五'],
'年龄': [25, 30, 35],
'城市': ['北京', '上海', '广州']
}
df = pd.DataFrame(data)
# 写入单个工作表
df.to_excel('output_single.xlsx', index=False)
# 写入多个工作表
with pd.ExcelWriter('output_multi.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
df.to_excel(writer, sheet_name='Sheet2', index=False)
代码解释：
pd.DataFrame用于创建一个DataFrame对象。df.to_excel用于将DataFrame写入 Excel 文件。index=False表示不保存行索引。pd.ExcelWriter用于将多个DataFrame写入同一个 Excel 文件的不同工作表。
3. 筛选和处理数据
import pandas as pd
# 读取 Excel 文件
df = pd.read_excel('example.xlsx')
# 筛选年龄大于 30 的数据
filtered_df = df[df['年龄'] > 30]
# 保存筛选后的数据到新的 Excel 文件
filtered_df.to_excel('filtered_output.xlsx', index=False)
代码解释：
df[df['年龄'] > 30]用于筛选出年龄大于 30 的行。filtered_df.to_excel用于将筛选后的数据保存到新的 Excel 文件。
4. 数据排序
import pandas as pd
# 读取 Excel 文件
df = pd.read_excel('example.xlsx')
# 按年龄降序排序
sorted_df = df.sort_values(by='年龄', ascending=False)
# 保存排序后的数据到新的 Excel 文件
sorted_df.to_excel('sorted_output.xlsx', index=False)
代码解释：
df.sort_values(by='年龄', ascending=False)用于按年龄列降序排序。sorted_df.to_excel用于将排序后的数据保存到新的 Excel 文件。
5. 数据合并
import pandas as pd
# 创建示例数据
data1 = {
'ID': [1, 2, 3],
'姓名': ['张三', '李四', '王五']
}
df1 = pd.DataFrame(data1)
data2 = {
'ID': [2, 3, 4],
'成绩': [80, 90, 70]
}
df2 = pd.DataFrame(data2)
# 合并数据
merged_df = pd.merge(df1, df2, on='ID', how='inner')
# 保存合并后的数据到新的 Excel 文件
merged_df.to_excel('merged_output.xlsx', index=False)
代码解释：
pd.merge用于将两个DataFrame按指定列（这里是ID）进行合并。how='inner'表示使用内连接，只保留两个DataFrame中ID相同的行。merged_df.to_excel用于将合并后的数据保存到新的 Excel 文件。
单元格操作
示例 1：修改单个单元格的值
import pandas as pd
# 创建一个简单的 DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35]
}
df = pd.DataFrame(data)
# 修改第 1 行（索引为 0）、'Name' 列的单元格值
df.at[0, 'Name'] = 'Alicia'
# 将修改后的 DataFrame 保存为 Excel 文件
df.to_excel('single_cell_modify.xlsx', index=False)
代码解释：
df.at[0, 'Name']用于定位到第 1 行（索引从 0 开始）Name列的单元格。- 通过赋值操作将该单元格的值修改为
Alicia。 - 最后使用
to_excel方法将修改后的DataFrame保存为 Excel 文件。
示例 2：根据条件修改单元格的值
import pandas as pd
# 创建一个简单的 DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35]
}
df = pd.DataFrame(data)
# 当年龄大于 30 时，将姓名改为 'Senior'
df.loc[df['Age'] > 30, 'Name'] = 'Senior'
# 将修改后的 DataFrame 保存为 Excel 文件
df.to_excel('conditional_cell_modify.xlsx', index=False)
代码解释：
df.loc[df['Age'] > 30, 'Name']用于筛选出Age列中值大于 30 的行，并定位到Name列的单元格。- 将这些单元格的值修改为
Senior。 - 最后保存为 Excel 文件。
示例 3：读取单个单元格的值
import pandas as pd
# 读取 Excel 文件
df = pd.read_excel('example.xlsx')
# 读取第 2 行（索引为 1）、第 1 列（索引为 0）的单元格值
cell_value = df.iat[1, 0]
print(f"第 2 行第 1 列的单元格值为: {cell_value}")
代码解释：
df.iat[1, 0]用于读取第 2 行（索引从 0 开始）、第 1 列的单元格值。- 将读取到的值存储在
cell_value变量中并打印。
示例 4：批量修改某列单元格的值
import pandas as pd
# 创建一个简单的 DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35]
}
df = pd.DataFrame(data)
# 给 'Age' 列的每个单元格值加 5
df['Age'] = df['Age'] + 5
# 将修改后的 DataFrame 保存为 Excel 文件
df.to_excel('batch_cell_modify.xlsx', index=False)
代码解释：
df['Age'] = df['Age'] + 5对Age列的每个单元格值都加上 5。- 最后保存修改后的
DataFrame为 Excel 文件。
示例 5：在特定单元格插入公式
import pandas as pd
# 创建一个简单的 DataFrame
data = {
'Num1': [1, 2, 3],
'Num2': [4, 5, 6]
}
df = pd.DataFrame(data)
# 在新的 'Sum' 列的每个单元格插入求和公式
df['Sum'] = '=A' + (df.index + 2).astype(str) + '+B' + (df.index + 2).astype(str)
# 将修改后的 DataFrame 保存为 Excel 文件
df.to_excel('formula_insert.xlsx', index=False)
代码解释：
- 首先创建一个包含两列数值的
DataFrame。 - 然后创建一个新的
Sum列，通过字符串拼接的方式为每个单元格插入求和公式（这里假设 Excel 中Num1对应 A 列，Num2对应 B 列）。 - 最后将
DataFrame保存为 Excel 文件，在 Excel 中打开该文件时，这些公式会自动计算结果。
操作多个单元格
1. 选择多个单元格
按行和列的范围选择
可以使用 loc 或 iloc 方法按行和列的范围来选择多个单元格。loc 基于标签（行索引名和列名）进行选择，iloc 基于整数位置进行选择。
import pandas as pd
# 创建示例 DataFrame
data = {
'Name': ['Alice', 'Bob', 'Charlie', 'David'],
'Age': [25, 30, 35, 40],
'Score': [85, 90, 78, 92]
}
df = pd.DataFrame(data)
# 使用 loc 按标签选择第 1 行到第 2 行，'Name' 列到 'Age' 列的单元格
selected_loc = df.loc[0:1, 'Name':'Age']
print("使用 loc 选择的单元格：")
print(selected_loc)
# 使用 iloc 按整数位置选择第 1 行到第 2 行，第 0 列到第 1 列的单元格
selected_iloc = df.iloc[0:2, 0:2]
print("\n使用 iloc 选择的单元格：")
print(selected_iloc)
按条件选择
可以根据某些条件筛选出满足条件的多个单元格。
# 选择 Age 大于 30 的所有行的 'Name' 和 'Age' 列单元格
selected_condition = df.loc[df['Age'] > 30, ['Name', 'Age']]
print("\n按条件选择的单元格：")
print(selected_condition)
2. 修改多个单元格的值
批量修改指定范围的值
# 将第 1 行到第 2 行，'Age' 列的单元格值都修改为 28
df.loc[0:1, 'Age'] = 28
print("\n修改指定范围单元格后的数据：")
print(df)
根据条件修改多个单元格的值
# 将 Age 大于 30 的行的 'Score' 列单元格值都加 5
df.loc[df['Age'] > 30, 'Score'] = df.loc[df['Age'] > 30, 'Score'] + 5
print("\n根据条件修改单元格后的数据：")
print(df)
3. 填充多个单元格
使用固定值填充
# 将第 1 行到第 3 行，'Score' 列的单元格用值 80 填充
df.loc[0:2, 'Score'] = 80
print("\n使用固定值填充单元格后的数据：")
print(df)
使用序列填充
# 用一个序列填充第 1 行到第 3 行，'Name' 列的单元格
new_names = ['Eve', 'Frank', 'Grace']
df.loc[0:2, 'Name'] = new_names
print("\n使用序列填充单元格后的数据：")
print(df)
4. 合并多个单元格的值
假设要将 Name 列和 Age 列的值合并到一个新列中。
# 创建一个新列 'Info'，将 'Name' 列和 'Age' 列的值合并
df['Info'] = df['Name'] + ' (' + df['Age'].astype(str) + ')'
print("\n合并单元格值后的数据：")
print(df)