excel-validate

This skill is read-only, no side effects. Uses pandas for fast scanning, outputs an issue report. 本技能只读不写，安全无副作用。用 pandas 快速扫描，输出问题报告。

Excel Data Validation / Excel 数据校验

Check Items / 检查项目

Step 0: Requirement Parsing / 第零步：需求解析

Step 1: Scout + Check / 第一步：勘察+检查

import pandas as pd
import numpy as np
import os

FILE = 'target.xlsx' / FILE = '目标文件.xlsx'
size_mb = os.path.getsize(FILE) / 1024 / 1024

df = pd.read_excel(FILE)
total = len(df)
cols = len(df.columns)

print(f'{"="*60}')
print(f'Data Quality Report / 数据质量报告: {os.path.basename(FILE)}')
print(f'File Size: {size_mb:.1f}MB | Rows: {total} | Cols: {cols} / 文件大小: {size_mb:.1f}MB | 行数: {total} | 列数: {cols}')
print(f'{"="*60}')

# ====== 1. Null Check / 空值检查 ======
print(f'\n【Null Rate / 空值率】')
null_report = []
for col in df.columns:
    null_count = df[col].isna().sum()
    null_pct = null_count / total * 100
    if null_pct > 0:
        level = '🔴' if null_pct > 30 else ('🟡' if null_pct > 10 else '🟢')
        null_report.append((col, null_count, null_pct, level))

null_report.sort(key=lambda x: -x[2])
if null_report:
    for col, cnt, pct, level in null_report[:20]:
        print(f'  {level} {col}: {cnt} nulls / 空 ({pct:.1f}%)')
    if len(null_report) > 20:
        print(f'  ... {len(null_report)-20} more columns with nulls / 还有 {len(null_report)-20} 列有空值')
else:
    print(f'  ✅ No nulls / 无空值')

# ====== 2. Uniqueness / 唯一值 ======
print(f'\n【Uniqueness Analysis / 唯一值分析】')
for col in df.columns:
    n_unique = df[col].nunique()
    if n_unique <= 1:
        print(f'  ⚠️ {col}: unique={n_unique} (all same or no data / 全列相同或无数据)')
    elif n_unique == total:
        print(f'  📌 {col}: unique={n_unique} (likely ID column / 可能是ID列)')

# ====== 3. Type Consistency / 类型一致性 ======
print(f'\n【Type Consistency / 类型一致性】')
mixed_cols = []
for col in df.columns:
    types = df[col].dropna().apply(type).unique()
    if len(types) > 1:
        type_names = [t.__name__ for t in types]
        mixed_cols.append((col, type_names))
if mixed_cols:
    for col, types in mixed_cols[:10]:
        print(f'  ⚠️ {col}: mixed types / 混合类型 {types}')
else:
    print(f'  ✅ Types consistent / 类型一致')

# ====== 4. Outliers (numeric columns) / 异常值（数值列）======
print(f'\n【Numeric Outliers / 数值列异常值】')
num_cols = df.select_dtypes(include=[np.number]).columns
found_anomaly = False
for col in num_cols:
    vals = df[col].dropna()
    if len(vals) < 2: continue
    q1, q3 = vals.quantile([0.25, 0.75])
    iqr = q3 - q1
    if iqr == 0: continue
    outliers = vals[(vals < q1 - 3*iqr) | (vals > q3 + 3*iqr)]
    if len(outliers) > 0:
        print(f'  📊 {col}: {len(outliers)} extreme values / 个极端值 (min={vals.min()}, max={vals.max()})')
        found_anomaly = True
if not found_anomaly:
    print(f'  ✅ No obvious outliers / 未发现明显异常值')

# ====== 5. Fully Duplicate Rows / 完全重复行 ======
print(f'\n【Duplicate Rows / 重复行】')
dup_rows = df.duplicated().sum()
if dup_rows > 0:
    print(f'  🔴 {dup_rows} rows fully duplicate / 行完全重复 ({dup_rows/total*100:.1f}%)')
else:
    print(f'  ✅ No fully duplicate rows / 无完全重复行')

# ====== 6. Potential Issues / 可能的问题 ======
print(f'\n【Potential Issues / 可能的问题】')

# Check for obviously formula-result columns (e.g. "Unnamed") / 检查是否包含明显是公式结果的列
unnamed = [c for c in df.columns if 'Unnamed' in str(c)]
if unnamed:
    print(f'  ⚠️ {len(unnamed)} unnamed columns / 个未命名列 -> possible hidden header issues / 可能有隐藏的表头问题')

# Check all-null columns / 检查全空列
all_null = [c for c in df.columns if df[c].isna().all()]
if all_null:
    print(f'  🔴 {len(all_null)} all-null columns / 个全空列: {all_null}')

# Check columns that look like dates but are stored as text / 检查看起来像日期但是字符串的列
for col in df.select_dtypes(include=['object']).columns:
    sample = df[col].dropna().head(5)
    date_like = sample.astype(str).str.match(r'\d{4}[-/]\d{2}[-/]\d{2}').sum()
    if date_like >= 3:
        print(f'  💡 {col}: looks like date but stored as text / 看起来像日期但存储为文本, suggest using excel-date-to-text / 建议用 excel-date-to-text 处理')

print(f'\n{"="*60}')
print(f'Check complete / 检查完成')