Pharmacy-Overview.Rmd

---
title: "Pharmacy-Overview"
output: html_document
editor_options: 
  chunk_output_type: console
---


```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

# Importing packages
```{r}

library(tidyverse)
library(readxl)
library(lubridate)
library(janitor)
library(scales)
library(openxlsx)
library(ggrepel)
library(broom)
library(infer)
library(pdftools)
library(tm)
library(glue)

Sys.setlocale("LC_TIME", "English")


```

##Importing farmaka
```{r}

cleaning_data <- function(my_data){
  
                bind_rows(my_data,.id="Date")%>%
                mutate(Date=ymd(Date))%>%
                arrange(Date)%>%
                select(1,2,4)%>%
                rename("Name"=2,"Quantity"=3)%>%
                filter(!str_detect(Name,"ΦΑΡΜΑΚΑ|ΓΕΝΙΚΑ|ΚΡΑΤΗΣΗ|4409|ΠΑΡΑΦΑΡΜΑΚΕΥΤΙΚΟ|ΠΑΡΑΦΑΡΜΑΚΟ"))%>%
                mutate(Quantity=as.numeric(Quantity))
}


##2020
setwd("C:/My Pharmacy/Ilyda exports/Farmaka/2020")

files_2020 <- list.files(pattern="xls")

df_2020 <- lapply(files_2020,read_excel)

i <- c("2020/04/01","2020/08/01","2020/12/01","2020/02/01","2020/01/01","2020/07/01","2020/06/01","2020/03/01","2020/05/01","2020/11/01","2020/10/01","2020/09/01")

names(df_2020) <- i

farmaka_2020 <- cleaning_data(df_2020)

##2021

setwd("C:/My Pharmacy/Ilyda exports/Farmaka/2021")

files_2021 <- list.files(pattern="xls")

df_2021 <- lapply(files_2021,read_excel)


l <- c("2021/04/01","2021/02/01","2021/01/01","2021/07/01","2021/06/01","2021/03/01","2021/05/01")

names(df_2021) <- l

farmaka_2021 <- cleaning_data(df_2021)

```

##Importing parafarmaka
```{r}


##2020
setwd("C:/My Pharmacy/Ilyda exports/Parafarmaka/2020")

files_2020_p <- list.files(pattern="xls")

df_2020_p <- lapply(files_2020_p,read_excel)

e <- c("2020/04/01","2020/08/01","2020/12/01","2020/02/01","2020/01/01","2020/07/01","2020/06/01","2020/03/01","2020/05/01","2020/11/01","20/10/01","2020/09/01")

names(df_2020_p) <- e

parafarmaka_2020 <- cleaning_data(df_2020_p)

##2021
setwd("C:/My Pharmacy/Ilyda exports/Parafarmaka/2021")

files_2021_p <- list.files(pattern="xls")

df_2021_p <- lapply(files_2021_p,read_excel)

r <- c("2021/04/01","2021/02/01","2021/01/01","2021/07/01","2021/06/01","2021/03/01","2021/05/01")

names(df_2021_p) <- r

parafarmaka_2021 <- cleaning_data(df_2021_p)

```


##Final cleaning steps before analysis
```{r}
farmaka <- bind_rows(farmaka_2020,farmaka_2021,.id="ID")%>%
  mutate(Year=year(Date),
         Year=as.factor(Year),
         Month=month(Date,label=TRUE))%>%
  filter(!is.na(Quantity))%>%
  mutate(Name = case_when(
    Name == "NASOSYN NASPR.SOL (0,1+5,0)% FLx10ML" ~ "NASASYN NASPR.SOL (0,1+5,0)% FLx10ML (ΕΝΗΛ.)",
    TRUE ~ as.character(Name)
  ))%>%
  mutate(Name = case_when(
    Name == "NASOSYN NASPR.SOL (0,05+5,0)% FLx10ML" ~ "NASASYN NASPR.SOL (0,05+5,0)% FLx10ML (ΠΑΙΔ.)",
    TRUE ~ as.character(Name)
  ))%>%
  mutate(Name = case_when(
    Name == "VOLTAREN FORTE GEL 2% TUBx100G" ~ "VOLTAROL FORTE GEL 2% TUBx100G",
    TRUE ~ as.character(Name)
  ))%>%
  mutate(Name = case_when(
    Name == "VOLTAREN EMULGEL 1% TUBx100G (WHITE)" ~ "VOLTAROL EMULGEL 1% TUBx100G (BLUE)",
    Name == "VOLTAREN EMULGEL 1% TUBx100G (BLUE)" ~ "VOLTAROL EMULGEL 1% TUBx100G (BLUE)",
    TRUE ~ as.character(Name)
  ))%>%
  mutate(Name = case_when(
    Name == "FENISTIL GEL 0,1% TUBx30G" ~ "FENISTIL GEL.EXT.US 0,1% TUBx30G",
    TRUE ~ as.character(Name)
  ))



