Statistical Programming: Problem Set 2 — Solutions

Author

Affiliation

Jan Teichert-Kluge

University of Hamburg

Download Notebook (.ipynb)

Notes

This is the solution file for Problem Set 2. It covers data loading, exploration, cleaning, joining, filtering, grouping, and visualization using the Times Higher Education World University Rankings and a continents mapping dataset.

---

Section 1: Loading Data

Exercise 1: Load the Datasets

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

url_times = "https://raw.githubusercontent.com/JanTeichertKluge/data-statprog/refs/heads/main/data/timesData.csv"
url_continents = "https://raw.githubusercontent.com/JanTeichertKluge/data-statprog/refs/heads/main/data/continents.csv"

df_times = pd.read_csv(url_times)
df_continents = pd.read_csv(url_continents)

print("Times Data shape:", df_times.shape)
print("Continents Data shape:", df_continents.shape)

Times Data shape: (2603, 14)
Continents Data shape: (285, 3)

---

Section 2: Data Exploration

Exercise 2: Inspect the Data

# a) First 5 rows
df_times.head()

	world_rank	university_name	country	teaching	international	research	citations	income	total_score	num_students	student_staff_ratio	international_students	female_male_ratio	year
0	1	Harvard University	United States of America	99.7	72.4	98.7	98.8	34.5	96.1	20,152	8.9	25%	NaN	2011
1	2	California Institute of Technology	United States of America	97.7	54.6	98.0	99.9	83.7	96.0	2,243	6.9	27%	33 : 67	2011
2	3	Massachusetts Institute of Technology	United States of America	97.8	82.3	91.4	99.9	87.5	95.6	11,074	9.0	33%	37 : 63	2011
3	4	Stanford University	United States of America	98.3	29.5	98.1	99.2	64.3	94.3	15,596	7.8	22%	42 : 58	2011
4	5	Princeton University	United States of America	90.9	70.3	95.4	99.9	-	94.2	7,929	8.4	27%	45 : 55	2011

# b) Data types and missing values
df_times.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2603 entries, 0 to 2602
Data columns (total 14 columns):
 #   Column                  Non-Null Count  Dtype  
---  ------                  --------------  -----  
 0   world_rank              2603 non-null   object 
 1   university_name         2603 non-null   object 
 2   country                 2603 non-null   object 
 3   teaching                2603 non-null   float64
 4   international           2603 non-null   object 
 5   research                2603 non-null   float64
 6   citations               2603 non-null   float64
 7   income                  2603 non-null   object 
 8   total_score             2603 non-null   object 
 9   num_students            2544 non-null   object 
 10  student_staff_ratio     2544 non-null   float64
 11  international_students  2536 non-null   object 
 12  female_male_ratio       2370 non-null   object 
 13  year                    2603 non-null   int64  
dtypes: float64(4), int64(1), object(9)
memory usage: 284.8+ KB

# c) Descriptive statistics
df_times.describe()

	teaching	research	citations	student_staff_ratio	year
count	2603.000000	2603.000000	2603.000000	2544.000000	2603.000000
mean	37.801498	35.910257	60.921629	18.445283	2014.075682
std	17.604218	21.254805	23.073219	11.458698	1.685733
min	9.900000	2.900000	1.200000	0.600000	2011.000000
25%	24.700000	19.600000	45.500000	11.975000	2013.000000
50%	33.900000	30.500000	62.500000	16.100000	2014.000000
75%	46.400000	47.250000	79.050000	21.500000	2016.000000
max	99.700000	99.400000	100.000000	162.600000	2016.000000

# d) Unique years and countries
n_years = df_times['year'].nunique()
n_countries = df_times['country'].nunique()

print(f"Unique years: {n_years}")
print(f"Unique countries: {n_countries}")
print(f"\nYear values: {sorted(df_times['year'].unique())}")

Unique years: 6
Unique countries: 72

Year values: [np.int64(2011), np.int64(2012), np.int64(2013), np.int64(2014), np.int64(2015), np.int64(2016)]

---

Section 3: Data Cleaning & Transformation

Exercise 3: Replace Missing Value Placeholders

# Replace '-' with NaN across the whole DataFrame
df_times = df_times.replace('-', np.nan)

# Verify
remaining = (df_times['total_score'] == '-').sum()
print(f"Remaining '-' in total_score: {remaining}")
print(f"Sample values: {df_times['total_score'].dropna().head().tolist()}")

Remaining '-' in total_score: 0
Sample values: ['96.1', '96.0', '95.6', '94.3', '94.2']

Exercise 4: Convert String Columns to Numeric

df_times['total_score'] = pd.to_numeric(df_times['total_score'])
df_times['income'] = pd.to_numeric(df_times['income'])

print(df_times[['total_score', 'income']].dtypes)
print(df_times[['total_score', 'income']].describe())

