finalll.Rmd

---
title: "Final Project"
author: "Team"
output: html_document
---

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

library(readxl)      
library(tidyverse)
library(ggplot2)
library(plotly)
library(reshape2)
source("http://www.sthda.com/upload/rquery_cormat.r")
library(corrplot)
library(lubridate)
library(zoo)
```


## Reading and Cleansing Data
```{r}
Alex_Detailed <- "Alex Water Detailed.xlsx"
year_2016 <- read_excel(Alex_Detailed, 1)
year_2017 <- read_excel(Alex_Detailed, 2)
year_2018 <- read_excel(Alex_Detailed, 3)
year_2019 <- read_excel(Alex_Detailed, 4)
names(year_2019)
#Stote duplicate columns in other df
complain_types <- unique(year_2019[, c('ComplainTypeID','ComplainTypeName')])
complain_classes <- unique(year_2019[, c('ComplainClassID','ComplainClassName')])
Sources <-  unique(year_2019[, c('SourceID...41','SourceName')]) %>% 
  rename(SourceID = SourceID...41)

Sectors <-  select(read_excel("Alex Water Area Sector Town.xlsx", 2),c("SectorID","SectorName"))
towns <- unique(select(year_2019 ,c("TownID","SectorID","TownName")))
Areas <-  select(read_excel("Alex Water Area Sector Town.xlsx", 1),c("AreaID","AreaName","LocationID","TownID","SectorID"))
#Location code 323 not found
Locations <-   select(read_excel("Alex Water Area Sector Town.xlsx", 4), c("LocationID","LocationName"))

## CONVERTING TO FACTORS
Sectors$SectorID <- as.factor(Sectors$SectorID)

towns$SectorID <- as.factor(towns$SectorID)
towns$TownID <- as.factor(towns$TownID)

Areas$SectorID <- as.factor(Areas$SectorID)
Areas$TownID <- as.factor(Areas$TownID)
Areas$LocationID <- as.factor(Areas$LocationID)
Areas$AreaID <- as.factor(Areas$AreaID)

#Drop duplicate columns and columns full of NULLS
#dropped LocationCode and  because we have another LocationID so we don't need it 
# we don't have cases dataset so we droped CaseID
# it's too hard to make filterations and decisions using Street names and notes because it has alot of typing mistakes
drops1 <- c("X","Y","MainStreetID", "LandMarkID","LocationTypeID","LocationCode", 'AreaCode',
           'ComplainTypeName','ComplainClassName',"SourceName","SectorName","TownName","AreaName","AreaCode",
           "CaseID", "HomeNumber", "SideStreet", "MainStreet", "Notes" , "Date_Time2")
drops2 <- c("Quarter","Month Value")
## Converting columns of double type to factor type
edited_col <- c("FloorNumberID","IsClosed","IsReported","IsReplied","IsFinalReplied","IsFollwed",
                "IsReturned","SourceID","AreaID","SectorID","TownID",
                "LocationID","ComplainTypeID","ComplainClassID","SourceID")
#create new columns and drop duplicate column
#extract companyID from completeNumber column then drop it because all other info in it we already have

year_2019 %>%
  select(- drops1,- ...10) %>%
  mutate(Company_ID = substr(CompleteNumber, 1, 1)) %>%
  mutate(Season = quarters(Date_Time),Year=year(Date_Time)) %>% 
  select(- CompleteNumber) %>% 
  select(- SourceID...41) %>% 
  rename(SourceID = SourceID...20) %>% 
  mutate_at( vars(edited_col), funs(as.factor)) %>%
  mutate_at(vars("AppendTo"), funs(as.numeric)) -> clean_year_2019

names(year_2019)

year_2018 %>%
  select(- drops1) %>%
  mutate(Company_ID = substr(CompleteNumber, 1, 1)) %>%
  mutate(Season = quarter(Date_Time),Year=year(Date_Time)) %>% 
  select(- CompleteNumber) %>% 
  select(- SourceID...19) %>% 
  rename(SourceID = SourceID...40) %>% 
  mutate_at( vars(edited_col), funs(as.factor)) %>%
  mutate_at(vars("AppendTo"), funs(as.numeric)) -> clean_year_2018



