Untitled

Complete Dataset

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

head(Dispense_Usage)

New table with the mean of the variable COGS_PRC_MTRC. Grouped by NDC_DESC.

Note: The mean calculation does not calculate NA’s.

T1 <- Dispense_Usage %>% 
  group_by(NDC_DESC) %>% 
  summarise(mean_cost = mean(COGS_PRC_MTRC, na.rm = TRUE))

head(T1)

Previous 2 tables joined by NDC_DESC

T2 <- inner_join(Dispense_Usage,T1,
                 by = c('NDC_DESC' = 'NDC_DESC'))

head(T2)

Replaced NA’s in COGS_PRC_MTRC with the mean of COGS_PRC_MTRC.

T3 <- T2 %>% 
  mutate(COGS_PRC_MTRC = ifelse(is.na(COGS_PRC_MTRC) == T , mean_cost , COGS_PRC_MTRC))

head(T3)

Removed all negative records from thefollowing fields:

PURCH_QTY_NDC_1WK_MTRC, PURCH_QTY_NDC_4WK_MTRC, PURCH_QTY_NDC_13WK_MTRC , and PURCH_QTY_NDC_26WK_MTRC

T4 <- T3 %>% 
  filter(PURCH_QTY_NDC_1WK_MTRC > 0) %>%
  filter(PURCH_QTY_NDC_4WKS_MTRC > 0) %>%  
  filter(PURCH_QTY_NDC_13WKS_MTRC > 0) %>% 
  filter(PURCH_QTY_NDC_26WKS_MTRC > 0)

head(T4)

New leakage variables for each time interval

Leakage = dispense - purchase quantities

T5 <- T4 %>% 
  mutate(LEAK_NDC_1WK = DSPN_COGS_NDC_1WK - PURCH_QTY_NDC_1WK_MTRC, 
         LEAK_NDC_4WKS = DSPN_COGS_NDC_4WKS - PURCH_QTY_NDC_4WKS_MTRC,
         LEAK_NDC_13WKS = DSPN_COGS_NDC_13WKS - PURCH_QTY_NDC_13WKS_MTRC,
         LEAK_NDC_26WKS = DSPN_COGS_NDC_26WKS - PURCH_QTY_NDC_26WKS_MTRC) %>% 
  select(LEAK_NDC_1WK , LEAK_NDC_4WKS, LEAK_NDC_13WKS , LEAK_NDC_26WKS )

head(T5)

Standardized per week metric the four leakage variables

T6 <- T5 %>% 
  mutate(STD_LEAK_NDC_1WK = LEAK_NDC_1WK,
         STD_LEAK_NDC_4WKS = LEAK_NDC_4WKS/4,
         STD_LEAK_NDC_13WKS = LEAK_NDC_13WKS/13,
         STD_LEAK_NDC_26WKS = LEAK_NDC_26WKS/26) %>% 
  select(STD_LEAK_NDC_1WK , STD_LEAK_NDC_4WKS , STD_LEAK_NDC_13WKS , STD_LEAK_NDC_26WKS )

head(T6)

Mean per week leakage for each time interval

T7 <- T6 %>% 
  summarise(MEAN_LEAK_NDC_1WK = mean(STD_LEAK_NDC_1WK),
            MEAN_LEAK_NDC_4WKS = mean(STD_LEAK_NDC_4WKS),
            MEAN_LEAK_NDC_13WKS = mean(STD_LEAK_NDC_13WKS),
            MEAN_LEAK_NDC_26WKS = mean(STD_LEAK_NDC_26WKS))

head(T7)

Differing leakage by time period

going to try and do in Shiny

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

## Warning: Ignoring unknown parameters: fun.y

## Warning: Removed 1 rows containing non-finite values (stat_summary).

## No summary function supplied, defaulting to `mean_se()`

Amerisource’s most relevant way to contain leakage

will be here next week…