<jupyter_start><jupyter_text>Homework 2 - Data from the Web Extracting & Analyzing TopUniversities and TimesHigherEducation Data  Library* required libraries for this homework<jupyter_code>from bs4 import BeautifulSoup
import requests
import pandas as pd
import seaborn
import unicodedata
import re
import matplotlib.pyplot as plt
import numpy as np
from IPython.core.display import display
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Preliminary of Data ScrapingUsing postman interceptor, we figure out that universities data for corresponding sites can be found on link below:top universities (QS):https://www.topuniversities.com/sites/default/files/qs-rankings-data/357051_indicators.txttimes higher education:link  VariablesAs we want to obtain the universities data from two main platforms that maintain global universities ranking, namely QS World University Rankings and Times World University ranking, we define prefix to indicate variables in the rest of this document as follow:Top Universities = "qs"Times Higher Education = "times"  Assignment of Static VariablesWe use pickle to store dataframes into files in order to avoid generating unnecessary traffic for every time executing this notebook.<jupyter_code># we store each main site url and its data url to web_urls
web_urls = [{'page':'https://www.topuniversities.com',
             'data':'https://www.topuniversities.com/sites/default/files/qs-rankings-data/357051_indicators.txt'},
            {'page':'https://www.timeshighereducation.com/world-university-rankings/2018/world-ranking#!/page/0/length/25/sort_by/rank/sort_order/asc/cols/stats',
             'data':'https://www.timeshighereducation.com/sites/default/files/the_data_rankings/world_university_rankings_2018_limit0_369a9045a203e176392b9fb8f8c1cb2a.json'}]

#reading from pickle, avoid generating too many web traffic
qs_top200    = pd.read_pickle("qs_top200_pickle")
times_top200 = pd.read_pickle("times_top200_pickle")<jupyter_output><empty_output><jupyter_text>FunctionsWe define 5 functions for later usage within this notebook:1. function get_number_elements : Is used to get the value inside the HTML div element with specific class="number".2. function crawl_topuniversities : Is used to obtain data from topuniversities and stores the result in panda Dataframe.3. function crawl_timeshighereducation : Is used to obtain data from timeshighereducation and stores the result in panda Dataframe.4. function convert_string: Is used to modify a string by removing parentheses (with its content inside) and converting non-ascii characters to ascii characters.5. function df_convert_index : Is used to create a panda Dataframe with modified university_name (removal of parentheses and non-ascii) as its index.<jupyter_code>def get_number_elements(htmlPage, className):
    """Function to return integer ELEMENT in <div class="number">ELEMENT</div>.
    Args:
        htmlPage  (BeautifulSoup obj) : HTML DOM to inspect for html <div> with class="number"
        className (string)            : parent class of html <div> with class="number" 
    Returns:
        number    (int) : Exact value of element inside <div> with class="number".
    """
    
    # Handle the exception if <div> class="number" cannot be found, setting default to zero
    try:
        number = int(((htmlPage.find('div', class_=className)).find('div', class_="number").text).replace(',',''))    
    except AttributeError:
        number = 0
    return number


def crawl_topuniversities(url, n_top):
    """
    Function to get universities data from topuniversities.com and to store it as panda dataframe.
    Gather the information of per university rank, name, country, region, total faculty members, total international faculty members,
    total students, and total international students.
    Args:
        url    (dict) : Dictionary obj contains main page URL with its corresponding data URL.
        n_top  (int)  : The number of desired N first top universities data, e.g. n_top=10 means the first top 10 universities. 
    Returns:
        universities (panda Dataframe) : Data of n_top universities from topuniversities.
    """
    
    # perform the HTTP request to data URL of the site and store the result to http_response
    http_response     = requests.get(url['data'])
    
    # render the result in json format and access "data" entry, pick only first n_top elements
    universities      = http_response.json()['data'][:n_top]
    
    # A list to form final dataframe
    list_universities = []
    
    # Iterate through the universities data
    for idx, university in enumerate(universities):
        
        # get the region, location, and overall_rank
        university_region      = university['region']
        university_country     = university['location']
        university_rank        = (int)(university['overall_rank'])
        
        # get HTML <a href> element by parsing HTML with BeautifulSoup
        university_info_html   = BeautifulSoup(university['uni'], 'html.parser')
        link_element           = university_info_html.find('a')
        
        # get the university name and link from <a href> element
        university_name        = link_element.text
        university_link        = link_element.get('href')

        # proceed a request to get detailed information of an university in its details page
        university_detail      = requests.get(url['page']+university_link)
        university_detail_html = BeautifulSoup(university_detail.text, 'html.parser')
        
        # Get the value inside <div class="number"> correspond to the given parent class
        n_f_members            = get_number_elements(university_detail_html,"total faculty")    
        n_inter_f_members      = get_number_elements(university_detail_html,"inter faculty") 
        n_students             = get_number_elements(university_detail_html,"total student") 
        n_inter_students       = get_number_elements(university_detail_html,"total inter") 
        
        # form a dictionary containing the desired information per university
        university_data = ({
                                'rank': university_rank,
                                'university_name': university_name,
                                'country': university_country,
                                'region': university_region,
                                'total_faculty_members': n_f_members,
                                'total_international_faculty_members': n_inter_f_members,
                                'total_students': n_students,
                                'total_international_students': n_inter_students
                          })
        
        # store the dictionary into a list
        list_universities.append(university_data)
    
    # form the dataframe from a list of dictionaries
    universities = pd.DataFrame.from_dict(list_universities)
    
    return universities


