-
Notifications
You must be signed in to change notification settings - Fork 0
/
DashBoard.Rmd
executable file
·259 lines (209 loc) · 9.73 KB
/
DashBoard.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
---
title: "DashBoard for Water Complains"
output:
flexdashboard::flex_dashboard:
orientation: columns
vertical_layout: fill
---
```{r setup, include=FALSE}
library(flexdashboard)
library(tidyverse)
library(plotly)
library(shiny)
library(readxl)
library(tidyverse)
library(ggplot2)
library(reshape2)
source("http://www.sthda.com/upload/rquery_cormat.r")
library(corrplot)
library(lubridate)
```
## 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)
```
Column {data-width=650}
-----------------------------------------------------------------------
### Chart A
```{r}
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")
ggplotly(No_of_comp_class)
```
Column {data-width=350}
-----------------------------------------------------------------------
### Chart B
```{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")
ggplotly(No_of_comp_closed)
```
### Chart C
```{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)) %>%
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")
ggplotly(No_of_comp_town)
```