names(year_2017)
year_2017 %>%
  select(- drops1) %>%
  mutate(Company_ID = substr(CompleteNumber, 1, 1)) %>%
  mutate(Season = quarter(Date_Time),Year=year(Date_Time)) %>% 
  select(- CompleteNumber) %>% 
  select(- SourceID...40) %>% 
  rename(SourceID=SourceID...19) %>% 
  mutate_at( vars(edited_col), funs(as.factor)) %>% 
  mutate_at(vars("AppendTo"), funs(as.numeric)) -> clean_year_2017


names(year_2016)
year_2016 %>%
  select(- drops1) %>%
  mutate(Company_ID = substr(CompleteNumber, 1, 1)) %>%
  mutate(Season = quarter(Date_Time), Year=year(Date_Time)) %>% 
  select(- CompleteNumber) %>% 
  select(- SourceID2) %>% 
  select(- drops2) %>% 
  mutate_at( vars(edited_col), funs(as.factor)) %>%
  mutate_at(vars("AppendTo"), funs(as.numeric)) -> clean_year_2016


All_Years <- rbind(clean_year_2019,rbind(clean_year_2018,rbind(clean_year_2017,rbind(clean_year_2016))))
## adding locationid column to broken data using join
broken_2017 <- read_excel('Book1.xlsx', 1)
broken_2018 <- read_excel('Book2.xlsx', 1)
broken_2019 <- read_excel('Book3.xlsx', 1)

broken_2018 %>% 
  inner_join(select(broken_2017, LocationID, LocationName) %>% rename(LocationID1 = LocationID)) %>%
  mutate(if(is.na(LocationID)){ LocationID1} else { LocationID}) %>%
  select(-c("LocationID", "LocationID1", "LocationName")) %>%
  rename(LocationID = `if (...) NULL`) -> broken_2018

broken_2019 %>% 
  inner_join(select(broken_2017, LocationID, LocationName) %>% rename(LocationID1 = LocationID)) %>%
  mutate(if(is.na(LocationID)){ LocationID1} else { LocationID}) %>%
  select(-c("LocationID", "LocationID1", "LocationName")) %>%
  rename(LocationID = `if (...) NULL`) -> broken_2019

broken_2017$CaseType <- broken_2019$CaseType[1]
#############
Names <- names(broken_2018)                        
broken_2019 <- unique(select(broken_2019, Names))
broken_2017 <- unique(select(broken_2017, Names))
broken_2018 <- unique(broken_2018)
all_broken <- rbind(broken_2019, rbind(broken_2018 ,broken_2017))
##############
complain_classes$ComplainClassID   <- as.factor(complain_classes$ComplainClassID)
complain_types$ComplainTypeID <- as.factor(complain_types$ComplainTypeID)
#############
#Group all brokens in one dataset and make new date columns
remove <- c("BrokeTime", "CloseTime", "FixDate", "OpenTime")
all_broken %>% 
  mutate(StartFix = ymd_hm(paste(BrokeTime, CloseTime)),
         EndFix = ymd_hm(paste(FixDate, OpenTime))) %>%
          select(- remove) -> all_broken

all_broken$CaseType <- as.factor(all_broken$CaseType)
mutate(all_broken, MeanTime = as.integer(EndFix - StartFix) / 60) -> all_broken
all_broken$LocationID <- as.factor(all_broken$LocationID)
levels(all_broken$LocationID) <- c("3"  , "43" ,  "83"  ,
                                   "123" , "163" , "203"  ,
                                   "243" , "363" , "283" ,
                                   "443" , "483" , "523"  ,"1803")
all_broken$LocationID[(all_broken$LocationID == 263)] <- 363
Locations$LocationID <- as.factor(Locations$LocationID)

## reading plant avg
plant_Avg <- "classified Plant Average Intakes Outlet WQI Final Results-2016-2019.xlsx"
Intake_WQI <- read_excel(plant_Avg,1)
Intake_WQI$Date <-ymd(paste(Intake_WQI$year,"-",Intake_WQI$month,"- 01"))
yq <- as.yearqtr(as.yearmon(Intake_WQI$Date, "%m/%d/%Y") + 1/12)
Intake_WQI$Season <- factor(format(yq, "%q"), levels = 1:4, 
                labels = c("winter", "spring", "summer", "fall"))

Outlet_WQI <- read_excel(plant_Avg,2) 
Outlet_WQI$Date <-ymd(paste(Outlet_WQI$year,"-",Outlet_WQI$month,"- 01"))
yq <- as.yearqtr(as.yearmon(Outlet_WQI$Date, "%m/%d/%Y") + 1/12)
Outlet_WQI$Season <- factor(format(yq, "%q"), levels = 1:4, 
                labels = c("winter", "spring", "summer", "fall"))