def crawl_timeshighereducation(url, n_top):
    """
    Function to get universities data from timeshighereducation.com and to store it as panda dataframe.
    Gather the information of per university rank, name, country, ratio of student per staff, ratio of international students,
    and total students
    Args:
        url    (dict) : Dictionary obj contains main page URL with its corresponding data URL.
        n_top  (int)  : The number of desired N first top universities data, e.g. n_top=10 means the first top 10 universities. 
    Returns:
        universities (panda Dataframe) : Data of n_top universities from timeshighereducation.
    """
    
    # perform the HTTP request to data URL of the site and store the result to http_response
    http_response     = requests.get(url['data'])
    
    # render the result in json format and access "data" entry, pick only first n_top elements
    universities      = http_response.json()['data'][:n_top]
    
    # A list to form final dataframe
    list_universities = []
    
    # Iterate through the universities data
    for university in universities:
        
        # get and store the university rank, name, country, number of students, ratio of student per staff, ratio of international student 
        university_rank          = (int)(university['rank'].replace('=',''))
        university_name          = university['name']
        university_country       = university['location']
        n_students               = int(university['stats_number_students'].replace(',',''))
        student_staff_ratio      = float(university['stats_student_staff_ratio'])
        inter_student_percentage = float(university['stats_pc_intl_students'].strip('%'))/100 
        
        # form a dictionary containing the desired information per university
        university_data = ({
                                'rank': university_rank,
                                'university_name': university_name,
                                'country': university_country,
                                'student_staff_ratio': student_staff_ratio,
                                'percentage_international_students': inter_student_percentage,
                                'total_students': n_students
                          })
        
        # store the dictionary into a list
        list_universities.append(university_data)
    
    # form the dataframe from a list of dictionaries
    universities = pd.DataFrame.from_dict(list_universities)
    
    return universities   

def convert_string(string):
    """
    Function to remove parentheses and non-ascii characters from a string.
    Args:
        string (string) : initial string.
    Returns:
        string_without_parentheses (string) : Converted string.
    """
    
    # using unicode to encode all characters in string into ascii
    str_byte                   = unicodedata.normalize("NFKD",string).encode('ascii','ignore')
    string_ascii               = str_byte.decode("utf-8", "strict")
    
    # remove parentheses and its content within a string
    string_without_parentheses = (re.sub(r'\([^)]*\)', '', string_ascii)).strip()
    
    return string_without_parentheses


def df_convert_index(df):
    """
    Function to index dataframe based on university name.
    University names will be converted beforehands to remove any parentheses and non-ascii characters.
    Args:
        df      (panda dataframe) : Original dataframe that we want to index.
    Returns:
        new_df  (panda Dataframe) : Indexed dataframe.
    """
    
    # create a panda series of indexes from converted university_name column in dataframe.
    new_index                 = pd.Series(list(map(convert_string, df['university_name'])))
    
    # form a new dataframe from original df, without university_name column.
    new_df                    = df.drop(['university_name'],axis=1)
    
    # set converted university_name as the index of the new dataframe 
    new_df['university_name'] = new_index
    new_df                    = new_df.set_index(['university_name'])
    
    return new_df<jupyter_output><empty_output><jupyter_text>Main Homework  1.Analysis of Top Universities DataNow let's begin to do data scarping from topuniversities.com. Please skip this part if you want to avoid generating web traffics.  Note that you will get the same data as from saved pickle file<jupyter_code># Store the data of 200 top-ranking universities in topuniversities into a dataframe
qs_top200 = crawl_topuniversities(web_urls[0], 200)
qs_top200.head(3)<jupyter_output><empty_output><jupyter_text>Writing Data into a fileNote: Run this cell to save the qs_top200 dataframe into pickle file. Later, we can read the pickle file to avoid generating redundant HTTP requestsPlease skip this part if you do not want to store the dataframe into a pickle file<jupyter_code>qs_top200.to_pickle("qs_top200_pickle")<jupyter_output><empty_output><jupyter_text>1a. Best Universities: Ratio Faculty Members / Students<jupyter_code># find out which universities are the best in terms of ratio faculty member/student
f_members_students_r                                 = pd.DataFrame(qs_top200.university_name)
f_members_students_r["faculty_member_student_ratio"] = (qs_top200.total_faculty_members/qs_top200.total_students)