parafarmaka <- bind_rows(parafarmaka_2020,parafarmaka_2021,.id="ID")%>%
  mutate(Year=year(Date),
         Year=as.factor(Year),
         Month=month(Date,label=TRUE))%>%
  filter(!is.na(Quantity))%>%
  mutate(Name = case_when(
    str_detect(Name, "BEPANTHOL CREAM") ~ "BEPANTHOL CREAM 100GR",
    TRUE ~ as.character(Name)
  ))%>%
  mutate(Name = case_when(
    Name == "FREZYDERM AFTERNIP CRILEN 30ML" ~ "CRILEN AFTER NIP GEL 30ML",
    TRUE ~ as.character(Name)
  ))%>%
  mutate(Name = case_when(
    Name == "ΛΕΟΝΤΟΣ ΕΜΠΛ Χ36 (45900)" ~ "ΛΕΟΝΤΟΣ MED.PLAST 4,8MG/PLAST SACHETx1 ΕΜΠΛΑΣΤΡΟ (18x12cm)",
    TRUE ~ as.character(Name)
  ))



```


## Tziros

#Import analosima apo tameio xronias
```{r}

setwd("C:/My Pharmacy/Tameio")


# 2020

tameio_2020 <- read_excel("2020 tameio.xlsx")%>%
  clean_names()%>%
  select(date="tameio_diastematos",aitiologia=x5,percent_fpa=x6,kathari_axia=x7,fpa=x8,synolo=x9)%>%
  filter(str_detect(aitiologia,"Ε.Ο.Π.Υ.Υ."))%>%
  mutate(date=dmy(date),
         year=year(date),
         month=month(date,abbr=T,label=T),
         percent_fpa=as.numeric(percent_fpa),
         kathari_axia=as.numeric(kathari_axia),
         fpa=as.numeric(fpa),
         synolo=as.numeric(synolo))%>%
  select(date,year,month,everything())


analosima_xwris_fpa_2020 <- tameio_2020%>%
  filter(str_detect(aitiologia,"ΥΓΕΙΟ"))%>%
  group_by(month)%>%
  summarize(total=sum(kathari_axia))%>%
  pull(total)


## 2021

tameio_2021 <- read_excel("2021 tameio.xlsx")%>%
  clean_names()%>%
  select(date="tameio_diastematos",aitiologia=x5,percent_fpa=x6,kathari_axia=x7,fpa=x8,synolo=x9)%>%
  filter(str_detect(aitiologia,"Ε.Ο.Π.Υ.Υ."))%>%
  mutate(date=dmy(date),
         year=year(date),
         month=month(date,abbr=T,label=T),
         percent_fpa=as.numeric(percent_fpa),
         kathari_axia=as.numeric(kathari_axia),
         fpa=as.numeric(fpa),
         synolo=as.numeric(synolo))%>%
  select(date,year,month,everything())


analosima_xwris_fpa_2021 <- tameio_2021%>%
  filter(str_detect(aitiologia,"ΥΓΕΙΟ"))%>%
  group_by(month)%>%
  summarize(total=sum(kathari_axia))%>%
  pull(total)

## Analosima all together

analosima_xwris_fpa <- c(analosima_xwris_fpa_2020,analosima_xwris_fpa_2021)

```



## Tziros xwris FPA
```{r}



## Cleaning without FPA

cleaning_data_without_fpa <- function(my_data) {

bind_rows(my_data,.id="Date")%>%
clean_names()%>%
fill(kategoria)%>%
filter(str_detect(kategoria,"Φάρμακα|Παραφάρμακα"))%>%
filter(kategoria %in% c("Φάρμακα","Παραφάρμακα") & analyse %in% c("ΣΥΝΟΛΟ ΦΑΡΜΑΚΩΝ","Ελεύθερες Πωλήσεις"))%>%
filter(!(kategoria=="Φάρμακα" & analyse=="Ελεύθερες Πωλήσεις"))%>%
mutate(date=ymd(date),
       year=year(date),
       month=month(date,abbr = TRUE,label=TRUE))%>%
select(year,month,kategoria,axia)%>%
arrange((month))%>%
spread(key="kategoria",value="axia")%>%
mutate(Παραφάρμακα=as.double(Παραφάρμακα),
      Φάρμακα=as.double(Φάρμακα))



}

##2020

setwd("C:/My Pharmacy/Tziros/2020")

