你想掌握的是用Python完成Excel数据清洗的核心方法，解决手动处理Excel中脏数据（空值、重复值、格式错误、异常值等）效率低、易出错的问题。本文以pandas（核心数据处理库）为核心，结合实战案例讲解Excel数据清洗的全流程，覆盖最常见的脏数据类型及对应的自动化处理方案，最终输出干净、可用的数据集。
一、核心工具准备
数据清洗的核心库是pandas，搭配openpyxl实现.xlsx文件的读写，先完成环境安装：
# 安装核心库
pip install pandas openpyxl
pandas：提供一站式数据清洗API，支持空值、重复值、格式转换、异常值处理等；openpyxl：作为pandas读写.xlsx文件的引擎，无需单独调用，安装即可。
二、Excel数据清洗完整流程（实战案例）
以“电商销售数据.xlsx”为例（包含字段：订单号、日期、商品类别、销售额、购买数量、客户地区），先读取数据，再按“识别脏数据→处理脏数据→验证清洗结果”的逻辑完成清洗。
步骤1：读取Excel数据并初步探查
先加载数据，通过基础方法识别脏数据类型（空值、重复值、数据类型异常等）：
import pandas as pd
# 读取Excel数据（指定工作表，跳过空行）
df = pd.read_excel(
"电商销售数据.xlsx",
sheet_name="销售明细",
skiprows=1 # 跳过首行标题外的空行
)
# 1. 查看数据基本信息（行数、列数、数据类型、非空值数量）
print("数据基本信息：")
print(df.info())
# 2. 查看前5行数据，直观识别异常
print("\n数据前5行：")
print(df.head())
# 3. 统计空值数量（重点关注关键字段）
print("\n各字段空值数量：")
print(df.isnull().sum())
# 4. 查看重复行数量
print("\n重复行数量：", df.duplicated().sum())
# 5. 数值字段的统计摘要（识别异常值，如销售额为负）
print("\n数值字段统计摘要：")
print(df[["销售额", "购买数量"]].describe())
步骤2：处理空值（缺失值）
空值是最常见的脏数据，需根据字段重要性选择“删除”或“填充”策略，避免手动逐个单元格补全：
# 策略1：删除关键字段（订单号、销售额）为空的行（无法补全的核心数据）
df = df.dropna(subset=["订单号", "销售额"])
# 策略2：填充非关键字段的空值（如客户地区、商品类别）
# 客户地区空值填充为“未知地区”（分类字段）
df["客户地区"] = df["客户地区"].fillna("未知地区")
# 商品类别空值填充为出现次数最多的类别（众数填充）
df["商品类别"] = df["商品类别"].fillna(df["商品类别"].mode()[0])
# 验证空值处理结果
print("\n空值处理后各字段空值数量：")
print(df.isnull().sum())
步骤3：处理重复值
重复行（如同一订单号多次出现）会导致统计结果错误，需批量删除：
# 按“订单号”去重（订单号唯一，重复即为无效数据）
# keep="first"保留第一次出现的行，删除后续重复行
df = df.drop_duplicates(subset=["订单号"], keep="first")
# 验证重复值处理结果
print("\n去重后数据行数：", len(df))
print("重复行数量：", df.duplicated().sum())
步骤4：数据类型转换
Excel中常出现“日期存为文本”“销售额存为字符串”等格式错误，需统一转换为正确类型：
# 1. 日期字段转换（将文本格式的日期转为datetime类型）
# errors="coerce"将无法转换的日期转为NaT，后续可删除
df["日期"] = pd.to_datetime(df["日期"], errors="coerce")
# 删除日期转换失败的行（无效日期）
df = df.dropna(subset=["日期"])
# 2. 数值字段转换（将字符串格式的销售额转为浮点数）
# 先替换销售额中的非数字字符（如“元”“,”），再转换
df["销售额"] = df["销售额"].astype(str).str.replace("元", "").str.replace(",", "")
df["销售额"] = pd.to_numeric(df["销售额"], errors="coerce")
# 3. 分类字段标准化（如客户地区统一为“XX省”格式）
# 将“北京”“北京市”统一为“北京市”
df["客户地区"] = df["客户地区"].replace({"北京": "北京市", "上海": "上海市"})
# 验证数据类型转换结果
print("\n数据类型转换后信息：")
print(df.dtypes)
步骤5：处理异常值
异常值（如销售额为负、购买数量过大）会影响分析结果，需识别并修正/删除：
# 1. 过滤数值异常值（销售额≥0、购买数量≥1）
df = df[(df["销售额"] >= 0) & (df["购买数量"] >= 1)]
# 2. 处理极端值（如购买数量超过100的异常订单，视为录入错误）
# 方法1：删除极端值（适合数据量较大的场景）
df = df[df["购买数量"] <= 100]
# 方法2：替换为合理值（如中位数，适合数据量小的场景）
# df.loc[df["购买数量"] > 100, "购买数量"] = df["购买数量"].median()
# 验证异常值处理结果
print("\n异常值处理后数值字段统计：")
print(df[["销售额", "购买数量"]].describe())
步骤6：字段标准化与衍生列（可选）
根据分析需求统一字段格式，或新增衍生列，替代Excel中的手动分列、公式计算：
# 1. 商品类别标准化（统一为小写，去空格）
df["商品类别"] = df["商品类别"].str.lower().str.strip()
# 2. 新增衍生列：单客单价（销售额/购买数量）
df["单客单价"] = df["销售额"] / df["购买数量"]
# 保留2位小数（避免多位小数混乱）
df["单客单价"] = df["单客单价"].round(2)
# 3. 按日期提取“月份”“季度”，方便后续分析
df["月份"] = df["日期"].dt.month
df["季度"] = df["日期"].dt.quarter
print("\n标准化后数据前5行：")
print(df.head())
步骤7：验证清洗结果并导出
清洗完成后，验证数据质量，再导出为干净的Excel文件，替代手动复制粘贴：
# 最终验证：无空值、无重复值、无异常值
print("最终清洗结果验证：")
print("空值数量：", df.isnull().sum().sum())
print("重复行数量：", df.duplicated().sum())
print("销售额异常值数量：", len(df[df["销售额"] < 0]))
print("最终数据行数：", len(df))
# 导出清洗后的数据到新Excel
df.to_excel(
"电商销售数据_清洗后.xlsx",
sheet_name="清洗后明细",
index=False, # 不导出索引列
engine="openpyxl"
)
print("\n清洗后的数据已导出到Excel！")
三、高频数据清洗场景拓展
场景1：批量清洗多份Excel文件
若有多个Excel文件（如各月份销售数据），可遍历文件夹批量清洗：
import os
# 定义文件夹路径
folder_path = "各月份销售数据"
# 存储所有清洗后的数据
cleaned_data = []
# 遍历文件夹中的Excel文件
for file_name in os.listdir(folder_path):
if file_name.endswith(".xlsx"):
file_path = os.path.join(folder_path, file_name)
# 读取单个文件
df_temp = pd.read_excel(file_path, sheet_name="销售明细")
# 执行相同的清洗逻辑（空值、重复值、异常值处理）
df_temp = df_temp.dropna(subset=["订单号", "销售额"])
df_temp = df_temp.drop_duplicates(subset=["订单号"])
df_temp["销售额"] = pd.to_numeric(df_temp["销售额"], errors="coerce")
df_temp = df_temp[df_temp["销售额"] >= 0]
# 标记数据来源月份
df_temp["数据月份"] = file_name.replace(".xlsx", "")
cleaned_data.append(df_temp)
# 合并所有清洗后的数据
df_all = pd.concat(cleaned_data, ignore_index=True)
# 导出合并后的结果
df_all.to_excel("各月份销售数据_合并清洗后.xlsx", index=False, engine="openpyxl")
print("多文件批量清洗完成！")
场景2：处理Excel中的合并单元格
Excel合并单元格会导致读取后出现大量空值，需先还原合并单元格再清洗：
from openpyxl import load_workbook
# 步骤1：用openpyxl读取Excel，还原合并单元格的值
wb = load_workbook("带合并单元格的数据.xlsx")
ws = wb.active
# 获取所有合并单元格的范围
merged_ranges = ws.merged_cells.ranges
for merged_range in merged_ranges:
# 获取合并单元格的左上角值
value = ws[merged_range.start_cell.coordinate].value
# 遍历合并范围，填充值
for row in ws[merged_range]:
for cell in row:
cell.value = value
# 步骤2：保存还原后的数据，再用pandas读取清洗
wb.save("还原合并单元格后.xlsx")
wb.close()
# 步骤3：pandas读取并继续常规清洗
df = pd.read_excel("还原合并单元格后.xlsx")
df = df.dropna(subset=["订单号"])
print("合并单元格处理完成！")
四、常见问题与解决方法
- 日期转换失败：Excel中日期格式不统一（如“2025/01/01”“2025-01-01”“01-01-2025”），可先用
str.replace统一分隔符，再转换：df["日期"] = df["日期"].str.replace("-", "/").str.replace(".", "/") df["日期"] = pd.to_datetime(df["日期"], errors="coerce") - 销售额含特殊字符：如“1000元”“1,200”，需先清理非数字字符再转换：
df["销售额"] = df["销售额"].astype(str).str.replace(r"[^\d.]", "", regex=True) - 大数据量清洗卡顿：读取时仅加载需要的列（
usecols参数），减少内存占用：df = pd.read_excel("大数据量.xlsx", usecols=["订单号", "日期", "销售额"]) - 分类字段取值混乱：如“电子产品”“电子类”“电子”，用
replace统一：df["商品类别"] = df["商品类别"].replace({"电子类": "电子产品", "电子": "电子产品"})
