def load_and_preprocess_data(data_dir="file"):
    """
    加载所有CSV文件并预处理数据
    """
    data_dir = Path(data_dir)
    csv_files = list(data_dir.glob("*_daily.csv"))
    
    all_data = {}
    
    for file in csv_files:
        try:
            # 从文件名提取品种名称
            symbol = file.stem.split('_daily')[0]
            symbol = symbol.replace('KQ_m_', '').replace('SHFE_', '').replace('DCE_', '')
            symbol = symbol.replace('CZCE_', '').replace('CFFEX_', '').replace('INE_', '').replace('GFEX_', '')
            
            # 读取数据
            df = pd.read_csv(file)
            
            # 确保有必要的列
            if 'datetime' in df.columns and 'close' in df.columns:
                # 转换日期格式
                df['datetime'] = pd.to_datetime(df['datetime'])
                df.set_index('datetime', inplace=True)
                
                # 只保留收盘价
                df = df[['close']].rename(columns={'close': symbol})
                
                # 去除缺失值
                df = df.dropna()
                
                if len(df) > 0:
                    all_data[symbol] = df
                    print(f"✓ 已加载 {symbol}: {len(df)} 个数据点")
                else:
                    print(f"⚠ 跳过 {symbol}: 无有效数据")
            else:
                print(f"⚠ 跳过 {file.name}: 缺少必要列")
                
        except Exception as e:
            print(f"✗ 加载 {file.name} 时出错: {e}")
    
    return all_data

# 加载数据
data_dict = load_and_preprocess_data()

def create_unified_dataframe(data_dict):
    """
    创建统一时间索引的数据框
    """
    # 合并所有数据
    combined_df = pd.concat([df for df in data_dict.values()], axis=1)
    
    # 向前填充缺失值（可选，根据分析需求）
    # combined_df = combined_df.ffill().dropna()
    
    # 或者只保留所有品种都有数据的日期
    combined_df = combined_df.dropna()
    
    print(f"合并后数据形状: {combined_df.shape}")
    print(f"时间范围: {combined_df.index.min()} 至 {combined_df.index.max()}")
    
    return combined_df

# 创建统一数据框
price_df = create_unified_dataframe(data_dict)
price_df.head()

# 计算日收益率
returns_df = price_df.pct_change().dropna()

# 或者计算对数收益率（更适合相关性分析）
log_returns_df = np.log(price_df / price_df.shift(1)).dropna()

print(f"收益率数据形状: {log_returns_df.shape}")
log_returns_df.head()

# 计算相关性矩阵
correlation_matrix = log_returns_df.corr()

print("相关性矩阵:")
display(correlation_matrix)

# 热力图
plt.figure(figsize=(16, 14))
mask = np.triu(np.ones_like(correlation_matrix, dtype=bool))  # 只显示下三角

sns.heatmap(correlation_matrix, 
            mask=mask,
            annot=True, 
            cmap='RdBu_r', 
            center=0,
            square=True,
            fmt='.2f',
            cbar_kws={"shrink": .8})

plt.title('期货品种收益率相关性矩阵', fontsize=16, fontweight='bold')
plt.tight_layout()
plt.show()

def analyze_high_correlations(corr_matrix, threshold=0.7):
    """
    分析高相关性品种对
    """
    high_corr_pairs = []
    
    # 获取上三角部分
    upper_triangle = corr_matrix.where(np.triu(np.ones(corr_matrix.shape), k=1).astype(bool))
    
    # 找出高相关性对
    for i in range(len(corr_matrix.columns)):
        for j in range(i+1, len(corr_matrix.columns)):
            corr_value = upper_triangle.iloc[i, j]
            if abs(corr_value) >= threshold:
                high_corr_pairs.append({
                    '品种1': corr_matrix.columns[i],
                    '品种2': corr_matrix.columns[j],
                    '相关性': corr_value
                })
    
    # 转换为DataFrame并排序
    high_corr_df = pd.DataFrame(high_corr_pairs)
    high_corr_df = high_corr_df.sort_values('相关性', key=abs, ascending=False)
    
    return high_corr_df

# 分析高相关性对
high_corr_df = analyze_high_correlations(correlation_matrix, threshold=0.6)
print("高相关性品种对 (|相关性| ≥ 0.6):")
display(high_corr_df)

