1.Load data and libraries

library(readxl)
library(tidyverse)

## ── Attaching packages ────────────────────────────────────────────────────── tidyverse 1.2.1 ──

## ✔ ggplot2 3.2.1     ✔ purrr   0.3.3
## ✔ tibble  2.1.3     ✔ dplyr   0.8.3
## ✔ tidyr   1.0.0     ✔ stringr 1.4.0
## ✔ readr   1.3.1     ✔ forcats 0.4.0

## ── Conflicts ───────────────────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()

GCN <- read_excel("~/Downloads/Dispense Usage Transactions Top 50 GCN Seq Nbrs Week 201836.xlsx")

2.Create a new table with the mean of the variable COGS_PRC_MTRC.Grouped by NDC_DESC.

new <- GCN  %>%
  group_by(NDC_DESC) %>%
  summarise(Mean_Price = mean(COGS_PRC_MTRC,na.rm = TRUE))

head(new)

## # A tibble: 6 x 2
##   NDC_DESC                           Mean_Price
##   <chr>                                   <dbl>
## 1 "\"ADVATE 1,801-2,400 UNIT VIAL\""     791.  
## 2 "\"ADVATE 801-1,200 UNIT VIAL\""         1.55
## 3 "\"ADYNOVATE 1,500 UNIT VIAL\""       2447.  
## 4 "\"HELIXATE FS 1,000 UNIT VIAL\""      968.  
## 5 "\"HELIXATE FS 2,000 UNIT VIAL\""        1.44
## 6 "\"KOGENATE FS 1,000 UNITS VIAL\""       1.55

3. Join the table created in question 2 with the data loaded in question 1. Join by NDC_DESC.

You can write some comment if needed

GCN_Join <- inner_join(GCN, new,
                      by=c('NDC_DESC'='NDC_DESC'))

head(GCN_Join)

## # A tibble: 6 x 44
##   WK_NBR WK_BEGIN_DTE WK_END_DTE NCPDP_TOKEN PHRMCY_NAME BUY_GRP_NAM NDC  
##   <chr>  <chr>        <chr>      <chr>       <chr>       <chr>       <chr>
## 1 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0007…
## 2 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0017…
## 3 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       1249…
## 4 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0000…
## 5 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0040…
## 6 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0059…
## # … with 37 more variables: NDC_DESC <chr>, BRX_GRX_CD <chr>,
## #   NDC_QTY_MTRC <dbl>, NDC_PKG_SZ <dbl>, NDC_CASE_PK_QTY <dbl>, MFG_NAM <chr>,
## #   GCNSEQ_NBR <chr>, GCNSEQ_DESC <chr>, GCNSEQ_RTE_OF_ADMIN_DESC <chr>,
## #   GCNSEQ_DSG_FORM_CD <chr>, GCNSEQ_STR_DESC <chr>, DSPN_QTY_NDC_1WK <dbl>,
## #   DSPN_QTY_NDC_4WKS <dbl>, DSPN_QTY_NDC_13WKS <dbl>,
## #   DSPN_QTY_NDC_26WKS <dbl>, DSPN_QTY_GCN_1WK <dbl>, DSPN_QTY_GCN_4WKS <dbl>,
## #   DSPN_QTY_GCN_13WKS <dbl>, DSPN_QTY_GCN_26WKS <dbl>,
## #   PURCH_QTY_NDC_1WK_MTRC <dbl>, PURCH_QTY_NDC_4WKS_MTRC <dbl>,
## #   PURCH_QTY_NDC_13WKS_MTRC <dbl>, PURCH_QTY_NDC_26WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_1WK_MTRC <dbl>, PURCH_QTY_GCN_4WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_13WKS_MTRC <dbl>, PURCH_QTY_GCN_26WKS_MTRC <dbl>,
## #   COGS_PRC_MTRC <dbl>, DSPN_COGS_NDC_1WK <dbl>, DSPN_COGS_NDC_4WKS <dbl>,
## #   DSPN_COGS_NDC_13WKS <dbl>, DSPN_COGS_NDC_26WKS <dbl>,
## #   DSPN_COGS_GCN_1WK <dbl>, DSPN_COGS_GCN_4WKS <dbl>,
## #   DSPN_COGS_GCN_13WKS <dbl>, DSPN_COGS_GCN_26WKS <dbl>, Mean_Price <dbl>

