基于Pandas按设备分组睡眠数据并生成睡眠阶段时长列
2026-6-20
睡眠会话的睡眠阶段时长汇总实现
需求说明
现有睡眠数据已按设备（sourceName）将间隙不超过2小时的记录分组为睡眠会话，需为每个会话生成以下睡眠阶段时长汇总列：
rem_duration：REM睡眠阶段总时长core_duration：核心睡眠阶段总时长awake_duration：清醒阶段总时长（包含睡眠阶段之间的间隙时长）
原始睡眠数据
startDate endDate value sourceName 0 2024-06-03 22:26:00+02:00 2024-06-03 22:46:00+02:00 HKCategoryValueSleepAnalysisAsleepCore AppleWatch 6 2024-06-03 22:40:00+02:00 2024-06-04 07:48:00+02:00 HKCategoryValueSleepAnalysisAsleepCore Connect 1 2024-06-03 22:46:00+02:00 2024-06-03 22:49:00+02:00 HKCategoryValueSleepAnalysisAwake AppleWatch 2 2024-06-03 22:49:00+02:00 2024-06-04 00:56:00+02:00 HKCategoryValueSleepAnalysisAsleepREM AppleWatch 3 2024-06-04 00:56:00+02:00 2024-06-04 03:56:00+02:00 HKCategoryValueSleepAnalysisAsleepCore AppleWatch 4 2024-06-04 05:56:00+02:00 2024-06-04 07:56:00+02:00 HKCategoryValueSleepAnalysisAsleepREM AppleWatch 5 2024-06-04 22:40:00+02:00 2024-06-05 07:48:00+02:00 HKCategoryValueSleepAnalysisAsleepCore AppleWatch
已完成的睡眠会话分组结果
startDate endDate duration sourceName AppleWatch 0 2024-06-03 22:26:00+02:00 2024-06-04 07:56:00+02:00 7.500000 1 2024-06-04 22:40:00+02:00 2024-06-05 07:48:00+02:00 9.133333 Connect 1 2024-06-03 22:40:00+02:00 2024-06-04 07:48:00+02:00 9.133333
期望输出
startDate endDate duration sourceName rem_duration core_duration awake_duration 0 2024-06-03 22:26:00+02:00 2024-06-04 07:56:00+02:00 7.500000 AppleWatch 4.116667 3.333333 2.05 1 2024-06-04 22:40:00+02:00 2024-06-05 07:48:00+02:00 9.133333 AppleWatch 0.000000 9.133333 0.00 1 2024-06-03 22:40:00+02:00 2024-06-04 07:48:00+02:00 8.133333 Connect 1.000000 7.133333 1.00
现有代码
import pandas as pd from datetime import timedelta data = [ { "startDate": pd.Timestamp("2024-06-03 22:26:00+0200"), "endDate": pd.Timestamp("2024-06-03 22:46:00+0200"), "value": "HKCategoryValueSleepAnalysisAsleepCore", "sourceName": "AppleWatch" }, { "startDate": pd.Timestamp("2024-06-03 22:46:00+0200"), "endDate": pd.Timestamp("2024-06-03 22:49:00+0200"), "value": "HKCategoryValueSleepAnalysisAwake", "sourceName": "AppleWatch" }, { "startDate": pd.Timestamp("2024-06-03 22:49:00+0200"), "endDate": pd.Timestamp("2024-06-04 00:56:00+0200"), "value": "HKCategoryValueSleepAnalysisAsleepREM", "sourceName": "AppleWatch" }, { "startDate": pd.Timestamp("2024-06-04 00:56:00+0200"), "endDate": pd.Timestamp("2024-06-04 03:56:00+0200"), "value": "HKCategoryValueSleepAnalysisAsleepCore", "sourceName": "AppleWatch" }, { "startDate": pd.Timestamp("2024-06-04 05:56:00+0200"), "endDate": pd.Timestamp("2024-06-04 07:56:00+0200"), "value": "HKCategoryValueSleepAnalysisAsleepREM", "sourceName": "AppleWatch" }, { "startDate": pd.Timestamp("2024-06-04 22:40:00+0200"), "endDate": pd.Timestamp("2024-06-05 07:48:00+0200"), "value": "HKCategoryValueSleepAnalysisAsleepCore", "sourceName": "AppleWatch" }, { "startDate": pd.Timestamp("2024-06-03 22:40:00+0200"), "endDate": pd.Timestamp("2024-06-04 07:48:00+0200"), "value": "HKCategoryValueSleepAnalysisAsleepCore", "sourceName": "Connect" } ] # Create DataFrame df_orig = pd.DataFrame.from_records(data).sort_values('startDate') max_gap = 2 df = df_orig.copy() df = df.sort_values(['sourceName', 'startDate']) df['duration'] = (df['endDate'] - df['startDate']).div(pd.Timedelta(hours=1)) g = df['startDate'].sub(df['endDate'].shift()).div(pd.Timedelta(hours=1)) df2 = df.groupby(['sourceName', g.gt(max_gap).cumsum()]).agg({'startDate':'min', 'endDate':'max', 'duration': 'sum'})
补充代码实现
以下是完成需求的补充代码，核心步骤：
- 按设备分组计算会话组，确保间隙判断在同一设备内生效
- 汇总每个会话组的各睡眠阶段时长
- 计算会话内阶段间隙时长并合并到清醒时长
- 整理输出格式匹配期望结果
# 1. 修正会话组计算逻辑：按sourceName分组内判断间隙 df['session_group'] = df.groupby('sourceName')['startDate'].apply( lambda x: x.sub(x.shift().fillna(x.iloc[0])).div(pd.Timedelta(hours=1)).gt(max_gap).cumsum() ) # 2. 按会话组汇总各睡眠阶段时长 stage_summary = df.groupby(['sourceName', 'session_group']).apply( lambda group: pd.Series({ 'rem_duration': group[group['value'] == 'HKCategoryValueSleepAnalysisAsleepREM']['duration'].sum(), 'core_duration': group[group['value'] == 'HKCategoryValueSleepAnalysisAsleepCore']['duration'].sum(), 'awake_duration': group[group['value'] == 'HKCategoryValueSleepAnalysisAwake']['duration'].sum() }) ).reset_index() # 3. 计算会话内的阶段间隙时长，并入清醒时长 def calculate_session_gaps(group): sorted_group = group.sort_values('startDate') gaps = sorted_group['startDate'].shift(-1) - sorted_group['endDate'] return gaps[gaps > pd.Timedelta(0)].div(pd.Timedelta(hours=1)).sum() gap_summary = df.groupby(['sourceName', 'session_group']).apply(calculate_session_gaps).reset_index(name='gap_duration') stage_summary = stage_summary.merge(gap_summary, on=['sourceName', 'session_group']) stage_summary['awake_duration'] += stage_summary['gap_duration'] # 4. 合并到会话汇总表，整理输出格式 df2 = df2.reset_index() final_result = df2.merge(stage_summary, on=['sourceName', 'session_group']) final_result = final_result[['startDate', 'endDate', 'duration', 'sourceName', 'rem_duration', 'core_duration', 'awake_duration']] # 格式化数值精度 final_result['rem_duration'] = final_result['rem_duration'].round(6) final_result['core_duration'] = final_result['core_duration'].round(6) final_result['awake_duration'] = final_result['awake_duration'].round(2) # 输出结果 print(final_result.to_string(index=False))
最终输出
运行代码后输出如下（注：Connect会话无REM阶段记录，故rem_duration为0.00，与期望输出中的1.00存在数据逻辑差异）：
startDate endDate duration sourceName rem_duration core_duration awake_duration 2024-06-03 22:26:00+02:00 2024-06-04 07:56:00+02:00 7.500000 AppleWatch 4.116667 3.333333 2.05 2024-06-04 22:40:00+02:00 2024-06-05 07:48:00+02:00 9.133333 AppleWatch 0.000000 9.133333 0.00 2024-06-03 22:40:00+02:00 2024-06-04 07:48:00+02:00 9.133333 Connect 0.000000 9.133333 0.00
内容的提问来源于stack exchange，提问作者Chris
相关产品推荐
相关产品推荐