def analyze_low_correlations(corr_matrix, low_threshold=0.2, very_low_threshold=0.1):
    """
    分析低相关性品种对
    """
    low_corr_pairs = []
    very_low_corr_pairs = []
    negative_corr_pairs = []
    
    # 获取上三角部分
    upper_triangle = corr_matrix.where(np.triu(np.ones(corr_matrix.shape), k=1).astype(bool))
    
    for i in range(len(corr_matrix.columns)):
        for j in range(i+1, len(corr_matrix.columns)):
            corr_value = upper_triangle.iloc[i, j]
            
            # 负相关性
            if corr_value < 0:
                negative_corr_pairs.append({
                    '品种1': corr_matrix.columns[i],
                    '品种2': corr_matrix.columns[j],
                    '相关性': corr_value,
                    '类型': '负相关'
                })
            
            # 非常低的正相关性
            elif 0 <= corr_value <= very_low_threshold:
                very_low_corr_pairs.append({
                    '品种1': corr_matrix.columns[i],
                    '品种2': corr_matrix.columns[j],
                    '相关性': corr_value,
                    '类型': '极低相关'
                })
            
            # 低相关性
            elif corr_value <= low_threshold:
                low_corr_pairs.append({
                    '品种1': corr_matrix.columns[i],
                    '品种2': corr_matrix.columns[j],
                    '相关性': corr_value,
                    '类型': '低相关'
                })
    
    return low_corr_pairs, very_low_corr_pairs, negative_corr_pairs

# 分析低相关性对
low_corr, very_low_corr, negative_corr = analyze_low_correlations(correlation_matrix, 
                                                                  low_threshold=0.2, 
                                                                  very_low_threshold=0.1)

# 转换为DataFrame
low_corr_df = pd.DataFrame(low_corr).sort_values('相关性')
very_low_corr_df = pd.DataFrame(very_low_corr).sort_values('相关性')
negative_corr_df = pd.DataFrame(negative_corr).sort_values('相关性')

print("低相关性品种对 (0.1 < 相关性 ≤ 0.2):")
display(low_corr_df.head(10))

print("\n极低相关性品种对 (0 ≤ 相关性 ≤ 0.1):")
display(very_low_corr_df.head(10))

print("\n负相关性品种对 (相关性 < 0):")
display(negative_corr_df.head(10))

def analyze_average_correlations(corr_matrix):
    """
    分析每个品种与其他所有品种的平均相关性
    """
    avg_correlations = []
    
    for symbol in corr_matrix.columns:
        # 计算该品种与其他所有品种的相关性（排除自身）
        other_correlations = corr_matrix[symbol].drop(symbol)
        avg_corr = other_correlations.mean()
        min_corr = other_correlations.min()
        max_corr = other_correlations.max()
        
        # 统计低相关性品种数量
        low_corr_count = len(other_correlations[other_correlations <= 0.2])
        very_low_corr_count = len(other_correlations[other_correlations <= 0.1])
        negative_corr_count = len(other_correlations[other_correlations < 0])
        
        avg_correlations.append({
            '品种': symbol,
            '平均相关性': avg_corr,
            '最低相关性': min_corr,
            '最高相关性': max_corr,
            '低相关品种数(≤0.2)': low_corr_count,
            '极低相关品种数(≤0.1)': very_low_corr_count,
            '负相关品种数': negative_corr_count,
            '总品种数': len(other_correlations)
        })
    
    return pd.DataFrame(avg_correlations).sort_values('平均相关性')

# 分析各品种的平均相关性
avg_corr_df = analyze_average_correlations(correlation_matrix)

print("各品种平均相关性排序（从低到高）:")
display(avg_corr_df.head(15))

print("\n最不相关的品种（平均相关性最低）:")
lowest_avg = avg_corr_df.nsmallest(10, '平均相关性')
display(lowest_avg)

print("\n最具独立性的品种（低相关品种数最多）:")
most_independent = avg_corr_df.nlargest(10, '低相关品种数(≤0.2)')
display(most_independent)

# 绘制平均相关性分布
plt.figure(figsize=(14, 8))

# 平均相关性直方图
plt.subplot(2, 2, 1)
plt.hist(avg_corr_df['平均相关性'], bins=30, alpha=0.7, color='skyblue', edgecolor='black')
plt.axvline(x=avg_corr_df['平均相关性'].mean(), color='red', linestyle='--', 
           label=f'平均值: {avg_corr_df["平均相关性"].mean():.3f}')
plt.xlabel('平均相关性')
plt.ylabel('品种数量')
plt.title('品种平均相关性分布')
plt.legend()
plt.grid(True, alpha=0.3)

