import pandas as pd
import numpy as np

##  創(chuàng)建包含缺失值的示例數(shù)據(jù)
data = {
    '姓名': ['張三', '李四', '王五', '趙六', None],
    '年齡': [25, np.nan, 30, 35, 28],
    '工資': [5000, 6000, None, 7000, 5500],
    '部門': ['技術(shù)部', '銷售部', None, '技術(shù)部', '銷售部'],
    '入職日期': pd.to_datetime(['2020-01-01', '2019-05-15', None, '2021-03-20', '2020-11-10'])
}

df = pd.DataFrame(data)
print("原始數(shù)據(jù):")
print(df)
print(f"\n數(shù)據(jù)形狀: {df.shape}")

# 查看每列的缺失值數(shù)量
print("每列缺失值數(shù)量:")
print(df.isnull().sum())

# 查看每列的缺失值比例
print("\n每列缺失值比例:")
print(df.isnull().mean().round(4))

# 查看是否有缺失值
print(f"\n數(shù)據(jù)中是否存在缺失值: {df.isnull().any().any()}")

# 查看完整行（無(wú)缺失值的行）
complete_rows = df.dropna()
print(f"完整行數(shù)量: {len(complete_rows)}")

import matplotlib.pyplot as plt
import seaborn as sns

# 設(shè)置中文字體
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

def plot_missing_values(df):
    """繪制缺失值熱力圖"""
    plt.figure(figsize=(10, 6))
    
    # 方法1：熱力圖
    plt.subplot(1, 2, 1)
    sns.heatmap(df.isnull(), cbar=True, yticklabels=False, cmap='viridis')
    plt.title('缺失值熱力圖')
    
    # 方法2：條形圖
    plt.subplot(1, 2, 2)
    missing_data = df.isnull().sum()
    missing_data = missing_data[missing_data > 0]
    missing_data.plot(kind='bar')
    plt.title('各列缺失值數(shù)量')
    plt.xticks(rotation=45)
    
    plt.tight_layout()
    plt.show()

# 顯示缺失值分布
plot_missing_values(df)

# 刪除包含任何缺失值的行
df_dropped_any = df.dropna()
print(f"刪除任何缺失值后的形狀: {df_dropped_any.shape}")

# 刪除所有列都為缺失值的行
df_dropped_all = df.dropna(how='all')
print(f"刪除全為缺失值后的形狀: {df_dropped_all.shape}")

# 刪除特定列有缺失值的行
df_dropped_specific = df.dropna(subset=['工資', '年齡'])
print(f"刪除工資或年齡缺失后的形狀: {df_dropped_specific.shape}")

# 刪除缺失值比例超過(guò)閾值的行
threshold = 0.5  # 缺失值比例閾值
df_dropped_thresh = df.dropna(thresh=int(df.shape[1] * threshold))
print(f"按閾值刪除后的形狀: {df_dropped_thresh.shape}")

# 創(chuàng)建副本進(jìn)行操作
df_filled = df.copy()

# 固定值填充
df_filled['部門'] = df_filled['部門'].fillna('未知部門')
print("固定值填充后的部門列:")
print(df_filled['部門'])

# 前向填充和后向填充
df_filled['年齡'] = df_filled['年齡'].fillna(method='ffill')  # 前向填充
df_filled['工資'] = df_filled['工資'].fillna(method='bfill')  # 后向填充
print("\n前向/后向填充后的數(shù)值列:")
print(df_filled[['年齡', '工資']])

# 統(tǒng)計(jì)值填充
mean_age = df_filled['年齡'].mean()
median_salary = df_filled['工資'].median()
mode_department = df_filled['部門'].mode()[0]

df_filled['年齡'] = df_filled['年齡'].fillna(mean_age)
df_filled['工資'] = df_filled['工資'].fillna(median_salary)
df_filled['部門'] = df_filled['部門'].fillna(mode_department)

print("\n統(tǒng)計(jì)值填充后的數(shù)據(jù):")
print(df_filled)

