Project: University Entrance Exam Analysis
Dol_R
8/24/2020
Introduction and Key Takeaways
In Turkey, every year millions of students take the university entrance exam. After the announcement of results, participants list their university and department choices and they are placed according to their ranks.
This analysis focuses on universities and departments popularity over the years. Hopefully, it would help future participants in their decision making process.
Key Takeaways:
- Medicine maintained its popularity over the years.
- Koç University has a significantly higher popularity.
Summary of the Data and Explanations
Using University Exam data from Hacettepe University’s Website, we obtained university results of years 2016-2020. Each year is on a separate Excel spreadsheet. Since every year some departments are opened and some are shut down, datasets will have different number of rows. There are 9 variables and more than 10000 rows for each dataset.
university: Name of the university
city: University’s location
department: Name of the department
type: Type of the exam
quota: Maximum number of participants to be accepted, upper bound for accepted_number
accepted_number: Accepted number of participants to the selected university’s selected department
lowest_score: Lowest score of the accepted participant’s scores
highest_score: Highest score of the accepted participant’s scores
lowest_ranking: Lowest ranking of the accepted participant’s scores, last accepted person’s ranking
Objectives:
- Analyze University Exam Entrance data
- Cleaning and manipulation of datasets
- Exploration of the popularity trends of the universities and departments
- Comparison by visualization
- Finding top universities and departments
Data Preprocessing
In order to ease the reading process, functions mani97 and mani86 are created. These functions remove the columns related to the percentage of change regarding the previous year. They also rename the columns and remove the last empty rows. The raw data for some years have an additional column at the end, therefore two functions were needed.
knitr::opts_chunk$set(echo = TRUE, message=FALSE, warning=FALSE) # applies to all chunks
mani97 <- function(data){
newdata <- data %>%
rename(
number = c(1),
difference = c(2),
university = c(3),
city = c(4),
department = c(5),
type = c(6),
quota = c(7),
difference_quota = c(8),
accepted_number = c(9),
lowest_score = c(10),
highest_score = c(11),
lowest_ranking = c(12),
difference_ranking = c(13)
) %>% select(-number, -difference, -difference_quota, -difference_ranking) %>% slice_head(n=nrow(data)-9)
return(newdata)
}
mani86 <- function(data){
newdata <- data %>%
rename(
number = c(1),
difference = c(2),
university = c(3),
city = c(4),
department = c(5),
type = c(6),
quota = c(7),
difference_quota = c(8),
accepted_number = c(9),
lowest_score = c(10),
highest_score = c(11),
lowest_ranking = c(12)
) %>% select(-number, -difference, -difference_quota) %>% slice_head(n=nrow(data)-9)
return(newdata)
}After loading the required libraries, we download the .xlsx file from our project repository. After reading it, we remove the file.
library(tidyverse)
library(lubridate)
library(tinytex)
library(readxl) # read_excel
library(tidyr)
library(httr) # GET
library(reshape2)
library(ggforce) # circle data points
url<-'https://github.com/pjournal/boun01g-dol-r/blob/gh-pages/uni_exam_project/uni_exam.xlsx?raw=true'
GET(url, write_disk(tf <- tempfile(fileext = ".xlsx")))raw_df20 <- read_excel(tf, sheet="20", skip=21)
raw_df19 <- read_excel(tf, sheet="19", skip=21)
raw_df18 <- read_excel(tf, sheet="18", skip=21)
raw_df17 <- read_excel(tf, sheet="17", skip=21)
raw_df16 <- read_excel(tf, sheet="16", skip=21)
file.remove(tf)Loading the Libraries and Datasets
We have 5 datasets for the last 5 years. Each dataset has 9 columns and they all have different number of rows. For example there are 10617 rows in dataset for the year 2020 and 11402 rows in dataset for the year 2019. Some departments or even universities may have been opened or closed.
data2020 <- mani97(raw_df20)
data2020 <- mani97(raw_df20)
data2019 <- mani97(raw_df19)
data2018 <- mani86(raw_df18)
data2017 <- mani97(raw_df17)
data2016 <- mani97(raw_df16)
data2020 %>% summarise(exam20=n()) %>% mutate(data2019 %>% summarise(exam19=n())) %>% mutate(data2018 %>% summarise(exam18=n())) %>% mutate(data2017 %>% summarise(exam17=n())) %>% mutate(data2016 %>% summarise(exam16=n())) ## # A tibble: 1 x 5
## exam20 exam19 exam18 exam17 exam16
## <int> <int> <int> <int> <int>
## 1 10617 11402 11958 11484 10657As an example, let’s observe dataset for the year 2018. First four variables are categorical and the rest is numerical. We could also see that there are 11958 rows for this year.
data2018 %>% arrange(desc(lowest_score)) %>% glimpse()## Rows: 11,958
## Columns: 9
## $ university [3m[90m<chr>[39m[23m "İSTANBUL MEDİPOL ÜNİVERSİTESİ", "KOÇ ÜNİVERSİTESİ", …
## $ city [3m[90m<chr>[39m[23m "İSTANBUL", "İSTANBUL", "İSTANBUL", "İSTANBUL", "İSTA…
## $ department [3m[90m<chr>[39m[23m "Tıp Fakültesi (İngilizce) (Burslu)", "Tıp Fakültesi …
## $ type [3m[90m<chr>[39m[23m "SAY", "SAY", "SAY", "SAY", "SAY", "SAY", "SAY", "SAY…
## $ quota [3m[90m<dbl>[39m[23m 10, 15, 8, 11, 52, 82, 11, 30, 82, 50, 9, 175, 50, 12…
## $ accepted_number [3m[90m<dbl>[39m[23m 10, 15, 8, 11, 52, 82, 11, 30, 82, 50, 9, 175, 50, 12…
## $ lowest_score [3m[90m<dbl>[39m[23m 549.1749, 548.1738, 543.2350, 542.8008, 538.5325, 536…
## $ highest_score [3m[90m<dbl>[39m[23m 562.9543, 556.2859, 546.1979, 548.5940, 559.5140, 562…
## $ lowest_ranking [3m[90m<dbl>[39m[23m 56, 84, 207, 221, 400, 496, 510, 511, 622, 637, 739, …Exploratory Data Analysis
When the exam procedure changes, the types of grading systems also change. Therefore, there is no point of comparing type variable for different years. But it can be an identifier in each year.
Top Universities and Departments
Some universities’ departments may be selected by the participants with lowest scores in different types of grading systems. After grouping by university and department, lowest score column is put in decreasing order. Lowest score is the last entering person’s score to a specific university and department. By putting it into decreasing order, we get the highest scores of last entering people. Let’s see how the top 10 departments changed over the years.
To do this, we count unique values and use full_join. Finally, we replace NULL values with zero, which means this university or department was not in top 10 for that specific year.
score <- function(data){
data %>% group_by(university, department) %>% select(-type, -city) %>% arrange(desc(lowest_score)) %>% head(10) %>% mutate(department=replace(department, str_detect(department,"Tıp"), "Tıp")) %>% mutate(department=str_replace(department, "Burslu", "")) %>% mutate(department=str_replace(department, "İngilizce", "")) %>% mutate(department=str_replace(department, " \\([^()]{0,}\\)", "")) %>% mutate(department=str_replace(department, " \\([^()]{0,}\\)", "")) %>% ungroup()
}
dept <- function(data){
data %>% count(department) %>% arrange(desc(n))
}
uni <- function(data){
data %>% count(university) %>% arrange(desc(n))
}top_uni <- full_join(uni(score(data2016)), uni(score(data2017)), by="university", suffix=c(".2016", ".2017")) %>% full_join(., uni(score(data2018)), by="university", suffix=c(".2016", ".2018"))
top_uni <- full_join(top_uni, uni(score(data2019)), by="university", suffix=c(".2018", ".2019"))
top_uni <- full_join(top_uni, uni(score(data2020)), by="university", suffix=c(".2019", ".2020"))
names(top_uni)[names(top_uni)=='n.2016']<-'year2016'
names(top_uni)[names(top_uni)=='n.2017']<-'year2017'
names(top_uni)[names(top_uni)=='n.2018']<-'year2018'
names(top_uni)[names(top_uni)=='n.2019']<-'year2019'
names(top_uni)[names(top_uni)=='n']<-'year2020'
top_dept <- full_join(dept(score(data2016)), dept(score(data2017)), by="department", suffix=c(".2016", ".2017")) %>% full_join(., dept(score(data2018)), by="department", suffix=c(".2016", ".2018"))
top_dept <- full_join(top_dept, dept(score(data2019)), by="department", suffix=c(".2018", ".2019"))
top_dept <- full_join(top_dept, dept(score(data2020)), by="department", suffix=c(".2019", ".2020"))
names(top_dept)[names(top_dept)=='n.2016']<-'year2016'
names(top_dept)[names(top_dept)=='n.2017']<-'year2017'
names(top_dept)[names(top_dept)=='n.2018']<-'year2018'
names(top_dept)[names(top_dept)=='n.2019']<-'year2019'
names(top_dept)[names(top_dept)=='n']<-'year2020'top_uni <- top_uni %>% replace_na(list(year2016 = 0, year2017 = 0, year2018 = 0, year2019 = 0, year2020 = 0))
top_dept <- top_dept %>% replace_na(list(year2016 = 0, year2017 = 0, year2018 = 0, year2019 = 0, year2020 = 0))
top_uni.long <- melt(top_uni)
plot_uni <- ggplot(top_uni.long, aes(x=value, y=university, col=variable)) + geom_jitter(width=0.1, height=0.1) + stat_smooth() + ggtitle("Top Universities") + xlab("Number of Participants") + ylab("Universities") + theme(plot.caption=element_text(hjust = 0.5))
plot_uni
top_dept.long <- melt(top_dept)
plot_dept <- ggplot(top_dept.long, aes(x=value, y=department, col=variable)) + geom_jitter(width=0.1, height=0.1) + stat_smooth() + ggtitle("Top Departments") + xlab("Number of Participants") + ylab("Departments") + theme(plot.caption=element_text(hjust = 0.5))
plot_dept
Top Cities
total_data <- full_join(data2016,data2017,by=c('university','department','city','type'),suffix = c('.2016','.2017'))%>%
full_join(. ,data2018,by=c('university','department','city','type'))%>%
full_join(. ,data2019,by=c('university','department','city','type'),suffix = c('.2018','.2019'))%>%
full_join(. ,data2020,by=c('university','department','city','type'))
names(total_data)[names(total_data)=='type']<-'type.2020'
names(total_data)[names(total_data)=='quota']<-'quota.2020'
names(total_data)[names(total_data)=='accepted_number']<-'accepted_number.2020'
names(total_data)[names(total_data)=='lowest_score']<-'lowest_score.2020'
names(total_data)[names(total_data)=='highest_score']<-'highest_score.2020'
names(total_data)[names(total_data)=='lowest_ranking']<-'lowest_ranking.2020'University and city distribution of the first thousand students in the university exam
bin_tr<- total_data%>%
filter(lowest_ranking.2020<1000)
tab2<-table(bin_tr$city,bin_tr$type.2020)
tab2##
## DİL EA SAY SÖZ
## ANKARA 0 4 2 1
## İSTANBUL 1 15 8 5tab<-table(bin_tr$university,bin_tr$type.2020)
tab##
## DİL EA SAY SÖZ
## ACIBADEM MEHMET ALİ AYDINLAR ÜNİVERSİTESİ 0 0 2 0
## BOĞAZİÇİ ÜNİVERSİTESİ 0 3 2 1
## GALATASARAY ÜNİVERSİTESİ 0 1 0 0
## İHSAN DOĞRAMACI BİLKENT ÜNİVERSİTESİ 0 3 2 0
## İSTANBUL 29 MAYIS ÜNİVERSİTESİ 0 0 0 1
## İSTANBUL MEDİPOL ÜNİVERSİTESİ 0 0 1 0
## İSTANBUL ÜNİVERSİTESİ 0 1 0 0
## İSTANBUL ÜNİVERSİTESİ-CERRAHPAŞA 0 0 1 0
## KOÇ ÜNİVERSİTESİ 1 6 2 2
## ÖZYEĞİN ÜNİVERSİTESİ 0 1 0 1
## SABANCI ÜNİVERSİTESİ 0 2 0 0
## TOBB EKONOMİ VE TEKNOLOJİ ÜNİVERSİTESİ 0 1 0 1
## YEDİTEPE ÜNİVERSİTESİ 0 1 0 0University and city distribution of the first five thousand students in the university exam
besbin_tr<- total_data%>%
filter(lowest_ranking.2020<5000)
tab3<-table(besbin_tr$city,besbin_tr$type.2020)
tab3##
## DİL EA SAY SÖZ
## ANKARA 9 19 21 10
## ESKİŞEHİR 0 0 1 0
## İSTANBUL 14 42 35 47
## İZMİR 2 0 1 2tab4<-table(besbin_tr$university,besbin_tr$type.2020)
tab4##
## DİL EA SAY SÖZ
## ACIBADEM MEHMET ALİ AYDINLAR ÜNİVERSİTESİ 0 0 2 0
## ANKARA ÜNİVERSİTESİ 0 1 2 0
## BAHÇEŞEHİR ÜNİVERSİTESİ 0 2 1 7
## BAŞKENT ÜNİVERSİTESİ 1 0 2 1
## BEYKENT ÜNİVERSİTESİ 0 0 0 1
## BEZM-İ ÂLEM VAKIF ÜNİVERSİTESİ 0 0 1 0
## BOĞAZİÇİ ÜNİVERSİTESİ 4 7 4 3
## EGE ÜNİVERSİTESİ 0 0 1 0
## ESKİŞEHİR TEKNİK ÜNİVERSİTESİ 0 0 1 0
## GALATASARAY ÜNİVERSİTESİ 1 5 0 1
## GAZİ ÜNİVERSİTESİ 0 0 2 0
## HACETTEPE ÜNİVERSİTESİ 2 1 2 2
## İBN HALDUN ÜNİVERSİTESİ 0 0 0 3
## İHSAN DOĞRAMACI BİLKENT ÜNİVERSİTESİ 3 8 7 1
## İSTANBUL 29 MAYIS ÜNİVERSİTESİ 1 0 0 4
## İSTANBUL BİLGİ ÜNİVERSİTESİ 2 2 0 6
## İSTANBUL MEDİPOL ÜNİVERSİTESİ 0 0 1 0
## İSTANBUL OKAN ÜNİVERSİTESİ 1 0 0 0
## İSTANBUL SABAHATTİN ZAİM ÜNİVERSİTESİ 0 0 0 2
## İSTANBUL TEKNİK ÜNİVERSİTESİ 0 1 3 0
## İSTANBUL ÜNİVERSİTESİ 1 2 2 0
## İSTANBUL ÜNİVERSİTESİ-CERRAHPAŞA 0 0 2 0
## İSTİNYE ÜNİVERSİTESİ 0 0 2 0
## İZMİR EKONOMİ ÜNİVERSİTESİ 1 0 0 2
## KADİR HAS ÜNİVERSİTESİ 0 0 0 3
## KOÇ ÜNİVERSİTESİ 2 10 11 3
## MALTEPE ÜNİVERSİTESİ 0 0 0 1
## MARMARA ÜNİVERSİTESİ 0 0 1 1
## ORTA DOĞU TEKNİK ÜNİVERSİTESİ 2 3 2 2
## ÖZYEĞİN ÜNİVERSİTESİ 0 9 3 2
## SABANCI ÜNİVERSİTESİ 0 2 1 0
## TED ÜNİVERSİTESİ 0 0 0 1
## TOBB EKONOMİ VE TEKNOLOJİ ÜNİVERSİTESİ 1 6 4 3
## YAŞAR ÜNİVERSİTESİ 1 0 0 0
## YEDİTEPE ÜNİVERSİTESİ 2 2 1 10besbin_tr%>%
ggplot(aes(x = lowest_ranking.2020, fill = city)) +
geom_density(alpha = 0.3)
Quota Informations
The cities, universities and departments with the highest quotas in recent years can be seen from the tables below.
all_years_data<-bind_rows(mutate(data2016,year=2016),
mutate(data2017,year=2017),
mutate(data2018,year=2018),
mutate(data2019,year=2019),
mutate(data2020,year=2020))
all_years_data%>%
group_by(year,department)%>%
summarise(department_quota=sum(quota))%>%
arrange(desc(department_quota))%>%head(20) ## # A tibble: 20 x 3
## # Groups: year [5]
## year department department_quota
## <dbl> <chr> <dbl>
## 1 2020 Hemşirelik 12469
## 2 2019 Hemşirelik 11994
## 3 2017 İşletme (Açıköğretim) 11788
## 4 2018 Hemşirelik 11629
## 5 2017 Hemşirelik 11306
## 6 2016 Hemşirelik 10921
## 7 2020 Tıp Fakültesi 10702
## 8 2019 Tıp Fakültesi 10203
## 9 2018 Tıp Fakültesi 9648
## 10 2017 İşletme 9628
## 11 2016 İşletme 9481
## 12 2016 İşletme (Açıköğretim) 9226
## 13 2017 Tıp Fakültesi 9029
## 14 2017 İktisat 8840
## 15 2019 İlahiyat 8698
## 16 2018 İşletme 8593
## 17 2016 İktisat 8591
## 18 2020 İlahiyat 8573
## 19 2018 İlahiyat 8558
## 20 2017 İlahiyat 8501 all_years_data%>%
group_by(year,city)%>%
summarise(city_quota=sum(quota))%>%
arrange(desc(city_quota))%>%head(20)## # A tibble: 20 x 3
## # Groups: year [5]
## year city city_quota
## <dbl> <chr> <dbl>
## 1 2018 İSTANBUL 112514
## 2 2020 İSTANBUL 106565
## 3 2019 İSTANBUL 102296
## 4 2017 İSTANBUL 101551
## 5 2016 İSTANBUL 94271
## 6 2017 ESKİŞEHİR 65815
## 7 2016 ESKİŞEHİR 52348
## 8 2020 ANKARA 45092
## 9 2019 ANKARA 40273
## 10 2018 ANKARA 40069
## 11 2018 ESKİŞEHİR 38118
## 12 2017 ANKARA 37907
## 13 2016 ANKARA 35065
## 14 2019 ESKİŞEHİR 29768
## 15 2020 ESKİŞEHİR 26229
## 16 2020 İZMİR 23462
## 17 2019 İZMİR 22564
## 18 2018 İZMİR 22163
## 19 2020 ERZURUM 21999
## 20 2017 İZMİR 19236 all_years_data%>%
group_by(year,university)%>%
summarise(university_quota=sum(quota))%>%
arrange(desc(university_quota))%>%head(20)## # A tibble: 20 x 3
## # Groups: year [5]
## year university university_quota
## <dbl> <chr> <dbl>
## 1 2017 ANADOLU ÜNİVERSİTESİ 61485
## 2 2016 ANADOLU ÜNİVERSİTESİ 48110
## 3 2018 ANADOLU ÜNİVERSİTESİ 32351
## 4 2019 ANADOLU ÜNİVERSİTESİ 24057
## 5 2018 İSTANBUL ÜNİVERSİTESİ 22687
## 6 2020 ATATÜRK ÜNİVERSİTESİ 21203
## 7 2020 ANADOLU ÜNİVERSİTESİ 20939
## 8 2017 İSTANBUL ÜNİVERSİTESİ 18309
## 9 2016 İSTANBUL ÜNİVERSİTESİ 18169
## 10 2018 ATATÜRK ÜNİVERSİTESİ 15735
## 11 2020 İSTANBUL ÜNİVERSİTESİ 15283
## 12 2019 İSTANBUL ÜNİVERSİTESİ 15115
## 13 2016 ATATÜRK ÜNİVERSİTESİ 14239
## 14 2019 ATATÜRK ÜNİVERSİTESİ 13663
## 15 2017 ATATÜRK ÜNİVERSİTESİ 13524
## 16 2017 SELÇUK ÜNİVERSİTESİ 9738
## 17 2016 SELÇUK ÜNİVERSİTESİ 8364
## 18 2020 ANKARA ÜNİVERSİTESİ 8196
## 19 2018 SELÇUK ÜNİVERSİTESİ 8162
## 20 2017 GAZİ ÜNİVERSİTESİ 8055In some cases we would wonder the departments whose quote more increased or decreased. However, there are some highly volatile departments ending with "(Açıköğretim) , (İÖ) or (..indirimli). they may be misleading, so we should eliminate the departments ending with these words in order to get more established departments. Also another criteria can be necessity of being founded at least 4 years ago. So that we can see trends of department quotes. Here is the top 20 departments whose quote increased most and whose decreased most in the last 4 years. Note: Ranked by taking mean quote differences of last 4 years.
quote_by_dept <- all_years_data%>%
group_by(year,department)%>%
summarise(department_quota=sum(quota))
quote_by_dept <- data.frame(quote_by_dept) #after group_by we should convert it to df.
quote_by_dept <- quote_by_dept %>%
group_by(department) %>%
mutate(Diff = department_quota - lag(department_quota)) %>% arrange(desc(Diff))
quote_by_dept <- data.frame(quote_by_dept) # Again convert to df
quote_by_dept <- quote_by_dept %>% subset(!substr(department,nchar(department)-1,nchar(department)) %in% c("i)","m)","Ö)")) # eliminating some departments
# Also eliminating new founded departments
quote_by_dept <- quote_by_dept %>% group_by(department) %>% filter(n()>= 4)
quote_by_dept <- data.frame(quote_by_dept) # Again convert to df
quote_by_dept %>% group_by(department) %>% summarise(mean_differnce=mean(Diff,na.rm = T)) %>% arrange(desc(mean_differnce)) %>% head(20)#top 20 dept increased quota## # A tibble: 20 x 2
## department mean_differnce
## <chr> <dbl>
## 1 Tıp Fakültesi 578.
## 2 Psikoloji 464.
## 3 Diş Hekimliği Fakültesi 462.
## 4 Sağlık Yönetimi 461.
## 5 Finans ve Bankacılık 454.
## 6 Gastronomi ve Mutfak Sanatları 424.
## 7 İslami İlimler 391.
## 8 Bilgisayar Mühendisliği 388
## 9 Hemşirelik 387
## 10 Mimarlık 370.
## 11 Beslenme ve Diyetetik 344.
## 12 Fizyoterapi ve Rehabilitasyon 342
## 13 Radyo, Televizyon ve Sinema 317.
## 14 İngiliz Dili ve Edebiyatı (İngilizce) 294
## 15 Rehberlik ve Psikolojik Danışmanlık 293
## 16 Sosyal Hizmet 282.
## 17 Siyaset Bilimi ve Kamu Yönetimi 266.
## 18 Matematik 257.
## 19 Türk Dili ve Edebiyatı 236.
## 20 Hukuk Fakültesi 224.quote_by_dept %>% group_by(department) %>% summarise(mean_differnce=mean(Diff,na.rm = T)) %>% arrange(desc(mean_differnce)) %>% tail(20)#20 dept with most decrease in quota ## # A tibble: 20 x 2
## department mean_differnce
## <chr> <dbl>
## 1 Kimya Mühendisliği -88
## 2 İmalat Mühendisliği -90.5
## 3 Sosyal Bilgiler Öğretmenliği -92.5
## 4 Jeoloji Mühendisliği -94.8
## 5 Turizm İşletmeciliği ve Otelcilik -97.7
## 6 Ekonometri -100
## 7 Makine Mühendisliği -105
## 8 Konaklama İşletmeciliği -159.
## 9 Metalurji ve Malzeme Mühendisliği -172.
## 10 Uluslararası Ticaret -185.
## 11 Enerji Sistemleri Mühendisliği -192.
## 12 Kamu Yönetimi -216
## 13 Arkeoloji -219
## 14 Sınıf Öğretmenliği -237.
## 15 Çevre Mühendisliği -417.
## 16 Gıda Mühendisliği -450
## 17 Fen Bilgisi Öğretmenliği -457
## 18 Bilgisayar ve Öğretim Teknolojileri Öğretmenliği -577
## 19 İktisat -736.
## 20 İşletme -790.