Days of therapy and Defined Daily dose for selected antibiotics in AFHSR for the year 2018
Mahmoud Elsheikh
1/20/2019
Section A: Report:
1. Synopsis:
The aim of report is to show the Days of therapy (DOTs) and Defined Daily Dose (DDDs) and quartrly expenditures for selected antibiotics in 2018. The antibiotics selected by antibiotic committee are:
1. Meropenem 1 gm and 500 mg inj.
2. Colistin sulfomethate sodium 1 MU and 0.5 MU inj.
3. Anidulafungin 100 mg/30 ml inj.
4. Caspofungin 70 mg and 50 mg inj.
5. Tigecycline 50 mg inj.
6. Linezolid 600 mg inj.
7. Vancomycin 500 mg inj.
8. Piperacillin/Tazobactam 2.25 gm and 4.5 gm inj.
2. Data sources:
- Wipro Hospital information system report (HIS DRUG WISE PRESCRIPTION DETAILS).
- Inpatient days from Hospital medical records department.(Appendix 1).
- Antibiotics prices from hospitals ERP-app.(Appendix 2).
3. Preprocessing:
- The excel file used in this analysis is extracted from Wipro (HIS DRUG WISE PRESCRIPTION DETAILS).
- Report and saved as excel file with extension (.xlsx).
- Open the excel file(s) and select all active range in the file click button [Merge & Center] to unmerge all cells.
- Select columns I,J and P. Right click then select Delete for each column. Click Ctrl+S or similar on other Operating systems to save the changes then close the file.
4. Data reading and manipulation:
- Reading the main excel file.
- Extracting the required columns(MRN,Item Name,Frequency,Quantity,Issue Date/Dispence date,Prescription Date) and transforming them to a suitable formate.
- Add Expenditures column depending on Item name strength if there are different strengths for the medication in question.
- Filtering the data on Date to get quarterly based tables which will be used to calculate DDD and DOTs for each quarter in 2018.
Calculate DDD, DOTs, Expenditure.SAR for each quarter. following these formulas:
For Defined Daily Dose \[DDD = \sum_{for all patients} Quantity_{dispensed}/frequency\]
For Days of Therapy
\[DOT = Count_{unique}(MRN * Issue.date)\]
For Quarter Expenditures
\[Expenditure.SAR = Quantity * Unit price\]Calculate DDD1000 (Defined Daily Dose per 1000 patient days) , DOT1000 (Days Of Therapy per 1000 patient days) and Expenditure.SAR1000 (Expenditure.SAR per 1000 patient days).
5. Results:
a. Meropenem 1 gm and 500 mg inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/Meropenem.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
items<-levels(as.factor(tab2$`Medication`))
tab2$price[tab2$Medication ==items[1]] <-37.991
tab2$price[tab2$Medication ==items[2]] <-19.53
tab2$price<-as.numeric(tab2$price)
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * price)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt, round(as.numeric(DDD1000),3), round(as.numeric(DOT1000),3), round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients days") +
ggtitle("Meropenem 1 gm and 500 mg DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Meropenem 1 gm and 500 mg DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Meropenem 1 gm and 500 mg expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 44.677 | 43.397 | 132895.35 |
| 2nd quarter | 38.261 | 45.506 | 124776.66 |
| 3rd quarter | 29.991 | 39.095 | 100247.47 |
| 4th quarter | 18.464 | 22.239 | 56460.71 |
print(g2)
print(g3)
print(g)
b. Colistin sulfomethate sodium 1 MU and 0.5 MU inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/COLISTIN.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
colnames(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
items<-levels(as.factor(tab2$`Medication`))
tab2$price[tab2$Medication ==items[1]] <-15.75*2
tab2$price[tab2$Medication ==items[2]] <-15.75
tab2$price<-as.numeric(tab2$price)
b<-tail(sort(tab1$Quantity),7)
tab2$quantity[tab2$quantity == b[1]]<-1
tab2$quantity[tab2$quantity == b[2]]<-1
tab2$quantity[tab2$quantity == b[3]]<-2
tab2$quantity[tab2$quantity == b[4]]<-2
tab2$quantity[tab2$quantity == b[5]]<-2
tab2$quantity[tab2$quantity == b[6]]<-2
tab2$quantity[tab2$quantity == b[7]]<-2
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab3 <- tab2 %>% mutate(Expenditures.SAR = quantity * price)
fstqrt <-
tab3 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab3 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab3 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab3 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
tab4<-rbind(DDD,DOT,Expenditures.SAR)
colnames(tab4)<-c("1st Quarter","2nd Quarter","3rd Quarter","4th Quarter")
patientdays<-c(12071,11152,12125,12670,14256,12453,13298,13297,12694,13498,12617,12961)
ptdys<-as.data.frame(patientdays)
rownames(ptdys)<-c("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
colnames(ptdys)<-c("Patient days")
ptdys<-t(ptdys)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt, round(as.numeric(DDD1000),3), round(as.numeric(DOT1000),3), round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients days") +
ggtitle("Colistin 1 MU and 0.5 MU DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 8,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Colistin 1 MU and 0.5 MU DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 8,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Colistin 1 MU and 0.5 MU expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 8,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 17.658 | 6.082 | 32114.25 |
| 2nd quarter | 18.288 | 8.279 | 37563.75 |
| 3rd quarter | 11.929 | 4.200 | 24774.75 |
| 4th quarter | 19.995 | 4.350 | 35453.25 |
print(g2)
print(g3)
print(g)
c. Anidulafungin 100 mg/30 ml inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/Anidulafungin.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * 724.5)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt, round(as.numeric(DDD1000),3), round(as.numeric(DOT1000),3), round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients day") +
ggtitle("Anidulafungin 100 mg DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value,3),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Anidulafungin 100 mg DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Anidulafungin 100 mg expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 0.509 | 0.424 | 13041.0 |
| 2nd quarter | 0.762 | 0.457 | 21735.0 |
| 3rd quarter | 1.247 | 0.764 | 35500.5 |
| 4th quarter | 0.665 | 0.563 | 18837.0 |
print(g2)
print(g3)
print(g)
d. Caspofungin 70 mg and 50 mg inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/Caspofungin.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
items<-levels(as.factor(tab2$`Medication`))
tab2$price[tab2$Medication ==items[1]] <-1928.26
tab2$price[tab2$Medication ==items[2]] <-1975.74
tab2$price<-as.numeric(tab2$price)
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * price)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt,round(as.numeric(DDD1000),3),round(as.numeric(DOT1000),3),round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se =FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients day") +
ggtitle("Caspofungin 50 and 70 mg DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value,3),
colour = Measure
),
size = 4,
alpha = 0.5) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Caspofungin 50 and 70 mg DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Caspofungin 50 and 70 mg expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 0.764 | 0.255 | 52205.46 |
| 2nd quarter | 0.203 | 0.203 | 15568.52 |
| 3rd quarter | 0.764 | 0.764 | 57990.24 |
| 4th quarter | 0.486 | 0.179 | 36684.42 |
print(g2)
print(g3)
print(g)
e. Tigecycline 50 mg inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/tigecycline.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * 187.5)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt, round(as.numeric(DDD1000),3), round(as.numeric(DOT1000),3), round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients day") +
ggtitle("Tigecycline 50 mg DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Tigecycline 50 mg DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Tigecycline 50 mg expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 3.862 | 3.480 | 46875.0 |
| 2nd quarter | 4.177 | 4.673 | 58312.5 |
| 3rd quarter | 1.514 | 1.604 | 21187.5 |
| 4th quarter | 2.175 | 1.663 | 30375.0 |
print(g2)
print(g3)
print(g)
f. Linezolid 600 mg inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/linezolid.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * 215.48)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt, round(as.numeric(DDD1000),3), round(as.numeric(DOT1000),3), round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black", se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients day") +
ggtitle("Linezolid 600 mg inj. DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Linezolid 600 mg inj DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Linezolid 600 mg inj expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 1.188 | 2.603 | 21763.48 |
| 2nd quarter | 1.807 | 2.539 | 33399.40 |
| 3rd quarter | 1.226 | 1.604 | 24349.24 |
| 4th quarter | 0.883 | 1.382 | 17669.36 |
print(g2)
print(g3)
print(g)
g. Vancomycin 500 mg inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/VANCOMYCIN 500MG INJ.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * 215.48)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt, round(as.numeric(DDD1000),3), round(as.numeric(DOT1000),3), round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients day") +
ggtitle("Vancomycin 500 mg inj. DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Vancomycin 500 mg inj. DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Vancomycin 500 mg inj. expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 30.457 | 22.123 | 414368.0 |
| 2nd quarter | 27.561 | 21.103 | 386355.6 |
| 3rd quarter | 22.375 | 17.155 | 299732.7 |
| 4th quarter | 26.387 | 20.038 | 364376.7 |
print(g2)
print(g3)
print(g)
h. Piperacillin,Tazobactam 2.25 gm and 4.5 gm inj.:
filePath <-
"/Users/mahmoudelsheikh/Google Drive/Coursera Data analysis/Courseraworkingspace/Antibiotic/tazocin.xlsx"
tab1 <-
readxl::read_xlsx(filePath,
skip = 5)
tab2 <-
tab1 %>% select(MRN,
`Item Name`,
Frequency,
Quantity,
`Issue Date/Dispence date`,
`Prescription Date`)
names(tab2) <-
c("MRN",
"Medication",
"frequency",
"quantity",
"issue.date",
"presc.date")
items<-levels(as.factor(tab2$`Medication`))
tab2$price[tab2$Medication ==items[1]] <-14.69
tab2$price[tab2$Medication ==items[2]] <-20.77
tab2$price<-as.numeric(tab2$price)
tab2$frequency <-
str_replace_all(
tab2$frequency,
c(
"EVERY EIGHT HOURS" = "3",
"EVERY SIX HOURS" = "4",
"EVERY TWELVE HOURS" = "2",
"EVERY TWENTY FOUR HOURS" = "1",
"IN THE EVENING" = "1",
"ONCE DAILY" = "1",
"STAT" = "1",
"THREE TIMES A DAY" = "3",
"TWICE A DAY" = "2",
"THREE DAYS A WEEK" = "1",
"FOUR DAYS A WEEK" = "1",
"FOUR TIMES A DAY" = "4",
"EVERY FOUR HOURS" = "6",
"EVERY OTHER DAY" = "1",
"EVERY SIXTEEN HOURS" = "1",
"EVERY 36 HOURS" = "1",
"EVERY THREE DAYS" = "1",
"EVERY THREE WEEKS" = "1",
"EVERY TWO WEEKS" = "1",
"MIDDAY" = "1",
"ONE DAY A WEEK" = "1",
"TWO DAYS A WEEK" = "1"
)
)
tab2$frequency <- as.numeric(tab2$frequency)
tab2$issue.date <- as.numeric(tab2$issue.date)
tab2$MRN <- gsub("KM35.35", "", tab2$MRN)
tab2$MRN <- as.numeric(tab2$MRN)
tab2 <- tab2 %>% mutate(Expenditures.SAR = quantity * price)
fstqrt <-
tab2 %>% filter(`presc.date` >= "2018-01-01" &
`presc.date` <= "2018-03-31")
secqrt <-
tab2 %>% filter(`presc.date` >= "2018-04-01" &
`presc.date` <= "2018-06-30")
trdqrt <-
tab2 %>% filter(`presc.date` >= "2018-07-01" &
`presc.date` <= "2018-09-30")
frtqrt <-
tab2 %>% filter(`presc.date` >= "2018-10-01" &
`presc.date` <= "2018-12-31")
DDD1 <-
sum((fstqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD2 <-
sum((secqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD3 <-
sum((trdqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DDD4 <-
sum((frtqrt %>% mutate(DDD = quantity / frequency))$DDD, na.rm = TRUE)
DOT1 <- length(unique(fstqrt$MRN * fstqrt$issue.date))
DOT2 <- length(unique(secqrt$MRN * secqrt$issue.date))
DOT3 <- length(unique(trdqrt$MRN * trdqrt$issue.date))
DOT4 <- length(unique(frtqrt$MRN * frtqrt$issue.date))
Exp1 <- sum(fstqrt$Expenditures.SAR, na.rm = TRUE)
Exp2 <- sum(secqrt$Expenditures.SAR, na.rm = TRUE)
Exp3 <- sum(trdqrt$Expenditures.SAR, na.rm = TRUE)
Exp4 <- sum(frtqrt$Expenditures.SAR, na.rm = TRUE)
DDD <- c(DDD1, DDD2, DDD3, DDD4)
DOT <- c(DOT1, DOT2, DOT3, DOT4)
Expenditures.SAR <- c(Exp1, Exp2, Exp3, Exp4)
inpatientdays <-
c(12071,
11152,
12125,
12670,
14256,
12453,
13298,
13297,
12694,
13498,
12617,
12961)
qrtinptdays <-
c(
sum(inpatientdays[1:3]),
sum(inpatientdays[4:6]),
sum(inpatientdays[7:9]),
sum(inpatientdays[10:12])
)
DDD1000 <- DDD * 1000 / qrtinptdays
DOT1000 <- DOT * 1000 / qrtinptdays
Exp1000 <- Expenditures.SAR * 1000 / qrtinptdays
Qrt <- c("1st quarter", "2nd quarter", "3rd quarter", "4th quarter")
result <- as.data.frame(cbind(Qrt,round(as.numeric(DDD1000),3),round(as.numeric(DOT1000),3),round(as.numeric(Expenditures.SAR),3)))
colnames(result)<-c("Quarter 2018","DDD1000","DOT1000","Expenditures.SAR1000")
result$DDD1000<-as.numeric(as.character(result$DDD1000))
result$DOT1000<-as.numeric(as.character(result$DOT1000))
result$Expenditures.SAR1000<-as.numeric(as.character(result$Expenditures.SAR1000))
resultm <- melt(result, id = c("Quarter 2018"))
colnames(resultm) <- c("Quarter 2018", "Measure", "Value")
resultm$Value <- as.numeric(resultm$Value)
g <-
ggplot(result, aes(x = DDD1000, y = DOT1000))
g <- g + geom_point(size = 4, color = "Blue")
g <- g + geom_smooth(method = "lm", colour = "black",se = FALSE)
g <-
g + xlab("Daily Defined Dose per 1000 patients day") + ylab("Days of Therapy per 1000 patients day") +
ggtitle("Piperacillin/tazobactam 2.25 gm and 4.5 gm DOT and DDD \n per 1000 patients day for 2018")
g <-
g + theme_classic() + theme(plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
))
g <-
g + theme(axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
))
g2 <-
ggplot(resultm[1:8, ]) + geom_point(aes(
x = `Quarter 2018`,
y = round(Value),
colour = Measure
),
size = 4,
alpha = 0.7) + ylab("DOT and DDD per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Piperacillin/tazobactam 2.25 gm and 4.5 gm DOT and DDD \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
g3 <-
ggplot(resultm[9:12, ]) + geom_point(aes(x = `Quarter 2018`, y = Value, colour = Measure),
size = 4,
alpha = 0.7) +
geom_path(aes(x = `Quarter 2018`, y = Value, group = 1),color ="Pink",size = 2) +
ylab("Expenditure per 1000 patients day") + xlab("Quarter 2018") +
ggtitle("Piperacillin/tazobactam 2.25 gm and 4.5 gm expenditure \n per 1000 patients day for 2018") +
theme_classic() + theme(
plot.title = element_text(
color = "Black",
face = "bold",
size = 9,
hjust = 0
),
axis.title = element_text(
color = "black",
face = "italic",
size = 7,
hjust = 0
),axis.text.x = element_text(angle = 45, hjust = 1)
)
kable(result)| Quarter 2018 | DDD1000 | DOT1000 | Expenditures.SAR1000 |
|---|---|---|---|
| 1st quarter | 13.565 | 15.871 | 27249.11 |
| 2nd quarter | 11.675 | 17.928 | 27527.73 |
| 3rd quarter | 16.676 | 23.187 | 39500.23 |
| 4th quarter | 11.640 | 17.402 | 28607.97 |
print(g2)
print(g3)
print(g)
Section B: Appendix:
1. Patient Days data:
patientdays<-c(12071,11152,12125,12670,14256,12453,13298,13297,12694,13498,12617,12961)
ptdys<-as.data.frame(patientdays)
rownames(ptdys)<-c("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
colnames(ptdys)<-c("Patient days")
kable(ptdys,align = "c",caption = "Patient days per month for 2018")| Patient days | |
|---|---|
| Jan | 12071 |
| Feb | 11152 |
| Mar | 12125 |
| Apr | 12670 |
| May | 14256 |
| Jun | 12453 |
| Jul | 13298 |
| Aug | 13297 |
| Sep | 12694 |
| Oct | 13498 |
| Nov | 12617 |
| Dec | 12961 |
2. Antibiotics prices:
Antibiotic<-c("Meropenem 1gm","Meropenem 500 mg","Colistin 0.5 MU","Anidulafungin","Caspofungin 70 mg","Caspofungin 50 mg","Tigecyclin 50 mg","Linezolide 600 mg","Vancomycin 500 mg","Tazocin 2.25 gm","Tazocin 4.5 gm")
Price<-c(19.53,37.991,15.75,724.5,1975.74,1928.26,187.5,215.48,5.5916,14.69,20.77)
Pricetbl<-as.data.frame(cbind(Antibiotic,Price))
colnames(Pricetbl)<-c("Antibiotic", "Unit price in SAR")
kable(Pricetbl,caption = "Antibiotic purchasing prices in 2018")| Antibiotic | Unit price in SAR |
|---|---|
| Meropenem 1gm | 19.53 |
| Meropenem 500 mg | 37.991 |
| Colistin 0.5 MU | 15.75 |
| Anidulafungin | 724.5 |
| Caspofungin 70 mg | 1975.74 |
| Caspofungin 50 mg | 1928.26 |
| Tigecyclin 50 mg | 187.5 |
| Linezolide 600 mg | 215.48 |
| Vancomycin 500 mg | 5.5916 |
| Tazocin 2.25 gm | 14.69 |
| Tazocin 4.5 gm | 20.77 |
3. Data entries count for each antibiotic:
Antibiotic2<-c("Meropenem 1gm/500mg","Colistin 0.5 MU","Anidulafungin","Caspofungin 70 mg/50 mg","Tigecyclin 50 mg","Linezolide 600 mg","Vancomycin 500 mg","Tazocin 2.25 gm /4.5 gm")
Entries<-c(5949,896,92,54,461,314,3316,2952)
counttbl<-as.data.frame(cbind(Antibiotic2,Entries))
colnames(counttbl)<-c("Antibiotic", "Number of entries per antibiotic")
kable(counttbl,caption = "Antibiotic entry count in 2018")| Antibiotic | Number of entries per antibiotic |
|---|---|
| Meropenem 1gm/500mg | 5949 |
| Colistin 0.5 MU | 896 |
| Anidulafungin | 92 |
| Caspofungin 70 mg/50 mg | 54 |
| Tigecyclin 50 mg | 461 |
| Linezolide 600 mg | 314 |
| Vancomycin 500 mg | 3316 |
| Tazocin 2.25 gm /4.5 gm | 2952 |