A 股酒类板块 — 分红股息率全景对比¶

import sys
from pathlib import Path

def _find_project_root() -> Path:
    for p in [Path.cwd(), *Path.cwd().parents]:
        if (p / "src" / "backtest").is_dir() and (p / "notebooks").is_dir():
            return p
    raise RuntimeError("找不到项目根目录")

project_root = _find_project_root()
if str(project_root) not in sys.path:
    sys.path.insert(0, str(project_root))

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker

pd.set_option('display.max_columns', None)
pd.set_option('display.float_format', lambda x: f'{x:.2f}')
pd.set_option('display.max_rows', 50)

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False

from notebooks.utils import query, get_stock
from src.data.manager import DataManager

print("✅ 酒类分红对比分析环境就绪")

# 酒类股票池（白酒 + 啤酒 + 红黄酒）
dm = DataManager(read_only=True)
alcohol_stocks = dm.storage.conn.execute("""
    SELECT ts_code, name, industry, list_date
    FROM stock_basic 
    WHERE industry IN ('白酒', '啤酒', '红黄酒')
    ORDER BY industry, name
""").fetchall()

# 获取每只股票最新的股息率和估值数据
stock_data = []
for ts_code, name, industry, list_date in alcohol_stocks:
    row = dm.storage.conn.execute("""
        SELECT trade_date, close, dv_ttm, dv_ratio, pe_ttm, pb, total_mv
        FROM daily_basic 
        WHERE ts_code = ? AND dv_ttm IS NOT NULL AND dv_ttm > 0
        ORDER BY trade_date DESC LIMIT 1
    """, [ts_code]).fetchone()
    
    if row:
        trade_date, close, dv_ttm, dv_ratio, pe_ttm, pb, total_mv = row
        # 计算每股分红（估算）
        est_dps = close * dv_ttm / 100 if dv_ttm else 0
        stock_data.append({
            '代码': ts_code,
            '名称': name,
            '行业': industry,
            '上市日期': str(list_date),
            '最新收盘价': round(close, 2),
            '股息率_TTM(%)': round(dv_ttm, 2),
            '股息率(%)': round(dv_ratio, 2) if dv_ratio else None,
            'PE_TTM': round(pe_ttm, 1) if pe_ttm else None,
            'PB': round(pb, 2) if pb else None,
            '总市值(亿)': round(total_mv / 1e4, 0) if total_mv else None,
            '估算每股分红(元)': round(est_dps, 2),
            '数据日期': str(trade_date),
        })

dm.close()

df = pd.DataFrame(stock_data).sort_values('股息率_TTM(%)', ascending=False)

# 排除 ST 和异常数据
df_normal = df[~df['名称'].str.contains('ST|\\*ST')].copy()
df_normal = df_normal[df_normal['股息率_TTM(%)'] < 15]  # 排除极端异常值

print(f"酒类股票池: 共 {len(df)} 只（含ST），正常 {len(df_normal)} 只")
print(f"\n=== 股息率排名 TOP 15 ===")
display(df_normal.head(15)[['代码', '名称', '行业', '最新收盘价', '股息率_TTM(%)', 'PE_TTM', '总市值(亿)', '估算每股分红(元)']].reset_index(drop=True))

print(f"\n=== 股息率排名 BOTTOM 10 ===")
display(df_normal.tail(10)[['代码', '名称', '行业', '最新收盘价', '股息率_TTM(%)', 'PE_TTM', '总市值(亿)']].reset_index(drop=True))

# 股息率可视化排名
top20 = df_normal.head(20).copy()

fig, axes = plt.subplots(1, 2, figsize=(18, 10))

# 图1: 股息率横向柱状图
colors = ['#C62828' if ind == '白酒' else '#1565C0' if ind == '啤酒' else '#F9A825' 
          for ind in top20['行业']]
