Story:
Many times, when a patient purchases a certain Drug, we define the first purchase of that drug as the index date. We then want to find out from that index date ( you can think as time 0), look back 12 months and 12 months forward and count how many purchases he has made in that 2 time period.
Requirement:
In my sample data, I have 2 patients and also includes index date.
- For all patients I want to find out how many Drug purchases has been made from:
- [-12 months to 0 months]
- (0 months to 12 months]
For patient 1 & 2 its 3 purchases & 3 purchases respectively 12 months back from the index date. Therefore the total count is 6 purchases.
- For all patients I want to find out how unique category of drugs they have purchased from:
- [-12 months to 0 months]
- (0 months to 12 months]
For patient 1 & 2, it's 2 & 3 unique counts in Cateogry_Level_1 therefore, the total sum is 5
In my real data, I've millions of patients
I'd be grateful, If I could have:
- Solution
- Solution based on tidyverse preferred
- Without writing a new function (if possible)
- Maybe entirely new solution design to get the same result (I don't maybe it's efficient to convert all dmy format to time 0, time 1 month from index, time 2 month from index..... something like that)
Thanks
library(tidyverse)
library(lubridate)
Sample_data <- tibble::tribble(
~PatientCode, ~Drugs, ~Date_of_Purchased, ~Index_Date, ~Category_Level_1, ~Category_Level_2,
1L, "Drug_A", "15/12/2012", "5/03/2015", "CARDIOVASCULAR SYSTEM", "CALCIUM CHANNEL BLOCKERS",
1L, "Drug_A", "3/05/2014", "5/03/2015", "CARDIOVASCULAR SYSTEM", "CALCIUM CHANNEL BLOCKERS",
1L, "Drug_C", "4/02/2015", "5/03/2015", "CARDIOVASCULAR SYSTEM", "LIPID MODIFYING AGENTS",
1L, "Drug_B", "5/03/2015", "5/03/2015", "DERMATOLOGICALS", "ANTIFUNGALS FOR DERMATOLOGICAL USE",
1L, "Drug_B", "6/04/2015", "5/03/2015", "DERMATOLOGICALS", "EMOLLIENTS AND PROTECTIVES",
1L, "Drug_D", "7/04/2016", "5/03/2015", "NERVOUS SYSTEM", "ANESTHETICS",
2L, "Drug_A", "15/12/2013", "30/04/2015", "CARDIOVASCULAR SYSTEM", "CALCIUM CHANNEL BLOCKERS",
2L, "Drug_A", "3/05/2014", "30/04/2015", "CARDIOVASCULAR SYSTEM", "CALCIUM CHANNEL BLOCKERS",
2L, "Drug_C", "4/02/2015", "30/04/2015", "NERVOUS SYSTEM", "LIPID MODIFYING AGENTS",
2L, "Drug_B", "30/04/2015", "30/04/2015", "DERMATOLOGICALS", "ANTIFUNGALS FOR DERMATOLOGICAL USE",
2L, "Drug_B", "6/04/2016", "30/04/2015", "DERMATOLOGICALS", "EMOLLIENTS AND PROTECTIVES",
2L, "Drug_D", "7/04/2016", "30/04/2015", "NERVOUS SYSTEM", "ANESTHETICS"
) %>%
mutate(Date_of_Purchased = dmy(Date_of_Purchased), Index_Date = dmy(Index_Date))
Exected_Result <- tibble::tribble(
~Followup_period, ~Category, ~Count,
"- 12 months to 0 months", "No_of_Purchases", 6L,
"0 months to 12 months", "No_of_Purchases", 3L,
"- 12 months to 0 months", "Category_Level_1_unique", 5L,
"0 months to 12 months", "Category_Level_1_unique", 3L,
"- 12 months to 0 months", "Category_Level_2_unique", 6L,
"0 months to 12 months", "Category_Level_2_unique", 3L
)