代码收藏家技术教程 2025-06-11

Python办公自动化实战：利用Pandas高效处理Excel数据的核心技巧详解——批量操作与可视化案例全解析

引言

实战案例升级

一、环境准备

1. 安装 Pandas 库

2. 导入库与数据

二、数据初探：快速了解数据结构

2. 查看数据基本信息

3. 查看列名与索引

三、数据清洗：处理缺失值与重复值

1. 检测缺失值

2. 处理缺失值

3. 处理重复值

四、数据处理与分析：核心操作示例

1. 数据筛选与过滤

2. 数据分组与聚合

3. 数据排序与排名

五、数据可视化：快速生成统计图表

六、可视化效果升级

1. 图表添加数据标签

2. 新增交互式图表提示

七、结果输出：保存处理后的数据

八、实战案例：批量处理多个 Excel 文件

九、常见问题与解决方案

10 总结

11 下期预告：用 Python + PPTX 实现自动化报告生成，动态图表与数据一键同步！

12 参考资料

引言

在日常办公中，处理 Excel 数据是高频需求。手动操作不仅耗时费力，还容易出错。借助 Python 的pandas库，我们可以轻松实现数据读取、清洗、分析和可视化等操作，大幅提升工作效率。本文将通过实际案例，带你快速掌握用 Pandas 处理 Excel 的核心技巧。

为什么要处理缺失值？
缺失值可能导致统计结果偏差（如均值失真）、模型训练失败（如 Scikit-learn 要求数值非空）。实际场景中，需根据业务逻辑选择处理方式：

删除法：适用于缺失比例 > 30% 且非关键列

填充法：适用于缺失比例 < 10% 的连续型 / 离散型数据

插值法：适用于时间序列数据（如df.interpolate(method="linear")）

实战案例升级

需求：合并多个 Excel 文件时，需跳过含 “测试数据” 的文件，并自动识别日期格式列。
优化代码：

import re  
from datetime import datetime  

# 新增数据清洗函数  
def clean_date_columns(df):  
    date_cols = df.select_dtypes(include=["object"]).columns[  
        df.select_dtypes(include=["object"]).apply(lambda col: col.str.contains(r'\d{4}-\d{2}-\d{2}').any())  
    ]  
    for col in date_cols:  
        df[col] = pd.to_datetime(df[col], errors="coerce")  # 转换失败设为NaT  
    return df  

# 合并逻辑升级  
combined_data = pd.DataFrame()  
for file in all_files:  
    if "测试数据" in file:  
        continue  
    file_path = os.path.join(folder_path, file)  
    df = pd.read_excel(file_path)  
    df = clean_date_columns(df)  # 自动清洗日期列  
    combined_data = pd.concat([combined_data, df], ignore_index=True, sort=False)

一、环境准备

1. 安装 Pandas 库

pip install pandas openpyxl  # openpyxl用于读写xlsx格式文件

2. 导入库与数据

import pandas as pd  

# 读取Excel文件（支持.xlsx和.csv格式）  
df = pd.read_excel("data.xlsx")  # 读取工作簿中的第一个工作表  
# df = pd.read_excel("data.xlsx", sheet_name="Sheet2")  # 指定工作表名

二、数据初探：快速了解数据结构

1. 查看数据前 5 行

print(df.head())  # 默认显示前5行，可传入参数如head(10)显示前10行

2. 查看数据基本信息

print(df.info())  # 查看列名、数据类型、非空值数量  
print(df.describe())  # 查看数值型列的统计信息（均值、标准差、分位数等）

3. 查看列名与索引

print("列名：", df.columns.tolist())  
print("索引：", df.index)

三、数据清洗：处理缺失值与重复值

1. 检测缺失值

# 查看各列缺失值数量  
print(df.isnull().sum())  

# 可视化缺失值分布（需提前导入matplotlib）  
import matplotlib.pyplot as plt  
plt.figure(figsize=(10, 6))  
sns.heatmap(df.isnull(), cbar=False, cmap='viridis')  
plt.title("Missing Values Distribution")  
plt.show()

2. 处理缺失值

删除缺失值：

df_clean = df.dropna()  # 删除包含缺失值的行  
# df_clean = df.dropna(subset=["关键列"])  # 仅删除指定列有缺失值的行

填充缺失值：

# 用均值/中位数填充数值型列  
df["数值列"].fillna(df["数值列"].mean(), inplace=True)  
# 用众数填充字符型列  
mode_value = df["字符列"].mode()[0]  
df["字符列"].fillna(mode_value, inplace=True)

