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:

  1. Wipro Hospital information system report (HIS DRUG WISE PRESCRIPTION DETAILS).
  2. Inpatient days from Hospital medical records department.(Appendix 1).
  3. Antibiotics prices from hospitals ERP-app.(Appendix 2).

3. Preprocessing:

  1. The excel file used in this analysis is extracted from Wipro (HIS DRUG WISE PRESCRIPTION DETAILS).
  2. Report and saved as excel file with extension (.xlsx).
  3. Open the excel file(s) and select all active range in the file click button [Merge & Center] to unmerge all cells.
  4. 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:

  1. Reading the main excel file.
  2. Extracting the required columns(MRN,Item Name,Frequency,Quantity,Issue Date/Dispence date,Prescription Date) and transforming them to a suitable formate.
  3. Add Expenditures column depending on Item name strength if there are different strengths for the medication in question.
  4. Filtering the data on Date to get quarterly based tables which will be used to calculate DDD and DOTs for each quarter in 2018.

  5. 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\]

  6. 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.:

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

b. Colistin sulfomethate sodium 1 MU and 0.5 MU inj.:

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

c. Anidulafungin 100 mg/30 ml inj.:

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

d. Caspofungin 70 mg and 50 mg inj.:

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

e. Tigecycline 50 mg inj.:

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

f. Linezolid 600 mg inj.:

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

g. Vancomycin 500 mg inj.:

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

h. Piperacillin,Tazobactam 2.25 gm and 4.5 gm inj.:

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

Section B: Appendix:

1. Patient Days data:

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 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:

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