import pandas as pd

data = {
    '訂單號(hào)': ['A001', 'A002', 'A001', 'A003'],
    '商品': ['手機(jī)', '耳機(jī)', '手機(jī)', '充電器'],
    '價(jià)格': [2999, 199, 2999, 89]
}
df = pd.DataFrame(data)

data = {
    '訂單號(hào)': ['B001', 'B001', 'B002'],
    '商品': ['手機(jī)', '手機(jī)殼', '充電器'],
    '數(shù)量': [1, 2, 1]
}

data = {
    '客戶名': ['張三', '張三 ', '張  三', 'zhangsan'],
    '電話': ['13800138000', '13800138000', '13800138000', '13800138000']
}

# 檢測(cè)完全重復(fù)行（默認(rèn)保留第一個(gè)出現(xiàn)）
duplicates = df[df.duplicated()]
print(duplicates)

# 只根據(jù)訂單號(hào)檢測(cè)重復(fù)
key_duplicates = df[df.duplicated(subset=['訂單號(hào)'])]

# 統(tǒng)計(jì)每個(gè)訂單號(hào)的出現(xiàn)次數(shù)
duplicate_counts = df['訂單號(hào)'].value_counts()

# 篩選出重復(fù)的訂單號(hào)
repeated_orders = duplicate_counts[duplicate_counts > 1]
print(repeated_orders)

import matplotlib.pyplot as plt

# 繪制重復(fù)訂單數(shù)量分布
duplicate_counts.value_counts().sort_index().plot(kind='bar')
plt.title('重復(fù)訂單數(shù)量分布')
plt.xlabel('重復(fù)次數(shù)')
plt.ylabel('訂單數(shù)量')
plt.show()

# 生成模擬數(shù)據(jù)
import numpy as np
np.random.seed(42)

orders = {
    '訂單號(hào)': [f'ORD{str(i).zfill(6)}' for i in range(100000)],
    '客戶ID': np.random.randint(1000, 9999, size=100000),
    '商品ID': np.random.randint(100, 999, size=100000),
    '金額': np.round(np.random.uniform(10, 1000, size=100000), 2),
    '下單時(shí)間': pd.date_range('2023-01-01', periods=100000, freq='min')
}

# 故意制造5%的重復(fù)數(shù)據(jù)
df = pd.DataFrame(orders)
duplicate_mask = np.random.rand(len(df)) < 0.05
df_duplicates = df[duplicate_mask].copy()

# 給重復(fù)數(shù)據(jù)添加微小變化（模擬數(shù)據(jù)錄入錯(cuò)誤）
for col in ['金額', '客戶ID']:
    df_duplicates[col] += np.random.uniform(-0.5, 0.5, size=len(df_duplicates))

# 合并數(shù)據(jù)集
df = pd.concat([df, df_duplicates]).reset_index(drop=True)

# 完全重復(fù)檢測(cè)
exact_duplicates = df[df.duplicated()]
print(f"完全重復(fù)記錄數(shù): {len(exact_duplicates)}")

# 關(guān)鍵字段重復(fù)檢測(cè)（訂單號(hào)+客戶ID）
key_duplicates = df[df.duplicated(subset=['訂單號(hào)', '客戶ID'])]
print(f"關(guān)鍵字段重復(fù)記錄數(shù): {len(key_duplicates)}")

# 方法1：直接刪除完全重復(fù)行（保留第一個(gè)）
df_cleaned1 = df.drop_duplicates()

# 方法2：對(duì)關(guān)鍵字段去重，保留金額最大的記錄（假設(shè)金額越大越可能是有效記錄）
def keep_max_amount(group):
    return group.loc[group['金額'].idxmax()]

df_cleaned2 = df.groupby(['訂單號(hào)', '客戶ID'], as_index=False).apply(keep_max_amount)