4 From the table created in question 3, replace NA’s in COGS_PRC_MTRC with the mean of COGS_PRC_MTRC.

 GCN_Join <- GCN_Join %>% 
  mutate (COGS_PRC_MTRC = ifelse (is.na (COGS_PRC_MTRC) == T , Mean_Price , COGS_PRC_MTRC))

head (GCN_Join)

## # A tibble: 6 x 44
##   WK_NBR WK_BEGIN_DTE WK_END_DTE NCPDP_TOKEN PHRMCY_NAME BUY_GRP_NAM NDC  
##   <chr>  <chr>        <chr>      <chr>       <chr>       <chr>       <chr>
## 1 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0007…
## 2 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0017…
## 3 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       1249…
## 4 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0000…
## 5 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0040…
## 6 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0059…
## # … with 37 more variables: NDC_DESC <chr>, BRX_GRX_CD <chr>,
## #   NDC_QTY_MTRC <dbl>, NDC_PKG_SZ <dbl>, NDC_CASE_PK_QTY <dbl>, MFG_NAM <chr>,
## #   GCNSEQ_NBR <chr>, GCNSEQ_DESC <chr>, GCNSEQ_RTE_OF_ADMIN_DESC <chr>,
## #   GCNSEQ_DSG_FORM_CD <chr>, GCNSEQ_STR_DESC <chr>, DSPN_QTY_NDC_1WK <dbl>,
## #   DSPN_QTY_NDC_4WKS <dbl>, DSPN_QTY_NDC_13WKS <dbl>,
## #   DSPN_QTY_NDC_26WKS <dbl>, DSPN_QTY_GCN_1WK <dbl>, DSPN_QTY_GCN_4WKS <dbl>,
## #   DSPN_QTY_GCN_13WKS <dbl>, DSPN_QTY_GCN_26WKS <dbl>,
## #   PURCH_QTY_NDC_1WK_MTRC <dbl>, PURCH_QTY_NDC_4WKS_MTRC <dbl>,
## #   PURCH_QTY_NDC_13WKS_MTRC <dbl>, PURCH_QTY_NDC_26WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_1WK_MTRC <dbl>, PURCH_QTY_GCN_4WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_13WKS_MTRC <dbl>, PURCH_QTY_GCN_26WKS_MTRC <dbl>,
## #   COGS_PRC_MTRC <dbl>, DSPN_COGS_NDC_1WK <dbl>, DSPN_COGS_NDC_4WKS <dbl>,
## #   DSPN_COGS_NDC_13WKS <dbl>, DSPN_COGS_NDC_26WKS <dbl>,
## #   DSPN_COGS_GCN_1WK <dbl>, DSPN_COGS_GCN_4WKS <dbl>,
## #   DSPN_COGS_GCN_13WKS <dbl>, DSPN_COGS_GCN_26WKS <dbl>, Mean_Price <dbl>

5 From the table created in question 4, remove all records where there are negative values in any of the following fields: PURCH_QTY_NDC_1WK_MTRC, PURCH_QTY_NDC_4WK_MTRC, PURCH_QTY_NDC_13WK_MTRC , and PURCH_QTY_NDC_26WK_MTRC

GCN_POS <- GCN_Join %>% filter(PURCH_QTY_NDC_1WK_MTRC> 0,
                          PURCH_QTY_NDC_4WKS_MTRC > 0,
                          PURCH_QTY_NDC_13WKS_MTRC >0,
                       PURCH_QTY_NDC_26WKS_MTRC> 0)
select(GCN_POS, 27:30) %>% 
  head()

## # A tibble: 6 x 4
##   PURCH_QTY_NDC_1WK_… PURCH_QTY_NDC_4WK… PURCH_QTY_NDC_13WK… PURCH_QTY_NDC_26WK…
##                 <dbl>              <dbl>               <dbl>               <dbl>
## 1               300                540                  840                 1560
## 2               540               1350                 4110                 8610
## 3                30                 60                  210                  510
## 4                90                270                  720                 1440
## 5                18                 63                  135                  216
## 6                10.2               61.2                224.                 510

