class: inverse, center, middle

# Table of Contents

.pull-left[

* Our Goals

* About Trading Data Set

* Data Preprocess

* Line Graph


]

.pull-right[


* Mapping

* Reporting Part

* Thanks for Listening

]


---

class: inverse, center, middle

# Goals

Our goal is to achieve logical results by using monthly import and export data between 2013 and 2020 by visualizing and analyzing their distribution by cities for Turkey. In addition, we aimed to reach the balance between export and import figures.

---

class: inverse, center, middle

# About Trading Data Set

.left[
We have 7 different variables in our main data set:

* `Year`: Trading Year.
* `Month`: Trading Month.
* `City`: City Name.
* `TradingType`: Type of the Trading. Export or Import.
* `AmountUSD`: Trading amount in USD.
* `AmountEUR`: Trading amount in EUR.
* `AmountTL`: Trading amount in TL.
]
---


class: inverse, center, middle

# Data Preprocess

.left[
* RData file was created for Trading, Population, Coordinates, Geospatial and Region data.
* Year and Month variables converted to a valid date format by merging and translating.
* City variables cleaned by separating string from City Codes.
* All Turkish characters are converted into English characters.
* Geospatial dataframe's city variable converted into English characters.  
]
---



class: inverse, center, middle

# Line Graph

When we visualized the import and export figures by months of the last two years, we observed that the difference was at least in November 2018 and at most in August 2020.
While the period with the highest import figures is September 2020, the period with the highest export figures is May 2019.

---

class: inverse, center, middle

```{r eval=TRUE, fig.width=10.5, fig.height=6,echo=FALSE, message=FALSE, warning=FALSE}
library(tidyverse)
library(devtools)
library(leaflet)
library(ggplot2)
library(dplyr)
library(DT)
library(tidyverse)
library(rio)
library(leaflet)
library(shiny)
library(treemap)
library(plotly)
library(ggmap)
library(leaflet.extras)
library(d3treeR)

githubURL <- "https://github.com/pjournal/mef04g-rsizlar/blob/gh-pages/Data/data.RData?raw=true"
load(url(githubURL))

#Clean Month and City variables
data$Month<-str_sub(data$Month,start= 4)

data<-data %>% 
  separate(City, c("CityCode", "City"),sep = "( - )")

data<-data%>%
  mutate(Month=case_when(data$Month=="Ocak"~"01",
                         data$Month=="Subat"~"02",
                         data$Month=="Mart"~"03",
                         data$Month=="Nisan"~"04",
                         data$Month=="Mayis"~"05",
                         data$Month=="Haziran"~"06",
                         data$Month=="Temmuz"~"07",
                         data$Month=="Agustos"~"08",
                         data$Month=="Eylul"~"09",
                         data$Month=="Ekim"~"10",
                         data$Month=="Kasim"~"11",
                         data$Month=="Aralik"~"12"))

data<-data%>%
  mutate(TradingType=case_when(data$TradingType=="Ithalat"~"Import",
                               data$TradingType=="Ihracat"~"Export"))

#Create Date Variable from Year and Month
data$Date<-paste0(data$Year,as.character("-"),data$Month,as.character("-01"))
data$Date<-as.Date(data$Date, format="%Y-%m-%d")

data<-left_join(data,reg)

turkceden_ingilizceye <- function(dataset){
  turkce_harfler<- c("Ç","Ş","Ğ","İ","Ü","Ö","ç","ş","ğ","ı","ü","ö")
  ingilizce_harfler<- c("C","S","G","I","U","O","c","s","g","i","u","o")
  dataset=mgsub(turkce_harfler,ingilizce_harfler,dataset)
  return(dataset)
}
  

mgsub <- function(pattern, replacement, x, ...) {
  n = length(pattern)
  if (n != length(replacement)) {
    stop("pattern and replacement do not have the same length.")
  }
  result = x
  for (i in 1:n) {
    result <- gsub(pattern[i],replacement[i],result)
  }
  return(result)
}

tur@data$NAME_1 <- turkceden_ingilizceye(tur@data$NAME_1 )
tur@data$NAME_1 <- gsub("K. Maras", "Kahramanmaras",tur@data$NAME_1 )
tur@data$NAME_1 <- gsub("Kinkkale","Kirikkale",tur@data$NAME_1 )
tur@data$NAME_1 <- gsub("Zinguldak", "Zonguldak", tur@data$NAME_1 )
tur@data$NAME_1 <- gsub("Afyon","Afyonkarahisar", tur@data$NAME_1 )

tur@data$NAME_1 <- toupper(tur@data$NAME_1) 

data <-data %>% left_join(koordinat,by=c("City"))   

data_mean <- data  %>% 
  filter(Year>=2018)%>%
  select(City,TradingType,AmountUSD,LONGITUDE,LATITUDE)%>%
  group_by(City,TradingType,LONGITUDE,LATITUDE) %>% 
  summarise(AmountUSD=sum(AmountUSD))%>%
  pivot_wider(names_from = TradingType, values_from = AmountUSD)%>%
  mutate(Balance=round((Export-Import)/1000000,2))%>%
  select(City,LONGITUDE,LATITUDE,Balance)

tur@data <- tur@data%>%left_join(data_mean,by=c("NAME_1"="City"))

cols<- c("Export"="#F8766D","Import"="#00BFC4","Balance"="#FFDB6D")

p<-data%>%
  filter(Date>"2018-08-01")%>%
  select(Date,TradingType,AmountUSD)%>%
  group_by(Date,TradingType)%>%
  summarise(Total=round(sum(AmountUSD)/1000000000,2))%>%
  pivot_wider(names_from = TradingType, values_from = Total)%>%
  mutate(Balance = Export-Import)%>%
  ggplot() +
  geom_col(aes(x=Date, y=Balance, fill = "Balance"),size = 0.5, color = "#C4961A")+
  geom_line(aes(x=Date, y=Export,color="Export"),size=1) +
  geom_line(aes(x=Date, y=Import,color="Import"),size=1) +
  scale_colour_manual(name="Lines",values=cols) + scale_fill_manual(name="Bar",values=cols)+
  theme_test() + 
  labs(x="Date", y="Total Amount (billion dollars)", title = "Yearly Total Export/Import Comparison")

ggplotly(p,tooltip = c("Balance","Export","Import","Date"))%>%
  layout(legend = list(orientation = "h", x = 0.25, y = -0.2))

```