# display the top 3 in this context
f_members_students_r.sort_values("faculty_member_student_ratio", ascending=False).head(3)<jupyter_output><empty_output><jupyter_text>1b. Best Universities: International Student Ratio<jupyter_code># find out which universities are the best in terms of ratio international student/total student
inter_students_r                                = pd.DataFrame(qs_top200.university_name)
inter_students_r["international_student_ratio"] = (qs_top200.total_international_students/qs_top200.total_students)

# display the top 3 in this context
inter_students_r.sort_values("international_student_ratio",ascending=False).head(3)<jupyter_output><empty_output><jupyter_text>1c. Best Universities In terms of Faculty Member/Student Ratio and Ratio of International Student, aggregated per Country<jupyter_code># form a Dataframe with columns of country, university_name, faculty_member_student_ratio, and international_student_ratio
qs_country_grouped = f_members_students_r
qs_country_grouped['country'] = qs_top200['country']
qs_country_grouped['international_student_ratio'] = inter_students_r['international_student_ratio']

# Display the best universities in each country based on faculty_member_student_ratio
print("Best universities in each country based on faculty_member_student_ratio")
display(qs_country_grouped.sort_values('faculty_member_student_ratio', ascending=False).groupby('country', as_index=False).first())

# Display the best universities in each country based on international_student_ratio
print("\nBest universities in each country based on international_student_ratio")
display(qs_country_grouped.sort_values('international_student_ratio', ascending=False).groupby('country', as_index=False).first())<jupyter_output>Best universities in each country based on faculty_member_student_ratio<jupyter_text>1d. Best Universities In terms of Faculty Member/Student Ratio and Ratio of International Student, aggregated per Region<jupyter_code># form a Dataframe with columns of region, university_name, faculty_member_student_ratio, and international_student_ratio
qs_region_grouped = f_members_students_r
qs_region_grouped['region'] = qs_top200['region']
qs_region_grouped['international_student_ratio'] = inter_students_r['international_student_ratio']

# Display the best universities in each region based on faculty_member_student_ratio
print("Best universities in each region based on faculty_member_student_ratio")
display(qs_country_grouped.sort_values('faculty_member_student_ratio', ascending=False).groupby('region', as_index=False).first())

# Display the best universities in each region based on international_student_ratio
print("\nBest universities in each region based on international_student_ratio")
display(qs_country_grouped.sort_values('international_student_ratio', ascending=False).groupby('region', as_index=False).first())<jupyter_output>Best universities in each region based on faculty_member_student_ratio<jupyter_text>Plot : Bar ChartsBased on results 1c and 1d, we draw 4 bar charts<jupyter_code># Aggregating the result as in 1c
plot01_data = qs_country_grouped.sort_values('faculty_member_student_ratio', ascending=False).groupby('country', as_index=False).first()
plot02_data = qs_country_grouped.sort_values('international_student_ratio', ascending=False).groupby('country', as_index=False).first()

# Aggregating the result as in 1d
plot03_data = qs_region_grouped.sort_values('faculty_member_student_ratio', ascending=False).groupby('region', as_index=False).first()
plot04_data = qs_region_grouped.sort_values('international_student_ratio', ascending=False).groupby('region', as_index=False).first()

# Drawing the result 1c as plot01 and plot02
plot01=plot01_data.plot(y='faculty_member_student_ratio', x=['country','university_name'], kind='bar', grid=True, figsize=(10, 6), title='1(c):1')
plot02=plot02_data.plot(y='international_student_ratio', x=['country','university_name'], kind='bar', grid=True, figsize=(10, 6), title='1(c):2')