total_score    float64
income         float64
dtype: object
       total_score       income
count  1201.000000  2385.000000
mean     59.846128    48.979874
std      12.803446    21.179938
min      41.400000    24.200000
25%      50.300000    33.000000
50%      56.000000    41.000000
75%      66.200000    59.000000
max      96.100000   100.000000

Exercise 5: Clean Comma-Formatted Numbers

df_times['num_students'] = pd.to_numeric(df_times['num_students'].str.replace(',', ''))

top_students = (df_times[df_times['year'] == 2016]
                .nlargest(5, 'num_students')
                [['university_name', 'country', 'num_students']])
print(top_students)

                               university_name       country  num_students
2413                        Anadolu University        Turkey      379231.0
2430                          Cairo University         Egypt      231941.0
2562                University of South Africa  South Africa      197102.0
2259  National Autonomous University of Mexico        Mexico      137378.0
2408                     Alexandria University         Egypt      127431.0

Exercise 6: Clean Percentage Strings

df_times['international_students'] = (
    pd.to_numeric(df_times['international_students'].str.replace('%', '')) / 100
)

mean_intl = df_times[df_times['year'] == 2016]['international_students'].mean()
print(f"Mean international student share (2016): {mean_intl:.1%}")

Mean international student share (2016): 12.7%

Exercise 7: Split a Column into Two

# Split on ' : '
split_ratios = df_times['female_male_ratio'].str.split(' : ', expand=True)

df_times['female_ratio'] = pd.to_numeric(split_ratios[0])
df_times['male_ratio'] = pd.to_numeric(split_ratios[1])

# Verify sum ~100
ratio_sum = (df_times['female_ratio'] + df_times['male_ratio']).dropna()
print(f"All ratios sum to ~100: {(ratio_sum.round(0) == 100).all()}")
print(df_times[['female_male_ratio', 'female_ratio', 'male_ratio']].dropna().head())

All ratios sum to ~100: True
  female_male_ratio  female_ratio  male_ratio
1           33 : 67          33.0        67.0
2           37 : 63          37.0        63.0
3           42 : 58          42.0        58.0
4           45 : 55          45.0        55.0
5           46 : 54          46.0        54.0

---

Section 4: Joining Datasets

Exercise 8: Merge with the Continents Dataset

# Inspect
print(df_continents.head())

# Rename columns
df_continents = df_continents.rename(columns={
    'Entity': 'country',
    'Countries Continents': 'continent'
})

# Keep only necessary columns (drop the Year column if present)
df_continents = df_continents[['country', 'continent']].drop_duplicates()

# Left merge
df_merged = pd.merge(df_times, df_continents, on='country', how='left')

print(f"\nMerged shape: {df_merged.shape}")
print(df_merged[['university_name', 'country', 'continent']].head())

                  Entity  Year Countries Continents
0               Abkhazia  2015                 Asia
1            Afghanistan  2015                 Asia
2  Akrotiri and Dhekelia  2015                 Asia
3                Albania  2015               Europe
4                Algeria  2015               Africa

Merged shape: (2603, 17)
                         university_name                   country continent
0                     Harvard University  United States of America       NaN
1     California Institute of Technology  United States of America       NaN
2  Massachusetts Institute of Technology  United States of America       NaN
3                    Stanford University  United States of America       NaN
4                   Princeton University  United States of America       NaN

Exercise 9: Handle Mismatched Country Names

# a) Countries with missing continents
missing = df_merged[df_merged['continent'].isna()]['country'].unique()
print("Countries with missing continent:", missing)

# b) Fix country names in df_times and re-merge
df_times['country'] = df_times['country'].replace({
    'United States of America': 'United States',
    'Russian Federation': 'Russia',
    'Republic of Ireland': 'Ireland',
})
df_merged = pd.merge(df_times, df_continents, on='country', how='left')

# c) Check remaining
still_missing = df_merged[df_merged['continent'].isna()]['country'].unique()
print(f"\nCountries still missing a continent: {still_missing}")
print(f"Rows without continent: {df_merged['continent'].isna().sum()}")

Countries with missing continent: ['United States of America' 'Republic of Ireland' 'Russian Federation'
 'Czech Republic' 'Macau' 'Unisted States of America' 'Unted Kingdom']

Countries still missing a continent: ['Czech Republic' 'Macau' 'Unisted States of America' 'Unted Kingdom']
Rows without continent: 17

---

Section 5: Filtering & Selection

Exercise 10: Filter the Data

# a) Clean world_rank and filter
df_merged['world_rank_num'] = pd.to_numeric(
    df_merged['world_rank'].str.replace('=', '', regex=False), errors='coerce'
)