---
class: inverse, center, middle

# Mapping

We used the map method in order to examine the Import and Export figures according to the provinces in more detail.
In this way, by clicking on the cities, you can better understand the distribution between Export and Import over the years and access its details.

---

class: inverse, center, middle


```{r eval=TRUE, fig.width=10,5, fig.height=6,echo=FALSE, message=FALSE, warning=FALSE}


labels <- sprintf("<strong>%s</strong><br/>%s $ ",tur@data$NAME_1,prettyNum(tur@data$Balance, format = "f", big.mark = ".",decimal.mark ="," )
) %>% lapply(htmltools::HTML)

bins <- c(-Inf,-15000, -5000, 0, 1000, 3000, 5000, 10000)
pal <- colorBin("viridis", domain =tur@data$Balance, bins = bins)

leaflet(tur)  %>% 
  addProviderTiles("Stamen.TonerBackground", group = "Terrain")%>%
  addProviderTiles("CartoDB.DarkMatterNoLabels", group = "CartoDB"  ) %>% 
  addProviderTiles("Esri.OceanBasemap", group = "Esri") %>% 
  addPolygons(smoothFactor = 0.3, fillOpacity = 10,weight = 2,
              opacity = 1,color = "white",
              dashArray = "3",
              highlight = highlightOptions(stroke = 2,
                                           weight = 4,
                                           color = "#666",
                                           dashArray = NULL,
                                           fillOpacity = 0.7,
                                           bringToFront = TRUE),fillColor = ~pal(Balance),label=labels,labelOptions = labelOptions(
                                             style = list("font-weight" = "normal", padding = "3px 8px"),
                                             textsize = "15px",
                                             direction = "auto")) %>%
  addLegend(pal = pal, values = ~Balance, opacity = 1.0) %>%
  
  addLayersControl(baseGroups = c( "CartoDB", "Esri","Terrain")) %>% 
  setView(lng = 33.243322, lat =	41.963745, zoom =5.4)%>% addSearchOSM()%>% addReverseSearchOSM()%>% addResetMapButton()

```

---

class: inverse, top, top