# Drawing the result 1d as plot03 and plot04
plot03=plot03_data.plot(y='faculty_member_student_ratio', x=['region','university_name'], kind='bar', grid=True, figsize=(8, 3), title='1(d):1')
plot04=plot04_data.plot(y='international_student_ratio', x=['region','university_name'], kind='bar', grid=True, figsize=(8, 3), title='1(d):2')<jupyter_output><empty_output><jupyter_text>ObersvationBy Country:For the faculty members and students ratio we can see that the US and the UK have a clearly higher ratio than other countries, while for the international students ratio the US are slightly below some countries in Europe as The UK or Switzerland. We can also notice a very weak portion of international students in some countries of Latin America as Brazil or Chile but the smallest part remains in India.By Region:We observe similar things in regions, North America and Europe are ahead in terms of faculty members and students ratio and as well for the proportion of international students. We also have that the weakest region for both ratio is Africa, this is probably due, in a way, to the fact that there is only one university in the top 200.  2.Analysis of Times Higher Education DataSecond is to do data scarping from timeshighereducation.com. Please skip this part if you want to avoid generating web traffics.  Note that you will get the same data as from saved pickle file<jupyter_code># Store the data of the 200 top-ranking universities in timeshighereducation into a dataframe
times_top200 = crawl_timeshighereducation(web_urls[1],200)
times_top200.head(3)<jupyter_output><empty_output><jupyter_text>Writing Data into a fileNote: Run this cell to save the times_top200 dataframe into pickle file. Later, we can read the pickle file to avoid generating redundant HTTP requestsPlease skip this part if you do not want to store the dataframe into a pickle file<jupyter_code>times_top200.to_pickle("times_top200_pickle")<jupyter_output><empty_output><jupyter_text>2a. Best Universities: Student/Staff Ratio Note that for this attributes, the lower ratio indicates a better university in rank. In QS the ratio is reversed which is staff/student ratio<jupyter_code># find out which universities are the best in terms of ratio student/staff
# display the top 3 in this context
times_top200.sort_values("student_staff_ratio",ascending=True).head(3)<jupyter_output><empty_output><jupyter_text>2b. Best Universities: International Student Ratio<jupyter_code># find out which universities are the best in terms of ratio international student/total student
# display the top 3 in this context
times_top200.sort_values("percentage_international_students",ascending=False).head(3)<jupyter_output><empty_output><jupyter_text>2c. Best Universities In terms of Ratio of Student/Staff and Percentage of International Students, aggregated per Country<jupyter_code># Display the best universities in each country based on faculty_member_student_ratio
display(times_top200.sort_values('student_staff_ratio', ascending=True).groupby('country', as_index=False).first())

# Display the best universities in each country based on international_student_ratio
display(times_top200.sort_values('percentage_international_students', ascending=False).groupby('country', as_index=False).first())<jupyter_output><empty_output><jupyter_text>Plot : Bar ChartsBased on results 2c, we draw 2 bar charts<jupyter_code># Aggregating the result as in 2c
plot05_data = times_top200.sort_values('student_staff_ratio', ascending=True).groupby('country', as_index=False).first()
plot06_data = times_top200.sort_values('percentage_international_students', ascending=False).groupby('country', as_index=False).first()

# Drawing the result 2c as plot05 and plot06
plot05=plot05_data.plot(y='student_staff_ratio', x=['country','university_name'], kind='bar', grid=True, figsize=(10, 6), title='2(c):1')
plot06=plot06_data.plot(y='percentage_international_students', x=['country','university_name'], kind='bar', grid=True, figsize=(10, 6), title='2(c):2')<jupyter_output><empty_output><jupyter_text>ObersvationFor the Times ranking we have a similliar ranking in terms of faculty members and students ratio but in which the ratio is reversed (the lower the better). The US are very low compared to other countries and Belgium is way ahead with Ireland. If we had regions for the Times ranking we would clearly have Europe as dominant region for the first ratio.In terms of international students, the Times and QS rankings are quite similar, Europe has the most important proportion and the UK, Switzerland and Luxembourg form the top 3. It also looks like Asia has the lowest rate of international students as Japan and Taiwan are the countries at the bottom of the chart.  3.Merging Dataframe To accurately merge both data from topuniversities (qs) and timehighereducation (times), we divide the merging process into 5 parts as follows:1. Forming dataframe df1 and df2 with university_name as the index. University_name must not contains parentheses and non-ascii characters.2. Performing inner join from df1 and df2 based on university_name3. Doing manual matching for the rest of universities in df1 that fails exact matching in inner join at step 2.4. Doing manual matching for the rest of universities in df2 that fails exact matching in inner join at step 2.5. Combining the result from 2,3,4  3.1. Forming New Dataframes with university_name as indexTo avoid failures when performing inner join, we remove any form of parentheses in both dataframe from qs and times. We descibe the problem as follows:For example, in QS the name of remarkable MIT is "Massachusetts Institute of Technology (MIT)" meanwhile in times it is "Massachusetts Institute of Technology", without abbreviation.In addition, in in qs our beloved campus is ranked as "Ecole Polytechnique Fédérale de Lausanne (EPFL)". However in times the name is recorded as "École Polytechnique Fédérale de Lausanne", with non-ascii character (É) as first letter.Therefore, to perform inner join with exact matching of university_name, we figure out that we need to remove parentheses and non-ascii characters from university_name. We use function "convert_index" as explained in section functions.<jupyter_code># Forming the dataframe df1, using university_name with its parentheses and non-ascii characters removed and converted
df1 = df_convert_index(qs_top200)

