BKM Assignment
Tunahan Kılıç
11 Kasım 2019
1.BKM Dataset
BKM data includes the numerical values for transactions made from credit card and debit card, including the number of transactions and transaction amount according to sectoral groups. The analysis was conducted using data from the beginning of 2017 to the period of June 2019.
2.Preparing and Cleaning Dataset
2.1. Preparing Dataset
Loading required libraries for analysis
library(rvest)
library(tidyverse)
library(lubridate)
library(ggplot2)getYearMonth<- function(year,month) {
url <- paste('https://bkm.com.tr/secilen-aya-ait-sektorel-gelisim/?filter_year=',year,'&filter_month=',month,'&List=Listele', sep='',collapse = NULL)
return(url)
}
bkm_year <-c('2017','2018','2019')
bkm_month <- c('01','02','03','04','05','06','07','08','09','10','11','12')
bkm_full <- ""
for (i in 1:length(bkm_year)) {
for (j in 1:length(bkm_month)) {
if (bkm_year[i] == "2019" & bkm_month[j] == "07") {break}
url<-getYearMonth(bkm_year[i],bkm_month[j])
pages <- read_html(url)
df <- pages %>% html_nodes("table") %>% .[(4)] %>% html_table(pages, fill = TRUE, header = FALSE) %>% as.data.frame() %>% slice(3:max(nrow(.)))
df <- df %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(yearMonth = paste(bkm_year[i],bkm_month[j],sep='-'))
bkm_full <- rbind(bkm_full, df)
}
}
colnames(bkm_full) <- c("isyeri_grubu", "kk_islem_adedi", "bk_islem_adedi", "kk_islem_tutari", "bk_islem_tutari", "yil_ay")2.2. Cleaning Dataset
options(scipen = 999)
#Removing "TOPLAM" rows that belongs to each month
bkm_full <- bkm_full %>% filter(!str_detect(isyeri_grubu,"TOPLAM"))
#Converting char yil_ay column to date format
bkm_full$yil_ay <- lubridate::ymd(bkm_full$yil_ay, truncated = 1L)
bkm_full = bkm_full[-1,]
head(bkm_full)## isyeri_grubu kk_islem_adedi bk_islem_adedi
## 2 ARABA KİRALAMA 234.458 29.881
## 3 ARAÇ KİRALAMA-SATIŞ/SERVİS/YEDEK PARÇA 2.693.110 433.196
## 4 BENZİN VE YAKIT İSTASYONLARI 22.457.882 6.460.535
## 5 BIREYSEL EMEKLILIK 2.568.154 508
## 6 ÇEŞİTLİ GIDA 20.568.348 10.214.579
## 7 DOĞRUDAN PAZARLAMA 425.443 34.240
## kk_islem_tutari bk_islem_tutari yil_ay
## 2 116,02 5,24 2017-01-01
## 3 1.601,78 76,65 2017-01-01
## 4 3.861,49 427,61 2017-01-01
## 5 607,99 0,17 2017-01-01
## 6 3.142,49 354,55 2017-01-01
## 7 113,06 2,92 2017-01-01str(bkm_full)## 'data.frame': 780 obs. of 6 variables:
## $ isyeri_grubu : chr "ARABA KİRALAMA" "ARAÇ KİRALAMA-SATIŞ/SERVİS/YEDEK PARÇA" "BENZİN VE YAKIT İSTASYONLARI" "BIREYSEL EMEKLILIK" ...
## $ kk_islem_adedi : chr "234.458" "2.693.110" "22.457.882" "2.568.154" ...
## $ bk_islem_adedi : chr "29.881" "433.196" "6.460.535" "508" ...
## $ kk_islem_tutari: chr "116,02" "1.601,78" "3.861,49" "607,99" ...
## $ bk_islem_tutari: chr "5,24" "76,65" "427,61" "0,17" ...
## $ yil_ay : Date, format: "2017-01-01" "2017-01-01" ...#Creating year column in a df
bkm_full$yil <- year(ymd(bkm_full$yil_ay))
#Changing chars to numeric value
bkm_full$kk_islem_adedi <- as.numeric(str_replace_all(bkm_full$kk_islem_adedi, pattern=fixed("."), ""))
bkm_full$bk_islem_adedi<-as.numeric(str_replace_all(bkm_full$bk_islem_adedi, pattern=fixed("."), ""))
#Replacing thousand separator with " " and decimal separator "," with "."
bkm_full$kk_islem_tutari<-str_replace_all(bkm_full$kk_islem_tutari, pattern=fixed("."), "")
bkm_full$kk_islem_tutari<-as.numeric(str_replace_all(bkm_full$kk_islem_tutari, pattern=fixed(","), "."))
bkm_full$bk_islem_tutari<-str_replace_all(bkm_full$bk_islem_tutari, pattern=fixed("."), "")
bkm_full$bk_islem_tutari<-as.numeric(str_replace_all(bkm_full$bk_islem_tutari, pattern=fixed(","), "."))
head(bkm_full)## isyeri_grubu kk_islem_adedi bk_islem_adedi
## 2 ARABA KİRALAMA 234458 29881
## 3 ARAÇ KİRALAMA-SATIŞ/SERVİS/YEDEK PARÇA 2693110 433196
## 4 BENZİN VE YAKIT İSTASYONLARI 22457882 6460535
## 5 BIREYSEL EMEKLILIK 2568154 508
## 6 ÇEŞİTLİ GIDA 20568348 10214579
## 7 DOĞRUDAN PAZARLAMA 425443 34240
## kk_islem_tutari bk_islem_tutari yil_ay yil
## 2 116.02 5.24 2017-01-01 2017
## 3 1601.78 76.65 2017-01-01 2017
## 4 3861.49 427.61 2017-01-01 2017
## 5 607.99 0.17 2017-01-01 2017
## 6 3142.49 354.55 2017-01-01 2017
## 7 113.06 2.92 2017-01-01 20173.Exploratory Data Analysis for BKM Dataset
3.1.Top 3 Monthly Card Transaction Amounts in Total
top_sales_bkm <- bkm_full %>% group_by(yil_ay, isyeri_grubu) %>% summarise(islem_tutari = sum(kk_islem_tutari) + sum(bk_islem_tutari)) %>% top_n(3)
top_sales_bkm## # A tibble: 90 x 3
## # Groups: yil_ay [30]
## yil_ay isyeri_grubu islem_tutari
## <date> <chr> <dbl>
## 1 2017-01-01 BENZİN VE YAKIT İSTASYONLARI 4289.
## 2 2017-01-01 GİYİM VE AKSESUAR 3871.
## 3 2017-01-01 MARKET VE ALIŞVERİŞ MERKEZLERİ 9438.
## 4 2017-02-01 BENZİN VE YAKIT İSTASYONLARI 4214.
## 5 2017-02-01 GİYİM VE AKSESUAR 3520.
## 6 2017-02-01 MARKET VE ALIŞVERİŞ MERKEZLERİ 9135.
## 7 2017-03-01 BENZİN VE YAKIT İSTASYONLARI 4888.
## 8 2017-03-01 GİYİM VE AKSESUAR 4156.
## 9 2017-03-01 MARKET VE ALIŞVERİŞ MERKEZLERİ 10559.
## 10 2017-04-01 BENZİN VE YAKIT İSTASYONLARI 4710.
## # ... with 80 more rowsggplot(top_sales_bkm, aes(x = ordered(yil_ay), y = islem_tutari, fill = isyeri_grubu)) + geom_bar(stat = "identity") +
theme(axis.text.x = element_text(angle = 90, hjust = 1)) +
labs(x = "Aylık Dönemler", y = "Toplam İşlem Tutarı (KK+BK)", fill = "Isyeri Grubu")
3.2.Top 5 Debit Card Transactions Ratio in Total Card Transactions
bkm_debit_ratio <- bkm_full %>% group_by(yil, isyeri_grubu) %>% summarise(toplam_islem_bk = sum(bk_islem_adedi), toplam_kart_islem = sum(bk_islem_adedi) + sum(kk_islem_adedi), ratio_bk = toplam_islem_bk/toplam_kart_islem) %>% top_n(5) %>% arrange(desc(ratio_bk))
bkm_debit_ratio## # A tibble: 15 x 5
## # Groups: yil [3]
## yil isyeri_grubu toplam_islem_bk toplam_kart_isl~ ratio_bk
## <dbl> <chr> <dbl> <dbl> <dbl>
## 1 2018 DİĞER 151227594 204841514 0.738
## 2 2019 DİĞER 57344411 88334580 0.649
## 3 2017 DİĞER 48986013 99776234 0.491
## 4 2019 YEMEK 231259184 486166406 0.476
## 5 2019 KUMARHANE/İÇKİLİ YERLER 2413929 5275161 0.458
## 6 2018 YEMEK 368313677 823028341 0.448
## 7 2017 YEMEK 301403819 679323209 0.444
## 8 2018 KUMARHANE/İÇKİLİ YERLER 4119623 9393501 0.439
## 9 2017 KUMARHANE/İÇKİLİ YERLER 3148142 7220984 0.436
## 10 2019 ÇEŞİTLİ GIDA 106714408 286302262 0.373
## 11 2019 MARKET VE ALIŞVERİŞ MER~ 342461968 957742221 0.358
## 12 2018 ÇEŞİTLİ GIDA 169614842 475281316 0.357
## 13 2017 ÇEŞİTLİ GIDA 140995648 406632272 0.347
## 14 2018 MARKET VE ALIŞVERİŞ MER~ 580057869 1718749111 0.337
## 15 2017 MARKET VE ALIŞVERİŞ MER~ 470178217 1479037772 0.318#Required libraries for animated plots
library(gganimate)
library(gifski)
library(png)
theme_set(theme_bw())
p <- ggplot(bkm_debit_ratio, aes(x = yil, y = ratio_bk, group = isyeri_grubu, color = isyeri_grubu)) + geom_line() +
scale_x_continuous(breaks = 0:2100) +
labs(x = "Yıl", y = "Banka Kartı Kullanım Oranı", color = "Isyeri Grubu") +
theme(legend.position = "right")
p + geom_point(aes(group = seq_along(yil))) + transition_reveal(yil) #Animated debit card ratio plot