library(tidyverse)
## ── Attaching packages ───────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
## ✔ ggplot2 3.2.1 ✔ purrr 0.3.2
## ✔ tibble 2.1.3 ✔ dplyr 0.8.3
## ✔ tidyr 1.0.0 ✔ stringr 1.4.0
## ✔ readr 1.3.1 ✔ forcats 0.4.0
## ── Conflicts ──────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
library(rvest)
## Loading required package: xml2
##
## Attaching package: 'rvest'
## The following object is masked from 'package:purrr':
##
## pluck
## The following object is masked from 'package:readr':
##
## guess_encoding
library(readxl)
library(dplyr)
tables_united <- ""
for (i in (2017:2019)){
file_url_year <- paste0("https://bkm.com.tr/secilen-aya-ait-sektorel-gelisim/?filter_year=",i)
for (j in (1:12)){
file_url <- paste0(file_url_year,"&filter_month=",j, "&List=Listele")
if ((j>6)&(i==2019))
break
wp <- read_html(file_url)
tbls <- html_nodes(wp,"table")[[4]] %>%
html_table(wp, fill = TRUE,header = FALSE)%>%
as.data.frame() %>%slice(3:max(nrow(.)))
tbls <- tbls%>%mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>%
mutate(year_month=paste0(i,"/",j))
tables_united <- rbind(tables_united,tbls)
}
}
## Warning: funs() is soft deprecated as of dplyr 0.8.0
## Please use a list of either functions or lambdas:
##
## # Simple named list:
## list(mean = mean, median = median)
##
## # Auto named with `tibble::lst()`:
## tibble::lst(mean, median)
##
## # Using lambdas
## list(~ mean(., trim = .2), ~ median(., na.rm = TRUE))
## This warning is displayed once per session.
colnames(tables_united) <- c('kategori','kk_islem_adedi','bk_islem_adedi','kk_islem_tutari','bk_islem_tutari' ,'year_month')
head(tables_united,10)
## kategori kk_islem_adedi bk_islem_adedi
## 1
## 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
## 8 EĞİTİM / KIRTASİYE / OFİS MALZEMELERİ 4.772.413 1.855.520
## 9 ELEKTRÄ°K-ELEKTRONÄ°K EÅžYA, BÄ°LGÄ°SAYAR 6.633.372 1.140.438
## 10 GÄ°YÄ°M VE AKSESUAR 26.155.139 7.640.918
## kk_islem_tutari bk_islem_tutari year_month
## 1
## 2 116,02 5,24 2017/1
## 3 1.601,78 76,65 2017/1
## 4 3.861,49 427,61 2017/1
## 5 607,99 0,17 2017/1
## 6 3.142,49 354,55 2017/1
## 7 113,06 2,92 2017/1
## 8 1.198,04 108,68 2017/1
## 9 2.536,99 202,96 2017/1
## 10 3.245,08 625,81 2017/1
tables_united_clear <- tables_united%>%
filter(kategori!='TOPLAM')%>%
filter(row_number()!=1)
tables_united_clear$kk_islem_adedi <- as.numeric(gsub("\\.","",tables_united_clear$kk_islem_adedi))
tables_united_clear$bk_islem_adedi <- as.numeric(gsub("\\.","",tables_united_clear$bk_islem_adedi))
tables_united_clear$kk_islem_tutari <- as.numeric(gsub(",", ".", gsub("\\.", "", tables_united_clear$kk_islem_tutari)))
tables_united_clear$bk_islem_tutari <- as.numeric(gsub(",", ".", gsub("\\.", "", tables_united_clear$bk_islem_tutari)))
head(tables_united_clear,30)
## kategori kk_islem_adedi bk_islem_adedi
## 1 ARABA KÄ°RALAMA 234458 29881
## 2 ARAÇ KİRALAMA-SATIŞ/SERVİS/YEDEK PARÇA 2693110 433196
## 3 BENZÄ°N VE YAKIT Ä°STASYONLARI 22457882 6460535
## 4 BIREYSEL EMEKLILIK 2568154 508
## 5 ÇEŞİTLİ GIDA 20568348 10214579
## 6 DOÄžRUDAN PAZARLAMA 425443 34240
## 7 EĞİTİM / KIRTASİYE / OFİS MALZEMELERİ 4772413 1855520
## 8 ELEKTRÄ°K-ELEKTRONÄ°K EÅžYA, BÄ°LGÄ°SAYAR 6633372 1140438
## 9 GÄ°YÄ°M VE AKSESUAR 26155139 7640918
## 10 HAVAYOLLARI 2083535 218903
## 11 HİZMET SEKTÖRLERİ 11503915 3300761
## 12 KAMU/VERGI ODEMELERI 6776564 820859
## 13 KONAKLAMA 1368836 478595
## 14 KULÃœP / DERNEK /SOSYAL HÄ°ZMETLER 1035199 190069
## 15 KUMARHANE/İÇKİLİ YERLER 261032 207169
## 16 KUYUMCULAR 799560 249465
## 17 MARKET VE ALIÅžVERÄ°Åž MERKEZLERÄ° 78792184 34615321
## 18 MOBÄ°LYA VE DEKORASYON 4709197 1168593
## 19 MÃœTEAHHÄ°T Ä°ÅžLERÄ° 426384 90778
## 20 SAÄžLIK/SAÄžLIK ÃœRÃœNLERÄ°/KOZMETÄ°K 10946442 4076293
## 21 SEYAHAT ACENTELERÄ°/TAÅžIMACILIK 4616128 1271759
## 22 SÄ°GORTA 5616056 13197
## 23 TELEKOMÃœNÄ°KASYON 10307728 1764601
## 24 YAPI MALZEMELERÄ°, HIRDAVAT, NALBURÄ°YE 2829504 629476
## 25 YEMEK 26336314 19935250
## 26 DÄ°ÄžER 3315572 543385
## 27 ARABA KÄ°RALAMA 230884 25280
## 28 ARAÇ KİRALAMA-SATIŞ/SERVİS/YEDEK PARÇA 2580310 428016
## 29 BENZÄ°N VE YAKIT Ä°STASYONLARI 21640215 6320782
## 30 BIREYSEL EMEKLILIK 2410445 427
## kk_islem_tutari bk_islem_tutari year_month
## 1 116.02 5.24 2017/1
## 2 1601.78 76.65 2017/1
## 3 3861.49 427.61 2017/1
## 4 607.99 0.17 2017/1
## 5 3142.49 354.55 2017/1
## 6 113.06 2.92 2017/1
## 7 1198.04 108.68 2017/1
## 8 2536.99 202.96 2017/1
## 9 3245.08 625.81 2017/1
## 10 798.56 128.85 2017/1
## 11 2418.57 237.86 2017/1
## 12 2817.52 71.55 2017/1
## 13 680.50 100.98 2017/1
## 14 252.31 21.34 2017/1
## 15 34.24 14.19 2017/1
## 16 631.47 90.03 2017/1
## 17 8272.22 1165.52 2017/1
## 18 1505.25 122.22 2017/1
## 19 383.14 13.23 2017/1
## 20 1607.85 262.24 2017/1
## 21 1199.79 110.58 2017/1
## 22 2381.26 5.57 2017/1
## 23 1235.88 121.65 2017/1
## 24 1635.59 74.61 2017/1
## 25 1393.97 497.84 2017/1
## 26 1164.36 47.91 2017/1
## 27 114.96 4.50 2017/2
## 28 1538.58 73.57 2017/2
## 29 3789.30 424.21 2017/2
## 30 571.14 0.13 2017/2
###Analysis
##Total Transactions from Jan 2017 to June 2019
total_transactions <- tables_united_clear%>%
select(kategori, kk_islem_adedi,bk_islem_adedi) %>%
group_by(kategori)%>%
summarise(kk_islem_adedi=sum(kk_islem_adedi),bk_islem_adedi=sum(bk_islem_adedi))%>%
mutate(total_trans=kk_islem_adedi+bk_islem_adedi)%>%
arrange(desc(total_trans))
total_transactions
## # A tibble: 26 x 4
## kategori kk_islem_adedi bk_islem_adedi total_trans
## <chr> <dbl> <dbl> <dbl>
## 1 MARKET VE ALIÅžVERÄ°Åž MERKEZLERÄ° 2762831050 1392698054 4155529104
## 2 YEMEK 1087541276 900976680 1988517956
## 3 ÇEŞİTLİ GIDA 750890952 417324898 1168215850
## 4 GÄ°YÄ°M VE AKSESUAR 811099235 297653970 1108753205
## 5 BENZÄ°N VE YAKIT Ä°STASYONLARI 809769482 278936262 1088705744
## 6 HİZMET SEKTÖRLERİ 552345940 138892136 691238076
## 7 SAĞLIK/SAĞLIK ÜRÜNLERİ/KOZMET… 390454048 173606236 564060284
## 8 TELEKOMÃœNÄ°KASYON 428471230 74722137 503193367
## 9 DÄ°ÄžER 135394310 257558018 392952328
## 10 ELEKTRİK-ELEKTRONİK EŞYA, BİL… 215340696 53635846 268976542
## # … with 16 more rows
ggplot(total_transactions, aes(x=kategori, y=total_trans)) +
geom_bar(stat="identity",fill="lime green")+ coord_flip() +
labs(title = "Total Transactions from Jan 2017 to June 2019") +
theme_minimal()
##Average Spending per transaction by category
average_spending_all <- tables_united_clear%>%
select(kategori, kk_islem_adedi,bk_islem_adedi,kk_islem_tutari,bk_islem_tutari) %>%
group_by(kategori)%>%
summarise(kk_islem_adedi=sum(kk_islem_adedi),bk_islem_adedi=sum(bk_islem_adedi),kk_islem_tutari=sum(kk_islem_tutari),bk_islem_tutari=sum(bk_islem_tutari))%>%
mutate(total_trans=kk_islem_adedi+bk_islem_adedi, total_spending=kk_islem_tutari+bk_islem_tutari,average_spending=(total_spending/total_trans)*100000 )%>%
arrange(desc(average_spending))
average_spending_all
## # A tibble: 26 x 8
## kategori kk_islem_adedi bk_islem_adedi kk_islem_tutari bk_islem_tutari
## <chr> <dbl> <dbl> <dbl> <dbl>
## 1 MÜTEAHH… 17086819 5579997 19740. 889.
## 2 KUYUMCU… 30926666 10064088 26716. 6820.
## 3 DOĞRUDA… 22658874 1050650 17327. 286.
## 4 HAVAYOL… 60409730 10668159 37346. 11127.
## 5 ARAÇ Kİ… 93689324 19594468 66318. 3928.
## 6 ARABA K… 9071164 1597444 5826. 536.
## 7 YAPI MA… 105943956 30243320 73760. 4436.
## 8 SÄ°GORTA 150532354 745012 82441. 270.
## 9 KONAKLA… 54344284 23724559 32822. 8026.
## 10 KAMU/VE… 114323861 32445823 54831. 4194.
## # … with 16 more rows, and 3 more variables: total_trans <dbl>,
## # total_spending <dbl>, average_spending <dbl>
ggplot(average_spending_all, aes(x=kategori, y=average_spending)) +
geom_bar(stat="identity",fill="dark blue")+ coord_flip() +
labs(title = "Average Spending per transaction by category") +
theme_minimal()
##Monthly Credit Card Spending change of top 3 category (regarding Credit Cart Spendings)
top3_cc_spendings <- tables_united_clear%>%
group_by(kategori)%>%
summarise(kk_islem_tutari=sum(kk_islem_tutari))%>%
arrange(desc(kk_islem_tutari))%>%
filter(row_number()<4)
top3_cc_spendings
## # A tibble: 3 x 2
## kategori kk_islem_tutari
## <chr> <dbl>
## 1 MARKET VE ALIÅžVERÄ°Åž MERKEZLERÄ° 306239.
## 2 BENZÄ°N VE YAKIT Ä°STASYONLARI 157509.
## 3 GÄ°YÄ°M VE AKSESUAR 130276.
cc_spendings_monthly<-tables_united_clear%>%
group_by(kategori,year_month)%>%
summarise(kk_islem_tutari=sum(kk_islem_tutari))%>%
filter(kategori %in% c("MARKET VE ALIŞVERİŞ MERKEZLERİ","BENZİN VE YAKIT İSTASYONLARI","ÇEŞİTLİ GIDA"))
cc_spendings_monthly
## # A tibble: 90 x 3
## # Groups: kategori [3]
## kategori year_month kk_islem_tutari
## <chr> <chr> <dbl>
## 1 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/1 3861.
## 2 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/10 5003.
## 3 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/11 4860.
## 4 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/12 4894.
## 5 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/2 3789.
## 6 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/3 4402.
## 7 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/4 4195.
## 8 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/5 4349.
## 9 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/6 4292.
## 10 BENZÄ°N VE YAKIT Ä°STASYONLARI 2017/7 4926.
## # … with 80 more rows
cc_spendings_plot <- ggplot(cc_spendings_monthly, aes(x = year_month, y = kk_islem_tutari, group = kategori, color = kategori))+geom_line()+
labs(x = "Ay", y = "Monthly Credit Card Spending", color = "kategori") +
theme(axis.text.x = element_text(angle = 90))
cc_spendings_plot