tziros_files_2020 <- list.files(pattern="xlsx")

tziros_df_2020 <- lapply(tziros_files_2020,read_excel)

i <- c("2020/04/01","2020/08/01","2020/12/01","2020/02/01","2020/01/01","2020/07/01","2020/06/01","2020/03/01","2020/05/01","2020/11/01","2020/10/01","2020/09/01")

names(tziros_df_2020) <- i

##2021

setwd("C:/My Pharmacy/Tziros/2021")

tziros_files_2021 <- list.files(pattern="xlsx")

tziros_df_2021 <- lapply(tziros_files_2021,read_excel)

o <- c("2021/04/01","2021/02/01","2021/01/01","2021/07/01","2021/06/01","2021/03/01","2021/05/01")

names(tziros_df_2021) <- o






tziros_2020_without_fpa <- cleaning_data_without_fpa(tziros_df_2020)

tziros_2021_without_fpa <- cleaning_data_without_fpa(tziros_df_2021)


tziros_without_fpa<- bind_rows(tziros_2020_without_fpa,tziros_2021_without_fpa)%>%
  rename("Year"=year,"Month"=month,"Farmaka"=Φάρμακα,"Parafarmaka"=Παραφάρμακα)%>%
  mutate(Analosima=analosima_xwris_fpa,
         Total=Farmaka + Parafarmaka + Analosima)%>%
  mutate(Year = as.factor(Year))



##xwris FPA gia oli tin xronia

tziros_without_fpa%>%
  mutate(Year=as.factor(Year))%>%
  filter(Year %in% c(2020))%>%
  group_by(Year)%>%
  summarize(total_per_year=sum(Total))%>%
  mutate(total_per_year=round(total_per_year,digits=0),
         End=lag(total_per_year),
         xpos=1:n()-0.5,
         Diff=total_per_year-End,
         Percent=paste(round(Diff/End*100,2),"%"),
         Year=as.factor(Year))%>%
  ggplot(aes(x=Year,y=total_per_year,fill=Year))+
  geom_col(width = 0.6)+
  stat_summary(aes(label=scales::comma(..y..)),fun="sum",geom="text",col="black",vjust=0.01)+
  geom_segment(aes(x=xpos,y=End,xend=xpos,yend=total_per_year))+
  geom_text(aes(x=xpos,y=End-Diff/2,label=Percent),size=3.5)+
  labs(title="Τζίρος ανά χρονιά",
       subtitle="χωρίς ΦΠΑ",
       x="",
       y="")+
  scale_y_continuous(labels = dollar_format(suffix="€",prefix=""))+
  theme(legend.position = "none",
        panel.grid.major.x = element_blank(),
                axis.ticks=element_blank())

##xwris FPA mono gia tous 7 prwtous mines

tziros_without_fpa%>%
  filter(Year %in% c(2020,2021))%>%
  filter(Month %in% c("Jan","Feb","Mar","Apr","May","Jun", "Jul"))%>%
  group_by(Year)%>%
  summarize(total_per_year=sum(Total))%>%
  mutate(total_per_year=round(total_per_year,digits=0),
         End=lag(total_per_year),
         xpos=1:n()-0.5,
         Diff=total_per_year-End,
         Percent=paste(round(Diff/End*100,2),"%"),
         Year=as.factor(Year))%>%
  ggplot(aes(x=Year,y=total_per_year,fill=Year))+
  geom_col(width = 0.6)+
  stat_summary(aes(label=scales::comma(..y..)),fun="sum",geom="text",col="black",vjust=0.01)+
  geom_segment(aes(x=xpos,y=End,xend=xpos,yend=total_per_year))+
  geom_text(aes(x=xpos,y=End-Diff/2,label=Percent),size=3.5)+
  labs(title="Τζίρος ανά χρονιά",
       subtitle="χωρίς ΦΠΑ",
       x="",
       y="")+
  scale_y_continuous(labels = dollar_format(suffix="€",prefix=""))+
  theme(legend.position = "none",
        panel.grid.major.x = element_blank(),
                axis.ticks=element_blank())

## Difference in tziros me ekkremotites between each month and the month of previous year
tziros_without_fpa%>%
  filter(Year %in% c(2020,2021))%>%
  filter(Month %in% c("Jan","Feb","Mar","Apr","May","Jun", "Jul"))%>%
  group_by(Month)%>%
  mutate(Start=lag(Total),
         Difference=((Total/Start)-1)*100)

```


## Pharmacy

```{r }
#Importing

setwd("C:/My Pharmacy")

combined_farmaka_parafarmaka <- bind_rows(farmaka,parafarmaka)%>%
  mutate(Name=as.factor(Name))%>%
  rename("Drug"=Name)