6 Create four leakage variables, one for each time interval. Leakage = dispense - purchase quantities.

  GCN_POS_LK <- GCN_POS %>% 
 mutate(Leakage_NDC_1WK = DSPN_QTY_NDC_1WK - PURCH_QTY_NDC_1WK_MTRC,
         Leakage_NDC_4WKS = DSPN_QTY_NDC_4WKS - PURCH_QTY_NDC_4WKS_MTRC,
     Leakage_NDC_13WKS = DSPN_QTY_NDC_13WKS - PURCH_QTY_NDC_13WKS_MTRC,
   Leakage_NDC_26WKS = DSPN_QTY_NDC_26WKS - PURCH_QTY_NDC_26WKS_MTRC)
   select(GCN_POS_LK, Leakage_NDC_1WK, Leakage_NDC_4WKS, Leakage_NDC_13WKS,Leakage_NDC_26WKS)

## # A tibble: 33,080 x 4
##    Leakage_NDC_1WK Leakage_NDC_4WKS Leakage_NDC_13WKS Leakage_NDC_26WKS
##              <dbl>            <dbl>             <dbl>             <dbl>
##  1            -300           -300                 0                60  
##  2            -335           -252              -411              -399  
##  3             -30              0                30                30  
##  4             -90            -60               111                81  
##  5               0             -9                 9                 9  
##  6               0            -20.4               0                30.6
##  7            -260            -88                35              -407  
##  8              17            -17               -25.5              17  
##  9            -300            120                 0                 0  
## 10             -10             10               -10               -20  
## # … with 33,070 more rows

   GCN_POS_LK_POS <- GCN_POS_LK %>%
    filter(Leakage_NDC_1WK > 0,
    Leakage_NDC_4WKS > 0,
    Leakage_NDC_13WKS > 0,
    Leakage_NDC_26WKS >0)

select(GCN_POS_LK_POS, Leakage_NDC_1WK, Leakage_NDC_4WKS, Leakage_NDC_13WKS,Leakage_NDC_26WKS)

## # A tibble: 3,612 x 4
##    Leakage_NDC_1WK Leakage_NDC_4WKS Leakage_NDC_13WKS Leakage_NDC_26WKS
##              <dbl>            <dbl>             <dbl>             <dbl>
##  1            60               60                60               360  
##  2            60               90                45               115  
##  3            17               17                17                25.5
##  4           180              180               120               180  
##  5             9                9                 9                 9  
##  6            15               36                81                66  
##  7            30.6             40.8              30.6              40.8
##  8            90               90               270               342  
##  9            30              120                59                74  
## 10            42.5             25.5              42.5               8.5
## # … with 3,602 more rows

   head (GCN_Join)

## # A tibble: 6 x 44
##   WK_NBR WK_BEGIN_DTE WK_END_DTE NCPDP_TOKEN PHRMCY_NAME BUY_GRP_NAM NDC  
##   <chr>  <chr>        <chr>      <chr>       <chr>       <chr>       <chr>
## 1 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0007…
## 2 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0017…
## 3 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       1249…
## 4 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0000…
## 5 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0040…
## 6 201836 2018-09-02   2018-09-08 4410677998… PHARMACY 1… SPURS       0059…
## # … with 37 more variables: NDC_DESC <chr>, BRX_GRX_CD <chr>,
## #   NDC_QTY_MTRC <dbl>, NDC_PKG_SZ <dbl>, NDC_CASE_PK_QTY <dbl>, MFG_NAM <chr>,
## #   GCNSEQ_NBR <chr>, GCNSEQ_DESC <chr>, GCNSEQ_RTE_OF_ADMIN_DESC <chr>,
## #   GCNSEQ_DSG_FORM_CD <chr>, GCNSEQ_STR_DESC <chr>, DSPN_QTY_NDC_1WK <dbl>,
## #   DSPN_QTY_NDC_4WKS <dbl>, DSPN_QTY_NDC_13WKS <dbl>,
## #   DSPN_QTY_NDC_26WKS <dbl>, DSPN_QTY_GCN_1WK <dbl>, DSPN_QTY_GCN_4WKS <dbl>,
## #   DSPN_QTY_GCN_13WKS <dbl>, DSPN_QTY_GCN_26WKS <dbl>,
## #   PURCH_QTY_NDC_1WK_MTRC <dbl>, PURCH_QTY_NDC_4WKS_MTRC <dbl>,
## #   PURCH_QTY_NDC_13WKS_MTRC <dbl>, PURCH_QTY_NDC_26WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_1WK_MTRC <dbl>, PURCH_QTY_GCN_4WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_13WKS_MTRC <dbl>, PURCH_QTY_GCN_26WKS_MTRC <dbl>,
## #   COGS_PRC_MTRC <dbl>, DSPN_COGS_NDC_1WK <dbl>, DSPN_COGS_NDC_4WKS <dbl>,
## #   DSPN_COGS_NDC_13WKS <dbl>, DSPN_COGS_NDC_26WKS <dbl>,
## #   DSPN_COGS_GCN_1WK <dbl>, DSPN_COGS_GCN_4WKS <dbl>,
## #   DSPN_COGS_GCN_13WKS <dbl>, DSPN_COGS_GCN_26WKS <dbl>, Mean_Price <dbl>