bars = axes[0].barh(range(len(top20)), top20['股息率_TTM(%)'], color=colors, edgecolor='white')
axes[0].set_yticks(range(len(top20)))
axes[0].set_yticklabels([f"{n} ({c})" for n, c in zip(top20['名称'], top20['代码'])], fontsize=9)
axes[0].invert_yaxis()
axes[0].set_xlabel('股息率 TTM (%)', fontsize=12)
axes[0].set_title('酒类股票股息率排名 TOP 20', fontsize=14, fontweight='bold')
axes[0].grid(True, alpha=0.3, axis='x')
for i, v in enumerate(top20['股息率_TTM(%)'].values):
    axes[0].text(v + 0.1, i, f'{v:.2f}%', va='center', fontsize=9, fontweight='bold')

# 图2: 股息率 vs PE 气泡图
valid_pe = top20[top20['PE_TTM'].notna() & (top20['PE_TTM'] > 0) & (top20['PE_TTM'] < 100)]
sizes = top20.loc[valid_pe.index, '总市值(亿)'].fillna(100).values
scatter = axes[1].scatter(valid_pe['PE_TTM'], valid_pe['股息率_TTM(%)'], 
                         s=sizes / 5, alpha=0.6, c='#C62828')
for _, row in valid_pe.iterrows():
    axes[1].annotate(row['名称'], (row['PE_TTM'], row['股息率_TTM(%)']),
                    xytext=(5, 5), textcoords='offset points', fontsize=9)
axes[1].set_xlabel('PE(TTM)', fontsize=12)
axes[1].set_ylabel('股息率 TTM (%)', fontsize=12)
axes[1].set_title('股息率 vs PE（气泡=市值）', fontsize=14, fontweight='bold')
axes[1].axhline(y=top20['股息率_TTM(%)'].median(), color='gray', linestyle=':', alpha=0.4, label=f'中位股息率')
axes[1].legend(fontsize=9)
axes[1].grid(True, alpha=0.3)

plt.tight_layout()
plt.show()

# 选取股息率前8的股票，查看历史股息率变化
top_codes = df_normal.head(8)['代码'].tolist()
# 加入茅台作为基准
if '600519.SH' not in top_codes:
    top_codes.append('600519.SH')

dm = DataManager(read_only=True)

fig, axes = plt.subplots(3, 3, figsize=(18, 14))
axes = axes.flatten()

for i, code in enumerate(top_codes[:9]):
    name = df[df['代码'] == code]['名称'].values[0]
    industry = df[df['代码'] == code]['行业'].values[0]
    
    dv_hist = dm.storage.conn.execute("""
        SELECT trade_date, dv_ttm, close
        FROM daily_basic 
        WHERE ts_code = ? AND dv_ttm IS NOT NULL AND dv_ttm > 0
        ORDER BY trade_date
    """, [code]).df()
    
    if len(dv_hist) > 50:
        dv_hist['trade_date'] = pd.to_datetime(dv_hist['trade_date'])
        
        color = '#C62828' if industry == '白酒' else '#1565C0' if industry == '啤酒' else '#F9A825'
        axes[i].fill_between(dv_hist['trade_date'], 0, dv_hist['dv_ttm'], alpha=0.2, color=color)
        axes[i].plot(dv_hist['trade_date'], dv_hist['dv_ttm'], color=color, linewidth=0.8)
        axes[i].axhline(y=dv_hist['dv_ttm'].median(), color='gray', linestyle=':', alpha=0.5, 
                       label=f"中位数={dv_hist['dv_ttm'].median():.2f}%")
        axes[i].set_title(f'{name} ({code}) - {industry}', fontsize=11, fontweight='bold')
        axes[i].set_ylabel('股息率 TTM (%)')
        axes[i].legend(fontsize=8)
        axes[i].grid(True, alpha=0.3)
    else:
        axes[i].text(0.5, 0.5, f'{name}\n数据不足', ha='center', va='center', transform=axes[i].transAxes)

plt.tight_layout()
plt.suptitle('酒类股票历史股息率 TTM 走势', fontsize=16, fontweight='bold', y=1.02)
plt.show()

dm.close()