my_OTC_with_profit <- read_excel("my_OTC.xlsx")%>%
  select(Drug,Profit_in_Euros)%>%
  mutate(Drug=as.factor(Drug))%>%
  distinct(Drug,.keep_all = T)


OTC_automated_profit <- combined_farmaka_parafarmaka%>%
  left_join(my_OTC_with_profit,by=c("Drug"))%>%
  filter(!is.na(Profit_in_Euros))

OTC_automated_profit%>%
  group_by(Drug,Year)%>%
  summarize(total=sum(Quantity))


## Thirty most profitable products (automated)

OTC_profit <- OTC_automated_profit%>%
  group_by(Drug,Profit_in_Euros)%>%
  summarize(Total=sum(Quantity,na.rm=TRUE))%>%
  ungroup()%>%
  mutate(Total_profit=round(Profit_in_Euros*Total))%>%
  arrange(desc(Total_profit))%>%
  mutate(Drug=fct_reorder(Drug,Total_profit))%>%
  head(30)

thirty_most_profitable <- OTC_profit$Drug


 
```


```{r}


## Tziros graphs


#Συνολικό τζίρο ανά χρονιά 
tziros_without_fpa%>%
  filter(Year %in% c(2020,2021))%>%
  group_by(Year)%>%
  summarize(total_per_year=sum(Total))%>%
  mutate(total_per_year=round(total_per_year,digits=0))%>%
  ggplot(aes(x=Year,y=total_per_year,fill=as.factor(Year)))+
  geom_col(width = 0.6)+
  geom_text(aes(label=formatC(total_per_year,big.mark=".",format="f",digits = 0,decimal.mark=","),vjust=0.001),color="black")+
  labs(title="Συνολικο τζίρο ανά χρονιά",
       x="",
       y="")+
  scale_y_continuous(labels = dollar_format(suffix="€",prefix=""))+
  theme(legend.position = "none",
        panel.grid.major.x = element_blank(),
                axis.ticks=element_blank())
  
#Συνολικό τζίρο ανά μήνα
tziros_without_fpa%>%
  ggplot(aes(x=Month,y=Total,color=Year,group=Year))+
  geom_line()+
  geom_point()+
  scale_y_continuous(labels = dollar_format(suffix="€",prefix=""))+
  labs(title="Συνολικό τζίρο ανά μήνα")






```


## Farmaka graphs
```{r}

## Τζίρο φαρμάκων ανά χρονιά
tziros_without_fpa%>%
  filter(Year %in% c(2020,2021))%>%
  group_by(Year)%>%
  summarize(total_per_year=sum(Farmaka))%>%
  ggplot(aes(x=Year,y=total_per_year,fill=Year))+
  geom_col(width = 0.6)+
  geom_text(aes(label=formatC(total_per_year,big.mark=".",format="f",digits = 0,decimal.mark=","),vjust=0.001),color="black")+
  labs(title="Τζίρο φαρμάκων ανά χρονιά",
       x="",
       y="")+
  scale_y_continuous(labels= dollar_format(suffix="€",prefix=""),breaks=seq(0,400000,by=100000),limits=c(0,400000))+
  theme(legend.position = "none",
        panel.grid.major.x = element_blank(),
        axis.ticks=element_blank())

```

## Parafarmaka graphs
```{r}
#Τζίρο παραφαρμάκων ανά χρονιά
tziros_without_fpa%>%
  filter(Year %in% c(2020,2021))%>%
  group_by(Year)%>%
  summarize(total_per_year=sum(Parafarmaka))%>%
  ggplot(aes(x=Year,y=total_per_year,fill=Year))+
  geom_col(width = 0.6)+
  geom_text(aes(label=formatC(total_per_year,big.mark=".",format="f",digits = 0,decimal.mark=","),vjust=0.001),color="black")+
  labs(title="Τζίρο παραφαρμάκων ανά χρονιά",
       x="",
       y="")+
  scale_y_continuous(labels = dollar_format(suffix="€",prefix=""))+
  theme(legend.position = "none",
        axis.ticks=element_blank(),
        panel.grid.major.x=element_blank())


```


## Analosima graphs
```{r Γραφήματα Αναλώσιμων}
#Τζίρο αναλώσιμων ανά χρονιά
tziros_without_fpa%>%
  filter(Year %in% c(2020,2021))%>%
  group_by(Year)%>%
  summarize(total_per_year=sum(Analosima))%>%
  ggplot(aes(x=Year,y=total_per_year,fill=Year))+
  geom_col(width = 0.6)+
  geom_text(aes(label=formatC(total_per_year,big.mark=".",format="f",digits = 0,decimal.mark=","),vjust=0.001),color="black")+
  labs(title="Τζίρο αναλώσιμων ανά χρονιά",
       x="",
       y="")+
  scale_y_continuous(labels = dollar_format(suffix="€",prefix=""))+
  theme(legend.position = "none",
        axis.ticks=element_blank(),
        panel.grid.major.x=element_blank())