7 Standardize to a per week metric the four leakage variables, note that the 1 week leakage does not need to be standardized.

GCN_POS_LK_POS <- GCN_POS_LK_POS %>%
   mutate(Std_Leakage_NDC_1WK =Leakage_NDC_1WK,
      Std_Leakage_NDC_4WKS = Leakage_NDC_4WKS/4,
      Std_Leakage_NDC_13WKS = Leakage_NDC_13WKS/13, 
     Std_Leakage_NDC_26WKS = Leakage_NDC_26WKS/26)
print(GCN_POS_LK_POS)

## # A tibble: 3,612 x 52
##    WK_NBR WK_BEGIN_DTE WK_END_DTE NCPDP_TOKEN PHRMCY_NAME BUY_GRP_NAM NDC  
##    <chr>  <chr>        <chr>      <chr>       <chr>       <chr>       <chr>
##  1 201836 2018-09-02   2018-09-08 1457398620… PHARMACY 1… WARRIERS    0007…
##  2 201836 2018-09-02   2018-09-08 1457398620… PHARMACY 1… WARRIERS    0000…
##  3 201836 2018-09-02   2018-09-08 2030168260… PHARMACY 3… CLIPPERS    5931…
##  4 201836 2018-09-02   2018-09-08 2030168260… PHARMACY 3… CLIPPERS    0002…
##  5 201836 2018-09-02   2018-09-08 2030168260… PHARMACY 3… CLIPPERS    0016…
##  6 201836 2018-09-02   2018-09-08 2307913226… PHARMACY 4… WARRIERS    0008…
##  7 201836 2018-09-02   2018-09-08 2756447862… PHARMACY 5… WARRIERS    0018…
##  8 201836 2018-09-02   2018-09-08 2804764134… PHARMACY 5… CAVALIERS   0017…
##  9 201836 2018-09-02   2018-09-08 2866165325… PHARMACY 6… WARRIERS    0000…
## 10 201836 2018-09-02   2018-09-08 2866165325… PHARMACY 6… WARRIERS    5931…
## # … with 3,602 more rows, and 45 more variables: NDC_DESC <chr>,
## #   BRX_GRX_CD <chr>, NDC_QTY_MTRC <dbl>, NDC_PKG_SZ <dbl>,
## #   NDC_CASE_PK_QTY <dbl>, MFG_NAM <chr>, GCNSEQ_NBR <chr>, GCNSEQ_DESC <chr>,
## #   GCNSEQ_RTE_OF_ADMIN_DESC <chr>, GCNSEQ_DSG_FORM_CD <chr>,
## #   GCNSEQ_STR_DESC <chr>, DSPN_QTY_NDC_1WK <dbl>, DSPN_QTY_NDC_4WKS <dbl>,
## #   DSPN_QTY_NDC_13WKS <dbl>, DSPN_QTY_NDC_26WKS <dbl>, DSPN_QTY_GCN_1WK <dbl>,
## #   DSPN_QTY_GCN_4WKS <dbl>, DSPN_QTY_GCN_13WKS <dbl>,
## #   DSPN_QTY_GCN_26WKS <dbl>, PURCH_QTY_NDC_1WK_MTRC <dbl>,
## #   PURCH_QTY_NDC_4WKS_MTRC <dbl>, PURCH_QTY_NDC_13WKS_MTRC <dbl>,
## #   PURCH_QTY_NDC_26WKS_MTRC <dbl>, PURCH_QTY_GCN_1WK_MTRC <dbl>,
## #   PURCH_QTY_GCN_4WKS_MTRC <dbl>, PURCH_QTY_GCN_13WKS_MTRC <dbl>,
## #   PURCH_QTY_GCN_26WKS_MTRC <dbl>, COGS_PRC_MTRC <dbl>,
## #   DSPN_COGS_NDC_1WK <dbl>, DSPN_COGS_NDC_4WKS <dbl>,
## #   DSPN_COGS_NDC_13WKS <dbl>, DSPN_COGS_NDC_26WKS <dbl>,
## #   DSPN_COGS_GCN_1WK <dbl>, DSPN_COGS_GCN_4WKS <dbl>,
## #   DSPN_COGS_GCN_13WKS <dbl>, DSPN_COGS_GCN_26WKS <dbl>, Mean_Price <dbl>,
## #   Leakage_NDC_1WK <dbl>, Leakage_NDC_4WKS <dbl>, Leakage_NDC_13WKS <dbl>,
## #   Leakage_NDC_26WKS <dbl>, Std_Leakage_NDC_1WK <dbl>,
## #   Std_Leakage_NDC_4WKS <dbl>, Std_Leakage_NDC_13WKS <dbl>,
## #   Std_Leakage_NDC_26WKS <dbl>