# 股息率统计：均值、稳定性、趋势
dm = DataManager(read_only=True)
dv_stats = []
for _, row in df_normal.iterrows():
    code = row['代码']
    name = row['名称']
    industry = row['行业']
    
    dv_hist = dm.storage.conn.execute("""
        SELECT dv_ttm FROM daily_basic 
        WHERE ts_code = ? AND dv_ttm IS NOT NULL AND dv_ttm > 0
        ORDER BY trade_date
    """, [code]).df()
    
    if len(dv_hist) > 200:
        vals = dv_hist['dv_ttm'].values
        # 近3年均值
        recent = vals[-252*3:] if len(vals) > 252*3 else vals
        dv_stats.append({
            '代码': code, '名称': name, '行业': industry,
            '当前股息率': row['股息率_TTM(%)'],
            '历史均值': round(np.mean(vals), 2),
            '历史中位数': round(np.median(vals), 2),
            '标准差': round(np.std(vals), 2),
            '最低': round(np.min(vals), 2),
            '最高': round(np.max(vals), 2),
            '当前分位(%)': round((vals < row['股息率_TTM(%)']).mean() * 100, 0),
            '近3年均值': round(np.mean(recent), 2),
            '趋势': '↑上升' if np.mean(recent) > np.mean(vals) * 1.05 else '↓下降' if np.mean(recent) < np.mean(vals) * 0.95 else '→平稳',
        })

dm.close()

dv_stats_df = pd.DataFrame(dv_stats).sort_values('当前股息率', ascending=False)

print("=== 股息率稳定性与趋势分析 ===")
print(f"\n{'='*80}")
print("📌 高股息 + 上升趋势（最佳分红增长型）:")
display(dv_stats_df[(dv_stats_df['当前股息率'] > 3) & (dv_stats_df['趋势'] == '↑上升')]
        [['名称', '行业', '当前股息率', '历史均值', '近3年均值', '当前分位(%)', '趋势']].head(10))

print(f"\n📌 高股息 + 低波动（最稳定分红型）:")
display(dv_stats_df[(dv_stats_df['当前股息率'] > 3) & (dv_stats_df['标准差'] < dv_stats_df['标准差'].median())]
        [['名称', '行业', '当前股息率', '标准差', '最低', '最高', '当前分位(%)']].head(10))

# 计算各酒类股票近 1/3/5 年的总回报
dm = DataManager(read_only=True)

total_return_data = []
for _, row in df_normal.iterrows():
    code = row['代码']
    name = row['名称']
    industry = row['行业']
    current_price = row['最新收盘价']
    
    price_df = dm.storage.conn.execute("""
        SELECT trade_date, close FROM daily_quotes
        WHERE ts_code = ? ORDER BY trade_date
    """, [code]).df()
    
    dv_df = dm.storage.conn.execute("""
        SELECT trade_date, dv_ttm FROM daily_basic
        WHERE ts_code = ? AND dv_ttm IS NOT NULL AND dv_ttm > 0
        ORDER BY trade_date
    """, [code]).df()
    
    if len(price_df) < 250:
        continue
    
    price_df['trade_date'] = pd.to_datetime(price_df['trade_date'])
    price_df = price_df.set_index('trade_date').sort_index()
    
    if len(dv_df) > 0:
        dv_df['trade_date'] = pd.to_datetime(dv_df['trade_date'])
        dv_df = dv_df.set_index('trade_date').sort_index()
    
    latest_date = price_df.index[-1]
    
    result = {'代码': code, '名称': name, '行业': industry, '当前价': current_price}
    
    for period_name, days in [('1年', 252), ('3年', 252*3), ('5年', 252*5)]:
        start_date = latest_date - pd.Timedelta(days=days)
        # 找最接近的交易日
        if start_date < price_df.index[0]:
            result[f'{period_name}股价涨跌(%)'] = None
            result[f'{period_name}累计股息(%)'] = None
            result[f'{period_name}总回报(%)'] = None
            continue
        
        closest = price_df.index[price_df.index >= start_date]
        if len(closest) == 0:
            continue
        start_idx = closest[0]
        start_price = price_df.loc[start_idx, 'close']
        end_price = price_df.iloc[-1]['close']
        price_return = (end_price / start_price - 1) * 100
        
        # 近似累计股息：区间内平均年股息率 × 年数
        if len(dv_df) > 0:
            period_dv = dv_df[(dv_df.index >= start_idx) & (dv_df.index <= latest_date)]
            if len(period_dv) > 0:
                avg_dv = period_dv['dv_ttm'].mean()
                cumulative_dv = avg_dv * (days / 252)
            else:
                cumulative_dv = 0
        else:
            cumulative_dv = 0
        
        total = price_return + cumulative_dv
        result[f'{period_name}股价涨跌(%)'] = round(price_return, 1)
        result[f'{period_name}累计股息(%)'] = round(cumulative_dv, 1)
        result[f'{period_name}总回报(%)'] = round(total, 1)
    
    total_return_data.append(result)