```

## Analysis and Graphs
```{r}


##Change of OTC quantitys between 2020 and 2021 of 30 most profitable products (over 1/2017-07/2021)

OTC_automated_profit%>%
  filter(Month %in% c("Jan","Feb","Mar","Apr","May","Jun", "Jul"))%>%
  group_by(Drug,Year)%>%
  summarize(Total=sum(Quantity,na.rm=TRUE))%>%
  filter(Year %in% c(2020,2021))%>%
  mutate(Change=Total-lag(Total,default=0))%>%
  filter(Year %in% c(2021),Change!=0)%>%
  filter(Drug %in% thirty_most_profitable)%>%
  ungroup()%>%
  mutate(positive=ifelse(Change>0,TRUE,FALSE),
         Drug=str_to_title(Drug))%>%
  arrange(desc(Change))%>%
  ggplot(aes(x=fct_reorder(Drug,Change),y=Change,fill=positive))+
  geom_col()+
  geom_text(aes(label=Change),color="black",size=2.6,hjust=1)+
  coord_flip()+
  scale_fill_brewer(palette="Pastel1")+
  theme(legend.position="none")+
  labs(title="Μεταβολή σε ποσότητα πωληθέντων OTC μεταξύ 2020 & 2021",
       subtitle="Τα 30 πιο προσοδοφόρα προιόντα κατά την περίοδο 1/2017-06/2021",
       x="",
       y="")+
  scale_y_continuous(limits=c(-150,150),breaks=c(-150,-100,-50,0,50,100,150))


#Change of OTC quantitys between 2020 and 2021 plot
OTC_automated_profit%>%
  filter(Month %in% c("Jan","Feb","Mar","Apr","May","Jun", "Jul"))%>%
  group_by(Drug,Year)%>%
  summarize(Total=sum(Quantity,na.rm=TRUE))%>%
  mutate(Change=Total-lag(Total,default=0))%>%
  filter(Year %in% c(2021),Change!=0)%>%
  ungroup()%>%
  mutate(positive=ifelse(Change>0,TRUE,FALSE),
         Drug=str_to_title(Drug))%>%
  ggplot(aes(x=fct_reorder(Drug,Change),y=Change,fill=positive))+
  geom_col()+
  geom_text(aes(label=Change),color="black",size=2.6,hjust=1)+
  coord_flip()+
  scale_fill_brewer(palette="Pastel1")+
  theme(legend.position="none")+
  labs(title="Μεταβολή σε ποσότητα πωληθέντων OTC μεταξύ 2020 & 2021",
       x="",
       y="")+
  scale_y_continuous(limits=c(-150,150),breaks=c(-150,-100,-50,0,50,100,150))


#Change of OTC quantitys between 2020 and 2021 table 
OTC_automated_profit%>% 
  filter(Month %in% c("Jan","Feb","Mar","Apr","May","Jun", "Jul"))%>% 
  group_by(Drug,Year,Profit_in_Euros)%>% 
  summarize(Total=sum(Quantity,na.rm=TRUE))%>% 
  ungroup()%>% 
  mutate(Change=Total-lag(Total,default=0), 
         Sign=if_else(Change>0,"POSITIVE","NEGATIVE"))%>% 
  filter(Year %in% c("2021"))%>% 
  mutate(Sign=as.factor(Sign))

OTC_automated_profit%>%
  filter(Month %in% c("Jan","Feb","Mar","Apr","May","Jun", "Jul"))%>%
  group_by(Drug,Year)%>%
  summarize(Total=sum(Quantity,na.rm=TRUE))%>%
  ungroup()%>%
  mutate(Change=Total-lag(Total,default=0),
         Sign=if_else(Change>0,"POSITIVE","NEGATIVE"))%>%
  filter(Year %in% c("2021"))%>%
  mutate(Sign=as.factor(Sign))%>%
  count(Sign)






```

## Analysis of prescriptions

##Final cleaning
```{r}



setwd("C:/My Pharmacy/Prescriptions")

files <- list.files(pattern="pdf")

number_of_files <- length(files)

presc_list <- lapply(files,pdf_text)




mysplit <- function(x){
  
  strsplit(x,split="\n")
}

result <- lapply(presc_list,mysplit)


