| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180 |
- """
- analyze_health.py
- =================
- 三个分析:
- 1. 运动与睡眠的关系
- 2. HRV / 静息心率作为疲劳指标
- 3. 经期周期对心率/睡眠/运动的影响
- 输出:终端报告 + output/ 下的 PNG 图
- """
- from __future__ import annotations
- from pathlib import Path
- import pymysql
- import pandas as pd
- import numpy as np
- import matplotlib.pyplot as plt
- import matplotlib
- matplotlib.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei']
- matplotlib.rcParams['axes.unicode_minus'] = False
- OUT = Path(__file__).parent / "output"
- OUT.mkdir(exist_ok=True)
- c = pymysql.connect(host='localhost', port=3306, user='analytics', password='123456', database='campus_health')
- df = pd.read_sql("SELECT * FROM daily_health_metrics WHERE metric_date >= '2024-09-01'", c)
- c.close()
- df['metric_date'] = pd.to_datetime(df['metric_date'])
- df = df.sort_values('metric_date').reset_index(drop=True)
- print(f"加载 {len(df)} 天数据 ({df['metric_date'].min().date()} ~ {df['metric_date'].max().date()})")
- # 只保留有静息心率的天作为有效样本
- valid = df[df['resting_heart_rate'].notna() & df['hrv_sdnn'].notna()].copy()
- print(f"有完整 HR 数据的天: {len(valid)}")
- # ============ 分析 1:运动 vs 睡眠 ============
- print("\n" + "="*60)
- print("分析 1:运动与睡眠的关系")
- print("="*60)
- # 当晚睡眠 vs 当天是否锻炼
- df['is_workout'] = df['workout_count'] > 0
- sleep_valid = df[df['sleep_duration_hours'] > 0]
- g = sleep_valid.groupby('is_workout').agg(
- nights=('metric_date', 'count'),
- sleep_h=('sleep_duration_hours', 'mean'),
- deep_h=('deep_sleep_hours', 'mean'),
- rem_h=('rem_sleep_hours', 'mean'),
- deep_pct=('deep_sleep_hours', lambda x: (x / sleep_valid.loc[x.index, 'sleep_duration_hours'] * 100).mean()),
- )
- g.index = ['非锻炼日', '锻炼日']
- print("\n[1a] 锻炼日 vs 非锻炼日(睡眠指标):")
- print(g.round(2).to_string())
- # 相关性:当天活跃能量 vs 当晚深睡占比
- df['deep_pct'] = df['deep_sleep_hours'] / df['sleep_duration_hours'].replace(0, np.nan)
- corr_df = df[['active_energy_kcal', 'exercise_minutes', 'deep_pct', 'sleep_duration_hours',
- 'resting_heart_rate', 'hrv_sdnn']].dropna()
- print("\n[1b] 关键指标 Pearson 相关系数(n=%d):" % len(corr_df))
- cols = ['active_energy_kcal', 'exercise_minutes', 'deep_pct', 'sleep_duration_hours', 'resting_heart_rate', 'hrv_sdnn']
- print(corr_df[cols].corr().round(3).to_string())
- # 图 1:锻炼日 vs 非锻炼日对比柱状图
- fig, axes = plt.subplots(1, 2, figsize=(12, 5))
- axes[0].bar(['非锻炼日', '锻炼日'], [g.loc['非锻炼日','deep_h'], g.loc['锻炼日','deep_h']], color=['#888', '#e74c3c'])
- axes[0].set_title('深睡时长(小时)')
- axes[0].set_ylabel('小时')
- for i, v in enumerate([g.loc['非锻炼日','deep_h'], g.loc['锻炼日','deep_h']]):
- axes[0].text(i, v+0.02, f'{v:.2f}h', ha='center')
- axes[1].bar(['非锻炼日', '锻炼日'], [g.loc['非锻炼日','rem_h'], g.loc['锻炼日','rem_h']], color=['#888', '#3498db'])
- axes[1].set_title('REM 时长(小时)')
- for i, v in enumerate([g.loc['非锻炼日','rem_h'], g.loc['锻炼日','rem_h']]):
- axes[1].text(i, v+0.02, f'{v:.2f}h', ha='center')
- plt.suptitle('锻炼日 vs 非锻炼日的睡眠结构对比')
- plt.tight_layout()
- plt.savefig(OUT / '01_workout_vs_sleep.png', dpi=120)
- plt.close()
- print(f"\n[图] 保存: output/01_workout_vs_sleep.png")
- # ============ 分析 2:HRV / 疲劳 ============
- print("\n" + "="*60)
- print("分析 2:HRV / 静息心率 作为疲劳指标")
- print("="*60)
- hrv_valid = df[df['hrv_sdnn'].notna()]
- print(f"\n[2a] HRV 分布(n={len(hrv_valid)}):")
- print(f" 均值={hrv_valid['hrv_sdnn'].mean():.1f} ms 中位={hrv_valid['hrv_sdnn'].median():.1f} "
- f"P25={hrv_valid['hrv_sdnn'].quantile(0.25):.1f} P75={hrv_valid['hrv_sdnn'].quantile(0.75):.1f}")
- # 疲劳日 vs 非疲劳日
- fatigue_days = df[df['fatigue_reported'] == True]
- non_fatigue = df[(df['fatigue_reported'] == False) & df['hrv_sdnn'].notna()]
- print(f"\n[2b] 你记录了 {len(fatigue_days)} 个疲劳日")
- if len(fatigue_days) >= 2:
- print(f" 疲劳日 HRV 均值: {fatigue_days['hrv_sdnn'].mean():.1f} ms (n={fatigue_days['hrv_sdnn'].notna().sum()})")
- print(f" 非疲劳日 HRV 均值: {non_fatigue['hrv_sdnn'].mean():.1f} ms (n={len(non_fatigue)})")
- print(f" 疲劳日静息心率均值: {fatigue_days['resting_heart_rate'].mean():.1f} bpm")
- print(f" 非疲劳日静息心率均值: {non_fatigue['resting_heart_rate'].mean():.1f} bpm")
- # 图 2:HRV 时间序列 + 疲劳日标注
- fig, ax = plt.subplots(figsize=(14, 5))
- ax.plot(hrv_valid['metric_date'], hrv_valid['hrv_sdnn'], color='#2ecc71', linewidth=1, alpha=0.7, label='HRV (ms)')
- ax.scatter(fatigue_days['metric_date'], fatigue_days['hrv_sdnn'], color='red', s=80, zorder=5, label='记录为疲劳')
- ax.set_title('HRV 时间序列(红点 = 你标记为疲劳的日子)')
- ax.set_ylabel('HRV SDNN (ms)')
- ax.legend()
- plt.tight_layout()
- plt.savefig(OUT / '02_hrv_fatigue.png', dpi=120)
- plt.close()
- print(f"[图] 保存: output/02_hrv_fatigue.png")
- # ============ 分析 3:经期影响 ============
- print("\n" + "="*60)
- print("分析 3:经期周期对心率/睡眠/运动的影响")
- print("="*60)
- # 找经期的起始日(从非经期到经期的跳变)
- df_sorted = df.sort_values('metric_date').reset_index(drop=True)
- periods = []
- in_period = False
- for i, row in df_sorted.iterrows():
- if row['is_menstruating'] and not in_period:
- periods.append(row['metric_date'])
- in_period = True
- elif not row['is_menstruating']:
- in_period = False
- print(f"\n[3a] 识别到 {len(periods)} 次经期开始")
- # 对每次经期,计算前后各 7 天的指标
- window_records = []
- for start in periods:
- for offset in range(-7, 8):
- d = start + pd.Timedelta(days=offset)
- row = df_sorted[df_sorted['metric_date'] == d]
- if len(row):
- r = row.iloc[0]
- window_records.append({
- 'offset': offset,
- 'resting_hr': r['resting_heart_rate'],
- 'hrv': r['hrv_sdnn'],
- 'sleep_h': r['sleep_duration_hours'],
- 'workout': r['workout_count'],
- 'active_kcal': r['active_energy_kcal'],
- })
- w = pd.DataFrame(window_records)
- if len(w):
- profile = w.groupby('offset').agg(
- n=('resting_hr', 'count'),
- resting_hr=('resting_hr', 'mean'),
- hrv=('hrv', 'mean'),
- sleep_h=('sleep_h', 'mean'),
- workout=('workout', 'mean'),
- ).round(2)
- print("\n[3b] 经期前后 7 天平均指标(offset=0 为经期第 1 天):")
- print(profile.to_string())
- # 图 3:经期 profile
- fig, axes = plt.subplots(2, 2, figsize=(13, 8))
- axes[0,0].plot(profile.index, profile['resting_hr'], 'o-', color='#e74c3c')
- axes[0,0].axvline(0, color='red', ls='--', alpha=0.5)
- axes[0,0].set_title('静息心率 (bpm)')
- axes[0,1].plot(profile.index, profile['hrv'], 'o-', color='#2ecc71')
- axes[0,1].axvline(0, color='red', ls='--', alpha=0.5)
- axes[0,1].set_title('HRV (ms)')
- axes[1,0].plot(profile.index, profile['sleep_h'], 'o-', color='#3498db')
- axes[1,0].axvline(0, color='red', ls='--', alpha=0.5)
- axes[1,0].set_title('睡眠时长 (h)')
- axes[1,1].plot(profile.index, profile['workout'], 'o-', color='#f39c12')
- axes[1,1].axvline(0, color='red', ls='--', alpha=0.5)
- axes[1,1].set_title('当日锻炼次数')
- for ax in axes.flat:
- ax.set_xlabel('距经期开始天数')
- ax.grid(alpha=0.3)
- plt.suptitle('经期前后 7 天生理指标变化曲线')
- plt.tight_layout()
- plt.savefig(OUT / '03_menstrual_profile.png', dpi=120)
- plt.close()
- print(f"[图] 保存: output/03_menstrual_profile.png")
- print("\n✓ 全部分析完成")
|