3. 处理重复值

# 检测重复行  
print(df.duplicated().sum())  

# 删除重复行（保留第一个出现的行）  
df_clean = df.drop_duplicates()

四、数据处理与分析：核心操作示例

1. 数据筛选与过滤

按条件筛选行：

# 筛选“销售额”> 1000的行  
filtered_data = df[df["销售额"] > 1000]  

# 同时满足多个条件  
filtered_data = df[(df["销售额"] > 1000) & (df["地区"] == "上海")]

选取指定列：

selected_columns = df[["姓名", "销售额"]]

2. 数据分组与聚合

# 按“地区”分组，计算各组销售额总和  
grouped_data = df.groupby("地区")["销售额"].sum().reset_index()  

# 多列分组+多聚合函数  
grouped = df.groupby("地区").agg({  
    "销售额": ["sum", "mean"],  
    "订单量": "count"  
}).reset_index()

3. 数据排序与排名

# 按“销售额”降序排列  
sorted_data = df.sort_values(by="销售额", ascending=False)  

# 添加排名列（按销售额降序排名）  
df["排名"] = df["销售额"].rank(ascending=False, method="dense")

五、数据可视化：快速生成统计图表

import matplotlib.pyplot as plt  
import seaborn as sns  
plt.rcParams["font.sans-serif"] = ["SimHei"]  # 解决中文乱码问题  

# 绘制销售额柱状图  
plt.figure(figsize=(12, 6))  
sns.barplot(x="地区", y="销售额", data=grouped_data, palette="viridis")  
plt.title("各地区销售额对比", fontsize=16)  
plt.xlabel("地区", fontsize=12)  
plt.ylabel("销售额（元）", fontsize=12)  
plt.xticks(rotation=45)  
plt.show()  

# 绘制销售额分布直方图  
plt.figure(figsize=(10, 6))  
sns.histplot(df["销售额"], bins=20, kde=True, color="skyblue")  
plt.title("销售额分布", fontsize=14)  
plt.show()

六、可视化效果升级

1. 图表添加数据标签

# 柱状图添加数值标签  
ax = sns.barplot(x="地区", y="销售额", data=grouped_data, palette="viridis")  
for p in ax.patches:  
    height = p.get_height()  
    ax.annotate(  
        f'{height:,.0f}',  # 格式化千位分隔符  
        xy=(p.get_x() + p.get_width() / 2., height),  
        xytext=(0, 3),  # 3点垂直偏移  
        textcoords="offset points",  
        ha='center', va='bottom'  
    )

2. 新增交互式图表提示

补充说明：

若需更高阶可视化，可使用plotly库生成交互式图表：

import plotly.express as px  
fig = px.bar(grouped_data, x="地区", y="销售额",  
            title="各地区销售额对比",  
            labels={"销售额": "销售额（元）"},  
            hover_data=["销售额"])  # 鼠标悬浮显示具体数值  
fig.show()

七、结果输出：保存处理后的数据

# 保存为Excel文件（需确保openpyxl已安装）  
df_clean.to_excel("处理后数据.xlsx", index=False)  

# 保存为CSV文件  
df_clean.to_csv("处理后数据.csv", index=False, encoding="utf-8-sig")  # 防止中文乱码

八、实战案例：批量处理多个 Excel 文件

假设需要合并文件夹中所有 Excel 文件的数据，可使用以下代码：

import os  

# 文件夹路径  
folder_path = "excel_files/"  
all_files = [f for f in os.listdir(folder_path) if f.endswith(".xlsx")]  

# 合并数据  
combined_data = pd.DataFrame()  
for file in all_files:  
    file_path = os.path.join(folder_path, file)  
    df = pd.read_excel(file_path)  
    combined_data = pd.concat([combined_data, df], ignore_index=True)  

# 保存合并结果  
combined_data.to_excel("合并后数据.xlsx", index=False)

九、常见问题与解决方案

问题：读取 Excel 时提示 “xlrd.biffh.XLRDError: Excel xlsx file； not supported”
解决：

pip uninstall xlrd  
pip install xlrd==2.0.1  # 锁定兼容版本

问题：数据透视表生成后列顺序混乱
解决：

pivot_table = df.pivot_table(  
    index="地区",  
    columns="季度",  
    values="销售额",  
    aggfunc="sum"  
).reindex(columns=["Q1", "Q2", "Q3", "Q4"])  # 显式指定列顺序