BKM Datasets Explanation and Gathering Data

I obtained the Sectoral Growing data between 2017-2019 directly from BKM website. The data includes 25 main sectors with number of transactions and transaction amounts (in million TL). Transactions are divided into two groups: credit and debit card.

library(rvest)
library(xml2)
library(tidyverse)
library(formattable)
library(scales)

Gathering 2017 and 2018 Data

for (i in (2017:2018)){
  for (a in (1:12)){
    
    url <- paste0("https://bkm.com.tr/secilen-aya-ait-sektorel-gelisim/?filter_year=",i,"&filter_month=",a,"&List=Listele",sep ="")
    
    read<- read_html(url)
    
    z<-html_table(read,fill = T)
    
    nam <- paste("data",i,a,sep="_")
    
    x <- data.frame ("Isyeri_Grubu"=z[[4]][["X1"]], 
                                   "Islem_Adedi_Kredi_Kartı" =z[[4]][["X2"]],
                                   "Islem_Adedi_Banka_Kartı" =z[[4]][["X3"]] , 
                                   "Islem_Tutari_Kredi_Kartı" = z[[4]][["X4"]],
                                   "Islem_Tutari_Banka_Kartı"=z[[4]][["X5"]],
                                   "Yıl"=i,"Ay"=a, stringsAsFactors = FALSE)
    
    assign(nam,x)
  }

}

Gathering 2019 Data

for (i in (1:6)){
  url <- paste0("https://bkm.com.tr/secilen-aya-ait-sektorel-gelisim/?filter_year=2019&filter_month=",i,"&List=Listele",sep ="")
    
  read<- read_html(url)
    
  z<-html_table(read,fill = T)
   
  nam <- paste("data_2019",i,sep="_")
    
  x <- data.frame ("Isyeri_Grubu"=z[[4]][["X1"]], 
                                 "Islem_Adedi_Kredi_Kartı"=z[[4]][["X2"]],
                                 "Islem_Adedi_Banka_Kartı"=z[[4]][["X3"]],
                                 "Islem_Tutari_Kredi_Kartı"=z[[4]][["X4"]],
                                 "Islem_Tutari_Banka_Kartı"=z[[4]][["X5"]],                 
                                 "Yıl"=2019,"Ay"=i, stringsAsFactors = FALSE)               
                                  
                                   
    
    
  assign(nam,x)
}

Data Cleaning and Merging

Slicecing Rows and Mering Data

data_2017_1 <- data_2017_1 %>% slice(-c(1,2,29))
data_2017_2 <- data_2017_2 %>% slice(-c(1,2,29))
data_2017_3 <- data_2017_3 %>% slice(-c(1,2,29))
data_2017_4 <- data_2017_4 %>% slice(-c(1,2,29))
data_2017_5 <- data_2017_5 %>% slice(-c(1,2,29))
data_2017_6 <- data_2017_6 %>% slice(-c(1,2,29))
data_2017_7 <- data_2017_7 %>% slice(-c(1,2,29))
data_2017_8 <- data_2017_8 %>% slice(-c(1,2,29))
data_2017_9 <- data_2017_9 %>% slice(-c(1,2,29))
data_2017_10 <- data_2017_10 %>% slice(-c(1,2,29))
data_2017_11 <- data_2017_11 %>% slice(-c(1,2,29))
data_2017_12 <- data_2017_12 %>% slice(-c(1,2,29))

data_2018_1 <- data_2018_1 %>% slice(-c(1,2,29))
data_2018_2 <- data_2018_2 %>% slice(-c(1,2,29))
data_2018_3 <- data_2018_3 %>% slice(-c(1,2,29))
data_2018_4 <- data_2018_4 %>% slice(-c(1,2,29))
data_2018_5 <- data_2018_5 %>% slice(-c(1,2,29))
data_2018_6 <- data_2018_6 %>% slice(-c(1,2,29))
data_2018_7 <- data_2018_7 %>% slice(-c(1,2,29))
data_2018_8 <- data_2018_8 %>% slice(-c(1,2,29))
data_2018_9 <- data_2018_9 %>% slice(-c(1,2,29))
data_2018_10 <- data_2018_10 %>% slice(-c(1,2,29))
data_2018_11 <- data_2018_11 %>% slice(-c(1,2,29))
data_2018_12 <- data_2018_12 %>% slice(-c(1,2,29))