dm.close()

tr_df = pd.DataFrame(total_return_data)

# === 各周期排名 ===
for period in ['1年', '3年', '5年']:
    col = f'{period}总回报(%)'
    if col in tr_df.columns:
        ranked = tr_df.dropna(subset=[col]).sort_values(col, ascending=False)
        print(f"\n{'='*60}")
        print(f"📊 近{period}总回报排名 TOP 10（股价涨跌 + 股息）")
        display(ranked[['名称', '行业', f'{period}股价涨跌(%)', f'{period}累计股息(%)', f'{period}总回报(%)']].head(10))
        
        print(f"\n近{period}总回报排名 BOTTOM 5:")
        display(ranked[['名称', '行业', f'{period}股价涨跌(%)', f'{period}累计股息(%)', f'{period}总回报(%)']].tail(5))

# 可视化：近5年总回报 vs 当前股息率
fig, axes = plt.subplots(1, 2, figsize=(18, 7))

# 图1: 5年总回报柱状图 TOP 15
if '5年总回报(%)' in tr_df.columns:
    top15_5y = tr_df.dropna(subset=['5年总回报(%)']).nlargest(15, '5年总回报(%)')
    
    x = range(len(top15_5y))
    price_returns = top15_5y['5年股价涨跌(%)'].values
    dv_returns = top15_5y['5年累计股息(%)'].values
    
    axes[0].bar(x, price_returns, color='#1565C0', alpha=0.8, label='股价涨跌')
    axes[0].bar(x, dv_returns, bottom=price_returns, color='#2E7D32', alpha=0.8, label='累计股息')
    axes[0].axhline(y=0, color='black', linewidth=0.5)
    axes[0].set_xticks(x)
    axes[0].set_xticklabels([f"{n}\n({c})" for n, c in zip(top15_5y['名称'], top15_5y['代码'])], 
                           rotation=45, ha='right', fontsize=8)
    axes[0].set_title('近5年总回报 TOP 15（股价+股息）', fontsize=13, fontweight='bold')
    axes[0].set_ylabel('总回报 (%)')
    axes[0].legend(fontsize=10)
    axes[0].grid(True, alpha=0.3, axis='y')
    
    for i, (pr, dv) in enumerate(zip(price_returns, dv_returns)):
        total = pr + dv
        axes[0].text(i, total + 3, f'{total:.0f}%', ha='center', fontsize=8, fontweight='bold')

# 图2: 股息率 vs 1年总回报（先合并股息率到 tr_df）
dv_col = df_normal[['代码', '股息率_TTM(%)']].copy()
valid = tr_df.dropna(subset=['1年总回报(%)', '5年总回报(%)'])
valid = valid.merge(dv_col, on='代码', how='left')
valid = valid[(valid['1年总回报(%)'].between(-80, 150))]

sc = axes[1].scatter(valid['股息率_TTM(%)'], 
                     valid['1年总回报(%)'],
                     c=valid['5年总回报(%)'], cmap='RdYlGn', s=60, alpha=0.7, edgecolors='white')
axes[1].axhline(y=0, color='gray', linestyle='--', alpha=0.5)
for _, row in valid.iterrows():
    axes[1].annotate(row['名称'], (row['股息率_TTM(%)'], row['1年总回报(%)']),
                    xytext=(5, 5), textcoords='offset points', fontsize=8)