# 比較清理前后數(shù)據(jù)量
print(f"原始數(shù)據(jù)量: {len(df)}")
print(f"清理后數(shù)據(jù)量(方法1): {len(df_cleaned1)}")
print(f"清理后數(shù)據(jù)量(方法2): {len(df_cleaned2)}")

# 檢查是否還有重復(fù)
print("方法1是否還有重復(fù):", df_cleaned1.duplicated(subset=['訂單號(hào)', '客戶ID']).any())
print("方法2是否還有重復(fù):", df_cleaned2.duplicated(subset=['訂單號(hào)', '客戶ID']).any())

from datetime import datetime

def generate_cleaning_report(df_original, df_cleaned, subset=None):
    # 計(jì)算關(guān)鍵指標(biāo)
    original_count = len(df_original)
    cleaned_count = len(df_cleaned)
    reduction_rate = (1 - cleaned_count/original_count) * 100
    
    # 檢測(cè)重復(fù)
    if subset:
        duplicates = df_original[df_original.duplicated(subset=subset)]
        duplicate_counts = df_original[subset[0]].value_counts()
        top_duplicates = duplicate_counts[duplicate_counts > 1].head(10)
    else:
        duplicates = df_original[df_original.duplicated()]
        top_duplicates = None
    
    # 創(chuàng)建報(bào)告內(nèi)容
    report = f"""
    === 數(shù)據(jù)清理報(bào)告 ===
    生成時(shí)間: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}
    
    1. 數(shù)據(jù)概覽
    - 原始記錄數(shù): {original_count}
    - 清理后記錄數(shù): {cleaned_count}
    - 記錄減少比例: {reduction_rate:.2f}%
    
    2. 重復(fù)檢測(cè)結(jié)果
    {''.join([f"- 檢測(cè)依據(jù)字段: {', '.join(subset)}\n" for subset in ([subset] if subset else [])])}
    - 發(fā)現(xiàn)重復(fù)記錄數(shù): {len(duplicates)}
    """
    
    if top_duplicates is not None and not top_duplicates.empty:
        report += "\n3. 高頻重復(fù)值TOP10\n"
        report += top_duplicates.to_string()
    
    # 添加可視化（需要先運(yùn)行繪圖代碼）
    # 這里可以添加保存圖表的代碼，然后在報(bào)告中引用圖片路徑
    
    return report

# 生成報(bào)告
print(generate_cleaning_report(df, df_cleaned2, subset=['訂單號(hào)', '客戶ID']))

# 創(chuàng)建畫(huà)布
fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# 重復(fù)記錄比例餅圖
duplicate_ratio = len(df[df.duplicated(subset=['訂單號(hào)', '客戶ID'])]) / len(df)
labels = ['唯一記錄', '重復(fù)記錄']
sizes = [1-duplicate_ratio, duplicate_ratio]
axes[0].pie(sizes, labels=labels, autopct='%1.1f%%', startangle=90)
axes[0].set_title('重復(fù)記錄占比')

# 清理前后對(duì)比柱狀圖
counts = [len(df), len(df_cleaned2)]
axes[1].bar(['原始數(shù)據(jù)', '清理后'], counts, color=['blue', 'green'])
axes[1].set_title('數(shù)據(jù)量變化對(duì)比')
axes[1].set_ylabel('記錄數(shù)')

plt.tight_layout()
plt.savefig('cleaning_report_visualization.png')

# 錯(cuò)誤示范：未指定subset參數(shù)，可能誤刪有效數(shù)據(jù)
df.drop_duplicates(inplace=True)  # 危險(xiǎn)操作！

# 錯(cuò)誤示范：未考慮下單時(shí)間微小差異
df[df.duplicated(subset=['訂單號(hào)'])]  # 可能漏檢同訂單不同時(shí)間的記錄

# 錯(cuò)誤示范：去重后未檢查關(guān)鍵字段唯一性
df_cleaned = df.drop_duplicates(subset=['訂單號(hào)'])
# 應(yīng)該添加驗(yàn)證
assert not df_cleaned.duplicated(subset=['訂單號(hào)']).any()