import pandas as pd
import numpy as np

# ============================================================
# 第1步：读取数据，检查基本情况
# ============================================================
df = pd.read_csv("010 企业并购重组数据.csv", encoding="utf-8")

print("=" * 60)
print("【基本数据信息】")
print(f"文件编码：CSV格式，UTF-8读取正常")
print(f"数据读取成功 | 共 {len(df)} 行 | 共 {len(df.columns)} 列")
print(f"列名：{list(df.columns)}")
print(f"\n各列数据类型：")
print(df.dtypes)
print(f"\n前5行预览：")
print(df.head())

# ============================================================
# 第2步：逐列检查数据质量问题
# ============================================================
problems = []  # 收集所有问题

print("\n" + "=" * 60)
print("【数据质量检查结果】")

# --- 2.1 空值/缺失值 ---
print("\n--- 2.1 空值检查 ---")
for col in df.columns:
    null_rows = df[df[col].isnull()]
    if len(null_rows) > 0:
        for idx in null_rows.index:
            row_num = idx + 2  # Excel行号（第1行是表头）
            print(f"  ❌ 第 {row_num} 行的 \"{col}\" 为空")
            problems.append(("空值", row_num, col))

# --- 2.2 数值列异常字符 ---
print("\n--- 2.2 数值列异常字符检查 ---")
for col in df.columns:
    if df[col].dtype == object:  # 文本类型列，尝试转数值
        converted = pd.to_numeric(df[col], errors="coerce")
        bad_mask = converted.isnull() & df[col].notnull() & (df[col] != "")
        bad_rows = df[bad_mask]
        if len(bad_rows) > 0:
            for idx in bad_rows.index:
                row_num = idx + 2
                val = df.at[idx, col]
                print(f"  ❌ 第 {row_num} 行 \"{col}\" 含异常值: \"{val}\" (无法转为数值)")
                problems.append(("异常字符", row_num, col, str(val)))

# --- 2.3 负数异常 ---
print("\n--- 2.3 负数异常检查 ---")
positive_cols = [c for c in df.columns if "案例" in c or "金额" in c or "数量" in c or "规模" in c]
for col in positive_cols:
    numeric_col = pd.to_numeric(df[col], errors="coerce")
    neg_rows = df[numeric_col < 0]
    for idx in neg_rows.index:
        row_num = idx + 2
        val = df.at[idx, col]
        print(f"  ❌ 第 {row_num} 行 \"{col}\" 为负数: {val}")
        problems.append(("负数", row_num, col, str(val)))

# --- 2.4 重复行 ---
print("\n--- 2.4 重复行检查 ---")
dup = df[df.duplicated(keep=False)]
if len(dup) > 0:
    dup_indices = dup.index.tolist()
    print(f"  ❌ 发现 {len(dup)} 行重复数据，行号: {[i+2 for i in dup_indices]}")
    for idx in dup_indices:
        problems.append(("重复行", idx + 2, "整行", ""))
else:
    print("  ✅ 无重复行")

# --- 2.5 季度格式检查 ---
print("\n--- 2.5 季度格式检查 ---")
if "季度" in df.columns:
    quarter_col = df["季度"].astype(str)
    bad_quarter = quarter_col[~quarter_col.str.match(r"^\d{4}[Qq]\d$|^\d{6}$")]
    if len(bad_quarter) > 0:
        for idx in bad_quarter.index:
            row_num = idx + 2
            val = df.at[idx, "季度"]
            print(f"  ❌ 第 {row_num} 行 \"季度\" 格式异常: \"{val}\"")
            problems.append(("格式异常", row_num, "季度", str(val)))
    else:
        print("  ✅ 季度格式均正常")

# ============================================================
# 第3步：汇总问题
# ============================================================
print("\n" + "=" * 60)
print(f"【问题汇总】共发现 {len(problems)} 个问题")
for p in problems:
    print(f"  {p[0]} | 行{p[1]} | {p[2]} {p[3] if len(p)>3 else ''}")

# ============================================================
# 第4步：执行清洗
# ============================================================
print("\n" + "=" * 60)
print("【开始清洗】")

df_clean = df.copy()

# 4.1 去除重复行
before = len(df_clean)
df_clean = df_clean.drop_duplicates()
after = len(df_clean)
if before != after:
    print(f"  去除重复行: {before} → {after} (删除 {before-after} 行)")

# 4.2 将混合类型数值列统一转为数值
for col in df_clean.columns:
    if df_clean[col].dtype == object:
        converted = pd.to_numeric(df_clean[col], errors="coerce")
        if converted.notnull().sum() > len(df_clean) * 0.5:
            df_clean[col] = converted
            print(f"  列 \"{col}\" 已统一转为数值类型")

# 4.3 负数修正（取绝对值）
for col in positive_cols:
    if col in df_clean.columns:
        numeric_col = pd.to_numeric(df_clean[col], errors="coerce")
        neg_count = (numeric_col < 0).sum()
        if neg_count > 0:
            df_clean[col] = numeric_col.abs()
            print(f"  列 \"{col}\" 有 {neg_count} 个负数，已取绝对值修正")

# 4.4 空值填充
for col in df_clean.columns:
    null_count = df_clean[col].isnull().sum()
    if null_count > 0:
        if df_clean[col].dtype in [np.float64, np.int64]:
            median_val = df_clean[col].median()
            df_clean[col] = df_clean[col].fillna(median_val)
            print(f"  列 \"{col}\" 有 {null_count} 个空值，已用中位数 {median_val} 填充")
        else:
            mode_val = df_clean[col].mode()[0] if len(df_clean[col].mode()) > 0 else "未知"
            df_clean[col] = df_clean[col].fillna(mode_val)
            print(f"  列 \"{col}\" 有 {null_count} 个空值，已用众数 \"{mode_val}\" 填充")

# ============================================================
# 第5步：保存清洗结果
# ============================================================
df_clean.to_csv("010企业并购重组数据_清洗完成.csv", index=False, encoding="utf-8-sig")

print("\n" + "=" * 60)
print("【清洗完成】")
print(f"清洗前: {len(df)} 行 × {len(df.columns)} 列")
print(f"清洗后: {len(df_clean)} 行 × {len(df_clean.columns)} 列")
print(f"已保存为: 010企业并购重组数据_清洗完成.csv")
print("✅ 全部完成")