原始数据

绘图结果

代码

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import math
from matplotlib.patches import Rectangle
import matplotlib.colors as mcolors

excel_path = 'result.xls'
sheet_name = 'Risk detector'

# 读取整个sheet
all_df = pd.read_excel(excel_path, sheet_name=sheet_name, header=None)

results = []
all_bar_x = []  # 用于全局x类别色带

# 解析Excel，提取risk和t-test表
i = 0
while i < len(all_df):
    if isinstance(all_df.iloc[i, 0], str) and all_df.iloc[i, 0].endswith(': risk'):
        var = all_df.iloc[i, 0].replace(': risk', '').strip()
        risk_data_start = i + 2
        risk_data_end = risk_data_start
        while risk_data_end < len(all_df) and not all(all_df.iloc[risk_data_end, :].isnull() | (all_df.iloc[risk_data_end, :].astype(str).str.strip() == '')):
            risk_data_end += 1
        risk_df = all_df.iloc[risk_data_start:risk_data_end, :].reset_index(drop=True)
        i = risk_data_end
        while i < len(all_df) and not (isinstance(all_df.iloc[i, 0], str) and all_df.iloc[i, 0].endswith('t-test: 0.05')):
            i += 1
        if i >= len(all_df):
            break
        ttest_header = i + 1
        ttest_data_start = i + 2
        ttest_data_end = ttest_data_start
        while ttest_data_end < len(all_df) and pd.notnull(all_df.iloc[ttest_data_end, 0]):
            ttest_data_end += 1
        ttest_df = all_df.iloc[ttest_data_start:ttest_data_end, :ttest_data_end-ttest_data_start+2]
        ttest_df.columns = all_df.iloc[ttest_header, :ttest_data_end-ttest_data_start+2]
        ttest_df = ttest_df.set_index(ttest_df.columns[0])
        if ttest_df.index.name in ttest_df.columns:
            ttest_df = ttest_df.drop(columns=ttest_df.index.name)
        results.append((var, risk_df, ttest_df))
        # 收集所有x类别用于全局色带
        headers = risk_df.columns.tolist()
        data_list = risk_df.values.tolist()
        risk_list = [headers] + data_list
        risk_df_tmp = pd.DataFrame(risk_list)
        risk_df_tmp = risk_df_tmp.dropna(axis=1)
        if len(risk_df_tmp) >= 2:
            all_bar_x.extend(risk_df_tmp.iloc[0, :].dropna().tolist())
        i = ttest_data_end
    else:
        i += 1

# 全局x类别色带（彩虹色，低饱和度）
unique_x = list(dict.fromkeys(all_bar_x))
def desaturate(color, factor=0.5):
    rgb = mcolors.to_rgb(color)
    white = (1, 1, 1)
    return tuple(factor * c + (1 - factor) * w for c, w in zip(rgb, white))
rainbow_cmap = plt.get_cmap('rainbow', len(unique_x))
xval2color = {v: desaturate(rainbow_cmap(i), factor=0.5) for i, v in enumerate(unique_x)}

# 原图T/F色号
orig_red = '#E64B35'
orig_blue = '#4DBBD5'

# 子图布局参数
n_vars = len(results)
ncols = 2
nrows = math.ceil(n_vars / ncols)
fig = plt.figure(figsize=(10 * ncols, 5 * nrows))
import matplotlib.gridspec as gridspec
outer_gs = gridspec.GridSpec(nrows, ncols, wspace=0.25, hspace=0.3)

for idx, (var, risk_df, ttest_df) in enumerate(results):
    # 处理t-test表，生成下三角T/F矩阵和色块
    var_names = list(ttest_df.columns)
    n = len(var_names)
    t_labels = np.full((n, n), '', dtype=object)
    t_colors = np.full((n, n, 4), 1.0)  # 默认全白
    for i in range(n):
        for j in range(n):
            if i >= j:
                cell = ttest_df.iloc[i, j]
                if pd.notnull(cell):
                    cell_str = str(cell)
                    if '1' in cell_str.upper():
                        t_labels[i, j] = 'T'
                        t_colors[i, j] = mcolors.to_rgba(orig_red)
                    elif '0' in cell_str.upper():
                        t_labels[i, j] = 'F'
                        t_colors[i, j] = mcolors.to_rgba(orig_blue)
    # 处理risk表，提取x/y和颜色
    headers = risk_df.columns.tolist()
    data_list = risk_df.values.tolist()
    risk_list = [headers] + data_list
    risk_df = pd.DataFrame(risk_list).dropna(axis=1)
    if len(risk_df) >= 2:
        bar_x = risk_df.iloc[0, :].dropna().values
        bar_y = risk_df.iloc[1, :].dropna().values
        bar_colors = [xval2color.get(x, '#888888') for x in bar_x]
    else:
        bar_x = []
        bar_y = []
        bar_colors = 'skyblue'
    # 子图布局
    row = idx // ncols
    col = idx % ncols
    gs = gridspec.GridSpecFromSubplotSpec(1, 2, subplot_spec=outer_gs[idx], width_ratios=[1,1], wspace=0.05)
    # 下三角T/F热力图
    ax1 = plt.subplot(gs[0])
    ax1.imshow(t_colors, aspect='equal', extent=[0, n, n, 0])
    for i in range(n):
        for j in range(n):
            if i >= j and t_labels[i, j]:
                ax1.text(j+0.5, i+0.5, t_labels[i, j], ha='center', va='center', fontsize=18, color='white' if t_labels[i, j]=='F' else 'black')
    ax1.set_title(f'{var} t-test: 0.05')
    ax1.set_xticks(np.arange(n)+0.5)
    ax1.set_yticks(np.arange(n)+0.5)
    ax1.set_xticklabels(var_names, rotation=90)
    ax1.set_yticklabels(var_names, rotation=0)
    ax1.add_patch(Rectangle((0, 0), n, n, fill=False, edgecolor='black', lw=2))  # 整体黑框
    ax1.axvline(x=n, color='black', lw=2)  # 右边界线
    # 柱状图
    ax2 = plt.subplot(gs[1])
    if len(bar_x) > 0 and len(bar_y) > 0:
        bars = ax2.barh(bar_x, pd.to_numeric(bar_y, errors='coerce'), color=bar_colors, height=0.8)
        for bar, value in zip(bars, bar_y):
            try:
                value_float = float(value)
                label = f'{value_float:.2f}'
            except:
                label = str(value)
            ax2.text(bar.get_width(), bar.get_y() + bar.get_height()/2, label, va='center', ha='left', fontsize=14, color='black')
    ax2.set_xlabel('Risk')
    ax2.set_title(f'{var} risk')
    ax2.set_ylabel('')
    ax2.set_xticklabels([])
    ax2.axvline(x=0, color='black', lw=2)  # 左边界线

# 布局调整，保存与展示
plt.subplots_adjust(left=0.06, right=0.98, top=0.95, bottom=0.06, wspace=0.25, hspace=0.3)
plt.savefig('RiskDetector_AllVars.png')
plt.show()