Python脚本实现Excel数据质量自动检测：重复、缺失与逻辑错误标记
在日常数据处理工作中，从Excel中人工检查几千行数据的重复项、缺失值和逻辑错误不仅耗时，而且容易遗漏。借助Python的pandas和openpyxl库，我们可以编写一个可复用的数据质量检测脚本，自动标记问题行并输出质检报告。## 检测原理
数据质量检测主要基于三类检查：
- 重复检测：通过 pandas 的 duplicated() 方法识别完全重复或关键字段重复的行。
- 缺失检测：利用 isna() 或 isnull() 找出空值、空字符串等缺失情况。
- 逻辑检测：根据业务规则（如金额≥0、年龄18-65等）使用布尔条件过滤异常值。
整个流程：读取原始数据 → 重复检测 → 缺失检测 → 逻辑检测 → 生成报告（包含问题数据表和高亮标记）。
## 环境准备
运行以下命令安装依赖：
pip install pandas openpyxl
## 完整代码实现
以下是一个自定义函数 data_quality_check，它接收输入文件、必填字段列表和自定义规则字典，输出检测报告和问题数据文件。
import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
def data_quality_check(input_file, required_columns=None, rules=None, output_file="数据质检报告.xlsx"):
"""
对 Excel 数据进行质量检查，输出检测报告
参数:
input_file: 待检查的 Excel 文件
required_columns: 必填字段列表
rules: 自定义规则字典，如 {'金额': '>=0', '年龄': '18-65'}
output_file: 输出报告文件名
"""
df = pd.read_excel(input_file, engine='openpyxl')
print(f"总数据: {len(df)} 行 × {len(df.columns)} 列\n")
# ==================== 1. 重复项检测 ====================
print("=" * 50)
print("【1. 重复项检测】")
duplicate_mask = df.duplicated(keep='first')
duplicate_count = duplicate_mask.sum()
print(f"完全重复行: {duplicate_count} 行")
if duplicate_count > 0:
df['是否重复'] = duplicate_mask
# ==================== 2. 缺失值检测 ====================
print("\n【2. 缺失值检测】")
missing_report = df.isnull().sum()
missing_report = missing_report
if len(missing_report) > 0:
for col, count in missing_report.items():
pct = count / len(df) * 100
print(f"{col}: {count} 个缺失 ({pct:.1f}%)")
else:
print("无缺失值 √")
if required_columns:
for col in required_columns:
if col in df.columns:
missing = df.isna().sum()
blank = (df.astype(str).str.strip() == '').sum()
total_issues = missing + blank
if total_issues > 0:
print(f"⚠ 必填字段 '{col}' 有 {total_issues} 个空值！")
df = df.isna() | (df.astype(str).str.strip() == '')
# ==================== 3. 逻辑错误检测 ====================
print("\n【3. 逻辑错误检测】")
if rules:
for col, rule in rules.items():
if col not in df.columns:
print(f"跳过: 列 '{col}' 不存在")
continue
try:
if rule.startswith('>='):
threshold = float(rule)
mask = df < threshold
df = mask
count = mask.sum()
print(f"{col} < {threshold}: {count} 行异常")
elif rule.startswith('<='):
threshold = float(rule)
mask = df > threshold
df = mask
count = mask.sum()
print(f"{col} > {threshold}: {count} 行异常")
elif '-' in rule:
min_val, max_val = rule.split('-')
min_val, max_val = float(min_val), float(max_val)
mask = (df < min_val) | (df > max_val)
df = mask
count = mask.sum()
print(f"{col} 不在 [{min_val}-{max_val}]: {count} 行异常")
elif rule == '>0':
mask = df <= 0
df = mask
count = mask.sum()
print(f"{col} <= 0: {count} 行异常")
except Exception as e:
print(f"规则执行失败 {col}: {e}")
# ==================== 4. 生成报告 ====================
print("\n" + "=" * 50)
print("【4. 生成质检报告】")
flag_columns =
if flag_columns:
df['是否有问题'] = df.any(axis=1)
issue_count = df['是否有问题'].sum()
print(f"问题行总数: {issue_count} / {len(df)} ({issue_count/len(df)*100:.1f}%)")
issue_df = df == True]
clean_df = df == False]
issue_df.drop(columns=['是否有问题'] + flag_columns, inplace=True, errors='ignore')
issue_df.to_excel(output_file.replace('.xlsx', '_问题数据.xlsx'), index=False, engine='openpyxl')
print(f"问题数据已保存: {output_file.replace('.xlsx', '_问题数据.xlsx')}")
df.to_excel(output_file, index=False, engine='openpyxl')
print(f"完整报告已保存: {output_file}")
return df
# ==================== 使用示例 ====================
if __name__ == "__main__":
data_quality_check(
input_file="销售数据表.xlsx",
required_columns=["工号", "姓名", "部门", "金额"],
rules={
"金额": ">=0",
"数量": ">0",
"年龄": "18-65",
"折扣率": "0-1",
},
output_file="数据质检报告.xlsx"
)
## 进阶技巧
### 技巧1：在Excel中用颜色高亮问题行
利用 openpyxl 给问题行添加红色背景，方便直接查看。
def highlight_issues(excel_file):
"""用红色背景高亮有问题的行"""
wb = load_workbook(excel_file)
ws = wb.active
red_fill = PatternFill(start_color='FFC7CE', fill_type='solid')
issue_col = 'N'# 假设'是否有问题'列在第N列
for row in range(2, ws.max_row + 1):
if ws.value == True:
for cell in ws:
cell.fill = red_fill
wb.save(excel_file)
### 技巧2：自动计算数据质量评分
通过缺失比例和重复比例计算一个0-100的质量分数：
total_cells = len(df) * len(df.columns)
missing_cells = df.isnull().sum().sum()
duplicate_rows = df.duplicated().sum()
quality_score = 100 - (missing_cells / total_cells * 50) - (duplicate_rows / len(df) * 50)
print(f"数据质量评分: {quality_score:.1f} / 100")
### 技巧3：基于3σ原则检测异常值
对于数值列，可以使用统计学方法自动识别离群点：
def detect_outliers(df, column):
"""检测数值列的异常值（3σ 原则）"""
mean = df.mean()
std = df.std()
lower = mean - 3 * std
upper = mean + 3 * std
outliers = df[(df < lower) | (df > upper)]
print(f"{column} 异常值: {len(outliers)} 行 (范围: {lower:.2f} ~ {upper:.2f})")
return outliers
## 常见问题与处理
### Q1：日期列缺失检测不到？
如果Excel中日期实际存储为空字符串或0，pandas不会自动视为NaN。解决方法是提前统一替换：
df['日期'] = df['日期'].replace('', pd.NaT)
missing = df['日期'].isna().sum()
### Q2：数值列中混入了文本？
使用 pd.to_numeric 将列强制转换为数值，无法转换的会变成NaN，从而可以统计异常：
df['金额'] = pd.to_numeric(df['金额'], errors='coerce')
print(f"非数值单元格: {df['金额'].isna().sum()}")
### Q3：如何处理大小写不一致？
统一转为大写并去除空格，比如“北京”与“beijing”视为不同，但可统一格式：
df['城市'] = df['城市'].str.upper().str.strip()
## 总结
本文实现了一个通用的Excel数据质量自动检查脚本，覆盖了重复、缺失、逻辑三类核心检测，并提供了高亮行、质量评分、异常值检测等增强功能。这段代码可以直接集成到数据处理流水线中，作为数据清洗的前置步骤，显著提升质检效率与准确性。
Re: Python脚本实现Excel数据质量自动检测：重复、缺失与逻辑错误标记
感谢楼主分享这么实用的脚本！手动检查Excel数据确实是苦力活，这个自动化方案能节省大量时间。我特别关注逻辑检测部分，因为工作中经常遇到类似年龄范围、金额区间这类条件。想请教一下：如果规则像“年龄18-65”这种区间判断，代码示例里只处理了 `>=` 和 `Re: Python脚本实现Excel数据质量自动检测：重复、缺失与逻辑错误标记
很不错，脚本写得条理清晰，把三类检测封装成函数形式很实用，尤其是自定义规则用字符串表达逻辑，降低了上手门槛。想请教一下，如果数据里有日期字段，逻辑规则也能用同样的方式处理吗？比如 “2024-01-01Re: Python脚本实现Excel数据质量自动检测：重复、缺失与逻辑错误标记
感谢楼主分享！这个脚本结构清晰，代码封装也很实用，对日常数据质检工作太有帮助了。特别是把重复、缺失、逻辑检测整合到一个函数里，还支持自定义规则，直接拿来改改就能用。有个小想法：如果能在逻辑检测中支持字段间的比较（比如“结束日期必须大于开始日期”），覆盖的场景会更广。另外想问一下，对于超大Excel文件，用openpyxl会不会有性能瓶颈？有没有考虑增加一个chunksize选项？总之，非常棒的分享，收藏了！
页:
[1]