8 Calculate the mean per week leakage for each time interval

Mean_Leakage<-GCN_POS_LK_POS %>%
 summarise(mean_Leakage_NDC_1WK=mean(Std_Leakage_NDC_1WK),
         mean_Leakage_NDC_4WKS=mean(Std_Leakage_NDC_4WKS),
        mean_Leakage_NDC_13WKS=mean(Std_Leakage_NDC_13WKS),
         mean_Leakage_NDC_26WKS=mean(Std_Leakage_NDC_26WKS))
 print(Mean_Leakage)

## # A tibble: 1 x 4
##   mean_Leakage_NDC_1… mean_Leakage_NDC_… mean_Leakage_NDC_1… mean_Leakage_NDC_2…
##                 <dbl>              <dbl>               <dbl>               <dbl>
## 1                86.5               45.8                33.2                31.7

9 Does the leakage by time period differ significantly? Use your better judgement.

    Gather_1 <- gather(Mean_Leakage, mean)
  ggplot(Gather_1)+
 geom_bar(aes(x=mean, y= value), stat= 'summary', fun.y= 'mean', fill="blue", alpha=1/4)

10 Using the 13 week leakage variable you created, identify the pharmacies most relevant to contain leakage. Identify as many or as few as you believe prudent. Your logic in identifying pharmacies is more important than anything else.

Step 1 – $ amount for leakage

GCN_13WKS <- GCN_POS_LK_POS %>% select(PHRMCY_NAME,Leakage_NDC_13WKS, COGS_PRC_MTRC) %>%
 mutate(Dollar_Amount_Leakage = Leakage_NDC_13WKS * COGS_PRC_MTRC)
print(GCN_13WKS)

## # A tibble: 3,612 x 4
##    PHRMCY_NAME  Leakage_NDC_13WKS COGS_PRC_MTRC Dollar_Amount_Leakage
##    <chr>                    <dbl>         <dbl>                 <dbl>
##  1 PHARMACY 145              60            7.09                  426.
##  2 PHARMACY 145              45           13.7                   615.
##  3 PHARMACY 319              17            6.71                  114.
##  4 PHARMACY 319             120            8.13                  975.
##  5 PHARMACY 319               9           85.8                   773.
##  6 PHARMACY 430              81           25.6                  2073.
##  7 PHARMACY 581              30.6         30.7                   941.
##  8 PHARMACY 598             270            2.85                  769.
##  9 PHARMACY 620              59           13.7                   808.
## 10 PHARMACY 620              42.5          6.70                  285.
## # … with 3,602 more rows

Step 2 – Total leakage