data_2019_1 <- data_2019_1 %>% slice(-c(1,2,29))
data_2019_2 <- data_2019_2 %>% slice(-c(1,2,29))
data_2019_3 <- data_2019_3 %>% slice(-c(1,2,29))
data_2019_4 <- data_2019_4 %>% slice(-c(1,2,29))
data_2019_5 <- data_2019_5 %>% slice(-c(1,2,29))
data_2019_6 <- data_2019_6 %>% slice(-c(1,2,29))

combined_BKM_data <- rbind(data_2017_1,data_2017_2,data_2017_3,data_2017_4,data_2017_5, data_2017_6, data_2017_7, data_2017_8,data_2017_9,data_2017_10,data_2017_11,data_2017_12,data_2018_1, data_2018_2, data_2018_3,data_2018_4,data_2018_5,data_2018_6,data_2018_7,data_2018_8,data_2018_9,data_2018_10, 
data_2018_11,data_2018_12,data_2019_1,data_2019_2,data_2019_3, 
data_2019_4,data_2019_5,data_2019_6)

head(combined_BKM_data)
##                             Isyeri_Grubu Islem_Adedi_Kredi_Karti
## 1                         ARABA KIRALAMA                 234.458
## 2 ARAÇ KIRALAMA-SATIS/SERVIS/YEDEK PARÇA               2.693.110
## 3           BENZIN VE YAKIT ISTASYONLARI              22.457.882
## 4                     BIREYSEL EMEKLILIK               2.568.154
## 5                           ÇESITLI GIDA              20.568.348
## 6                     DOGRUDAN PAZARLAMA                 425.443
##   Islem_Adedi_Banka_Karti Islem_Tutari_Kredi_Karti
## 1                  29.881                   116,02
## 2                 433.196                 1.601,78
## 3               6.460.535                 3.861,49
## 4                     508                   607,99
## 5              10.214.579                 3.142,49
## 6                  34.240                   113,06
##   Islem_Tutari_Banka_Karti  Yil Ay
## 1                     5,24 2017  1
## 2                    76,65 2017  1
## 3                   427,61 2017  1
## 4                     0,17 2017  1
## 5                   354,55 2017  1
## 6                     2,92 2017  1
glimpse(combined_BKM_data)
## Observations: 780
## Variables: 7
## $ Isyeri_Grubu             <chr> "ARABA KIRALAMA", "ARAÇ KIRALAMA-SATI...
## $ Islem_Adedi_Kredi_Karti  <chr> "234.458", "2.693.110", "22.457.882",...
## $ Islem_Adedi_Banka_Karti  <chr> "29.881", "433.196", "6.460.535", "50...
## $ Islem_Tutari_Kredi_Karti <chr> "116,02", "1.601,78", "3.861,49", "60...
## $ Islem_Tutari_Banka_Karti <chr> "5,24", "76,65", "427,61", "0,17", "3...
## $ Yil                      <dbl> 2017, 2017, 2017, 2017, 2017, 2017, 2...
## $ Ay                       <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...

Changing Character Columns to Numeric Values

combined_BKM_data$Islem_Adedi_Kredi_Karti<-as.numeric(str_replace_all(combined_BKM_data$Islem_Adedi_Kredi_Karti, pattern=fixed("."), ""))

combined_BKM_data$Islem_Adedi_Banka_Karti<-as.numeric(str_replace_all(combined_BKM_data$Islem_Adedi_Banka_Karti, pattern=fixed("."), ""))

combined_BKM_data$Islem_Tutari_Kredi_Karti<-str_replace_all(combined_BKM_data$Islem_Tutari_Kredi_Karti, pattern=fixed("."), "")
combined_BKM_data$Islem_Tutari_Kredi_Karti<-as.numeric(str_replace_all(combined_BKM_data$Islem_Tutari_Kredi_Karti, pattern=fixed(","), "."))