axes[1].set_xlabel('当前股息率 TTM (%)', fontsize=12)
axes[1].set_ylabel('近1年总回报 (%)', fontsize=12)
axes[1].set_title('股息率 vs 近1年总回报（颜色=5年回报）', fontsize=13, fontweight='bold')
cbar = plt.colorbar(sc, ax=axes[1])
cbar.set_label('5年总回报(%)', fontsize=10)
axes[1].grid(True, alpha=0.3)

plt.tight_layout()
plt.show()

# 从财务指标分析分红可持续性
dm = DataManager(read_only=True)

sustainability = []
for _, row in df_normal.iterrows():
    code = row['代码']
    name = row['名称']
    industry = row['行业']
    
    # 最新财务指标
    fin = dm.storage.conn.execute("""
        SELECT end_date, roe, roa, grossprofit_margin, netprofit_margin, 
               debt_to_assets, current_ratio, eps
        FROM fina_indicator
        WHERE ts_code = ? AND roe IS NOT NULL
        ORDER BY end_date DESC LIMIT 3
    """, [code]).fetchall()
    
    # 利润表数据
    inc = dm.storage.conn.execute("""
        SELECT end_date, revenue, n_income
        FROM income WHERE ts_code = ? AND report_type = '1'
        ORDER BY end_date DESC LIMIT 5
    """, [code]).fetchall()
    
    if len(fin) > 0 and len(inc) >= 2:
        latest_fin = fin[0]
        
        # 计算利润增速
        ni_values = [float(x[2]) if x[2] and float(x[2]) != 0 else None for x in inc]
        ni_values = [x for x in ni_values if x is not None and x > 0]
        
        ni_growth = None
        if len(ni_values) >= 5:
            ni_growth = ((ni_values[0] / ni_values[4]) ** (1/4) - 1) * 100
        elif len(ni_values) >= 3:
            ni_growth = ((ni_values[0] / ni_values[2]) ** (1/2) - 1) * 100
        
        sustainability.append({
            '代码': code, '名称': name, '行业': industry,
            '股息率_TTM(%)': row['股息率_TTM(%)'],
            'ROE(%)': round(latest_fin[1], 1) if latest_fin[1] else None,
            '毛利率(%)': round(latest_fin[3], 1) if latest_fin[3] else None,
            '净利率(%)': round(latest_fin[4], 1) if latest_fin[4] else None,
            '资产负债率(%)': round(latest_fin[5], 1) if latest_fin[5] else None,
            'EPS': round(latest_fin[7], 2) if latest_fin[7] else None,
            '净利润CAGR(%)': round(ni_growth, 1) if ni_growth else None,
        })

dm.close()

sus_df = pd.DataFrame(sustainability).sort_values('股息率_TTM(%)', ascending=False)

# 综合评分：股息率 + ROE + 成长性 + 低杠杆
sus_df['质量评分'] = (
    sus_df['股息率_TTM(%)'].rank(ascending=False) * 0.40 +
    sus_df['ROE(%)'].rank(ascending=False) * 0.25 +
    sus_df['净利润CAGR(%)'].fillna(0).rank(ascending=False) * 0.15 +
    sus_df['资产负债率(%)'].rank() * 0.20  # 杠杆越低越好
).round(0)

print("=== 分红可持续性综合评分 ===")
print(f"\n评分维度: 股息率(40%) + ROE(25%) + 利润增长(15%) + 低杠杆(20%)")
display(sus_df.nsmallest(20, '质量评分')[['名称', '行业', '股息率_TTM(%)', 'ROE(%)', '毛利率(%)', 
                                          '净利润CAGR(%)', '资产负债率(%)', '质量评分']])

# 可视化：质量评分 vs 股息率
fig, ax = plt.subplots(figsize=(14, 8))

top_quality = sus_df.nsmallest(20, '质量评分')
colors = ['#C62828' if ind == '白酒' else '#1565C0' if ind == '啤酒' else '#F9A825' 
          for ind in top_quality['行业']]