combined_clean <- unlist(result)%>%
  str_remove_all(pattern=" ")%>%
  as_tibble()%>%
  filter(str_detect(value,pattern=c("ΕΠΩΝΥΜΟ|ΟΝΟΜΑ|Α.Μ.Κ.Α.|ΠΛΗΡΩΤΕΟΠΟΣΟΑΠΟΑΣΦ|ΠΛΗΡΩΤΕΟΠΟΣΟΑΠΟΤΑΜΕΙΟ|ΕΤΟΣ|ΗΜ/ΝΙΑΕΚΤΕΛΕΣΗΣ")))%>%
  filter(!str_detect(value,pattern=c("Α.Μ.Κ.Α.ΦΑΡΜΑΚΟΠΟΙΟΥ:")))%>%
  mutate(value=str_remove_all(value,pattern=c("\r")))


last_name <- str_split(combined_clean$value,pattern="ΕΠΩΝΥΜΟ:",simplify=TRUE)

last_names <- as.data.frame(last_name)%>%
  filter(V1=="")%>%
  select(V2,V3)

last_names <- as.vector(t(last_names))


first_name <- str_split(combined_clean$value,pattern="ΟΝΟΜΑ:",simplify=TRUE)

first_names <- as.data.frame(first_name)%>%
  filter(V1=="")%>%
  select(V2,V3)

first_names <- as.vector(t(first_names))



price_patient <- str_split(combined_clean$value,pattern="ΠΛΗΡΩΤΕΟΠΟΣΟΑΠΟΑΣΦ/ΝΟ€",simplify=TRUE)

price_patients <- as.data.frame(price_patient)%>%
  filter(V1=="")%>%
  select(V2)

price_patients <- as.vector(price_patients$V2)


price_eoppy <- str_split(combined_clean$value,pattern="ΠΛΗΡΩΤΕΟΠΟΣΟΑΠΟΤΑΜΕΙΟ€",simplify=TRUE)

price_eoppys <- as.data.frame(price_eoppy)%>%
  filter(V1=="")%>%
  select(V2)

price_eoppys <- as.vector(price_eoppys$V2)


AMKA <- str_split(combined_clean$value,pattern="Α.Μ.Κ.Α.:",simplify=TRUE)

AMKAs <- as.data.frame(AMKA)%>%
  filter(V1=="")%>%
  select(V2,V3)
  
amkas <- as.vector(t(AMKAs))

execution_date <- str_split(combined_clean$value,pattern="ΗΜ/ΝΙΑΕΚΤΕΛΕΣΗΣ:",simplify=TRUE)

execution_date <- as.data.frame(execution_date)%>%
  filter(V1!="")%>%
  filter(V2!="")

execution_dates <- execution_date$V2

yob <- str_split(combined_clean$value,pattern="ΕΤΟΣΓΕΝΝΗΣΗΣ:",simplify=TRUE)

yob <- as.data.frame(yob)%>%
  filter(V2!="")

yobs <- yob$V2



teliko_tibble <- data.frame(last_names,first_names,amkas)%>%
  as_tibble()%>%
  mutate(price_of_patients=rep(price_patients,each=2),
         price_of_eoppy=rep(price_eoppys,each=2),
         dates=rep(execution_dates,each=2),
         year_of_birth=rep(yobs,each=2))%>%
  transform(ID=c("Doctor","Patient"))%>%
  unite(Name,last_names,first_names,sep=" ")%>%
  as_tibble()


```



##Final tibble cleaning
```{r}

teliko_tibble <- teliko_tibble%>%
  mutate(newid=rep(c(1:number_of_files),each=2))

amka_doc <- teliko_tibble%>%
  select(-price_of_patients,-price_of_eoppy,-dates,-year_of_birth)%>%
  filter(ID=="Doctor")

amka_pat <- teliko_tibble%>%
  filter(ID=="Patient")

doct_wide <- amka_doc%>%
  pivot_wider(names_from = ID,values_from=Name)

pat_wide <- amka_pat%>%
  pivot_wider(names_from = ID,values_from=Name)



all_prescriptions <- pat_wide%>%
  full_join(doct_wide,by=c("newid"),suffix=c(".patient",".doctor"))%>%
  rename("AMKA_patient"=amkas.patient,"AMKA_doctor"=amkas.doctor)%>%
  select(dates,AMKA_patient,Patient,year_of_birth,AMKA_doctor,Doctor,price_of_patients,price_of_eoppy)%>%
  mutate(dates=dmy(dates),
         Year=year(dates),
         Month=month(dates,abbr = TRUE,label=TRUE),
         Day=wday(dates,label=TRUE,abbr=TRUE),
         Age=2020-as.numeric(as.character(year_of_birth)),
         price_of_eoppy=as.numeric(gsub(",",".",price_of_eoppy)),
         price_of_patients=as.numeric(gsub(",",".",price_of_patients)),
         total_price=price_of_eoppy+price_of_patients)%>%
  arrange(dates)%>%
  distinct()%>%
  select(Year,Month,Day,everything())%>%
  rename("YOB"=year_of_birth)%>%
  filter(Month!="Aug")