combined_BKM_data$Islem_Tutari_Banka_Karti<-str_replace_all(combined_BKM_data$Islem_Tutari_Banka_Karti, pattern=fixed("."), "")
combined_BKM_data$Islem_Tutari_Banka_Karti<-as.numeric(str_replace_all(combined_BKM_data$Islem_Tutari_Banka_Karti, pattern=fixed(","), "."))

combined_BKM_data <- combined_BKM_data %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) 


head(combined_BKM_data)
##                             Isyeri_Grubu Islem_Adedi_Kredi_Karti
## 1                         ARABA KIRALAMA                  234458
## 2 ARAÇ KIRALAMA-SATIS/SERVIS/YEDEK PARÇA                 2693110
## 3           BENZIN VE YAKIT ISTASYONLARI                22457882
## 4                     BIREYSEL EMEKLILIK                 2568154
## 5                           ÇESITLI GIDA                20568348
## 6                     DOGRUDAN PAZARLAMA                  425443
##   Islem_Adedi_Banka_Karti Islem_Tutari_Kredi_Karti
## 1                   29881                   116.02
## 2                  433196                  1601.78
## 3                 6460535                  3861.49
## 4                     508                   607.99
## 5                10214579                  3142.49
## 6                   34240                   113.06
##   Islem_Tutari_Banka_Karti  Yil Ay
## 1                     5.24 2017  1
## 2                    76.65 2017  1
## 3                   427.61 2017  1
## 4                     0.17 2017  1
## 5                   354.55 2017  1
## 6                     2.92 2017  1
glimpse(combined_BKM_data)
## Observations: 780
## Variables: 7
## $ Isyeri_Grubu             <chr> "ARABA KIRALAMA", "ARAÇ KIRALAMA-SATI...
## $ Islem_Adedi_Kredi_Karti  <dbl> 234458, 2693110, 22457882, 2568154, 2...
## $ Islem_Adedi_Banka_Karti  <dbl> 29881, 433196, 6460535, 508, 10214579...
## $ Islem_Tutari_Kredi_Karti <dbl> 116.02, 1601.78, 3861.49, 607.99, 314...
## $ Islem_Tutari_Banka_Karti <dbl> 5.24, 76.65, 427.61, 0.17, 354.55, 2....
## $ Yil                      <dbl> 2017, 2017, 2017, 2017, 2017, 2017, 2...
## $ Ay                       <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...

Analysing the Data

1) Total Transaction Amounts and Shares of Sectors between 2017-2019

isyeri_grubu_total<-combined_BKM_data %>% group_by(Isyeri_Grubu) %>% summarise(Total_Islem_Adedi_Kredi_Karti=sum(Islem_Adedi_Kredi_Karti), Total_Islem_Tutari_Kredi_Karti=sum(Islem_Tutari_Kredi_Karti),Total_Islem_Adedi_Banka_Karti=sum(Islem_Adedi_Banka_Karti), Total_Islem_Tutari_Banka_Karti = sum(Islem_Tutari_Banka_Karti),Total_Islem = Total_Islem_Adedi_Banka_Karti + Total_Islem_Adedi_Kredi_Karti,Total_Tutar=Total_Islem_Tutari_Banka_Karti+Total_Islem_Tutari_Kredi_Karti) %>% ungroup() %>% mutate(Total_Islem_Share= percent(Total_Islem/sum(Total_Islem)),Total_Tutar_Share=percent(Total_Tutar/sum(Total_Tutar)))

isyeri_grubu_total %>% select (Isyeri_Grubu ,Total_Tutar,Total_Tutar_Share) %>% arrange(desc(Total_Tutar))
## # A tibble: 26 x 3
##    Isyeri_Grubu                          Total_Tutar Total_Tutar_Share
##    <chr>                                       <dbl> <chr>            
##  1 MARKET VE ALISVERIS MERKEZLERI            360422. 18.0%            
##  2 BENZIN VE YAKIT ISTASYONLARI              179316. 9.0%             
##  3 GIYIM VE AKSESUAR                         163796. 8.2%             
##  4 ÇESITLI GIDA                              137309. 6.9%             
##  5 HIZMET SEKTÖRLERI                         120958. 6.0%             
##  6 ELEKTRIK-ELEKTRONIK ESYA, BILGISAYAR      106834. 5.3%             
##  7 YEMEK                                      87569. 4.4%             
##  8 SIGORTA                                    82711. 4.1%             
##  9 YAPI MALZEMELERI, HIRDAVAT, NALBURIYE      78196. 3.9%             
## 10 SAGLIK/SAGLIK ÜRÜNLERI/KOZMETIK            76140. 3.8%             
## # ... with 16 more rows

