I am attempting to create a column that provides a sequential count which represents the day of dose in a medication course. The example dataset is represented by the following dataframe:
Discharge_ID <- c(1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2)
Medication_ID <- c(112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,121265,121265,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,112260,121105,121105)
Service_date <- c("11/15/2014","11/18/2014","11/20/2014","11/23/2014","11/30/2014","12/25/2014","12/26/2014","1/29/2015","2/8/2015","3/8/2015","3/10/2015","8/31/2014","9/1/2014","11/11/2014","11/12/2014","11/13/2014","11/14/2014","11/19/2014","11/20/2014","12/3/2014","12/7/2014","12/9/2014","12/10/2014","12/18/2014","12/19/2014","12/20/2014","12/21/2014","12/22/2014","1/3/2015","11/1/2014","11/2/2014")
example_df <- data.frame(Discharge_ID,Medication_ID,Service_date)
example_df
Discharge_ID Medication_ID Service_date
1 1 112260 11/15/2014
2 1 112260 11/18/2014
3 1 112260 11/20/2014
4 1 112260 11/23/2014
5 1 112260 11/30/2014
6 1 112260 12/25/2014
7 1 112260 12/26/2014
8 1 112260 1/29/2015
9 1 112260 2/8/2015
10 1 112260 3/8/2015
11 1 112260 3/10/2015
12 1 121265 8/31/2014
13 1 121265 9/1/2014
14 2 112260 11/11/2014
15 2 112260 11/12/2014
16 2 112260 11/13/2014
17 2 112260 11/14/2014
18 2 112260 11/19/2014
19 2 112260 11/20/2014
20 2 112260 12/3/2014
21 2 112260 12/7/2014
22 2 112260 12/9/2014
23 2 112260 12/10/2014
24 2 112260 12/18/2014
25 2 112260 12/19/2014
26 2 112260 12/20/2014
27 2 112260 12/21/2014
28 2 112260 12/22/2014
29 2 112260 1/3/2015
30 2 121105 11/1/2014
31 2 121105 11/2/2014
My goal is to put a column to the end of the data frame that counts the day of administration for each medication course per patient. Therefore, the count would reset for different medications and patients. However, an additional reset is applied to the count if there is a gap in therapy for greater than or equal to two days.
Below is an example of what I would like for this to appear as:
DayNum_byMed <- c(1,1,2,1,1,1,2,1,1,1,2,1,2,1,2,3,4,1,2,1,1,2,3,1,2,3,4,5,1,1,2)
example_df_goal <- data.frame(Discharge_ID,Medication_ID,Service_date,DayNum_byMed)
example_df_goal
Discharge_ID Medication_ID Service_date DayNum_byMed
1 1 112260 11/15/2014 1
2 1 112260 11/18/2014 1
3 1 112260 11/20/2014 2
4 1 112260 11/23/2014 1
5 1 112260 11/30/2014 1
6 1 112260 12/25/2014 1
7 1 112260 12/26/2014 2
8 1 112260 1/29/2015 1
9 1 112260 2/8/2015 1
10 1 112260 3/8/2015 1
11 1 112260 3/10/2015 2
12 1 121265 8/31/2014 1
13 1 121265 9/1/2014 2
14 2 112260 11/11/2014 1
15 2 112260 11/12/2014 2
16 2 112260 11/13/2014 3
17 2 112260 11/14/2014 4
18 2 112260 11/19/2014 1
19 2 112260 11/20/2014 2
20 2 112260 12/3/2014 1
21 2 112260 12/7/2014 1
22 2 112260 12/9/2014 2
23 2 112260 12/10/2014 3
24 2 112260 12/18/2014 1
25 2 112260 12/19/2014 2
26 2 112260 12/20/2014 3
27 2 112260 12/21/2014 4
28 2 112260 12/22/2014 5
29 2 112260 1/3/2015 1
30 2 121105 11/1/2014 1
31 2 121105 11/2/2014 2
I have set this up numerous times in Excel with IF(AND(...),...,...) statements but would like to reproduce this in R. I found other similar inquiries related to adding columns for sequential counts referencing ID numbers (like Create a sequential number (counter) for rows within each group of a dataframe); however, my requested count is reliant on more than one conditional.
Apologies in advance, first time asking a question and tried to use code fences but I cp'd the dataframe outputs anyways. Any feedback would be greatly appreciated.