# Adding prefix qs_ to keep track of data from topuniversities
df1 = df1.add_prefix("qs_")

# Forming the dataframe df2, using university_name with its parentheses and non-ascii characters removed and converted
df2 = df_convert_index(times_top200)

# Adding prefix times_ to keep track of data from timeshighereducation
df2 = df2.add_prefix("times_")

display(df1.head(3))
display(df2.head(3))<jupyter_output><empty_output><jupyter_text>3.2: Inner Join based on university_name (exact matching)We perform inner join and assign the result as df_merged_exact_match.In addition, we store the rest of universities record in qs as df1_rest and the rest of universities record in times as df2_rest<jupyter_code># find matching index in df2 from df1
df1_matching_idx      = (df1.index).isin(df2.index)

# forming with inner join, exact match
df_merged_exact_match = pd.concat([df1[df1_matching_idx], df2], axis=1, join='inner')

# display the first 10 top entry of the join result
display(df_merged_exact_match.head(10))


# form the rest of record in qs as df1_rest for step 3.3 & 3.4
df1_rest              = df1[~df1_matching_idx]

# find matching index in df1 from df2
df2_matching_idx      = (df2.index).isin(df1.index)

# form the rest of record in times as df2_rest for step 3.3 & 3.4
df2_rest              = df2[~df2_matching_idx]<jupyter_output><empty_output><jupyter_text>3.3: Manual Matching for the rest of df1We observed some exceptions that we cannot avoid to solve manually. For example, in qs Technische Universität Berlin differ in name with Technical University of Berlin in times but they are actually the same university. Thus we perform manual checking through if else statement (although it is a bit long here). But we confident that it is the best approaches we have for this task.Overall, we use the first three words of university_name as keyword, and we try to find "similiar" patern in df2_rest. * Exception for university_name less than < 3, we only use the first word.* Exception for keyword starts with "University of", we use the rest of words in university_name as keyword starting from the third.* Exception for keyword starts with "The", we use the rest of words in university_name as keyword starting from the second.<jupyter_code># a list to store the matching index of df1
university_list = []

# Loop through df1_rest
for index, row in df1_rest.iterrows():  
    
    # Handle the exception, some universities have less than 3 words in name
    try:       
        # Pick the first three words of university_name as keyword
        keyword = " ".join(index.split()[:3])
        
        # Remove thrilling comma (if any)
        if(keyword.endswith(",")):
            keyword = keyword[:-1]
            
    # Exception, pick only the first word in university_name        
    except IndexError:
        keyword = index.split()[0]
    
    #================ Manual Matching Starts ======================#
    if(keyword.startswith("The")):
        keyword = " ".join(index.split()[1:])
    elif(keyword.startswith("University of")):
        keyword = " ".join(index.split()[2:])
    elif(keyword.startswith("UCL")):
        keyword = "University College London"
    elif(keyword.startswith("King's College")):
        keyword = "Kings College"
    elif(keyword.startswith("KAIST")):
        keyword = "Korea Advanced Institute"
    elif(keyword.startswith("Ludwig")):
        keyword = "LMU"
    elif(keyword.endswith("Heidelberg")):
        keyword = "Heidelberg"
    elif(keyword.startswith("KIT")):
        keyword = "Karlsruhe"
    elif(keyword.startswith("Humboldt")):
        keyword = "Humboldt"
    elif(keyword.endswith("Montreal")):
        keyword = "Montreal"
    elif(keyword.startswith("Universite Pierre")):
        keyword = "Pierre"
    elif(keyword.startswith("Technische")):
        keyword = "Technical University of Berlin"
    elif(keyword.endswith("Catholique")):
        keyword = "Catholique"
    elif(keyword.startswith("ETH")):
        keyword = "ETH Zurich"
    elif(keyword.startswith("Freie")):
        keyword = "Free University"
    elif(keyword.startswith("Universite catholique")):
        keyword = "Louvain"
    elif(keyword.startswith("<NAME>")):
        keyword = "Tubingen"
    elif(keyword.startswith("Albert-Ludwigs-Universitaet")):
        keyword = "Freiburg"
    elif(keyword.startswith("Scuola Superiore")):
        keyword = "Scuola Superiore"
    elif(keyword.startswith("Universitat Autonoma")):
        keyword = "Autonomous University"    
    #================ Manual Matching Ends ======================#
    
    # Check for matching entry in df2_rest based on given keyword
    matched_times_data = df2_rest.filter(like=keyword, axis=0)
    
    # if matching entry is found in df2_rest
    if(matched_times_data.empty is False):
        # obtain the record from df1_rest
        matched_qs_data = df1_rest.loc[[index]]
        
        # form one-row dataframe, keep university_name of both qs and times to recheck that we pick the exact same university
        formed_qs_times = pd.concat([matched_qs_data.reset_index(), matched_times_data.reset_index()], axis=1)
        
        # set university_name as index
        formed_qs_times = formed_qs_times.set_index(['university_name'])
        
        # store the result of manual matching to list
        university_list.append(formed_qs_times.iloc[[0]])
    
        # remove the matching record from df2_rest 
        df2_rest = df2_rest.drop(matched_times_data.index)