Total_Leakage_13WKS <-sum(GCN_13WKS$Dollar_Amount_Leakage)
 print(Total_Leakage_13WKS)

## [1] 60140066

Step 3 – Total leakage by pharmacy

  GCN_13WKS_1 <- GCN_13WKS %>%
  group_by(PHRMCY_NAME) %>%
  mutate(Total_Leakage_Per_Pharmacy = sum(Dollar_Amount_Leakage))
 select(GCN_13WKS_1, Total_Leakage_Per_Pharmacy)

## Adding missing grouping variables: `PHRMCY_NAME`

## # A tibble: 3,612 x 2
## # Groups:   PHRMCY_NAME [1,580]
##    PHRMCY_NAME  Total_Leakage_Per_Pharmacy
##    <chr>                             <dbl>
##  1 PHARMACY 145                      1041.
##  2 PHARMACY 145                      1041.
##  3 PHARMACY 319                      1862.
##  4 PHARMACY 319                      1862.
##  5 PHARMACY 319                      1862.
##  6 PHARMACY 430                      2073.
##  7 PHARMACY 581                       941.
##  8 PHARMACY 598                       769.
##  9 PHARMACY 620                      1299.
## 10 PHARMACY 620                      1299.
## # … with 3,602 more rows

Step 4 - Sorting on descending order

GCN_13WKS_2 <- GCN_13WKS_1 %>%
 distinct(GCN_13WKS.PHRMCY_NAME, .keep_all = TRUE) %>%
  arrange(desc(Total_Leakage_Per_Pharmacy))

## Warning: Trying to compute distinct() for variables not found in the data:
## - `GCN_13WKS.PHRMCY_NAME`
## This is an error, but only a warning is raised for compatibility reasons.
## The operation will return the input unchanged.

Step 5 - Proportion of each pharmacy

GCN_13WKS_3 <- GCN_13WKS_2 %>%
 mutate(Percentage_Each_Pharmacy = (Total_Leakage_Per_Pharmacy/Total_Leakage_13WKS)*100)
select(GCN_13WKS_3,PHRMCY_NAME,Total_Leakage_Per_Pharmacy, Percentage_Each_Pharmacy )

## # A tibble: 1,580 x 3
## # Groups:   PHRMCY_NAME [1,580]
##    PHRMCY_NAME   Total_Leakage_Per_Pharmacy Percentage_Each_Pharmacy
##    <chr>                              <dbl>                    <dbl>
##  1 PHARMACY 1793                  29595033.                   49.2  
##  2 PHARMACY 2631                   4710779.                    7.83 
##  3 PHARMACY 1510                   1696190.                    2.82 
##  4 PHARMACY 456                    1599834.                    2.66 
##  5 PHARMACY 1773                   1134256.                    1.89 
##  6 PHARMACY 2192                    679328.                    1.13 
##  7 PHARMACY 905                     655553.                    1.09 
##  8 PHARMACY 2519                    620452.                    1.03 
##  9 PHARMACY 2460                    600991.                    0.999
## 10 PHARMACY 2580                    569475.                    0.947
## # … with 1,570 more rows

ITKM 549 Final Group Project

1.Load data and libraries

2.Create a new table with the mean of the variable COGS_PRC_MTRC.Grouped by NDC_DESC.

3. Join the table created in question 2 with the data loaded in question 1. Join by NDC_DESC.

4 From the table created in question 3, replace NA’s in COGS_PRC_MTRC with the mean of COGS_PRC_MTRC.

5 From the table created in question 4, remove all records where there are negative values in any of the following fields: PURCH_QTY_NDC_1WK_MTRC, PURCH_QTY_NDC_4WK_MTRC, PURCH_QTY_NDC_13WK_MTRC , and PURCH_QTY_NDC_26WK_MTRC

6 Create four leakage variables, one for each time interval. Leakage = dispense - purchase quantities.

7 Standardize to a per week metric the four leakage variables, note that the 1 week leakage does not need to be standardized.

8 Calculate the mean per week leakage for each time interval

9 Does the leakage by time period differ significantly? Use your better judgement.

10 Using the 13 week leakage variable you created, identify the pharmacies most relevant to contain leakage. Identify as many or as few as you believe prudent. Your logic in identifying pharmacies is more important than anything else.