```{r eval=TRUE, echo=FALSE, message=FALSE,fig.update_traces(marker_coloraxis=None),fig.dim = c(7, 5)}

tm_data<-data%>%
select(Year,Region,TradingType,AmountUSD)%>%
group_by(Year,Region,TradingType)%>%
summarise(Total=sum(AmountUSD)/1000000)%>%
filter(is.na(Region)==FALSE)

 
cat("\n") 
jpeg(filename="tree%d.jpeg")
d3tree3(treemap(tm_data, index = c("Region","TradingType","Year"),vSize="Total",
palette="Set3",vColor="Pop",
title="Annual Region Based Export/Import Distribution"),rootname = "Region")

```
---

class: inverse, center, middle

# Reporting Parts

When we examined the Import and Export figures by years, we observed that the Marmara region is at the highest level every year.

---

class: center, middle

```{r eval=TRUE,fig.width=2, fig.height=6, echo=FALSE, message=FALSE, warning=FALSE,fig.align="center"}

total_data<-data%>%
    filter(Year==2019)%>%
    select(City,TradingType,AmountUSD)%>%
    group_by(City,TradingType)%>%
    summarise(Amount = sum(AmountUSD))%>%
    pivot_wider(names_from = TradingType, values_from = Amount,values_fill = 0)

table_export<-left_join(total_data,pop)%>%
    select(City,Export,Pop,MenPop,WomenPop)%>%
    mutate(ExportPP = round(Export/Pop,2),
           WomenRatio = round(WomenPop/(WomenPop+MenPop),2),
           Export=round(Export/1000000,2))%>%
    arrange(desc(ExportPP))%>%
  select(City,Export,Pop,ExportPP)


DT::datatable(head(table_export, 10),
  fillContainer = FALSE, options = list(pageLength = 4))

```


Above table gives 2019 Trading figures sorted by Export amount per Person for each city. First 5 cities have huge industry operations and their Export metrics are better than other cities.

---

class: center, middle

```{r eval=TRUE,fig.width=2, fig.height=6, echo=FALSE, message=FALSE, warning=FALSE,fig.align="center"}

total_data<-data%>%
    filter(Year==2019)%>%
    select(City,TradingType,AmountUSD)%>%
    group_by(City,TradingType)%>%
    summarise(Amount = sum(AmountUSD))%>%
    pivot_wider(names_from = TradingType, values_from = Amount,values_fill = 0)

table_import<-left_join(total_data,pop)%>%
    select(City,Import,Pop,MenPop,WomenPop)%>%
    mutate(ImportPP = round(Import/Pop,2),
           WomenRatio = round(WomenPop/(WomenPop+MenPop),2),
           Import=round(Import/1000000,2))%>%
    arrange(desc(ImportPP))%>%
  select(City,Import,Pop,ImportPP)

DT::datatable(head(table_import, 10),
  fillContainer = FALSE, options = list(pageLength = 4))

```


Above table gives 2019 Trading figures sorted by Import amount per Person for each city. Cities in the first five rows changed. Karabük and Çorum took their places here. Due to the low population Import per Person metric seems to be high. 

---

class: center, middle

```{r eval=TRUE,fig.width=2, fig.height=6, echo=FALSE, message=FALSE, warning=FALSE,fig.align="center"}

total_data<-data%>%
    filter(Year==2019)%>%
    select(City,TradingType,AmountUSD)%>%
    group_by(City,TradingType)%>%
    summarise(Amount = sum(AmountUSD))%>%
    pivot_wider(names_from = TradingType, values_from = Amount,values_fill = 0)

table_balance<-left_join(total_data,pop)%>%
    select(City,Export,Import,Pop,MenPop,WomenPop)%>%
    mutate(BalancePP = round((Import-Export)/Pop,2),
           WomenRatio = round(WomenPop/(WomenPop+MenPop),2))%>%
    arrange(desc(BalancePP))%>%
    select(City,Pop,BalancePP)


DT::datatable(head(table_balance, 10),
  fillContainer = FALSE, options = list(pageLength = 4))

```


Above table gives 2019 Trading figures sorted by Balance amount per Person for each city. Cities with low population have higher Balance per Person value. Some of these cities have bigger industry operations than their population size.


---
class: inverse, center, middle

# Thanks for Listening