```

### Συνταγές αναλυση
```{r warning=FALSE}
#Number of unique patients
unique_patients <- all_prescriptions%>%
  distinct(AMKA_patient)%>%
  nrow()

#Number of prescriptions
number_of_prescriptions <- nrow(all_prescriptions)

avg_age <- all_prescriptions%>%
  #select(Dates,AMKA_patient,year_of_birth,Age,everything())%>%
  summarize(Average_age=mean(Age),
            Median_age=median(Age),
            Max=max(Age),
            Min=min(Age))

#Prescriptions per doctor
presc_per_doc <- all_prescriptions%>%
  count(Doctor,sort=TRUE)

#Average of total price of all prescriptions per doctor
avg_of_total_price <- all_prescriptions%>%
  group_by(Doctor)%>%
  summarize(Average=mean(total_price))%>%
  arrange(desc(Average))


cost_of_presc <- avg_of_total_price%>%
  left_join(presc_per_doc,by=c("Doctor"))%>%
  mutate(Total=round(Average*n,digits=0))%>%
  arrange(desc(Total))
  



##Questions to be answered

#1.Ποιος ειναι ο ασθενης με τη μεγαλυτερη συμμετοχή (group ανα μοναδικο ασθενη)
#2.Ποιος ειναι ο ασθενης με τη μεγαλυτερη αποζημιωση απο τον εοππυ
#3.Ποιος ειναι ο ασθενης με τη μεγαλυτερη συνολική τιμη (εοππυ + συμμετοχη)
#4.Ποιοι ειναι οι πιο πιστοι ασθενείς απο μηνα σε μηνα?
#5.Ποιοι ειναι οι γιατροί που συνεισφέρουν το μεγαλύτερο στο φαρμακείο?
#6.Ποιοι ειναι οι γιατροί με το μεγαλυτερο Μ.Ο. ανα μήνα?
#7.Πόσοι οι μοναδικοί ασθενείς ανά μήνα?
#8.Ποιος ο Μ.Ο. και ποια η διάμεση τιμή της ηλικίας των ασθενών, ελαχιστη και μεγιστη ηλικία ασθενή?
#9.Ποια η κατανομή της ηλικίας των ασθενών?
#10. Ποια η κατανομή του αριθμου των συνταγών ανή ημέρα του μήνα?
#11. Ποια η μερα του μήνα (Δευτερα, Τριτη κλπ.) με τον μεγαλυτερο αριθμο συνταγών?
#12. Αριθμος συνταγων ανά μήνα
#13. Ποιος ο Μ.Ο. των γιατρων ανά μήνα

#1.Ποιος ειναι ο ασθενης με τη μεγαλυτερη συμμετοχή (group ανα μοναδικο ασθενη)

all_prescriptions%>%
  group_by(Patient)%>%
  summarize(total_symmetoxi=sum(price_of_patients))%>%
  arrange(desc(total_symmetoxi))


#2.Ποιος ειναι ο ασθενης με τη μεγαλυτερη αποζημιωση απο τον εοππυ

all_prescriptions%>%
  group_by(Patient)%>%
  summarize(Total_eoppy=sum(price_of_eoppy))%>%
  arrange(desc(Total_eoppy))

#3.Ποιος ειναι ο ασθενης με τη μεγαλυτερη συνολική τιμη (εοππυ + συμμετοχή)

all_prescriptions%>%
  group_by(Patient)%>%
  summarize(total_symmetoxi_and_eoppy=price_of_patients+price_of_eoppy)%>%
  arrange(desc(total_symmetoxi_and_eoppy))

#5.Ποιοι ειναι οι γιατροί που συνεισφέρουν το μεγαλύτερο στο φαρμακείο?

all_prescriptions%>%
  count(Doctor,sort=TRUE)

cost_of_presc <- avg_of_total_price%>%
  left_join(presc_per_doc,by=c("Doctor"))%>%
  mutate(Total=round(Average*n,digits=0))%>%
  arrange(desc(Total))

##
all_prescriptions%>%
  add_count(Doctor,name="Prescriptions")%>%
  mutate(Doctor= glue("{Doctor} ({ Prescriptions })"))%>%
  group_by(Doctor)%>%
  summarize(Total=sum(total_price))%>%
  arrange(desc(Total))%>%
  head(15)%>%
  ggplot(aes(x=fct_reorder(Doctor,Total),y=Total))+
        geom_col()+
        coord_flip()+
        labs(x="",y="")
  
