EGM Analysis

Tarık Özçelik

31.11.2018

library(dplyr)
library(tidyverse)
library(scales)

Download Raw Data

Below the excel file is downloaded from github to a local data frame (raw_data) and refined for analysis

tmpEGM<-tempfile(fileext=".xlsx")
download.file("https://github.com/MEF-BDA503/pj18-TarikOzcelik81/blob/master/egm_example_data.xlsx?raw=true",destfile=tmpEGM,mode="wb")
EGM_raw_data<-readxl::read_excel(tmpEGM,col_names=TRUE)
file.remove(tmpEGM)
## [1] TRUE
head(EGM_raw_data)
## # A tibble: 6 x 15
##   date  pension_fund_co~ n_of_participan~ fund_size_parti~ gov_contribution
##   <chr> <chr>                       <dbl>            <dbl>            <dbl>
## 1 06.0~ Aegon Emeklilik~            42826        139193651         14409322
## 2 06.0~ Allianz Hayat v~           118867       1840701596        231156574
## 3 06.0~ Allianz Yaşam v~           726684       7387398235        815356276
## 4 06.0~ Anadolu Hayat E~          1119572       9975066501       1484745501
## 5 06.0~ Asya Emeklilik ~           129389        337754079         77689275
## 6 06.0~ Avivasa Emeklil~           876894      10550032358       1355045933
## # ... with 10 more variables: contribution <dbl>, n_of_pensioners <chr>,
## #   n_of_ind_contracts <dbl>, n_of_group_ind_contracts <chr>,
## #   n_of_employer_group_certificates <chr>, n_total <dbl>,
## #   size_of_ind_contracts <dbl>, size_of_group_ind_contracts <dbl>,
## #   size_of_employer_group_certificates <dbl>, size_total <dbl>
tail(EGM_raw_data)
## # A tibble: 6 x 15
##   date  pension_fund_co~ n_of_participan~ fund_size_parti~ gov_contribution
##   <chr> <chr>                       <dbl>            <dbl>            <dbl>
## 1 30.0~ Halk Hayat ve E~           511848       2845016752        527923905
## 2 30.0~ Katılım Emeklil~           205994        907989162        182110867
## 3 30.0~ Metlife Emeklil~           187483       1368676067        233517732
## 4 30.0~ NN Hayat ve Eme~           265725       2607702561        382046370
## 5 30.0~ Vakıf Emeklilik~           502994       5375415236        757466988
## 6 30.0~ Ziraat Hayat ve~           598703       3105479818        590220958
## # ... with 10 more variables: contribution <dbl>, n_of_pensioners <chr>,
## #   n_of_ind_contracts <dbl>, n_of_group_ind_contracts <chr>,
## #   n_of_employer_group_certificates <chr>, n_total <dbl>,
## #   size_of_ind_contracts <dbl>, size_of_group_ind_contracts <dbl>,
## #   size_of_employer_group_certificates <dbl>, size_total <dbl>

Total company participants

max_part_number <- EGM_raw_data %>% 
        group_by(pension_fund_company) %>% 
        summarise(max(n_of_participants))
#  filter(auto_total > 0 & comm_total > 0) %>%
#  select(brand_name,total_total) %>%
#  arrange(desc(total_total))  %>%
#  mutate(percentage = percent(total_total/sum(total_total)))
colnames(max_part_number) <- c("fund_company","max_num")

max_part_number %>% ggplot() + geom_bar(stat="identity",aes(x = fund_company, y = max_num,fill=fund_company))+
  theme(legend.position = "none", axis.text.x = element_text(angle = 90, 
    vjust = 0.5, hjust = 0.5, size = 12)) 

#ggplot(data =max_part_number, aes(fund_company,max_num )) + geom_bar(stat = "identity")