bars = ax.barh(range(len(top_quality)), top_quality['股息率_TTM(%)'], color=colors, alpha=0.7, edgecolor='white')
ax.set_yticks(range(len(top_quality)))
ax.set_yticklabels([f"{n} #{s:.0f}" for n, s in zip(top_quality['名称'], top_quality['质量评分'])], fontsize=10)
ax.invert_yaxis()
ax.set_xlabel('股息率 TTM (%)', fontsize=12, fontweight='bold')
ax.set_title('综合质量评分 TOP 20（#越小越好）', fontsize=14, fontweight='bold')

# 添加 ROE 标注
for i, (_, row) in enumerate(top_quality.iterrows()):
    ax.text(row['股息率_TTM(%)'] + 0.1, i, 
            f"ROE:{row['ROE(%)']:.0f}% 杠杆:{row['资产负债率(%)']:.0f}%", 
            va='center', fontsize=8, color='gray')

ax.grid(True, alpha=0.3, axis='x')
plt.tight_layout()
plt.show()

# 合并所有数据，构建决策矩阵
decision = df_normal[['代码', '名称', '行业', '最新收盘价', '股息率_TTM(%)', 'PE_TTM', 'PB', '总市值(亿)']].copy()

# 合并总回报数据
tr_merge = tr_df[['代码', '1年总回报(%)', '3年总回报(%)', '5年总回报(%)']].copy()
decision = decision.merge(tr_merge, on='代码', how='left')

# 合并可持续性数据
sus_merge = sus_df[['代码', 'ROE(%)', '毛利率(%)', '净利润CAGR(%)', '资产负债率(%)', '质量评分']].copy()
decision = decision.merge(sus_merge, on='代码', how='left')

# 综合决策评分：
# - 当前股息率（权重30%）—— 越高的当期现金回报
# - 5年总回报（权重25%）—— 历史总回报验证
# - 质量评分逆序（权重25%）—— 分红可持续性
# - PE倒数（权重20%）—— 估值越低越好

decision_valid = decision.dropna(subset=['股息率_TTM(%)', 'ROE(%)']).copy()
decision_valid = decision_valid[decision_valid['PE_TTM'].notna() & (decision_valid['PE_TTM'] > 0) & (decision_valid['PE_TTM'] < 200)]

# 5年回报缺失的用3年或1年替代，都没有则用0
decision_valid['总回报参考'] = decision_valid['5年总回报(%)'].fillna(
    decision_valid['3年总回报(%)'].fillna(
        decision_valid['1年总回报(%)'].fillna(0)))

decision_valid['决策评分'] = (
    decision_valid['股息率_TTM(%)'].rank(ascending=False) * 0.30 +
    decision_valid['总回报参考'].rank(ascending=False) * 0.25 +
    decision_valid['质量评分'].rank() * 0.25 +  # 质量评分越小越好
    decision_valid['PE_TTM'].rank() * 0.20  # PE越低越好
).round(0)

result = decision_valid.nsmallest(20, '决策评分').copy()

print("=" * 80)
print("🏆 酒类分红投资综合排名 TOP 20")
print("评分维度: 股息率(30%) + 历史总回报(25%) + 质量(25%) + 低估(20%)")
print("=" * 80)
display(result[['名称', '行业', '最新收盘价', '股息率_TTM(%)', 'PE_TTM', 
               'ROE(%)', '毛利率(%)', '5年总回报(%)', '决策评分']].reset_index(drop=True))

# 最终可视化：决策矩阵气泡图
fig, axes = plt.subplots(1, 2, figsize=(18, 8))

top_result = result.head(12)

# 图1: 股息率 vs 决策评分
colors = ['#2E7D32' if s <= top_result['决策评分'].quantile(0.3) else 
          '#1565C0' if s <= top_result['决策评分'].quantile(0.7) else '#90A4AE'
          for s in top_result['决策评分']]