##

#6.Ποιοι ειναι οι γιατροί με το μεγαλυτερο Μ.Ο. ανα μήνα? (πανω απο 7 συνταγες τον μηνα)

cost_of_presc%>%
  filter(n>7)%>%
  arrange(desc(Average))
  
#7.Πόσοι οι μοναδικοί ασθενείς ανά μήνα?

  all_prescriptions%>%
    distinct(AMKA_patient, .keep_all = T)


#8.Ποιος ο Μ.Ο. και ποια η διάμεση τιμή της ηλικίας των ασθενών, ελάχιστη και μεγιστη ηλικία ασθενή?

all_prescriptions%>%
  summarize(Median_Age=median(Age),
            Mean_Age=mean(Age),
            Min_Age=min(Age),
            Max_Age=max(Age))

#9.Ποια η κατανομή της ηλικίας των ασθενών?

all_prescriptions%>%
  ggplot(aes(x=Age))+
  geom_histogram(bins=40)
  
#10. Ποια η κατανομή του αριθμου των συνταγών ανά ημέρα του μήνα?

all_prescriptions%>%
  filter(!str_detect(Month,pattern="Aug"))%>%
  count(dates,sort=TRUE)%>%
  ggplot(aes(x=dates,y=n))+
  geom_col()+
  coord_flip()
  
#11. Ποια η μερα του μήνα (Δευτερα, Τριτη κλπ.) με τον μεγαλυτερο αριθμο συνταγών?

all_prescriptions%>%
  filter(!str_detect(Month,pattern="Aug"))%>%
  count(Day,sort=TRUE)%>%
  ggplot(aes(x=fct_reorder(Day,n),y=n))+
  geom_col(fill="darkorange2")+
  coord_flip()+
  labs(x="",y="Αριθμος συνταγων")


all_prescriptions%>%
  count(week = floor_date(dates, "week"))%>%
  ggplot(aes(week, n ))+
  geom_point()+
  geom_line()+
  scale_x_date(date_breaks = "1 month", date_labels = "%b %y")+
  labs(x = "", y = "Number of prescriptions",
       subtitle = "Dots represent last day (Sunday) of each week")


#12. Αριθμος συνταγων ανά μήνα

all_prescriptions%>%
  filter(!Month=="Aug")%>%
  nrow()

#13.

doctors_with_highest_total <- all_prescriptions%>%
  add_count(Doctor,name="Prescriptions")%>%
  group_by(Doctor)%>%
  summarize(Total=sum(total_price))%>%
  arrange(desc(Total))%>%
  head(50)%>%
  pull(Doctor)

doctors_with_five_highest_total <- all_prescriptions%>%
  add_count(Doctor,name="Prescriptions")%>%
  group_by(Doctor)%>%
  summarize(Total=sum(total_price))%>%
  arrange(desc(Total))%>%
  head(5)%>%
  pull(Doctor)


all_prescriptions%>%
  filter(Doctor %in% doctors_with_five_highest_total)%>%
  group_by(Month,Doctor)%>%
  summarize(Average_per_month=mean(total_price))%>%
  ungroup()%>%
  ggplot(aes(x=Month,y=Average_per_month,color=Doctor,group=Doctor))+
  geom_line()+
  scale_y_continuous(breaks=seq(0,100,by=10))





```


# Analysis for doctors 
```{r}



# Pulling thirty doctors with highest total

doctors_with_thirty_highest_total <- all_prescriptions%>% 
  #add_count(Doctor,name="Prescriptions")%>% 
  group_by(Doctor)%>% 
  summarize(Total=sum(total_price))%>% 
  arrange(desc(Total))%>% 
  head(30)%>% 
  pull(Doctor) 

# 
  

all_prescriptions%>% 
  mutate(Year=year(dates))%>% 
  select(Year,everything())%>% 
  group_by(Year,Month,Doctor)%>% 
  summarize(total=sum(total_price))%>% 
  ungroup()%>%
  mutate(Doctor = fct_lump(Doctor, 29, w = total))%>%
  mutate(Date=str_c(Year,Month,"01",sep="-"), 
         Date=ymd(Date))%>% 
  ggplot(aes(x=Date,y=total,color=Doctor,group=Doctor))+ 
  geom_point()+ 
  geom_line()+
  facet_wrap(~Doctor)+ 
  guides(colour=FALSE)+
  scale_y_continuous(labels = dollar_format(suffix="\u20ac",prefix=""))


```