I am trying to create a flag of pre and post based on the specific service 'Bedday-HTHC' and its date time. The outcome will be in the column 'flag'. This way, I will be able to report pre-hthc and post-hthc costs.
The key here is the ordering of the ordering of the DayOfStay in ascending.
Context = 'better at home' is a new healthcare model by discharging patients earlier to their own home for saving hospital beds and patient convenience
Now, I am doing evaluation of the program by looking it the financial impact.
I hope you will help me in this important endeavour.
Thank you very much.
hthc <- tibble::tribble(
~id, ~area, ~service, ~Quantity, ~DayOfStay, ~StartDateTime, ~EndDateTime, ~Total.Cost, ~flag,
"patient1", "A0160_CCU", "Bedday-CCU", 9.18333, 1L, "24/04/2022 0:00", "24/04/2022 9:10", 1366.438369, "pre_hthc",
"patient1", "A4402_CardiacSurgery", "Med Days-CSUR", 60, 1L, "24/04/2022 0:00", "24/04/2022 23:59", 647.2966689, "pre_hthc",
"patient1", "P0052_Chaplaincy Service", "Chaplaincy", 1, 1L, "24/04/2022 0:00", "24/04/2022 23:59", 1.872038507, "pre_hthc",
"patient1", "R0155_Catering", "CATRG-", 1, 1L, "24/04/2022 0:00", "24/04/2022 23:59", 46.76093351, "pre_hthc",
"patient1", "A0903_Ward 5 East", "Bedday-5E", 14.81667, 1L, "24/04/2022 9:11", "24/04/2022 23:59", 16.83546044, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 3, 1L, "24/04/2022 10:16", "24/04/2022 10:16", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 8, 1L, "24/04/2022 10:16", "24/04/2022 10:16", 53.34547284, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 3, 1L, "24/04/2022 10:16", "24/04/2022 10:16", 2.363352182, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 8, 1L, "24/04/2022 10:16", "24/04/2022 10:16", 1.010536795, "pre_hthc",
"patient1", "A0903_Ward 5 East", "Bedday-5E", 24, 2L, "25/04/2022 0:00", "25/04/2022 23:59", 27.27003102, "pre_hthc",
"patient1", "A4402_CardiacSurgery", "Med Days-CSUR", 60, 2L, "25/04/2022 0:00", "25/04/2022 23:59", 647.2966689, "pre_hthc",
"patient1", "P0052_Chaplaincy Service", "Chaplaincy", 1, 2L, "25/04/2022 0:00", "25/04/2022 23:59", 1.872038507, "pre_hthc",
"patient1", "R0155_Catering", "CATRG-", 1, 2L, "25/04/2022 0:00", "25/04/2022 23:59", 46.76093351, "pre_hthc",
"patient1", "Y0252_Path_Biochemistry", "Path-Y0252-66500", 1, 2L, "25/04/2022 0:00", "25/04/2022 0:00", 2.881859708, "pre_hthc",
"patient1", "Y0252_Path_Biochemistry", "Path-Y0252-66512", 1, 2L, "25/04/2022 0:00", "25/04/2022 0:00", 5.250511523, "pre_hthc",
"patient1", "Y0352_Path_Haemotology", "Path-Y0352-65070", 1, 2L, "25/04/2022 0:00", "25/04/2022 0:00", 14.07869458, "pre_hthc",
"patient1", "Y0352_Path_Haemotology", "Path-Y0352-65120", 1, 2L, "25/04/2022 0:00", "25/04/2022 0:00", 11.37337679, "pre_hthc",
"patient1", "Y1106_Imaging Department", "IMG-R.XRCHEST.AER2.NH", 1, 2L, "25/04/2022 15:33", "25/04/2022 15:33", 84.73569345, "pre_hthc",
"patient1", "A0903_Ward 5 East", "Bedday-5E", 24, 3L, "26/04/2022 0:00", "26/04/2022 23:59", 27.27003102, "pre_hthc",
"patient1", "A4402_CardiacSurgery", "Med Days-CSUR", 60, 3L, "26/04/2022 0:00", "26/04/2022 23:59", 647.2966689, "pre_hthc",
"patient1", "P0052_Chaplaincy Service", "Chaplaincy", 1, 3L, "26/04/2022 0:00", "26/04/2022 23:59", 1.872038507, "pre_hthc",
"patient1", "R0155_Catering", "CATRG-", 1, 3L, "26/04/2022 0:00", "26/04/2022 23:59", 46.76093351, "pre_hthc",
"patient1", "Y0252_Path_Biochemistry", "Path-Y0252-66500", 1, 3L, "26/04/2022 0:00", "26/04/2022 0:00", 2.881859708, "pre_hthc",
"patient1", "Y0252_Path_Biochemistry", "Path-Y0252-66512", 1, 3L, "26/04/2022 0:00", "26/04/2022 0:00", 5.250511523, "pre_hthc",
"patient1", "Y0352_Path_Haemotology", "Path-Y0352-65070", 1, 3L, "26/04/2022 0:00", "26/04/2022 0:00", 14.07869458, "pre_hthc",
"patient1", "N2702_OT", "OCCUP", 1, 3L, "26/04/2022 9:31", "26/04/2022 9:31", 231.3588805, "pre_hthc",
"patient1", "N2702_OT", "OCCUP", 1, 3L, "26/04/2022 10:05", "26/04/2022 10:05", 231.3588805, "pre_hthc",
"patient1", "N2602_Interpreters", "Interpreter-interpreter", 1, 3L, "26/04/2022 13:37", "26/04/2022 14:22", 128.6254261, "pre_hthc",
"patient1", "A0903_Ward 5 East", "Bedday-5E", 14.03333, 4L, "27/04/2022 0:00", "27/04/2022 14:01", 15.94538935, "pre_hthc",
"patient1", "A4402_CardiacSurgery", "Med Days-CSUR", 60, 4L, "27/04/2022 0:00", "27/04/2022 23:59", 647.2966689, "pre_hthc",
"patient1", "P0052_Chaplaincy Service", "Chaplaincy", 1, 4L, "27/04/2022 0:00", "27/04/2022 23:59", 1.872038507, "pre_hthc",
"patient1", "R0155_Catering", "CATRG-", 1, 4L, "27/04/2022 0:00", "27/04/2022 23:59", 46.76093351, "pre_hthc",
"patient1", "Y0252_Path_Biochemistry", "Path-Y0252-66500", 1, 4L, "27/04/2022 0:00", "27/04/2022 0:00", 2.881859708, "pre_hthc",
"patient1", "Y0252_Path_Biochemistry", "Path-Y0252-66512", 1, 4L, "27/04/2022 0:00", "27/04/2022 0:00", 5.250511523, "pre_hthc",
"patient1", "Y0352_Path_Haemotology", "Path-Y0352-65070", 1, 4L, "27/04/2022 0:00", "27/04/2022 0:00", 14.07869458, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 4, 4L, "27/04/2022 8:31", "27/04/2022 8:31", 53.34547284, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 4, 4L, "27/04/2022 8:31", "27/04/2022 8:31", 1.108330678, "pre_hthc",
"patient1", "Y1106_Imaging Department", "IMG-R.XRCHEST.AER1.NH", 1, 4L, "27/04/2022 9:52", "27/04/2022 9:52", 84.73569345, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 1, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 1, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 7, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 30, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 30, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 30, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 100, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 53.34547284, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 1, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 6.06322077, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 30, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 2.265558298, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 30, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 2.656733832, "pre_hthc",
"patient1", "N0502_Pharmacy", "Pharm-N", 30, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 5.655746256, "pre_hthc",
"patient1", "N0896_Pharmacy PBS", "Pharm-Y", 1, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 309.1159459, "pre_hthc",
"patient1", "N0896_Pharmacy PBS", "Pharm-Y", 7, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 0.125355775, "pre_hthc",
"patient1", "N0896_Pharmacy PBS", "Pharm-Y", 100, 4L, "27/04/2022 10:47", "27/04/2022 10:47", 1.720792916, "pre_hthc",
"patient1", "N0002_Pharmacy_S&W", "Pharm_SW", 28, 4L, "27/04/2022 12:08", "27/04/2022 12:08", 53.34547284, "pre_hthc",
"patient1", "N0896_Pharmacy PBS", "Pharm-Y", 28, 4L, "27/04/2022 12:08", "27/04/2022 12:08", 7.954393743, "pre_hthc",
"patient1", "N2602_Interpreters", "Interpreter-interpreter", 1, 4L, "27/04/2022 12:46", "27/04/2022 13:31", 128.6254261, "pre_hthc",
"patient1", "F0423_Better_at_home_cardiac", "Bedday-HTHC", 9.96667, 4L, "27/04/2022 14:02", "27/04/2022 23:59", 344.3686508, "post_hthc",
"patient1", "A4402_CardiacSurgery", "Med Days-CSUR", 60, 5L, "28/04/2022 0:00", "28/04/2022 23:59", 647.2966689, "post_hthc",
"patient1", "F0423_Better_at_home_cardiac", "Bedday-HTHC", 24, 5L, "28/04/2022 0:00", "28/04/2022 23:59", 829.2486477, "post_hthc",
"patient1", "P0052_Chaplaincy Service", "Chaplaincy", 1, 5L, "28/04/2022 0:00", "28/04/2022 23:59", 1.872038507, "post_hthc",
"patient1", "R0155_Catering", "CATRG-", 1, 5L, "28/04/2022 0:00", "28/04/2022 23:59", 46.76093351, "post_hthc",
"patient1", "A4402_CardiacSurgery", "Med Days-CSUR", 60, 6L, "29/04/2022 0:00", "29/04/2022 14:00", 647.2966689, "post_hthc",
"patient1", "F0423_Better_at_home_cardiac", "Bedday-HTHC", 14, 6L, "29/04/2022 0:00", "29/04/2022 14:00", 483.7283779, "post_hthc",
"patient1", "P0052_Chaplaincy Service", "Chaplaincy", 1, 6L, "29/04/2022 0:00", "29/04/2022 14:00", 1.872038507, "post_hthc",
"patient1", "R0155_Catering", "CATRG-", 1, 6L, "29/04/2022 0:00", "29/04/2022 14:00", 46.76093351, "post_hthc"
)
hthc
#> # A tibble: 64 × 9
#> id area service Quantity DayOfStay StartDateTime EndDateTime Total.Cost
#> <chr> <chr> <chr> <dbl> <int> <chr> <chr> <dbl>
#> 1 patien… A016… Bedday… 9.18 1 24/04/2022 0… 24/04/2022… 1366.
#> 2 patien… A440… Med Da… 60 1 24/04/2022 0… 24/04/2022… 647.
#> 3 patien… P005… Chapla… 1 1 24/04/2022 0… 24/04/2022… 1.87
#> 4 patien… R015… CATRG- 1 1 24/04/2022 0… 24/04/2022… 46.8
#> 5 patien… A090… Bedday… 14.8 1 24/04/2022 9… 24/04/2022… 16.8
#> 6 patien… N000… Pharm_… 3 1 24/04/2022 1… 24/04/2022… 53.3
#> 7 patien… N000… Pharm_… 8 1 24/04/2022 1… 24/04/2022… 53.3
#> 8 patien… N050… Pharm-N 3 1 24/04/2022 1… 24/04/2022… 2.36
#> 9 patien… N050… Pharm-N 8 1 24/04/2022 1… 24/04/2022… 1.01
#> 10 patien… A090… Bedday… 24 2 25/04/2022 0… 25/04/2022… 27.3
#> # ℹ 54 more rows
#> # ℹ 1 more variable: flag <chr>
library(lubridate)
#>
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#>
#> date, intersect, setdiff, union
hthc$StartDateTime <- dmy_hm(hthc$StartDateTime)
hthc$EndDateTime <- dmy_hm(hthc$EndDateTime)
hthc
#> # A tibble: 64 × 9
#> id area service Quantity DayOfStay StartDateTime
#> <chr> <chr> <chr> <dbl> <int> <dttm>
#> 1 patient1 A0160_CCU Bedday… 9.18 1 2022-04-24 00:00:00
#> 2 patient1 A4402_CardiacSurgery Med Da… 60 1 2022-04-24 00:00:00
#> 3 patient1 P0052_Chaplaincy Ser… Chapla… 1 1 2022-04-24 00:00:00
#> 4 patient1 R0155_Catering CATRG- 1 1 2022-04-24 00:00:00
#> 5 patient1 A0903_Ward 5 East Bedday… 14.8 1 2022-04-24 09:11:00
#> 6 patient1 N0002_Pharmacy_S&W Pharm_… 3 1 2022-04-24 10:16:00
#> 7 patient1 N0002_Pharmacy_S&W Pharm_… 8 1 2022-04-24 10:16:00
#> 8 patient1 N0502_Pharmacy Pharm-N 3 1 2022-04-24 10:16:00
#> 9 patient1 N0502_Pharmacy Pharm-N 8 1 2022-04-24 10:16:00
#> 10 patient1 A0903_Ward 5 East Bedday… 24 2 2022-04-25 00:00:00
#> # ℹ 54 more rows
#> # ℹ 3 more variables: EndDateTime <dttm>, Total.Cost <dbl>, flag <chr>
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
hthc %>%
group_by(flag) %>%
summarise(cost = sum(Total.Cost))
#> # A tibble: 2 × 2
#> flag cost
#> <chr> <dbl>
#> 1 post_hthc 3049.
#> 2 pre_hthc 6132.
Several records in your test data start after the start time of your baseline (Bedday_HTHC) and finish on or before the end time of your baseline. These appear to be handled inconsistently in your output or - at least, I haven't been able to figure out the algorithm you've used. Nonetheless, this should get you headed in the right direction. You should be able to modify what I've given you to give the result you want based on knowledge you have but I don't.
The fact that you have more than one patient does not add great complexity to the problem.
Create the input data
library(tidyverse)
inputData <- hthc %>% select(-flag)
Obtain the baseline of reference date for each patient:
> firstHTHC <- inputData %>%
+ group_by(id) %>%
+ filter(service == "Bedday-HTHC") %>%
+ head(1) %>% select(id, EndDateTime) %>%
+ rename(FirstHTHCTime = EndDateTime) %>%
+ ungroup()
> firstHTHC
# A tibble: 1 × 2
id FirstHTHCTime
<chr> <chr>
1 patient1 27/04/2022 23:59
Now merge this information with the input data frame and determine whether each record is "pre" or "post" baseline. As I've already said, the fact that your records all refer to periods rather than instants of time make the definition of "pre" and "post" slightly ambiguous.
outputData <- hthc %>%
left_join(firstHTHC, by = "id") %>%
mutate(new_flag = ifelse(FirstHTHCTime >= EndDateTime, "pre_hthc", "post_hthc"))
Now check for differences:
outputData%>% filter(flag!= new_flag)
# A tibble: 4 × 11
id area service Quantity DayOfStay StartDateTime EndDateTime Total.Cost flag FirstHTHCTime new_flag
<chr> <chr> <chr> <dbl> <int> <chr> <chr> <dbl> <chr> <chr> <chr>
1 patient1 N0002_Pharmacy_S&W Pharm_SW 4 4 27/04/2022 8:31 27/04/2022 8:31 53.3 pre_hthc 27/04/2022 23:59 post_hthc
2 patient1 N0502_Pharmacy Pharm-N 4 4 27/04/2022 8:31 27/04/2022 8:31 1.11 pre_hthc 27/04/2022 23:59 post_hthc
3 patient1 Y1106_Imaging Department IMG-R.XRCHEST.AER1.NH 1 4 27/04/2022 9:52 27/04/2022 9:52 84.7 pre_hthc 27/04/2022 23:59 post_hthc
4 patient1 F0423_Better_at_home_cardiac Bedday-HTHC 9.97 4 27/04/2022 14:02 27/04/2022 23:59 344. post_hthc 27/04/2022 23:59 pre_hthc
If it weren't for the ambiguity in the definitions of "pre" and "post", I would have written the derivation as
outputData <- inputData %>%
left_join(firstHTHC, by = "id") %>%
mutate(flag = ifelse(FirstHTHCTime >= EndDateTime, "pre_hthc", "post_hthc")) %>%
select(-FirstHTHCTime)
I hope that sets you on the right track.