axes[0].barh(range(len(top_result)), top_result['股息率_TTM(%)'], color=colors, edgecolor='white')
axes[0].set_yticks(range(len(top_result)))
axes[0].set_yticklabels([f"{n} ({ind})" for n, ind in zip(top_result['名称'], top_result['行业'])], fontsize=10)
axes[0].invert_yaxis()
axes[0].set_xlabel('股息率 TTM (%)', fontweight='bold')
axes[0].set_title('TOP 12 股息率对比', fontsize=14, fontweight='bold')
for i, (_, row) in enumerate(top_result.iterrows()):
    axes[0].text(row['股息率_TTM(%)'] + 0.1, i, 
                f"PE:{row['PE_TTM']:.0f} ROE:{row['ROE(%)']:.0f}%", 
                va='center', fontsize=8, color='gray')
axes[0].grid(True, alpha=0.3, axis='x')

# 图2: 5年总回报 vs 股息率 气泡图
all_valid = decision_valid.dropna(subset=['5年总回报(%)'])
sc = axes[1].scatter(all_valid['股息率_TTM(%)'], all_valid['5年总回报(%)'], 
                     s=all_valid['总市值(亿)'] / 10, alpha=0.6, 
                     c=all_valid['决策评分'], cmap='RdYlGn_r', edgecolors='white', linewidth=0.5)

# 标注 TOP 10
for _, row in result.head(10).iterrows():
    if not pd.isna(row['5年总回报(%)']):
        axes[1].annotate(row['名称'], (row['股息率_TTM(%)'], row['5年总回报(%)']),
                        xytext=(5, 5), textcoords='offset points', fontsize=10, fontweight='bold',
                        bbox=dict(boxstyle='round,pad=0.3', facecolor='yellow', alpha=0.3))

axes[1].axhline(y=0, color='gray', linestyle='--', alpha=0.5)
axes[1].set_xlabel('股息率 TTM (%)', fontsize=12, fontweight='bold')
axes[1].set_ylabel('近5年总回报 (%)', fontsize=12, fontweight='bold')
axes[1].set_title('股息率 × 历史总回报（气泡=市值，颜色=决策评分）', fontsize=13, fontweight='bold')
cbar = plt.colorbar(sc, ax=axes[1])
cbar.set_label('决策评分（越小越好）', fontsize=10)
axes[1].grid(True, alpha=0.3)

plt.tight_layout()
plt.show()

# 按行业分组统计
segment_stats = decision_valid.groupby('行业').agg(
    平均股息率=('股息率_TTM(%)', 'mean'),
    中位股息率=('股息率_TTM(%)', 'median'),
    最高股息率=('股息率_TTM(%)', 'max'),
    平均ROE=('ROE(%)', 'mean'),
    平均PE=('PE_TTM', 'mean'),
    平均毛利率=('毛利率(%)', 'mean'),
    股票数=('代码', 'count'),
).round(1)

print("=== 酒类细分赛道对比 ===")
display(segment_stats)

# 各行业TOP3
for ind in ['白酒', '啤酒', '红黄酒']:
    subset = decision_valid[decision_valid['行业'] == ind].nsmallest(3, '决策评分')
    if len(subset) > 0:
        print(f"\n🏅 {ind}赛道 TOP 3:")
        display(subset[['名称', '股息率_TTM(%)', 'PE_TTM', 'ROE(%)', '5年总回报(%)', '决策评分']])

# 获取实际分红数据
import tushare as ts
pro = ts.pro_api()

dividend_detail = []
for _, row in df_normal.iterrows():
    code = row['代码']
    name = row['名称']
    
    try:
        div_df = pro.dividend(ts_code=code, fields='ts_code,end_date,cash_div,cash_div_tax,stk_div,ex_date,record_date')
        if len(div_df) > 0:
            # 找最近一次有实际现金分红且已除权的
            valid = div_df[(div_df['cash_div'] > 0) & (div_df['ex_date'].notna())]
            if len(valid) > 0:
                latest = valid.iloc[0]
                # 计算最近3年累计分红
                recent_total = valid['cash_div'].head(3).sum() if len(valid) >= 3 else valid['cash_div'].sum()
                dividend_detail.append({
                    '名称': name,
                    '代码': code,
                    '行业': row['行业'],
                    '最新每股分红(元)': round(latest['cash_div'], 2),
                    '除权日': latest['ex_date'],
                    '近3次累计分红(元)': round(recent_total, 2),
                    '历史分红次数': len(valid),
                    '收盘价': row['最新收盘价'],
                    '即时股息率(%)': round(latest['cash_div'] / row['最新收盘价'] * 100, 2),
                })
    except Exception as e:
        pass