top_10_2016 = df_merged[
    (df_merged['year'] == 2016) &
    (df_merged['world_rank_num'] >= 1) &
    (df_merged['world_rank_num'] <= 10)
]

# b) Display
print(top_10_2016[['world_rank', 'university_name', 'country', 'continent']].to_string(index=False))

# c) Count Europe
n_europe = (top_10_2016['continent'] == 'Europe').sum()
print(f"\nTop-10 universities from Europe: {n_europe}")

world_rank                                           university_name        country     continent
         1                        California Institute of Technology  United States North America
         2                                      University of Oxford United Kingdom        Europe
         3                                       Stanford University  United States North America
         4                                   University of Cambridge United Kingdom        Europe
         5                     Massachusetts Institute of Technology  United States North America
         6                                        Harvard University  United States North America
         7                                      Princeton University  United States North America
         8                                   Imperial College London United Kingdom        Europe
         9 ETH Zurich – Swiss Federal Institute of Technology Zurich    Switzerland        Europe
        10                                     University of Chicago  United States North America

Top-10 universities from Europe: 4

---

Section 6: Grouping & Aggregation

Exercise 11: Aggregate by Continent and Year

# a) Group and aggregate
continent_year_stats = (
    df_merged
    .groupby(['continent', 'year'])
    .agg(
        teaching=('teaching', 'mean'),
        research=('research', 'mean'),
        total_score=('total_score', 'mean'),
        num_students=('num_students', 'sum')
    )
    .reset_index()
)

# b) Filter for 2016 and sort
stats_2016 = (continent_year_stats[continent_year_stats['year'] == 2016]
              .sort_values('total_score', ascending=False))

print(stats_2016.to_string(index=False))

    continent  year  teaching  research  total_score  num_students
North America  2016 39.972832 37.064740    67.154286     4142002.0
      Oceania  2016 27.971053 31.160526    62.766667      869584.0
         Asia  2016 28.209406 22.950990    62.526667     4458513.0
       Europe  2016 30.930149 28.067463    59.469524     7728226.0
       Africa  2016 21.421429 17.228571    56.100000      935984.0
South America  2016 26.046154 16.319231          NaN      813732.0

---

Section 7: Visualization

Exercise 12: Scatter Plot — Teaching vs. Research

df_2016 = df_merged[(df_merged['year'] == 2016)].dropna(subset=['teaching', 'research', 'continent'])

plt.figure(figsize=(10, 7))

sns.scatterplot(data=df_2016, x='teaching', y='research',
                hue='continent', alpha=0.7, s=60)

plt.xlabel('Teaching Score', fontsize=12)
plt.ylabel('Research Score', fontsize=12)
plt.title('Teaching vs. Research Scores by Continent (2016)', fontsize=14, fontweight='bold')
plt.legend(title='Continent', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.grid(alpha=0.3)
plt.tight_layout()
plt.show()

Exercise 13: Box Plot — Score Distribution by Continent

df_box = (df_merged[(df_merged['year'] == 2016)]
          .dropna(subset=['total_score', 'continent']))

plt.figure(figsize=(11, 6))

sns.boxplot(data=df_box, x='continent', y='total_score',
            palette='Set2', order=sorted(df_box['continent'].unique()))

plt.title('University Total Score Distribution by Continent (2016)', fontsize=14, fontweight='bold')
plt.ylabel('Total Score', fontsize=12)
plt.xlabel('Continent', fontsize=12)
plt.xticks(rotation=45, ha='right')
plt.grid(axis='y', alpha=0.3)
plt.tight_layout()
plt.show()

/tmp/ipykernel_6783/837324671.py:6: FutureWarning:



Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

Exercise 14: Bar Chart — Top 10 Universities by Number of Students

top_sorted = top_10_2016.dropna(subset=['num_students']).sort_values('num_students', ascending=True)

# Assign colours by continent
continent_colors = {'Europe': '#4C72B0', 'North America': '#DD8452', 'Asia': '#55A868'}
colors = [continent_colors.get(c, '#999999') for c in top_sorted['continent']]

plt.figure(figsize=(10, 7))

bars = plt.barh(top_sorted['university_name'], top_sorted['num_students'], color=colors)

# Annotate with exact counts
for bar in bars:
    width = bar.get_width()
    plt.text(width + 200, bar.get_y() + bar.get_height() / 2,
             f'{int(width):,}', va='center', fontsize=9)

# Legend for continents
from matplotlib.patches import Patch
legend_elements = [Patch(facecolor=v, label=k) for k, v in continent_colors.items()]
plt.legend(handles=legend_elements, title='Continent', loc='lower right')

plt.xlabel('Number of Students', fontsize=12)
plt.title('Number of Students — Top 10 Universities 2016', fontsize=14, fontweight='bold')
plt.grid(axis='x', alpha=0.3)
plt.tight_layout()
plt.show()