I am working on a code where I have a few events on a daily basis. What I need to determine is the count of sleep events after a specific event (infusion completion) in pandas. In the below screenshot, after the infusion is complete, I have two sleep events happened. So in the new column, it has to come 2 for all the rows.
I was able to get the desired answer that I need however wanted to know any other approaches that I can use so that the code would look more professional and optimized.
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
Link for the schema and data Data