div_detail_df = pd.DataFrame(dividend_detail).sort_values('即时股息率(%)', ascending=False)

print("=== 最新一次实际现金分红 TOP 15 ===")
display(div_detail_df.head(15)[['名称', '行业', '最新每股分红(元)', '收盘价', '即时股息率(%)', '近3次累计分红(元)', '历史分红次数', '除权日']])

print(f"\n注意: 即时股息率 = 最新每股分红 / 当前股价")
print(f"      与 dv_ttm（过去12个月滚动股息率）可能不同，因分红频率和时点差异")

# 最终总结
top5 = result.head(5)

print("=" * 70)
print("🎯 酒类分红投资 — 最终推荐")
print("=" * 70)
print()
print("综合评分维度: 股息率 + 历史总回报 + 盈利能力(ROE) + 估值(PE)")
print()

for i, (_, row) in enumerate(top5.iterrows()):
    medal = ['🥇', '🥈', '🥉', '4️⃣', '5️⃣'][i]
    print(f"{medal} {row['名称']} ({row['代码']}) — {row['行业']}")
    print(f"   股息率: {row['股息率_TTM(%)']:.2f}%  |  PE: {row['PE_TTM']:.0f}  |  ROE: {row['ROE(%)']:.0f}%")
    print(f"   股价: {row['最新收盘价']:.2f}元  |  市值: {row['总市值(亿)']:.0f}亿")
    if not pd.isna(row['5年总回报(%)']):
        print(f"   5年总回报: {row['5年总回报(%)']:.0f}%  |  毛利率: {row['毛利率(%)']:.0f}%")
    print()

print("-" * 70)
print()
print("📊 关键发现:")
print()

# 找出股息率最高
best_dv = df_normal.iloc[0]
print(f"1. 当期股息率最高: {best_dv['名称']} ({best_dv['股息率_TTM(%)']:.2f}%)")

# 找出5年总回报最高
if '5年总回报(%)' in decision_valid.columns:
    best_tr = decision_valid.dropna(subset=['5年总回报(%)']).nlargest(1, '5年总回报(%)').iloc[0]
    print(f"2. 5年总回报最高: {best_tr['名称']} ({best_tr['5年总回报(%)']:.0f}%)")

# 找出ROE最高
best_roe = decision_valid.nlargest(1, 'ROE(%)').iloc[0]
print(f"3. ROE最高: {best_roe['名称']} ({best_roe['ROE(%)']:.0f}%)")

# 白酒平均 vs 啤酒平均
baijiu_avg = decision_valid[decision_valid['行业']=='白酒']['股息率_TTM(%)'].mean()
beer_avg = decision_valid[decision_valid['行业']=='啤酒']['股息率_TTM(%)'].mean()
print(f"4. 白酒平均股息率 {baijiu_avg:.2f}% vs 啤酒平均 {beer_avg:.2f}%")

print()
print("-" * 70)
print()
print("⚠️ 风险提示:")
print("  - 高股息可能伴随股价下跌（股息率=分红/股价）")
print("  - 需关注分红政策是否可持续（特别分红 vs 常规分红）")
print("  - 白酒行业面临消费趋势变化的长期风险")
print("  - 以上分析基于历史数据，不构成投资建议")
print()
print("💡 建议策略:")
print("  - 核心仓位选 ROE高+分红稳的白酒龙头（如五粮液/泸州老窖/茅台）")
print("  - 卫星仓位配啤酒高息标的（如重庆啤酒）做股息增强")
print("  - 定期复盘：每季度重新运行本 notebook 更新数据")