2) Bar Graph of Sectors Total Transaction Amounts between 2017-2019

ggplot(isyeri_grubu_total, aes(x=Isyeri_Grubu, y=Total_Tutar)) +
  geom_bar(stat="identity",fill="steelblue")+ coord_flip() + scale_y_continuous(labels=comma) +
  labs(title = "Total Amounts of Groups Between 2017-2019", x="", y="") +theme_minimal()

3) Progression of 3 Choosen Sector, Displayed in Line Graph

3.1)“Market ve Alışveriş Merkezi”

year_month_BKM_data_MAM<- combined_BKM_data %>% mutate(Total_Islem_Tutari = Islem_Tutari_Kredi_Karti + Islem_Tutari_Kredi_Karti, Yil_Ay=paste0(Yil,"/",Ay)) %>% filter(Isyeri_Grubu==c(Isyeri_Grubu[17]))

y_levels<- c("2017/1","2017/2","2017/3","2017/4","2017/5","2017/6","2017/7","2017/8","2017/9","2017/10","2017/11","2017/12","2018/1","2018/2","2018/3","2018/4","2018/5","2018/6","2018/7","2018/8","2018/9","2018/10","2018/11","2018/12","2019/1","2019/2","2019/3","2019/4","2019/5","2019/6")


ggplot(year_month_BKM_data_MAM, aes(x = ordered(Yil_Ay,levels=y_levels), y=Total_Islem_Tutari, group=Isyeri_Grubu)) + geom_line(color="red")+geom_point() +theme(axis.text.x = element_text(angle = 90, hjust = 1),legend.position="bottom") + labs(title = "Monthly Total Transaction Amount (million TL)",x="Date", y="Total Transaction Amount (million TL)") + expand_limits(y = 0)

3.2)“BENZİN VE YAKIT İSTASYONLARI”

year_month_BKM_data_BKI<- combined_BKM_data %>% mutate(Total_Islem_Adedi = Islem_Adedi_Kredi_Karti + Islem_Adedi_Kredi_Karti, Yil_Ay=paste0(Yil,"/",Ay)) %>% filter(Isyeri_Grubu==c(Isyeri_Grubu[3]))



ggplot(year_month_BKM_data_BKI, aes(x = ordered(Yil_Ay,levels=y_levels), y=Total_Islem_Adedi, group=Isyeri_Grubu)) + geom_line(color="blue") + geom_point() +theme(axis.text.x = element_text(angle = 90, hjust = 1),legend.position="bottom") + labs(title = "Monthly Total Count of Transaction",x="Date", y="Total Count of Transaction") + expand_limits(y = 0) + scale_y_continuous(labels=comma)

3.3)“KAMU/VERGI ODEMELERI”

year_month_BKM_data_Kamu<- combined_BKM_data %>% mutate(Total_Islem_Tutari = Islem_Tutari_Kredi_Karti + Islem_Tutari_Kredi_Karti, Yil_Ay=paste0(Yil,"/",Ay)) %>% filter(Isyeri_Grubu==c(Isyeri_Grubu[12]))



ggplot(year_month_BKM_data_Kamu, aes(x = ordered(Yil_Ay,levels=y_levels), y=Total_Islem_Tutari, group=Isyeri_Grubu)) + geom_line(color="darkgreen") + geom_point() +theme(axis.text.x = element_text(angle = 90, hjust = 1),legend.position="bottom") + labs(title = "Monthly Total Transaction Amount (million TL)", x="Date", y="Total Transaction Amount (million TL)") + expand_limits(y = 0)