# From list of matching universities, store it as panda DataFrame        
qs_times_matched_1 = pd.concat(university_list)

# Display the result of step 3.3
qs_times_matched_1<jupyter_output><empty_output><jupyter_text>How many matching universities?<jupyter_code># Find out how many matching university names obtained from step 3.3
qs_times_matched_1.shape[0]<jupyter_output><empty_output><jupyter_text>3.4: Manual Matching for the rest of df2Same as 3.3 but now we do the manual matching by iterating through the rest of df2 from step 3.2 & 3.3Overall, we use the first three words of university_name as keyword, and we try to find "similiar" patern in df1_rest. * Exception for university_name less than < 3, we only use the first word.* Exception for keyword starts with "University of", we use the rest of words in university_name as keyword starting from the third.* Exception for keyword starts with "The", we use the rest of words in university_name as keyword starting from the second.*  We observe that the only remaining university record presents in both qs and times is University of North Carolina at Chapel Hill<jupyter_code># a list to store the matching index of df2
university_list = []

# Loop through df2_rest
for index, row in df2_rest.iterrows():  
    
    # Handle the exception, some universities have less than 3 words in name
    try:       
        # Pick the first three words of university_name as keyword
        keyword = " ".join(index.split()[:3])
        
        # Remove thrilling comma (if any)
        if(keyword.endswith(",")):
            keyword = keyword[:-1]
            
    # Exception, pick only the first word in university_name        
    except IndexError:
        keyword = index.split()[0]
    
    #================ Manual Matching Starts ======================#
    if(keyword.startswith("The")):
        keyword = " ".join(index.split()[1:])
    elif(keyword.startswith("University of North")):
        keyword = "North Carolina"
    elif(keyword.startswith("University of")):
        keyword = " ".join(index.split()[2:])
    #================ Manual Matching Ends ======================#
    
    # Check for matching entry in df1_rest based on given keyword
    matched_qs_data = df1_rest.filter(like=keyword, axis=0)
    
    # if matching entry is found in df1_rest
    if(matched_qs_data.empty is False):
        
        # obtain the record from df2_rest
        matched_times_data = df2_rest.loc[[index]]
        
        # form one-row dataframe, keep university_name of both qs and times to recheck that we pick the exact same university
        formed_qs_times    = pd.concat([matched_times_data.reset_index(),matched_qs_data.reset_index()],axis=1)
        
        # set university_name as index
        formed_qs_times    = formed_qs_times.set_index(['university_name'])
        
        # store the result of manual matching to list
        university_list.append(formed_qs_times.iloc[[0]])

# From list of matching universities, store it as panda DataFrame        
qs_times_matched_2 = pd.concat(university_list)

# Display the result of step 3.4
qs_times_matched_2<jupyter_output><empty_output><jupyter_text>How many matching universities?<jupyter_code># Find out how many matching university names obtained from step 3.3
qs_times_matched_2.shape[0]<jupyter_output><empty_output><jupyter_text>3.5: Finally Merging Universities Data that Present in Both QS and TimesNow we merge dataframe obtained from step 3.2, 3.3, & 3.4.Note that we discard all records that not available in both dataframe.<jupyter_code># Merging final dataframe
dataframes        = [df_merged_exact_match, qs_times_matched_1, qs_times_matched_2]
combined_qs_times = pd.concat(dataframes)

# Display the data
display(combined_qs_times)<jupyter_output><empty_output><jupyter_text>4. Exploratory Data Analysis  Adding Columns of QS Percentage International Faculties and QS Percentage of International Students<jupyter_code># QS Percentage of international faculty members
combined_qs_times['qs_percentage_international_faculties'] = combined_qs_times.qs_total_international_faculty_members/combined_qs_times.qs_total_faculty_members

# QS Percentage of international students
combined_qs_times['qs_percentage_international_students'] = combined_qs_times.qs_total_international_students/combined_qs_times.qs_total_students<jupyter_output><empty_output><jupyter_text>Relation between two variablesWe will use the following table to reveal some phenomenon<jupyter_code>combined_qs_times.corr()<jupyter_output><empty_output><jupyter_text>Does the university with higher proportion of international students also have higher proportion of international faculties?In the result above, we could see the coefficient coefficient of % of international students and % of international faculties is 0.642269, which tells us that a university with more percentage of international students tends to have more percentage of international faculties (Like EPFL). However, we could also see some outliers in the plot. For example, there're some schools with lower proportion of international students but with higher proportion of international faculties, and it turns out to be the two universities in Switzerland: University of Zurich and University of Bern<jupyter_code># Observe the correlation with scatter plot
combined_qs_times.plot.scatter(x='times_percentage_international_students', y='qs_percentage_international_faculties');

