1. Reading the data:

Table after selecting specific columns of interest
Selected columns from full data
MRN Item Name Frequency Quantity Issue Date/Dispence date Prescription Date
KM35.3502238749 MEROPENEM 1GM INJ THREE TIMES A DAY NA NA 2017-12-12 16:58:32
KM35.3580008305 MEROPENEM 500MG INJ EVERY EIGHT HOURS 1 2018-01-01 06:34:34 2018-01-01 04:48:43
KM35.3580008305 MEROPENEM 500MG INJ EVERY EIGHT HOURS 1 2018-01-03 14:08:35 2018-01-01 04:48:43
KM35.3580004657 MEROPENEM 1GM INJ EVERY EIGHT HOURS 6 2018-01-01 10:39:05 2018-01-01 09:29:13
KM35.3503368255 MEROPENEM 1GM INJ THREE TIMES A DAY 1 2018-01-01 13:50:11 2018-01-01 10:44:55
KM35.3503368255 MEROPENEM 1GM INJ THREE TIMES A DAY 1 2018-01-01 14:25:37 2018-01-01 10:44:55

2. Data Cleaning and processing:

  1. Redefining column names for clearer and easy to use names.
  2. Assigning the price of each entry depending on which strength of Meropenem used. Unit price of 37.991 SAR for Merpopenem 1 gm vial and 19.53 SAR for Merpopenem 0.5 gm.
  3. Replacing frequency of administration with equivelant numbers.
  4. Transforming medical number of patients from the form KM35.3512-345678 to 12345678 and Issue date to a numerical form for DOTs calculation later.
  5. Calculating the Expenditure in SAR for each entry.
Data table after processing
MRN Medication frequency quantity issue.date presc.date price Expenditures.SAR
2238749 MEROPENEM 1GM INJ 3 NA NA 2017-12-12 16:58:32 37.991 NA
80008305 MEROPENEM 500MG INJ 3 1 1514788474 2018-01-01 04:48:43 19.530 19.530
80008305 MEROPENEM 500MG INJ 3 1 1514988516 2018-01-01 04:48:43 19.530 19.530
80004657 MEROPENEM 1GM INJ 3 6 1514803146 2018-01-01 09:29:13 37.991 227.946
3368255 MEROPENEM 1GM INJ 3 1 1514814611 2018-01-01 10:44:55 37.991 37.991
3368255 MEROPENEM 1GM INJ 3 1 1514816737 2018-01-01 10:44:55 37.991 37.991
  1. Separating the previous table based on date to four tables one for each quarter of 2018 for easier quarter based calculations.
First quarter of 2018
MRN Medication frequency quantity issue.date presc.date price Expenditures.SAR
80008305 MEROPENEM 500MG INJ 3 1 1514788474 2018-01-01 04:48:43 19.530 19.530
80008305 MEROPENEM 500MG INJ 3 1 1514988516 2018-01-01 04:48:43 19.530 19.530
80004657 MEROPENEM 1GM INJ 3 6 1514803146 2018-01-01 09:29:13 37.991 227.946
3368255 MEROPENEM 1GM INJ 3 1 1514814611 2018-01-01 10:44:55 37.991 37.991
3368255 MEROPENEM 1GM INJ 3 1 1514816737 2018-01-01 10:44:55 37.991 37.991
3368255 MEROPENEM 1GM INJ 3 3 1514901570 2018-01-01 10:44:55 37.991 113.973
  1. calculating DDD,DOTs and Expenditures.SAR for each quarter:
    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\]
First quarter of 2018
1st Quarter 2nd Quarter 3rd Quarter 4th Quarter
DDD 1579.25 1506.667 1178.333 721.50
DOT 1534.00 1792.000 1536.000 869.00
Expenditures.SAR 132895.35 124776.663 100247.471 56460.71
  1. Patient days to calculate DDD,DOTs and Expenditure.SAR per 1000 patient days as per the following equations:
\[Measure_{quarter}*1000/patientday_{quarter}\]
Patient days per month for 2018
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Patient days 12071 11152 12125 12670 14256 12453 13298 13297 12694 13498 12617 12961
Meropenem DDD1000,DOT1000,Expenditures.SAR1000 for 2018
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

as per the Meropenem 1 gm and 500 mg DOT and DDD per 1000 patients day for 2018 the slope of the regression model is 0.9813059 which indicates stronge corelation between the Defined Daily Dose and Days Of Therapy.