Pandas优化方案咨询：统计Infusion Complete后的Sleep事件数

我正在开发处理日常事件的代码，需要用Pandas统计特定事件（Infusion Complete）发生后的Sleep事件数量。示例中该事件发生后出现了2次Sleep事件，需在新列的所有行中填充数值2。我已实现需求，现希望获取更专业、更优化的实现思路。

target_event_phrases = 'Infusion Complete'

idx = data.eventdescription.str.contains(target_event_phrases).idxmax()
data.loc[idx:, 'infusion_status'] = target_event_phrases
data['complete_alarm_events'] = np.where((data['infusion_status'].str.contains(target_event_phrases))&(data['AlarmEvents'].str.contains('Sleep')), 1, 0)
data['no_of_alarm_events_after_completion'] = data['complete_alarm_events'].sum()-1

CREATE TABLE [dbo].[event_data](
    [Sequence_number] [bigint] NULL,
    [EventDescription] [nvarchar](500) NULL,
    [TIme] [datetime] NOT NULL
) ON [PRIMARY]

INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218394, N'Primary Bag selected', CAST(N'2020-10-12T10:41:21.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218395, N'Pump RUN', CAST(N'2020-10-12T10:41:29.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218406, N'Bag Near Empty alert', CAST(N'2020-10-12T11:11:30.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218407, N'Bag Near Empty ack''d', CAST(N'2020-10-12T11:11:33.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218408, N'Bag Near Empty Clr''d', CAST(N'2020-10-12T11:11:33.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218409, N'User STOP', CAST(N'2020-10-12T11:11:34.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218410, N'Pump RUN', CAST(N'2020-10-12T11:11:36.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218411, N'REVIEW key pressed', CAST(N'2020-10-12T11:11:41.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218412, N'User STOP', CAST(N'2020-10-12T11:11:45.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218413, N'Pump RUN', CAST(N'2020-10-12T11:11:46.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218424, N'Pump RUN - KVO', CAST(N'2020-10-12T11:41:33.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218423, N'Infusion Complete', CAST(N'2020-10-12T11:41:33.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218426, N'Infusion Complete Alarm!', CAST(N'2020-10-12T11:41:34.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218425, N'Pump rate updated:; - Rate 1 mL/hr', CAST(N'2020-10-12T11:41:34.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218427, N'User STOP', CAST(N'2020-10-12T11:41:37.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218428, N'Infusion Cmpl Clr''d', CAST(N'2020-10-12T11:41:38.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218429, N'Primary Bag Warning; No VTBI entered', CAST(N'2020-10-12T11:41:38.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218430, N'Unable to Run Alarm!', CAST(N'2020-10-12T11:41:38.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218431, N'Unable to Run Clr''d', CAST(N'2020-10-12T11:41:41.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218432, N'VTBI  - 30  mL', CAST(N'2020-10-12T11:41:43.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218434, N'Pump RUN', CAST(N'2020-10-12T11:41:44.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218433, N'VTBI Changed:; 30 mL', CAST(N'2020-10-12T11:41:44.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218436, N'User STOP', CAST(N'2020-10-12T11:47:23.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218437, N'Entered sleep mode', CAST(N'2020-10-12T11:47:24.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218438, N'Exited sleep mode', CAST(N'2020-10-12T11:47:33.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218440, N'Clamp inserted; Door opened', CAST(N'2020-10-12T11:47:34.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218439, N'Unload Set prompt', CAST(N'2020-10-12T11:47:34.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218442, N'Door closed', CAST(N'2020-10-12T11:47:36.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218444, N'Unload Set Clr''d', CAST(N'2020-10-12T11:47:37.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218443, N'Entered sleep mode', CAST(N'2020-10-12T11:47:37.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218445, N'Tube Stat: unloaded', CAST(N'2020-10-12T11:47:38.000' AS DateTime))
INSERT [dbo].[event_data] ([Sequence_number], [EventDescription], [TIme]) VALUES (3160250218525, N'Library recv''d: Network', CAST(N'2020-10-13T07:40:13.000' AS DateTime))

执行后提示：32 rows affected

核心优化点 #

• 消除不必要的中间列，减少内存占用与计算步骤
• 显式处理目标事件不存在的边界情况，避免运行时错误
• 用更直观的API替代idxmax()，提升代码可读性
• 加入缺失值处理逻辑，避免str.contains返回NaN干扰统计

优化代码 #

import pandas as pd

# 定义目标事件与Sleep事件匹配规则
TARGET_EVENT = 'Infusion Complete'
SLEEP_PATTERN = 'Entered sleep mode'

# 定位第一个目标事件的索引（处理缺失值）
target_mask = data['EventDescription'].str.contains(TARGET_EVENT, na=False)
target_idx = target_mask[target_mask].index.min() if target_mask.any() else None

# 统计目标事件后的Sleep事件数量
sleep_count = data.loc[target_idx:, 'EventDescription'].str.contains(SLEEP_PATTERN, na=False).sum() if target_idx is not None else 0

# 给全表添加结果列
data['no_of_alarm_events_after_completion'] = sleep_count

代码解释 #

1. 目标事件定位：通过布尔掩码筛选出所有目标事件，再取最小索引（即最早发生的目标事件），同时判断是否存在目标事件，避免后续索引错误。
2. Sleep事件统计：直接对目标事件之后的行进行匹配统计，无需创建中间标记列。
3. 结果填充：将统计结果直接赋值给新列，所有行统一填充该数值。
4. 缺失值处理：str.contains中加入na=False，确保缺失的EventDescription不会被误判为匹配。

内容的提问来源于stack exchange，提问作者Karthik Venkatraman