Statistical Programming: Problem Set 2

Author
Affiliation

Jan Teichert-Kluge

University of Hamburg

Notes

Exercises

This problem set focuses on data handling, manipulation, and visualization. You will work with two real-world datasets, clean and transform them, join them together, and then visualize the results.

General Note

You will very likely find the solution to these exercises online. I, however, strongly encourage you to work on these exercises without doing so. Understanding someone else’s solution is very different from coming up with your own. Use the lecture notes and try to solve the exercises independently.

Datasets

We will use two datasets:

  1. Times Higher Education (THE) World University Rankings (timesData.csv): Ranking data and performance scores for universities worldwide across multiple years.
  2. Continents (continents.csv): Maps country names to continents.

Section 1: Loading Data

Exercise 1: Load the Datasets

Load both datasets from the URLs below using pd.read_csv().

  • timesData.csv: "https://raw.githubusercontent.com/JanTeichertKluge/data-statprog/refs/heads/main/data/timesData.csv"
  • continents.csv: "https://raw.githubusercontent.com/JanTeichertKluge/data-statprog/refs/heads/main/data/continents.csv"
  1. Load both datasets into DataFrames called df_times and df_continents. Print the shape of each DataFrame.

Solution:

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)

Section 2: Data Exploration

Exercise 2: Inspect the Data

Before cleaning, inspect the structure of the data to identify which columns need fixing.

  1. Display the first 5 rows of df_times.

  2. Show the data types and missing value counts using .info().

  3. Show descriptive statistics using .describe().

  4. How many unique years are in the dataset? How many unique countries?

  • Hint: Use .nunique() on a column to count unique values.

Solution:

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())}")

Section 3: Data Cleaning & Transformation

Exercise 3: Replace Missing Value Placeholders

In df_times, missing values are sometimes stored as the string '-' instead of NaN. This causes pandas to treat those columns as strings (object dtype) instead of numbers.

  1. Replace all occurrences of the string '-' in the entire df_times DataFrame with np.nan.

  2. After replacing, verify that the string '-' no longer appears in the total_score column.

  • Hint: Use df.replace().

Solution:

# Replace the '-' string 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()}")

Exercise 4: Convert String Columns to Numeric

After replacing '-' with NaN, numeric columns like total_score and income are still stored as strings. Convert them to numeric (float) dtype.

  1. Convert total_score and income to numeric using pd.to_numeric().

  2. Print the dtypes of both columns after conversion to verify.

Solution:

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())

Exercise 5: Clean Comma-Formatted Numbers

The num_students column contains numbers as comma-formatted strings (e.g., "20,152"). Remove the commas and convert to a numeric type.

  1. Use .str.replace(',', '') to remove commas from num_students, then convert to numeric.

  2. Print the 5 universities with the most students.

  • Hint: You can chain: pd.to_numeric(df_times['num_students'].str.replace(',', '')).

Solution:

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)

Exercise 6: Clean Percentage Strings

The international_students column contains percentage values as strings (e.g., "25%"). Convert these to numeric decimals (e.g., 0.25).

  1. Remove the % character using .str.replace('%', ''), convert to numeric, and divide by 100.

  2. What is the mean percentage of international students across all universities in 2016?

Solution:

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%}")

Exercise 7: Split a Column into Two

The female_male_ratio column contains strings like "45 : 55". Split this into two separate numeric columns: female_ratio and male_ratio.

  1. Use .str.split(' : ', expand=True) to split the column into a two-column DataFrame. Assign the two columns to df_times['female_ratio'] and df_times['male_ratio'] and convert both to numeric.

  2. Check: For all rows where female_ratio and male_ratio are not NaN, they should sum to approximately 100. Verify this.

Solution:

# 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
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())

Section 4: Joining Datasets

Exercise 8: Merge with the Continents Dataset

We want to add the continent for each university. The df_continents DataFrame has columns Entity (country name) and Countries Continents (continent name).

  1. First inspect df_continents with .head() to understand its structure.

  2. Rename df_continents columns to 'country' and 'continent' to make the merge cleaner.

  3. Merge df_times with the cleaned df_continents using a left join on the 'country' column. Save the result as df_merged.

  • Hint: pd.merge(df_times, df_continents, on='country', how='left')

Solution:

# a) Inspect
print(df_continents.head())

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

# c) 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())

Exercise 9: Handle Mismatched Country Names

A left join can produce missing continents if the country names differ between the two datasets (e.g., "United States of America" vs. "United States").

  1. Find all country names in df_merged that have a missing continent, and print them.

  2. Fix the mismatched names in df_times using .replace() and re-run the merge. Use the mapping below:

'United States of America' -> 'United States'
'Russian Federation'       -> 'Russia'
'Republic of Ireland'      -> 'Ireland'
  1. How many universities still have a missing continent after the fix? Print any remaining unmatched countries.

Solution:

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

# b) Fix country names 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()}")

Section 5: Filtering & Selection

Exercise 10: Filter the Data

Now that the data is clean and joined, we can filter it for specific analyses.

  1. Create a DataFrame top_10_2016 containing only universities from the year 2016 with a world_rank of 1 through 10. Note that world_rank can contain strings like "=10", so clean this first.

    • Hint: Use pd.to_numeric(df_merged['world_rank'].str.replace('=', ''), errors='coerce') to create a numeric rank column world_rank_num.

  2. Print the result showing world_rank, university_name, country, and continent.

  3. How many of the top 10 are from Europe?

Solution:

# 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) Europe count
n_europe = (top_10_2016['continent'] == 'Europe').sum()
print(f"\nTop-10 universities from Europe: {n_europe}")

Section 6: Grouping & Aggregation

Exercise 11: Aggregate by Continent and Year

Use grouping to calculate summary statistics for different categories.

  1. Group df_merged by continent and year, and calculate:

    • the mean of teaching, research, and total_score
    • the total (sum) of num_students

    Save the result as continent_year_stats. Reset the index so continent and year become regular columns.

  2. Filter continent_year_stats for the year 2016 and sort by mean total_score descending. Which continent has the highest average total score?

Solution:

# 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))

Section 7: Visualization

Exercise 12: Scatter Plot - Teaching vs. Research

Use a scatter plot to visualize the relationship between two numerical variables.

  1. Create a scatter plot for the year 2016 showing teaching (x-axis) vs. research (y-axis), with one point per university.

  2. Color the points by continent. Add a legend, axis labels, and a title.

    • Hint: Loop over continents and plot each group separately with a different color, or use seaborn.scatterplot with hue='continent'.

Solution:

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

Box plots are useful for comparing the distribution of a variable across groups.

  1. Create a box plot of total_score grouped by continent for the year 2016. Drop NaN values in total_score before plotting.

  2. Rotate the x-axis labels by 45 degrees so they don’t overlap.

  3. Which continent shows the highest median total score? Which has the most spread (widest box)?

Solution:

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()

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

  1. Using top_10_2016 from Exercise 10, create a horizontal bar chart (plt.barh) showing the number of students per university. Sort so the university with the most students appears at the top.

  2. Annotate each bar with the exact student count.

  3. Add a color to the bars that distinguishes universities by continent.

  • Hint: Sort by num_students ascending for a horizontal bar chart (so the largest bar is at the top).

Solution:

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)

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()