Pandas按Id分组后识别Treatment周期取首尾记录计算分数降幅

对Id列执行groupby分组后，基于分类列Treatment的两个特定值（Active、Inactive）进一步拆分分组，按EncDate时间顺序识别完整治疗周期，提取每个周期的首尾行计算分数下降百分比。

注：已预先过滤DataFrame，仅保留Treatment为上述两个值的行，样例数据截图中EncDate取值与下方样例代码不一致。

样例数据代码 #

import pandas as pd


rng = pd.date_range('2015-02-24', periods=20, freq='M')

df = pd.DataFrame({
    'Id': [ '21','21','21','29','29','29','29','29','29','29','29','29','29','67','67','67','67','67','67','67'],
    'Score': [21,21,21,29,29,29,29,29,29,29,29,29,29,67,67,67,67,67,67,67],
    'Dx': ['F11','F11','F11','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72','F72'],
    'EncDate' :  rng, 
    'Treatment': ['Active','Active','Inactive','Inactive','Active','Active','Active','Active ','Inactive','Active','Active','Active ','Inactive','Active','Active','Active ','Inactive','Active','Active','Inactive'],
    'ProviderName': ["Doe, Kim","Doe, Kim","Doe, Kim","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Lee, Mei","Shah, Neha","Shah, Neha","Shah, Neha","Shah, Neha","Shah, Neha","Shah, Neha","Shah, Neha"]
})

分组规则 #

• 按Id分组后，依照EncDate时间升序识别Treatment周期：周期以Treatment = "Active"为起始标志，以同组后续出现的Treatment = "Inactive"为周期结束标志
• 示例校验：Id=29的记录共存在2次完整治疗周期，Id=67的记录同样存在2次完整治疗周期
• 输出要求：每个周期的第一条Active记录标记为周期首行，对应周期结束的Inactive记录标记为周期尾行，最终基于首尾行的Score值计算降幅

实现代码 #

注意样例数据中存在Treatment值带尾部空格的脏数据，第一步先做字段清洗避免匹配错误：

import pandas as pd

# 1. 数据预处理
# 清洗Treatment字段首尾空格，修正格式不一致问题
df['Treatment'] = df['Treatment'].str.strip()
# 按患者Id、就诊日期升序排序，保证时间顺序正确
df = df.sort_values(by=['Id', 'EncDate'], ascending=[True, True]).reset_index(drop=True)

# 2. 为每个治疗周期分配唯一编号
# 标记周期起始点：当前行是Active，且同患者上一条记录不是Active
df['is_cycle_start'] = (df['Treatment'] == 'Active') & (df.groupby('Id')['Treatment'].shift(1) != 'Active')
# 同患者下对起始点累加计数，生成周期ID
df['cycle_id'] = df.groupby('Id')['is_cycle_start'].cumsum()

# 3. 过滤无效周期：仅保留同时包含Active起始和Inactive结束的完整周期
valid_cycle = df.groupby(['Id', 'cycle_id'])['Treatment'].agg(
    lambda x: set(x) == {'Active', 'Inactive'}
).reset_index(name='is_valid')
df = df.merge(valid_cycle, on=['Id', 'cycle_id'], how='left')
df = df[df['is_valid']].reset_index(drop=True)

# 4. 提取每个周期的首尾记录
# 周期首行：每个周期第一条记录（即第一个Active就诊记录）
cycle_start = df.groupby(['Id', 'cycle_id']).first().reset_index()
cycle_start = cycle_start[cycle_start['Treatment'] == 'Active'][
    ['Id', 'cycle_id', 'EncDate', 'Score', 'Dx', 'ProviderName']
].rename(columns={'EncDate':'cycle_start_date', 'Score':'start_score'})

# 周期尾行：每个周期最后一条记录（即闭合周期的Inactive就诊记录）
cycle_end = df.groupby(['Id', 'cycle_id']).last().reset_index()
cycle_end = cycle_end[cycle_end['Treatment'] == 'Inactive'][
    ['Id', 'cycle_id', 'EncDate', 'Score']
].rename(columns={'EncDate':'cycle_end_date', 'Score':'end_score'})

# 5. 合并首尾记录，计算分数下降百分比
result = cycle_start.merge(cycle_end, on=['Id', 'cycle_id'], how='inner')
result['score_drop_pct'] = (result['start_score'] - result['end_score']) / result['start_score'] * 100

结果说明 #

最终输出的result表每行对应一个完整治疗周期，包含患者ID、周期编号、周期起止日期、起止分数、对应Dx和接诊医生信息，以及分数下降百分比。针对提供的样例数据，共识别出5个有效周期：Id=21对应1个周期，Id=29对应2个周期，Id=67对应2个周期，无匹配起始Active的孤立Inactive记录会被自动过滤。

内容的提问来源于stack exchange，提问作者Maja Smith