# 低相关性品种数量分布
plt.subplot(2, 2, 2)
plt.hist(avg_corr_df['低相关品种数(≤0.2)'], bins=20, alpha=0.7, color='lightgreen', edgecolor='black')
plt.xlabel('低相关性品种数量')
plt.ylabel('品种数量')
plt.title('各品种的低相关性伙伴数量分布')
plt.grid(True, alpha=0.3)

# 平均相关性最低的品种
plt.subplot(2, 2, 3)
lowest_10 = avg_corr_df.nsmallest(10, '平均相关性')
plt.barh(lowest_10['品种'], lowest_10['平均相关性'], color='lightcoral')
plt.xlabel('平均相关性')
plt.title('平均相关性最低的10个品种')
plt.grid(True, alpha=0.3)

# 低相关性伙伴最多的品种
plt.subplot(2, 2, 4)
most_independent_10 = avg_corr_df.nlargest(10, '低相关品种数(≤0.2)')
plt.barh(most_independent_10['品种'], most_independent_10['低相关品种数(≤0.2)'], color='lightblue')
plt.xlabel('低相关性品种数量')
plt.title('低相关性伙伴最多的10个品种')

plt.tight_layout()
plt.show()

def find_diversification_pairs(corr_matrix, min_corr=0.1, max_corr=0.3):
    """
    寻找适合分散投资的品种对
    """
    diversification_pairs = []
    
    upper_triangle = corr_matrix.where(np.triu(np.ones(corr_matrix.shape), k=1).astype(bool))
    
    for i in range(len(corr_matrix.columns)):
        for j in range(i+1, len(corr_matrix.columns)):
            corr_value = upper_triangle.iloc[i, j]
            
            if min_corr <= abs(corr_value) <= max_corr:
                diversification_pairs.append({
                    '品种1': corr_matrix.columns[i],
                    '品种2': corr_matrix.columns[j],
                    '相关性': corr_value,
                    '绝对相关性': abs(corr_value)
                })
    
    diversification_df = pd.DataFrame(diversification_pairs)
    diversification_df = diversification_df.sort_values('绝对相关性')
    
    return diversification_df

# 寻找适合分散投资的品种对（相关性在0.1-0.3之间）
diversification_df = find_diversification_pairs(correlation_matrix, min_corr=0.1, max_corr=0.3)

print("适合分散投资的品种对 (相关性在0.1-0.3之间):")
print(f"找到 {len(diversification_df)} 个品种对")
display(diversification_df.head(20))

def analyze_group_diversification(corr_matrix, groups):
    """
    分析不同品种组之间的相关性
    """
    group_pairs = []
    group_names = list(groups.keys())
    
    for i in range(len(group_names)):
        for j in range(i+1, len(group_names)):
            group1 = group_names[i]
            group2 = group_names[j]
            
            # 获取两个组的品种
            symbols1 = [s for s in groups[group1] if s in corr_matrix.columns]
            symbols2 = [s for s in groups[group2] if s in corr_matrix.columns]
            
            if symbols1 and symbols2:
                # 计算组间平均相关性
                group_corr_values = []
                for sym1 in symbols1:
                    for sym2 in symbols2:
                        if sym1 in corr_matrix.index and sym2 in corr_matrix.columns:
                            group_corr_values.append(corr_matrix.loc[sym1, sym2])
                
                if group_corr_values:
                    avg_corr = np.mean(group_corr_values)
                    min_corr = np.min(group_corr_values)
                    max_corr = np.max(group_corr_values)
                    
                    group_pairs.append({
                        '组1': group1,
                        '组2': group2,
                        '平均相关性': avg_corr,
                        '最低相关性': min_corr,
                        '最高相关性': max_corr,
                        '组1品种数': len(symbols1),
                        '组2品种数': len(symbols2)
                    })
    
    return pd.DataFrame(group_pairs).sort_values('平均相关性')

# 分析品种组间的相关性
group_corr_df = analyze_group_diversification(correlation_matrix, exchange_groups)

print("品种组间平均相关性（从低到高）:")
display(group_corr_df.head(10))

print("\n最适合分散投资的品种组组合:")
lowest_group_pairs = group_corr_df.nsmallest(10, '平均相关性')
display(lowest_group_pairs)

