analyze_health.py 7.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180
  1. """
  2. analyze_health.py
  3. =================
  4. 三个分析:
  5. 1. 运动与睡眠的关系
  6. 2. HRV / 静息心率作为疲劳指标
  7. 3. 经期周期对心率/睡眠/运动的影响
  8. 输出:终端报告 + output/ 下的 PNG 图
  9. """
  10. from __future__ import annotations
  11. from pathlib import Path
  12. import pymysql
  13. import pandas as pd
  14. import numpy as np
  15. import matplotlib.pyplot as plt
  16. import matplotlib
  17. matplotlib.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei']
  18. matplotlib.rcParams['axes.unicode_minus'] = False
  19. OUT = Path(__file__).parent / "output"
  20. OUT.mkdir(exist_ok=True)
  21. c = pymysql.connect(host='localhost', port=3306, user='analytics', password='123456', database='campus_health')
  22. df = pd.read_sql("SELECT * FROM daily_health_metrics WHERE metric_date >= '2024-09-01'", c)
  23. c.close()
  24. df['metric_date'] = pd.to_datetime(df['metric_date'])
  25. df = df.sort_values('metric_date').reset_index(drop=True)
  26. print(f"加载 {len(df)} 天数据 ({df['metric_date'].min().date()} ~ {df['metric_date'].max().date()})")
  27. # 只保留有静息心率的天作为有效样本
  28. valid = df[df['resting_heart_rate'].notna() & df['hrv_sdnn'].notna()].copy()
  29. print(f"有完整 HR 数据的天: {len(valid)}")
  30. # ============ 分析 1:运动 vs 睡眠 ============
  31. print("\n" + "="*60)
  32. print("分析 1:运动与睡眠的关系")
  33. print("="*60)
  34. # 当晚睡眠 vs 当天是否锻炼
  35. df['is_workout'] = df['workout_count'] > 0
  36. sleep_valid = df[df['sleep_duration_hours'] > 0]
  37. g = sleep_valid.groupby('is_workout').agg(
  38. nights=('metric_date', 'count'),
  39. sleep_h=('sleep_duration_hours', 'mean'),
  40. deep_h=('deep_sleep_hours', 'mean'),
  41. rem_h=('rem_sleep_hours', 'mean'),
  42. deep_pct=('deep_sleep_hours', lambda x: (x / sleep_valid.loc[x.index, 'sleep_duration_hours'] * 100).mean()),
  43. )
  44. g.index = ['非锻炼日', '锻炼日']
  45. print("\n[1a] 锻炼日 vs 非锻炼日(睡眠指标):")
  46. print(g.round(2).to_string())
  47. # 相关性:当天活跃能量 vs 当晚深睡占比
  48. df['deep_pct'] = df['deep_sleep_hours'] / df['sleep_duration_hours'].replace(0, np.nan)
  49. corr_df = df[['active_energy_kcal', 'exercise_minutes', 'deep_pct', 'sleep_duration_hours',
  50. 'resting_heart_rate', 'hrv_sdnn']].dropna()
  51. print("\n[1b] 关键指标 Pearson 相关系数(n=%d):" % len(corr_df))
  52. cols = ['active_energy_kcal', 'exercise_minutes', 'deep_pct', 'sleep_duration_hours', 'resting_heart_rate', 'hrv_sdnn']
  53. print(corr_df[cols].corr().round(3).to_string())
  54. # 图 1:锻炼日 vs 非锻炼日对比柱状图
  55. fig, axes = plt.subplots(1, 2, figsize=(12, 5))
  56. axes[0].bar(['非锻炼日', '锻炼日'], [g.loc['非锻炼日','deep_h'], g.loc['锻炼日','deep_h']], color=['#888', '#e74c3c'])
  57. axes[0].set_title('深睡时长(小时)')
  58. axes[0].set_ylabel('小时')
  59. for i, v in enumerate([g.loc['非锻炼日','deep_h'], g.loc['锻炼日','deep_h']]):
  60. axes[0].text(i, v+0.02, f'{v:.2f}h', ha='center')
  61. axes[1].bar(['非锻炼日', '锻炼日'], [g.loc['非锻炼日','rem_h'], g.loc['锻炼日','rem_h']], color=['#888', '#3498db'])
  62. axes[1].set_title('REM 时长(小时)')
  63. for i, v in enumerate([g.loc['非锻炼日','rem_h'], g.loc['锻炼日','rem_h']]):
  64. axes[1].text(i, v+0.02, f'{v:.2f}h', ha='center')
  65. plt.suptitle('锻炼日 vs 非锻炼日的睡眠结构对比')
  66. plt.tight_layout()
  67. plt.savefig(OUT / '01_workout_vs_sleep.png', dpi=120)
  68. plt.close()
  69. print(f"\n[图] 保存: output/01_workout_vs_sleep.png")
  70. # ============ 分析 2:HRV / 疲劳 ============
  71. print("\n" + "="*60)
  72. print("分析 2:HRV / 静息心率 作为疲劳指标")
  73. print("="*60)
  74. hrv_valid = df[df['hrv_sdnn'].notna()]
  75. print(f"\n[2a] HRV 分布(n={len(hrv_valid)}):")
  76. print(f" 均值={hrv_valid['hrv_sdnn'].mean():.1f} ms 中位={hrv_valid['hrv_sdnn'].median():.1f} "
  77. f"P25={hrv_valid['hrv_sdnn'].quantile(0.25):.1f} P75={hrv_valid['hrv_sdnn'].quantile(0.75):.1f}")
  78. # 疲劳日 vs 非疲劳日
  79. fatigue_days = df[df['fatigue_reported'] == True]
  80. non_fatigue = df[(df['fatigue_reported'] == False) & df['hrv_sdnn'].notna()]
  81. print(f"\n[2b] 你记录了 {len(fatigue_days)} 个疲劳日")
  82. if len(fatigue_days) >= 2:
  83. print(f" 疲劳日 HRV 均值: {fatigue_days['hrv_sdnn'].mean():.1f} ms (n={fatigue_days['hrv_sdnn'].notna().sum()})")
  84. print(f" 非疲劳日 HRV 均值: {non_fatigue['hrv_sdnn'].mean():.1f} ms (n={len(non_fatigue)})")
  85. print(f" 疲劳日静息心率均值: {fatigue_days['resting_heart_rate'].mean():.1f} bpm")
  86. print(f" 非疲劳日静息心率均值: {non_fatigue['resting_heart_rate'].mean():.1f} bpm")
  87. # 图 2:HRV 时间序列 + 疲劳日标注
  88. fig, ax = plt.subplots(figsize=(14, 5))
  89. ax.plot(hrv_valid['metric_date'], hrv_valid['hrv_sdnn'], color='#2ecc71', linewidth=1, alpha=0.7, label='HRV (ms)')
  90. ax.scatter(fatigue_days['metric_date'], fatigue_days['hrv_sdnn'], color='red', s=80, zorder=5, label='记录为疲劳')
  91. ax.set_title('HRV 时间序列(红点 = 你标记为疲劳的日子)')
  92. ax.set_ylabel('HRV SDNN (ms)')
  93. ax.legend()
  94. plt.tight_layout()
  95. plt.savefig(OUT / '02_hrv_fatigue.png', dpi=120)
  96. plt.close()
  97. print(f"[图] 保存: output/02_hrv_fatigue.png")
  98. # ============ 分析 3:经期影响 ============
  99. print("\n" + "="*60)
  100. print("分析 3:经期周期对心率/睡眠/运动的影响")
  101. print("="*60)
  102. # 找经期的起始日(从非经期到经期的跳变)
  103. df_sorted = df.sort_values('metric_date').reset_index(drop=True)
  104. periods = []
  105. in_period = False
  106. for i, row in df_sorted.iterrows():
  107. if row['is_menstruating'] and not in_period:
  108. periods.append(row['metric_date'])
  109. in_period = True
  110. elif not row['is_menstruating']:
  111. in_period = False
  112. print(f"\n[3a] 识别到 {len(periods)} 次经期开始")
  113. # 对每次经期,计算前后各 7 天的指标
  114. window_records = []
  115. for start in periods:
  116. for offset in range(-7, 8):
  117. d = start + pd.Timedelta(days=offset)
  118. row = df_sorted[df_sorted['metric_date'] == d]
  119. if len(row):
  120. r = row.iloc[0]
  121. window_records.append({
  122. 'offset': offset,
  123. 'resting_hr': r['resting_heart_rate'],
  124. 'hrv': r['hrv_sdnn'],
  125. 'sleep_h': r['sleep_duration_hours'],
  126. 'workout': r['workout_count'],
  127. 'active_kcal': r['active_energy_kcal'],
  128. })
  129. w = pd.DataFrame(window_records)
  130. if len(w):
  131. profile = w.groupby('offset').agg(
  132. n=('resting_hr', 'count'),
  133. resting_hr=('resting_hr', 'mean'),
  134. hrv=('hrv', 'mean'),
  135. sleep_h=('sleep_h', 'mean'),
  136. workout=('workout', 'mean'),
  137. ).round(2)
  138. print("\n[3b] 经期前后 7 天平均指标(offset=0 为经期第 1 天):")
  139. print(profile.to_string())
  140. # 图 3:经期 profile
  141. fig, axes = plt.subplots(2, 2, figsize=(13, 8))
  142. axes[0,0].plot(profile.index, profile['resting_hr'], 'o-', color='#e74c3c')
  143. axes[0,0].axvline(0, color='red', ls='--', alpha=0.5)
  144. axes[0,0].set_title('静息心率 (bpm)')
  145. axes[0,1].plot(profile.index, profile['hrv'], 'o-', color='#2ecc71')
  146. axes[0,1].axvline(0, color='red', ls='--', alpha=0.5)
  147. axes[0,1].set_title('HRV (ms)')
  148. axes[1,0].plot(profile.index, profile['sleep_h'], 'o-', color='#3498db')
  149. axes[1,0].axvline(0, color='red', ls='--', alpha=0.5)
  150. axes[1,0].set_title('睡眠时长 (h)')
  151. axes[1,1].plot(profile.index, profile['workout'], 'o-', color='#f39c12')
  152. axes[1,1].axvline(0, color='red', ls='--', alpha=0.5)
  153. axes[1,1].set_title('当日锻炼次数')
  154. for ax in axes.flat:
  155. ax.set_xlabel('距经期开始天数')
  156. ax.grid(alpha=0.3)
  157. plt.suptitle('经期前后 7 天生理指标变化曲线')
  158. plt.tight_layout()
  159. plt.savefig(OUT / '03_menstrual_profile.png', dpi=120)
  160. plt.close()
  161. print(f"[图] 保存: output/03_menstrual_profile.png")
  162. print("\n✓ 全部分析完成")