# 分組填充（按部門填充平均工資）
df_group_fill = df.copy()
department_salary = df_group_fill.groupby('部門')['工資'].transform('mean')
df_group_fill['工資'] = df_group_fill['工資'].fillna(department_salary)
print("分組填充后的工資列:")
print(df_group_fill[['部門', '工資']])

# 插值法填充（適用于時(shí)間序列）
df_interpolate = df.copy()
df_interpolate['年齡'] = df_interpolate['年齡'].interpolate(method='linear')
print("\n插值法填充后的年齡列:")
print(df_interpolate['年齡'])

# 模型預(yù)測(cè)填充（使用KNN）
from sklearn.impute import KNNImputer

# 只對(duì)數(shù)值列使用KNN
numeric_cols = ['年齡', '工資']
df_knn = df[numeric_cols].copy()

imputer = KNNImputer(n_neighbors=2)
df_knn_imputed = pd.DataFrame(imputer.fit_transform(df_knn), 
                             columns=numeric_cols)
print("\nKNN填充后的數(shù)值數(shù)據(jù):")
print(df_knn_imputed)

# 創(chuàng)建超市銷售數(shù)據(jù)
np.random.seed(42)
sales_data = {
    '商品ID': range(1, 101),
    '商品名稱': [f'商品_{i}' for i in range(1, 101)],
    '類別': np.random.choice(['食品', '飲料', '日用品', '電子產(chǎn)品'], 100),
    '銷售額': np.random.normal(1000, 300, 100),
    '成本': np.random.normal(600, 200, 100),
    '庫(kù)存': np.random.randint(0, 500, 100)
}

sales_df = pd.DataFrame(sales_data)

# 人為添加缺失值
missing_indices = {
    '銷售額': [5, 15, 25, 35, 45],
    '成本': [10, 20, 30, 40, 50, 60],
    '庫(kù)存': [3, 13, 23, 33, 43, 53, 63],
    '類別': [7, 17, 27]
}

for col, indices in missing_indices.items():
    sales_df.loc[indices, col] = np.nan

print("處理前的數(shù)據(jù)信息:")
print(sales_df.info())
print(f"\n缺失值統(tǒng)計(jì):")
print(sales_df.isnull().sum())

def analyze_missing_pattern(df):
    """分析缺失值模式"""
    # 缺失值相關(guān)性
    missing_corr = df.isnull().corr()
    print("缺失值相關(guān)性矩陣:")
    print(missing_corr)
    
    # 缺失模式分析
    missing_pattern = df.isnull().sum(axis=1)
    print(f"\n每行缺失值數(shù)量分布:")
    print(missing_pattern.value_counts().sort_index())
    
    return missing_corr

missing_correlation = analyze_missing_pattern(sales_df)

def handle_missing_data_strategic(df):
    """基于策略的缺失值處理"""
    df_processed = df.copy()
    
    # 策略1：對(duì)于類別變量，用眾數(shù)填充
    categorical_cols = ['類別']
    for col in categorical_cols:
        mode_val = df_processed[col].mode()
        if len(mode_val) > 0:
            df_processed[col] = df_processed[col].fillna(mode_val[0])
    
    # 策略2：對(duì)于銷售額和成本，按類別分組填充
    numeric_cols = ['銷售額', '成本']
    for col in numeric_cols:
        # 先嘗試按類別均值填充
        group_means = df_processed.groupby('類別')[col].transform('mean')
        df_processed[col] = df_processed[col].fillna(group_means)
        
        # 如果還有缺失，用整體均值填充
        df_processed[col] = df_processed[col].fillna(df_processed[col].mean())
    
    # 策略3：對(duì)于庫(kù)存，用0填充（假設(shè)缺失表示無(wú)庫(kù)存）
    df_processed['庫(kù)存'] = df_processed['庫(kù)存'].fillna(0)
    
    return df_processed

# 應(yīng)用處理策略
sales_processed = handle_missing_data_strategic(sales_df)
print("處理后的缺失值統(tǒng)計(jì):")
print(sales_processed.isnull().sum())

