Dear Sir/Madam
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.
G
Repex here.
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.
Created on 2023-07-21 with reprex v2.0.2