# List the outliers
print('\nUniversity with fewer than 20% of international students but with more than 50% of international faculties:')
outliers = (combined_qs_times.qs_percentage_international_faculties > 0.5) & \
                 (combined_qs_times.times_percentage_international_students <= 0.2)
for university in combined_qs_times.index[outliers]:
    print(university)<jupyter_output>University with fewer than 20% of international students but with more than 50% of international faculties

University of Zurich
University of Bern<jupyter_text>Does higher ratio of international faculties help the ranking?We could see generally this is true, and now we observer the outlier (the one with very high ranking but with few number of international faculties). We could find generally there are two kinds of groups. The first group is the universities in Asia, and the other group is the universities in USA.<jupyter_code># Observe the correlation with scatter plot
combined_qs_times.plot.scatter(x='qs_rank', y='qs_total_international_faculty_members');

# List the outliers
print('\nTop 50 Universities with International Faculties Less than 500:')
outliers = (combined_qs_times.qs_rank < 50) & (combined_qs_times.qs_total_international_faculty_members < 500)
for university in combined_qs_times.index[outliers]:
    print(university)<jupyter_output>California Institute of Technology
Princeton University
Duke University
Kyoto University
Seoul National University
Carnegie Mellon University
('The University of Tokyo', 'University of Tokyo')
('KAIST - Korea Advanced Institute of Science & Technology', 'Korea Advanced Institute of Science and Technology')<jupyter_text>Does the number of students move with number of faculties?The correlation between these two attributes is 0.59, and it reveals a natural result: If there are more students, we need more faculties. And there are some outliers with roughly 60000 students and fewer than 3000 faculties.<jupyter_code># Observe the correlation with scatter plot
combined_qs_times.plot.scatter(x='qs_total_students', y='qs_total_faculty_members');

# List the outliers
print('\nUniversity with fewer than 4000 faculties and more than 50000 students:')
outliers = (combined_qs_times.qs_total_students > 50000) & (combined_qs_times.qs_total_faculty_members < 4000)
for university in combined_qs_times.index[outliers]:
    print(university)<jupyter_output>University with fewer than 4000 faculties and more than 50000 students:
Monash University
University of Washington
Texas A&M University<jupyter_text>How does QS ranking and Times ranking differ?We could see an interesting phenomenon: There's no big difference within the top 30 universities in QS ranking and Times ranking (we could see an obvious line there), and after 30, the ranking starts to differ a lot.In the following we show the universities which are top 100 in QS but more than 150 in Times, and we can see the universities are mostly in Asia (Only KTH is in Europe). On the other hand, the universities which are top 100 in Times but more than 150 in QS are in Europe and USA.<jupyter_code># Observe the rank correlation with scatter plot
combined_qs_times.plot.scatter(x='qs_rank', y='times_rank');

# List the universities top in QS but not in Times
print('\nUniversity which is top 100 in QS but more than 150 in Times:')
outliers1 = (combined_qs_times.qs_rank < 100) & (combined_qs_times.times_rank > 150)
for university in combined_qs_times.index[outliers1]:
    print(university)

# List the universities top in Times but not in QS
print('\nUniversity which is top 100 in Times but more than 150 in QS:')
outliers2 = (combined_qs_times.times_rank < 100) & (combined_qs_times.qs_rank > 150)
for university in combined_qs_times.index[outliers2]:
    print(university)<jupyter_output>University which is top 100 in QS but more than 150 in Times:
Shanghai Jiao Tong University
National Taiwan University
Zhejiang University
Lomonosov Moscow State University
KTH Royal Institute of Technology
('The University of Auckland', 'University of Auckland')
('The Hong Kong Polytechnic University', 'Hong Kong Polytechnic University')

University which is top 100 in Times but more than 150 in QS:
University of Minnesota
University of California, Irvine
Dartmouth College
('Eberhard Karls Universitat Tubingen', 'University of Tubingen')
('Albert-Ludwigs-Universitaet Freiburg', 'University of Freiburg')<jupyter_text>Observation of Distribution of QS and Times rankings per region and country using Scatter Plot<jupyter_code># We draw 4 figures to do this observation
fig = plt.figure(figsize=(16,16))
ax  = fig.add_subplot(2, 2, 1)