def create_missing_indicators(df, columns=None):
    """創(chuàng)建缺失值指示器"""
    if columns is None:
        columns = df.columns
    
    df_with_indicators = df.copy()
    
    for col in columns:
        if df[col].isnull().any():
            indicator_name = f'{col}_missing'
            df_with_indicators[indicator_name] = df[col].isnull().astype(int)
    
    return df_with_indicators

# 使用缺失值指示器
df_with_indicators = create_missing_indicators(sales_df)
print("添加缺失值指示器后的列名:")
print(df_with_indicators.columns.tolist())

def advanced_imputation(df, target_col):
    """高級(jí)填充技術(shù)"""
    from sklearn.ensemble import RandomForestRegressor
    from sklearn.experimental import enable_iterative_imputer
    from sklearn.impute import IterativeImputer
    
    # 選擇數(shù)值列進(jìn)行多重填充
    numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()
    
    if target_col in numeric_cols:
        # 使用隨機(jī)森林進(jìn)行多重填充
        imputer = IterativeImputer(
            estimator=RandomForestRegressor(n_estimators=100, random_state=42),
            max_iter=10,
            random_state=42
        )
        
        df_imputed = df.copy()
        df_imputed[numeric_cols] = imputer.fit_transform(df[numeric_cols])
        
        return df_imputed
    else:
        print("目標(biāo)列不是數(shù)值類型，使用基礎(chǔ)填充方法")
        return df.fillna(df.median())

# 應(yīng)用高級(jí)填充
# sales_advanced = advanced_imputation(sales_df, '銷售額')

def validate_imputation(original_df, imputed_df, numeric_cols):
    """驗(yàn)證填充效果"""
    results = {}
    
    for col in numeric_cols:
        if original_df[col].isnull().any():
            # 獲取原始完整數(shù)據(jù)（用于比較）
            complete_mask = original_df[col].notnull()
            original_complete = original_df.loc[complete_mask, col]
            imputed_values = imputed_df.loc[complete_mask, col]
            
            # 計(jì)算誤差
            from sklearn.metrics import mean_absolute_error, mean_squared_error
            mae = mean_absolute_error(original_complete, imputed_values)
            rmse = np.sqrt(mean_squared_error(original_complete, imputed_values))
            
            results[col] = {
                'MAE': mae,
                'RMSE': rmse,
                '原始均值': original_complete.mean(),
                '填充后均值': imputed_values.mean()
            }
    
    return pd.DataFrame(results).T

# 驗(yàn)證填充效果（在知道真實(shí)值的情況下）
# validation_results = validate_imputation(original_complete_data, sales_processed, ['銷售額', '成本'])
# print(validation_results)

def comprehensive_missing_value_pipeline(df):
    """
    完整的缺失值處理流程
    """
    # 數(shù)據(jù)備份
    df_processed = df.copy()
    
    # 缺失值分析
    missing_summary = df_processed.isnull().sum()
    missing_ratio = missing_summary / len(df_processed)
    
    print("缺失值分析報(bào)告:")
    for col in df_processed.columns:
        if missing_summary[col] > 0:
            print(f"  {col}: {missing_summary[col]} 個(gè)缺失值 ({missing_ratio[col]:.2%})")
    
    # 創(chuàng)建缺失值指示器
    df_processed = create_missing_indicators(df_processed)
    
    # 分類型處理
    for col in df.columns:
        if df_processed[col].isnull().any():
            if df_processed[col].dtype == 'object':
                # 類別變量用眾數(shù)填充
                df_processed[col] = df_processed[col].fillna(df_processed[col].mode()[0])
            else:
                # 數(shù)值變量用中位數(shù)填充
                df_processed[col] = df_processed[col].fillna(df_processed[col].median())
    
    # 驗(yàn)證結(jié)果
    remaining_missing = df_processed.isnull().sum().sum()
    print(f"\n處理完成！剩余缺失值: {remaining_missing}")
    
    return df_processed

# 使用完整流程
final_df = comprehensive_missing_value_pipeline(sales_df)