EGM
From Raw to Civilized Data
First we find the data from EGM website.We download the data change the name to egm_example_data.xlsx. We will make a reproducible example of data analysis from the raw data located somewhere to the final analysis.
Download Row Data
We created a new file as name “tmp”, then download the file from repository to the “tmp”.We read that excel file by using readxl package’s read_excel function and removed to temporary file that we created.
tmp=tempfile(fileext=".xlsx")
download.file("https://github.com/pjournal/mef03g-polatalemd-r/blob/master/egm_example_data.xlsx?raw=true",destfile=tmp,mode='wb')
raw_data=readxl::read_excel(tmp)
file.remove(tmp)You can view head and tail of the dataset in below tables, respectively.
head(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 7.31~ Aegon Emeklilik~ 37671 132128516. 15559754.
## 2 7.31~ Allianz Hayat v~ 94630 2801049763. 385315268.
## 3 7.31~ Allianz Yasam v~ 728934 12158903088. 1435419831.
## 4 7.31~ Anadolu Hayat E~ 1091010 16312824287. 2768140863.
## 5 7.31~ Avivasa Emeklil~ 787046 16801925214. 2467365796.
## 6 7.31~ Axa Hayat ve Em~ 33794 426214299. 86715369.
## # ... with 10 more variables: contribution <dbl>, n_of_pensioners <dbl>,
## # n_of_ind_contracts <dbl>, n_of_group_ind_contracts <dbl>,
## # n_of_employer_group_certificates <dbl>, n_total <dbl>,
## # size_of_ind_contracts <dbl>, size_of_group_ind_contracts <dbl>,
## # size_of_employer_group_certificates <dbl>, size_total <dbl>tail(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 1.3.~ Groupama Emekli~ 77094 688162318. 20423655.
## 2 1.3.~ Halk Hayat ve E~ 134928 307863682. 30903648.
## 3 1.3.~ ING Emeklilik 238312 1335272560. 50634563.
## 4 1.3.~ Metlife Emeklil~ 121918 342887913. 26232485.
## 5 1.3.~ Vakif Emeklilik 291440 1630111936. 77968556.
## 6 1.3.~ Ziraat Hayat ve~ 118882 366475156. 33148249.
## # ... with 10 more variables: contribution <dbl>, n_of_pensioners <dbl>,
## # n_of_ind_contracts <dbl>, n_of_group_ind_contracts <dbl>,
## # n_of_employer_group_certificates <dbl>, n_total <dbl>,
## # size_of_ind_contracts <dbl>, size_of_group_ind_contracts <dbl>,
## # size_of_employer_group_certificates <dbl>, size_total <dbl>Analyses Related to Pension Dataset
Total Participant Comparision of All Companies | Jul 2019
To see the competition in the market, we compare the companies according to latest dated report. We analyzed top 5 firms individually and merge the other ones and named them “Others”. There is a big competition between “Garanti Emeklilik ve Hayat” and “Anadolu Hayat Emeklilik”. Total of the other 13 companies is in the third place with a 15.5% of market share. “Avivasa Emeklilik ve Hayat”, “Allianz Yasam ve Emeklilik” and “Ziraat Hayat ve Emeklilik” are following the Others respectively.
library(dplyr)
library(ggplot2)
library(scales)
library(zoo)
participant=raw_data[raw_data$date == '7.31.2019' ,c(1,2,3)] %>%
arrange(desc(n_of_participants)) %>%
mutate(pension_fund_company = case_when(pension_fund_company %in% c("Aegon Emeklilik ve Hayat", "Allianz Hayat ve Emeklilik", "Axa Hayat ve Emeklilik", "Bereket Emeklilik ve Hayat", "BNP Paribas Cardif Emeklilik", "Cigna Finans Emeklilik ve Hayat", "Fiba Emeklilik ve Hayat", "Groupama Hayat", "Katılım Emeklilik ve Hayat", "Metlife Emeklilik ve Hayat", "NN Hayat ve Emeklilik") ~ "Others", TRUE ~ pension_fund_company)) %>%
group_by(pension_fund_company) %>% mutate(n_of_participants = sum(n_of_participants)) %>% distinct() %>%
arrange(desc(n_of_participants)) %>% ungroup(pension_fund_company) %>%
transmute(pension_fund_company, share = percent(n_of_participants / sum(n_of_participants)))
print(participant)## # A tibble: 9 x 2
## pension_fund_company share
## <chr> <chr>
## 1 Garanti Emeklilik ve Hayat 16.4%
## 2 Anadolu Hayat Emeklilik 16.1%
## 3 Others 15.5%
## 4 Avivasa Emeklilik ve Hayat 11.6%
## 5 Allianz Yasam ve Emeklilik 10.7%
## 6 Ziraat Hayat ve Emeklilik 9.7%
## 7 Halk Hayat ve Emeklilik 8.3%
## 8 Vakif Emeklilik ve Hayat 8.1%
## 9 Katilim Emeklilik ve Hayat 3.7%You can see the pie chart that shows the distribution of the market share.
participant%>%
ggplot(data = ., aes(x = "", y = share, fill = pension_fund_company)) +
geom_bar(width = 1, stat = "identity", color = "black") +
coord_polar("y", start = 0)+
geom_text(aes(x = 1.6, y = share, label = share),position = position_stack(vjust = 0.5),color = "black")+
labs(fill = "Pension Companies")+
theme_void()
Top 5 Companies Based on Their Pension Fund Size | Jul 2019
Beside the number of participants, pension fund size is another indicator that shows how well a company is doing in the competition. To demonstrate it, we analyzed the latest report and visualized top 5 companies according to the fund size below. “Avivasa Emeklilik ve Hayat”, is ahead of their competitors and managed to be the number one company.
library(ggplot2)
###
raw_data2 = raw_data %>% filter(date == "7.31.2019") %>%
arrange(desc(fund_size_participants)) %>%top_n(5)
print(raw_data2)## # A tibble: 5 x 15
## date pension_fund_co~ n_of_participan~ fund_size_parti~ gov_contribution
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 7.31~ Avivasa Emeklil~ 787046 16801925214. 2467365796.
## 2 7.31~ Anadolu Hayat E~ 1091010 16312824287. 2768140863.
## 3 7.31~ Garanti Emeklil~ 1111079 12694233683. 2107530325.
## 4 7.31~ Allianz Yasam v~ 728934 12158903088. 1435419831.
## 5 7.31~ Vakif Emeklilik~ 547507 7224409572. 1096800286.
## # ... with 10 more variables: contribution <dbl>, n_of_pensioners <dbl>,
## # n_of_ind_contracts <dbl>, n_of_group_ind_contracts <dbl>,
## # n_of_employer_group_certificates <dbl>, n_total <dbl>,
## # size_of_ind_contracts <dbl>, size_of_group_ind_contracts <dbl>,
## # size_of_employer_group_certificates <dbl>, size_total <dbl>###
raw_data2 %>%
ggplot(data = ., aes(x = pension_fund_company, y = fund_size_participants,
fill = pension_fund_company)) +
geom_bar(stat = "identity") +
aes(x = reorder(pension_fund_company, -fund_size_participants), y = fund_size_participants) +
labs(x = "Companies in descending order", y = "Size", title = "Top 5 Big Companies based on their Pension Funds Size", fill= "Companies")+ theme_minimal() + theme(axis.text.x = element_text(angle = 15, hjust = 0.6))
Development of Pension Funds per Participant | Avivasa Emeklilik ve Hayat
As shown above, “Avivasa Emeklilik ve Hayat”, and “Anadolu Hayat Emeklilik” has the biggest pension funds size and they are in the top places according to number of participants. We would like to analyze amount of pension funds per participant in 2019 for these companies. Amount of funds per participant is rising since January, an now it is more than 21000 Turkish Liras.
library(ggplot2)
###
raw_data %>% select(date, pension_fund_company, n_of_participants, fund_size_participants) %>%
filter(pension_fund_company %in% c("Avivasa Emeklilik ve Hayat","Anadolu Hayat Emeklilik"), date %in% c("7.31.2019", "6.30.2019", "5.31.2019", "4.30.2019", "3.31.2019", "2.28.2019", "1.31.2019")) %>%
mutate(fund_per_participant = fund_size_participants / n_of_participants) %>%
ggplot(data=., aes(x=date, y=fund_per_participant, fill=pension_fund_company)) +
geom_bar(stat="identity", position=position_dodge())+
labs(x = "Jan-Jul 2019",y= "Funds per Person", title = "Development of Pension Funds per Participant", fill= "Company") +
theme_minimal()