Intakes <-  read_excel(plant_Avg,3)
Outlets <-  read_excel(plant_Avg,4)

Intakes$Total_Coliform <- as.numeric(Intakes$Total_Coliform)
Intakes$Total_Algae_Count <- as.numeric(Intakes$Total_Algae_Count)

Outlets$Total_Coliform <- as.numeric(Outlets$Total_Coliform)
Outlets$Total_Algae_Count <- as.numeric(Outlets$Total_Algae_Count)
## reading all_pop
Population <- "all_pop.xlsx"
Pop <- read_excel(Population,1)



```


<!-- ## Number of complains over date  -->
```{r}
Comp_over_time <- 
  merge(x = Sources, y = All_Years, by = 'SourceID', all = TRUE) %>%
    mutate(date = date(Date_Time)) %>% 
    group_by(date) %>%
    summarize(num_of_complains = n()) %>%
    ggplot(aes(x = date, y = num_of_complains, color = factor(year(date)))) +
    geom_line() + 
    labs(title = "Complains over date", x = "Date", y = "Num of Complains", color = "Year")

```




## Analysing Complains type to get insights of most high complain 
## Then Analysing if Complain closed or not
```{r}
## percentage of closed and non closed complains

No_of_comp_closed <-
  filter(All_Years,!is.na(IsClosed)) %>% 
  group_by(IsClosed) %>% 
  summarise(no_of_closed= n()) %>% 
  
  ggplot(mapping = aes(x = IsClosed , y = no_of_closed, fill= factor(IsClosed))) +
  geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=paste(round(no_of_closed / sum(no_of_closed) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Closed & non closed cases", y ="Number of Cclosed_Non",title = "Number of complain per class 2016", color = "ComplainClassID' IDs")
```

```{r}
### Analysing n of complain class 
No_of_comp_class <-
  merge(x = All_Years, y = complain_classes, by = "ComplainClassID", all = TRUE) %>% 
  filter(!is.na(ComplainClassID)) %>% 
  group_by(ComplainClassID,ComplainClassName) %>% 
  summarise(no_of_complains_class = n()) %>% 
  
  ggplot(mapping = aes(x = ComplainClassName , y = no_of_complains_class, fill = factor(ComplainClassID))) +
  geom_bar(stat="identity",width = 1)+
     
 geom_text(aes(label=paste(round(no_of_complains_class / sum(no_of_complains_class) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Complain Class' Names", y ="Number of Complains",title = "Number of complain per class ", color = "ComplainClassID' IDs", fill = "Complain Class ID")
```

```{r}
## CLASS VS Closed
No_of_comp_class_closed <-
  merge(x = All_Years, y = complain_classes, by = "ComplainClassID", all = TRUE) %>% 
  filter(!is.na(ComplainClassID)) %>% 
  group_by(ComplainClassID,ComplainClassName,IsClosed) %>% 
  summarise(no_of_complains_class = n()) %>% 
  
  ggplot(mapping = aes(x = ComplainClassName , y = no_of_complains_class, fill = factor(IsClosed))) +
  geom_bar(stat="identity",width = 1)+
     
 geom_text(aes(label=paste(round(no_of_complains_class / sum(no_of_complains_class) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Complain Class' Names", y ="Number of Complains",title = "Number of complain per class ", color = "ComplainClassID' IDs", fill = "Complain Class ID")
```


#=====================================

### Water Complain class
```{r}
Water_comp_type <- 
    All_Years %>% merge(y=complain_types,by="ComplainTypeID",all=TRUE) %>%
    group_by(ComplainClassID, ComplainTypeID, ComplainTypeName,IsClosed) %>%
    summarize(num_of_complains = n()) %>%
    filter(ComplainClassID == 1, num_of_complains> 400) %>%
    ggplot(aes(x = ComplainTypeName, y = num_of_complains, fill = IsClosed)) +
    geom_col() +
   geom_text(aes(label=paste(round(num_of_complains / sum(num_of_complains) * 100, 1), "%")), vjust=-0.3, size=3.5)+
    labs(title = "Complain num for each type in water class", x = "Complain type", y = "Num of Complains", fill = "Complain type") +
   theme(axis.text.x = element_text(angle = 90, size =  12, face = 90))
```


## table Min_fix_time for each pipe type
```{r}
Mean_fix_time <- all_broken[!is.na(all_broken$PipeType),] %>%
   group_by(PipeType) %>%
   summarise(Mean_Fix_Time = mean(MeanTime), num_of_cases = n()) %>%
  arrange(Mean_Fix_Time)
```



##chart of Min_fix_time
```{r}
Min_fix_time_chart <- 
  all_broken[!is.na(all_broken$PipeType),] %>%
   group_by(PipeType) %>%
   summarise(Mean_Fix_Time = mean(MeanTime), num_of_cases = n()) %>%
  arrange(Mean_Fix_Time) %>%
  ggplot(aes(x = reorder(PipeType,- Mean_Fix_Time) ,y = Mean_Fix_Time, fill =  PipeType)) +
  geom_col() + 
  theme(axis.text.x = element_text(angle = 90, size =  12, face = 90))+
labs(title = "Mean Fixing Time for each Pipe Type", x = "Pipe Type", y = "Mean Fixing Time", fill = "Pipe Type Name") 
```



## num_of_complains for type inqta3 per location sheet detailed
```{r}
Water_brokenDetaile_type <- 
  merge(x=All_Years,y=Locations,by="LocationID",all = TRUE) %>%
  filter(ComplainTypeID == 3) %>%
   group_by(LocationID, LocationName.y) %>%
  summarize(num_of_complains = n()) %>%
  ggplot(aes(x = LocationName.y, y = num_of_complains, fill = LocationID))+
  geom_col()+
   theme(axis.text.x = element_text(angle = 90, size =  12, face = 90))+
  labs(title = "Max Water outages Type Per Location", x = "Locations", y = "No. of Complains", fill = "Locations' IDs") 
#High num of cases or high mean number
```



## table for inqta3 water
```{r}
#hageb kman elshakawy fe locationID fe elsheet eltany 3shan aqaren
Not_broken_water <- 
  all_broken[ !(all_broken$CaseType == "كسر ماسورة مياه"),] %>%
   merge(y=Locations,by="LocationID",all = TRUE) %>%
   filter(!is.na(LocationID)) %>% 
   group_by(LocationName, LocationID) %>%
   summarise(Mean_Fix_Time = mean(MeanTime), num_of_cases = n()) %>%
  arrange(desc(num_of_cases)) 
```



## sheet broken
```{r}
#hageb kman elshakawy fe locationID fe elsheet eltany 3shan aqaren
Water_broken_type <- 
  all_broken[ !(all_broken$CaseType == "كسر ماسورة مياه"),] %>%
  merge(y=Locations,by="LocationID",all = TRUE) %>%
    filter(!is.na(LocationID)) %>% 
   group_by(LocationID, LocationName) %>%
   summarise(Mean_Fix_Time = mean(MeanTime), num_of_cases = n()) %>%
  arrange(desc(num_of_cases)) %>%
  ggplot(aes(x = reorder(LocationID,-num_of_cases) , y = Mean_Fix_Time, fill = LocationName)) +
  geom_col()
```



## arrange in sheet broken for 
```{r}
Table_max_locFixTime <- 
  all_broken %>%
   merge(y=Locations,by="LocationID",all = TRUE) %>%
   filter(!is.na(LocationID)) %>% 
  filter(CaseType == "كسر ماسورة مياه") %>% 
   group_by(LocationName) %>%
   summarise(Mean_Fix_Time = mean(MeanTime), num_of_cases = n()) %>%
  arrange(desc(num_of_cases)) 
```


#============================================================


##BILLS  Complain class
```{r}
## Bills issues
No_of_comp_Bills <-
  merge(x = All_Years, y = complain_classes, by = "ComplainClassID", all = TRUE) %>% 
  merge(y=complain_types,by ="ComplainTypeID",all=TRUE) %>% 
  filter(ComplainClassID==3) %>% 
  group_by(ComplainClassName,ComplainTypeName,IsClosed) %>% 
  summarise(no_of_complains_Bills = n()) %>% 
  
  ggplot(mapping = aes(x = ComplainTypeName , y = no_of_complains_Bills, fill = factor(IsClosed))) +
  geom_bar(stat="identity",width = 1)+
     
 geom_text(aes(label=paste(round(no_of_complains_Bills / sum(no_of_complains_Bills) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Bills issues", y ="Number of Complains",title = "Number of complain per Bills ", color = "IsClosed IDs") 
```

```{r}
Bills_Increasing <- 
  merge(x = All_Years, y = complain_classes, by = "ComplainClassID", all = TRUE) %>% 
  merge(y=Locations,by ="LocationID",all=TRUE) %>% 
  filter(ComplainTypeID==17) %>% 
  group_by(LocationID,LocationName.y) %>% 
  summarise(no_of_Inc_Bills = n()) %>% 
  
  ggplot(mapping = aes(x = LocationName.y , y = no_of_Inc_Bills, fill = factor(LocationID))) +
  geom_bar(stat="identity",width = 1)+
     
 geom_text(aes(label=paste(round(no_of_Inc_Bills / sum(no_of_Inc_Bills) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
  theme(axis.text.x = element_text(angle = 90, size =  12, face = 90))+
 labs(x = "Locations' Names", y ="Number of Inc_Bills",title = "Increasing in Bills issues ", fill = "Locations' IDs") 
```

```{r}
## Reason of Recipt issues
Recipt_Increasing <- 
  merge(x = All_Years, y = complain_classes, by = "ComplainClassID", all = TRUE) %>% 
  merge(y=Locations,by ="LocationID",all=TRUE) %>% 
  filter(ComplainTypeID %in% c(18,20,21)) %>% 
  group_by(LocationID,LocationName.y) %>% 
  summarise(no_of_Inc_Recipt = n()) %>% 
  
  ggplot(mapping = aes(x = LocationName.y , y = no_of_Inc_Recipt, fill = factor(LocationID))) +
  geom_bar(stat="identity",width = 1)+
     
 geom_text(aes(label=paste(round(no_of_Inc_Recipt / sum(no_of_Inc_Recipt) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
  theme(axis.text.x = element_text(angle = 90, size =  12, face = 90))+
 labs(x = "Locations' Names", y ="Number of Inc_Recipt",title = "Increasing in Recipt issues ", fill = "Locations' IDs") 


```


#============================================================


## Classes of water quality issues & types & its relation with towns and areas
```{r}

## complain of class quality for each type
No_of_comp_Quality <-
  merge(x = All_Years, y = complain_classes, by = "ComplainClassID", all = TRUE) %>% 
  merge(y=complain_types,by ="ComplainTypeID",all=TRUE) %>% 
  filter(ComplainClassID==4) %>% 
  group_by(ComplainClassName,ComplainTypeName,IsClosed) %>% 
  summarise(no_of_complains_quality = n()) %>% 
  
  ggplot(mapping = aes(x = ComplainTypeName , y = no_of_complains_quality, fill = factor(IsClosed))) +
  geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=paste(round(no_of_complains_quality / sum(no_of_complains_quality) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Quality issues", y ="Number of Complains",title = "Number of complain per Quality ", color = "ComplainClassID' IDs") 
```

```{r}
## Water Quality type for each town 
Water_Quality_Town <- 
  merge(x = All_Years,y=complain_types,by ="ComplainTypeID",all=TRUE) %>% 
  merge(y=towns,by ="TownID",all=TRUE) %>%
  filter(ComplainTypeID==4, TownName != "NULL") %>% 
  group_by(ComplainTypeName,TownName,TownID) %>% 
  summarise(no_of_complains_quality2 = n()) %>% 
  
  ggplot(mapping = aes(x = TownName , y = no_of_complains_quality2, fill = factor(TownID))) +
  geom_bar(stat="identity",width = 1)+
     
 geom_text(aes(label=paste(round(no_of_complains_quality2 / sum(no_of_complains_quality2) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Towns", y ="Number of Complains",title = "Number of complain per water Quality ", color = "TownName") 
```

```{r}
##Per Year
Water_Quality_Town_year <- 
  merge(x = All_Years,y=complain_types,by ="ComplainTypeID",all=TRUE) %>% 
  merge(y=towns,by ="TownID",all=TRUE) %>%
  filter(ComplainTypeID==4, TownName != "NULL",Year==2016) %>% 
  group_by(Year,TownName,TownID) %>% 
  summarise(no_of_complains_quality5 = n()) %>% 
  ggplot(mapping = aes(x = TownName , y = no_of_complains_quality5, fill =TownID)) +
  geom_bar(stat="identity",width = 1)+
  facet_grid(rows = vars(Year) )+
 geom_text(aes(label=paste(round(no_of_complains_quality5 / sum(no_of_complains_quality5) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Towns", y ="Number of Complains",title = "Number of complain per water Quality 2016", color = "TownName") 

  
```


## Population
```{r}
## population

Population1 <- 
  merge(x=Pop,y=towns,by ="TownID",all=TRUE) %>%
  filter( TownName != "NULL") %>% 
  group_by(TownID,TownName) %>% 
  summarise(classTown = n(),
            Sum_pop1 = sum(Sum_pop)) %>% 
  ggplot(mapping = aes(x = TownName , y = Sum_pop1, fill =factor(TownID))) +
  geom_bar(stat="identity",width = 1)+
  geom_text(aes(label=paste(round(Sum_pop1 / sum(Sum_pop1) * 100, 1), "%")),    vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "Towns", y ="Number of Population",title = "Population for each Town", color = "TownName") 
 
```


##WQI
```{r}
# AVG Intake WQI
WQI_Avg_I <-
  filter(Intake_WQI, year != 2015, !is.na(Column1)) %>% 
  group_by(StationName,Stationcode) %>% 
  summarise(stations_I = n(),
            Avg_I = mean(Column1)) %>% 
  ggplot(mapping = aes(x = Stationcode , y = Avg_I , fill =StationName)) +
  geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=floor(Avg_I)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x ="IntakeStationCode" , y ="Avg_I WQI",title = "Avg WQI for each Outlet Station", color = "Stations") 
```

```{r}
## AVG Outlet WQI
WQI_Avg_O <-
  filter(Outlet_WQI, year != 2015, !is.na(Column1)) %>% 
  group_by(StationName,Stationcode) %>% 
  summarise(stations = n(),
            Avg_O = mean(Column1)) %>% 
  ggplot(mapping = aes(x =Stationcode  , y = Avg_O,fill = StationName )) +
  geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=floor(Avg_O)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "OutletStationCode", y ="Avg_O WQI",title = "Avg WQI for each Outlet Station", color = "Stations") 
```

```{r}
## quality in seasons for intake with filter by years
WQI_Avg_I_season <-
  filter(Intake_WQI, year != 2015, !is.na(Column1)) %>% 
  group_by(StationName,Stationcode,Season) %>% 
  summarise(stations_I = n(),
            AvgQ_I = mean(Column1)) %>% 
  ggplot(mapping = aes(x = Stationcode , y = AvgQ_I , fill = StationName)) +
  geom_bar(stat="identity",width = 1)+
  facet_grid(rows = vars(Season) )+
 geom_text(aes(label=floor(AvgQ_I)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "IntakeStationCode", y ="Avg_I WQI",title = "Avg WQI for each Intake Station in season" ) 
```

```{r}
## quality in seasons for outlet with filter by years
WQI_Avg_O_season <-
  filter(Outlet_WQI, year != 2015, !is.na(Column1)) %>% 
  group_by(StationName,Stationcode,Season) %>% 
  summarise(stations_I = n(),
            AvgQ_O = mean(Column1)) %>% 
  ggplot(mapping = aes(x = Stationcode , y = AvgQ_O , fill =StationName)) +
  geom_bar(stat="identity",width = 1)+
  facet_grid(rows = vars(Season) )+
 geom_text(aes(label=floor(AvgQ_O)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x = "IntakeStationCode", y ="Avg_O WQI",title = "Avg WQI for each Outlet Station in season") 


```


##WQI Parameters 
```{r}
Avg_Coliform_I <- 
  filter(Intakes,!is.na(StationName), !is.na(Total_Coliform),year!=2015) %>% 
  merge(y =Intake_WQI,by ="StationName", all = TRUE) %>% 
  group_by(StationName,Stationcode) %>% 
  summarise(AVG_Coliform_I = mean(Total_Coliform) ) %>% 
  ggplot( aes(x = Stationcode, y =AVG_Coliform_I,fill =factor (StationName)  )) + 
   geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=round(AVG_Coliform_I)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x ="StationName" , y ="AVG_Coliform_I",title = "AVG_Coliform for each Intake Station", fill = "Stations' Code") 
```

```{r}
Avg_Coliform_O <- 
  filter(Outlets,!is.na(StationName), !is.na(Total_Coliform),year!=2015) %>% 
   merge(y =Outlet_WQI,by ="StationName", all = TRUE) %>% 
  group_by(StationName,Stationcode) %>% 
  summarise(AVG_Coliform_O = mean(Total_Coliform)) %>% 
  ggplot( aes(x = Stationcode, y =AVG_Coliform_O,fill = factor(StationName)  )) + 
   geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=round(AVG_Coliform_O,3)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x ="StationName" , y ="AVG_Coliform_O",title = "AVG_Coliform for each Outlet Station", fill = "Stations' Code") 
```

```{r}
## Total_Algae_Count
Avg_Algae_I <- 
  filter(Intakes,!is.na(StationName), !is.na(Total_Algae_Count),year!=2015) %>% 
  merge(y =Intake_WQI,by ="StationName", all = TRUE) %>% 
  group_by(StationName,Stationcode) %>% 
  summarise(AVG_Algae_I = mean(Total_Algae_Count) ) %>% 
  ggplot( aes(x = Stationcode, y =AVG_Algae_I,fill = (StationName)  )) + 
   geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=round(AVG_Algae_I)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x ="StationName" , y ="AVG_Algae_I",title = "AVG_Algae for each Intake Station", fill = "Stations' Code") 
```

```{r}
Avg_Algae_O <- 
  filter(Outlets,!is.na(StationName), Total_Algae_Count > 20,!is.na(Total_Algae_Count),year!=2015) %>% 
  merge(y =Outlet_WQI,by ="StationName", all = TRUE) %>% 
  group_by(StationName,Stationcode) %>% 
  summarise(AVG_Algae_O = mean(Total_Algae_Count)) %>% 
  ggplot( aes(x = Stationcode, y =AVG_Algae_O,fill = factor(StationName)  )) + 
   geom_bar(stat="identity",width = 1)+
 geom_text(aes(label=round(AVG_Algae_O,3)), vjust=-0.3, size=3.5)+
  theme_minimal()+
 labs(x ="StationName" , y ="AVG_Algae_O",title = "AVG_Algae for each Outlet Station", fill = "Stations' Code") 

```

#============================================================

## Relation btw no Complains and different towns & sectors
```{r}
## Relation btw complains & sector
No_of_comp_sector <-
  merge(x = All_Years, y = Sectors, by = "SectorID", all = TRUE) %>% 
  ##filter(Year==2016) %>% 
  group_by(SectorID,SectorName) %>% 
  summarise(no_of_complains1 = n()) %>% 
  ggplot(mapping = aes(x = SectorName , y = no_of_complains1, fill = SectorID))+
  geom_bar(stat="identity")+
  geom_text(aes(label=paste(round(no_of_complains1 / sum(no_of_complains1) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  scale_fill_brewer(palette="Blues")+
  theme_minimal()+
  labs(x = "Sectors' Names", y ="Number of Complains",title = "Number of complain per sector", color = "Sectors' IDs")
```

```{r}
## Relation btw complains & towns
No_of_comp_town <- 
  merge(x = All_Years, y = towns, by = "TownID", all = TRUE) %>% 
  filter(TownName != "NULL",!is.na(TownName), ComplainTypeID != 8) %>% 
  group_by(TownID,TownName) %>% 
  summarise(no_of_complains2 = n()) %>% 
  ggplot(mapping = aes(x = TownName, y = no_of_complains2, fill = TownID))+
  geom_bar(stat="identity")+
  geom_text(aes(label=paste(round(no_of_complains2 / sum(no_of_complains2) * 100, 1), "%")), vjust=-0.3, size=3.5)+
  theme_minimal()+
  labs(x = "Towns' Names", y ="Number of Complains",title = "Number of complains per Town", color = "Towns' IDs")
  
```


###Relation Between Population & Different Complains
```{r}
BI_POP <- cowplot::plot_grid(Bills_Increasing, Population1, align = "v", ncol = 1)
```

```{r}
WQ_POP <- cowplot::plot_grid(Water_Quality_Town, Population1, align = "v", ncol = 1)
```

```{r}
WQI_I_O <- cowplot::plot_grid(WQI_Avg_I, WQI_Avg_O, align = "v", ncol = 1)
```

```{r}
WQI_I_O_Season <-  cowplot::plot_grid(WQI_Avg_I_season, WQI_Avg_O_season, align = "v", ncol = 1)
```

```{r}
Coliform_O_I <- cowplot::plot_grid(Avg_Coliform_I, Avg_Coliform_O, align = "v", ncol = 1)
```

```{r}
Algia_O_I <- cowplot::plot_grid(Avg_Algae_I, Avg_Algae_O, align = "v", ncol = 1)
```