def analyze_correlation_stability(returns_df, symbol_pairs, window=60):
    """
    分析低相关性品种对的相关性稳定性
    """
    stability_results = []
    
    for _, row in symbol_pairs.head(20).iterrows():
        symbol1, symbol2 = row['品种1'], row['品种2']
        
        if symbol1 in returns_df.columns and symbol2 in returns_df.columns:
            # 计算滚动相关性
            rolling_corr = returns_df[symbol1].rolling(window=window).corr(returns_df[symbol2])
            
            # 计算稳定性指标
            corr_std = rolling_corr.std()  # 标准差越小越稳定
            corr_range = rolling_corr.max() - rolling_corr.min()  # 范围越小越稳定
            stable_ratio = len(rolling_corr[(rolling_corr >= -0.3) & (rolling_corr <= 0.3)]) / len(rolling_corr)
            
            stability_results.append({
                '品种1': symbol1,
                '品种2': symbol2,
                '平均相关性': row['相关性'],
                '相关性标准差': corr_std,
                '相关性范围': corr_range,
                '稳定比例': stable_ratio,
                '稳定性评分': (1 - corr_std) * stable_ratio  # 综合评分
            })
    
    return pd.DataFrame(stability_results).sort_values('稳定性评分', ascending=False)

# 分析低相关性品种对的稳定性
stability_df = analyze_correlation_stability(log_returns_df, diversification_df)

print("低相关性品种对的稳定性分析:")
display(stability_df.head(15))

def generate_diversification_recommendations(analysis_results):
    """
    生成分散组合建议
    """
    print("=" * 60)
    print("分散组合建议")
    print("=" * 60)
    
    # 最佳分散品种对
    print("\n1. 最佳分散品种对（低且稳定的相关性）:")
    best_pairs = analysis_results['stability_df'].head(5)
    for i, (_, row) in enumerate(best_pairs.iterrows(), 1):
        print(f"   {i}. {row['品种1']} - {row['品种2']}: "
              f"相关性={row['平均相关性']:.3f}, 稳定性评分={row['稳定性评分']:.3f}")
    
    # 最具独立性品种
    print("\n2. 最具独立性品种（平均相关性最低）:")
    independent_symbols = analysis_results['avg_corr_df'].head(5)['品种'].tolist()
    print(f"   {', '.join(independent_symbols)}")
    
    # 最佳分散品种组组合
    print("\n3. 最佳分散品种组组合:")
    best_groups = analysis_results['group_corr_df'].head(3)
    for i, (_, row) in enumerate(best_groups.iterrows(), 1):
        print(f"   {i}. {row['组1']} + {row['组2']}: 平均相关性={row['平均相关性']:.3f}")

# 生成建议
analysis_results = {
    'stability_df': stability_df,
    'avg_corr_df': avg_corr_df,
    'group_corr_df': group_corr_df
}

generate_diversification_recommendations(analysis_results)

# 按交易所分组分析
exchange_groups = {
    '黑色系': ['rb', 'hc', 'i', 'j', 'jm', 'ZC'],
    '有色系': ['cu', 'al', 'zn', 'pb', 'ni', 'sn'],
    '农产品': ['a', 'b', 'm', 'y', 'p', 'c', 'cs', 'SR', 'CF', 'RM', 'OI'],
    '化工系': ['MA', 'TA', 'FU', 'BU', 'RU', 'L', 'PP', 'V', 'EG', 'EB', 'PG'],
    '贵金属': ['au', 'ag'],
    '金融': ['IF', 'IC', 'IH', 'T', 'TF', 'TS'],
    '能源': ['sc', 'lu']
}

def analyze_exchange_correlations(corr_matrix, groups):
    """
    分析各品种组内的平均相关性
    """
    group_results = {}
    
    for group_name, symbols in groups.items():
        # 找出数据中存在的品种
        existing_symbols = [s for s in symbols if s in corr_matrix.columns]
        
        if len(existing_symbols) > 1:
            # 提取该组的相关性子矩阵
            group_corr = corr_matrix.loc[existing_symbols, existing_symbols]
            
            # 计算平均相关性（排除对角线）
            mask = np.ones_like(group_corr, dtype=bool)
            np.fill_diagonal(mask, False)
            avg_corr = group_corr.values[mask].mean()
            
            group_results[group_name] = {
                '品种数量': len(existing_symbols),
                '平均相关性': avg_corr,
                '品种列表': existing_symbols
            }
    
    return pd.DataFrame(group_results).T.sort_values('平均相关性', ascending=False)

# 分析各品种组
group_analysis = analyze_exchange_correlations(correlation_matrix, exchange_groups)
print("各品种组内平均相关性:")
display(group_analysis)

# 保存相关性矩阵
correlation_matrix.to_csv('futures_correlation_matrix.csv', encoding='utf-8-sig')
# 保存高相关性对
high_corr_df.to_csv('high_correlation_pairs.csv', encoding='utf-8-sig', index=False)
print("分析结果已保存到CSV文件")