# Figure 1: QS Ranking by Region
Xuniques, X = np.unique(combined_qs_times['qs_region'], return_inverse=True)
ax.scatter(X, combined_qs_times['qs_rank'], s=5, c='b')
ax.set(xticks=range(len(Xuniques)), xticklabels=Xuniques) 
ax.set_title('QS Ranking per region')
ax.set_xlabel('Region')
ax.set_ylabel('Qs_Rank')

# Figure 2: Times Ranking by Region
bx = fig.add_subplot(2, 2, 2)
bx.scatter(X, combined_qs_times['times_rank'], s=5, c='b')
bx.set(xticks=range(len(Xuniques)), xticklabels=Xuniques) 
bx.set_title('Times Ranking per region')
bx.set_xlabel('Region')
bx.set_ylabel('Times_Rank')

# Figure 3: QS Ranking by Country
Xuniques, X = np.unique(combined_qs_times['qs_country'], return_inverse=True)
cx = fig.add_subplot(2, 2, 3)
cx.scatter(combined_qs_times['qs_rank'],X, s=5, c='b')
cx.set(yticks=range(len(Xuniques)), yticklabels=Xuniques) 
cx.set_title('QS Ranking per country')
cx.set_ylabel('Country')
cx.set_xlabel('Qs_Rank')

# Figure 4: Times Ranking by Region
dx = fig.add_subplot(2, 2, 4)
dx.scatter(combined_qs_times['times_rank'],X, s=5, c='b')
dx.set(yticks=range(len(Xuniques)), yticklabels=Xuniques) 
dx.set_title('Times Ranking per country')
dx.set_ylabel('Country')
dx.set_xlabel('Times_Rank')
plt.show()<jupyter_output><empty_output><jupyter_text>Top universities for QS are mostly north american while it is Europe which has the top-notch universities for Times. We can also see that Europe has the most important number of universities in the top 200 for both rankings. Also for Asia and Oceania we have that QS ranking is less sparse than Times ranking, i. e. they are better ranked in QS than in Times. By looking more in detail we can clearly see that the most represented countries for North America and Europe are the US and the UK respectively. These two countries also have the top 10 universities for both rankings.One reason to the differences between the two rankings might also be that Times takes the age of the university as a more important parameter than QS does.  Does Times give worse ranking to universities in Asia?This question is naturally asked based on the observation above. We substract rank_qs with rank_times to observe the result and we can see that only one university gets higher rank in Times: Peking University<jupyter_code># Form the dataframe with value of (qs_rank) - (times_rank) 
df_asia_universities = combined_qs_times.qs_rank[combined_qs_times.qs_region == 'Asia'] - combined_qs_times.times_rank[combined_qs_times.qs_region == 'Asia']
(df_asia_universities).plot(x = 'QS ranking', title = 'QS ranking - Times ranking', rot = 90)

# List Exception
print('\nAsia University with higher ranking in Times:')
idx = (combined_qs_times.times_rank < combined_qs_times.qs_rank) & (combined_qs_times.qs_region == 'Asia')
for university in combined_qs_times.index[idx]:
    print(university)<jupyter_output>Asia University with higher ranking in Times:
Peking University<jupyter_text>5. Best university taking in consideration both rankings.In this question, we use harmonic mean of two rankings as the final ranking:$$ \overline{x} = \frac{2x_1x_2}{(x_1 + x_2)} $$the reason to use harmonic mean is that we want put more emphasis on better ranking. The property of harmonic mean is that the mean would be more close to the one with small number (i.e. higher ranking)We don't use regression to combine the ranking, and the reason is that we don't have too many features in our dataset (at least we know both rankings are based on lots of other attributes such as Citations per Faculty, Academic Reputation, Employer Reputation ... etc), and hence use regression to calculate the ranking would cause underfitting.<jupyter_code># Calculate the harmonic mean, x1 = qs_rank, x2 = times_rank
x1            = combined_qs_times.qs_rank
x2            = combined_qs_times.times_rank
harmonic_mean = (2 * x1 * x2)/(x1 + x2)

# Form the final dataframe with final_score (harmonic mean)
df_final                = combined_qs_times
df_final['final_score'] = harmonic_mean

# Assign the final_rank based on final_score, sort by ascending
df_final                = df_final.sort_values(['final_score'])
df_final['final_rank']  = range(1,len(df_final)+1)

# Display the top 20 universities taking consideration from both rankings
display(df_final[['final_score','final_rank']].head(20))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07790321825080808, 'fraction_numerical': 0.014264299433175621, 'mean_word_length': 4.496974378782027, 'pattern_counts': {'":': 0, '<': 141, '<?xml version=': 0, '>': 136, 'https://': 5, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '306994', 'license': 'MIT', 'n_tokens_mistral': 12390, 'n_tokens_neox': 11730, 'n_words': 4185, 'path': '02 - Data from the Web/Homework 2.ipynb', 'repo_name': 'sanadhis/ADA-2017', 'size': '42702'}