Playground: Chapter 3

Data Manipulation and Cleaning with Pandas

This page practises the ideas of Chapter 3 in four parts. Practise the chapter works through the book’s exercises, with starter code, hints, and solutions. Go further goes beyond the book, with new questions and datasets. Check your understanding asks short conceptual questions whose answers open with a click. Do it yourself gives open tasks with an empty code space and no answers: you decide how to prepare the data, as you will in your own thesis.

In the exercises, replace each ______ with your own code and press Run Code.

Practise the chapter

These are the exercises at the end of Chapter 3, with the same numbers.

Exercise 1: Average age by faculty

Using students, find the average age of students in each faculty, sorted from oldest to youngest.

NoteHint

Group by "faculty", calculate the mean of "age", and set ascending=False inside .sort_values().

TipSolution 
faculty_age = (
    students
    .groupby("faculty")["age"]
    .mean()
    .sort_values(ascending=False)
    .reset_index()
)
print(faculty_age)

Natural Sciences students are the oldest on average (30.7 years) and Humanities students the youngest (29.2). All five faculty averages lie within 1.5 years of each other.

Exercise 2: Sleep in three categories

Add a column sleep_group to semesters: "Short" when sleep_hours < 6, "Recommended" when sleep_hours >= 7, and "Moderate" otherwise. Count the semester records in each category, preserving missing values as NaN.

NoteHint

The comparison operator for “greater than or equal to” is >=. In .value_counts(dropna=False), unrecorded entries will display as NaN.

TipSolution 
conditions = [
    semesters["sleep_hours"].isna(),
    semesters["sleep_hours"] < 6,
    semesters["sleep_hours"] >= 7
]
choices = [np.nan, "Short", "Recommended"]

semesters["sleep_group"] = np.select(conditions, choices, default="Moderate")
print(semesters["sleep_group"].value_counts(dropna=False))

The count reveals: 861 Moderate, 762 Recommended, 682 Short, and 21 missing (NaN) semester records.

Exercise 3: One column per semester

Create a wide summary table of GPA with one row per student and one column per semester (Semesters 1 through 4) using .pivot().

NoteHint

Set index="student_id" and columns="semester".

TipSolution 
gpa_wide = (
    semesters[["student_id", "semester", "gpa"]]
    .pivot(index="student_id", columns="semester", values="gpa")
    .reset_index()
)
print(gpa_wide.head())

Each row represents an individual student, with longitudinal GPA measurements spanning columns 1 to 4. Students who departed early display NaN for later semesters.

Exercise 4: GPA by study mode

Merge semesters with students across student_id and compare the average semester GPA of full-time and part-time students.

NoteHint

Merge on="student_id" and then .groupby("study_mode").

TipSolution 
study_mode_gpa = (
    semesters
    .merge(students[["student_id", "study_mode"]], on="student_id", how="left")
    .groupby("study_mode")["gpa"]
    .mean()
    .reset_index()
)
print(study_mode_gpa)

Full-time students average 3.09 GPA and part-time students average 3.10 GPA—virtually identical performance.

Exercise 5: A reversed item

Explain why stress_4 must be reverse-coded before computing the composite stress score, and what would happen if it were left unreversed.

Item stress_4 (“I feel confident handling problems in my studies”) is worded positively: agreement denotes lower stress, unlike the other five items where agreement denotes higher stress. If averaged directly, it cancels out the other items, pulling scores toward the midpoint and compressing variance. Reversing it (\(6 - x\) on a 1-to-5 scale) aligns all six items in the same direction.

Exercise 6: Clean data in your own field

For each quality of clean data (valid, accurate, complete, consistent, unique), give one example of a data challenge from your field and the required cleaning action.

  • Valid: A blood pressure entry of 1,200 mmHg (set to NaN as an out-of-range clerical error).
  • Accurate: A laboratory assay result “below detection limit” entered as 0 (recode explicitly according to study protocol).
  • Complete: A missed clinical follow-up recorded as 0 (recode to NaN so it is not treated as zero symptoms).
  • Consistent: Body weight recorded in pounds in one clinic and kilograms in another (convert all to kg).
  • Unique: A patient registered under two distinct IDs after changing hospitals (identify via birthdate and merge into a single record).

Go further

These exercises deepen your skills with data validation, joins, and reshaping.

Exercise 7: The missing-code trap

Two skipped questionnaire answers were recorded as 99. Compare the mean and median before and after recoding 99 to NaN.

NoteHint

Call .replace(99, np.nan).

TipSolution 
answers = pd.Series([4, 3, 5, 99, 2, 99, 1])
print("With 99 - Mean:", answers.mean(), "Median:", answers.median())

clean_answers = answers.replace(99, np.nan)
print("With NaN - Mean:", clean_answers.mean(), "Median:", clean_answers.median())

With 99, the mean is 30.4 (impossible on a 5-point scale) and the median is 4.0. With NaN, both mean and median are 3.0.

Exercise 8: Checking two tables against each other

Count each supervisor’s students from semester 1 data, merge with supervisors, and verify whether the counted student numbers match n_students.

NoteHint

Use != (not equal) to filter for discrepancies between n_counted and n_students.

TipSolution 
sem1 = semesters[semesters["semester"] == 1]
check = (
    sem1
    .merge(students[["student_id", "supervisor_id"]], on="student_id", how="left")
    .groupby("supervisor_id")
    .agg(n_counted=("student_id", "count"))
    .reset_index()
    .merge(supervisors, on="supervisor_id", how="left")
)
print(check.head())
discrepancies = check[check["n_counted"] != check["n_students"]]
print("Discrepancies found:", len(discrepancies))

Exactly 0 discrepancies: all 120 supervisors match their official student supervision counts perfectly.

Exercise 9: The questionnaire in long format

Reshape the 22 questionnaire items from wide to long, and compute the mean answer for each item.

TipSolution 
long_q = pd.melt(
    questionnaire,
    id_vars=["student_id"],
    var_name="item",
    value_name="answer"
)
print("Long questionnaire shape:", long_q.shape)
item_means = long_q.groupby("item")["answer"].mean().reset_index()
print(item_means.head(8))

Unpivoting yields 13,200 rows (\(600 \times 22\) items). Notice that stress_4 has a lower mean (2.90) than the other stress items because it is reverse-worded.

Exercise 10: Cleaning a small messy DataFrame

Standardize faculty names and clean decimal commas in sleep numbers:

NoteHint

Replace "," with ".".

TipSolution 
clean = messy.assign(
    faculty = np.where(messy["faculty"].str.lower().str.contains("educ"), "Education", "Health Sciences"),
    sleep   = (messy["sleep"]
               .str.replace(",", ".")
               .str.extract(r"([\d\.]+)", expand=False)
               .astype(float))
)
print(clean)
print("Mean sleep:", clean["sleep"].mean())

Without fixing decimal commas, "6,5" is misinterpreted as 65, corrupting the mean.

Check your understanding

Answer each question in your own words first, then click to see the model answer.

1. What does each of the five clean data qualities (valid, accurate, complete, consistent, unique) protect against?

  • Valid: Out-of-range impossible values (e.g. age = 250).
  • Accuracy: Misinterpreting missing codes as genuine answers.
  • Completeness: Hidden unrecorded missingness.
  • Consistency: Fragmented category spellings treated as separate groups.
  • Uniqueness: Duplicated rows overweighting individual cases.

2. Why should raw data never be edited by hand in Excel?

Manual edits destroy the scientific audit trail. By maintaining an untouched raw file and executing all transformations via a Python script, every cleaning decision is documented, repeatable, and easily adjusted when new data arrives.

3. What is tidy data, and why does it simplify analysis?

In tidy data, each variable is a column, each observation is a row, and each cell is a value. This structure allows vectorized mathematical operations and straightforward grouped aggregations.

4. What is the operational difference between a left join and an anti-join?

A left join preserves all rows of the left table while appending matching columns from the right table. An anti-join filters the left table to retain only cases that have no matching record in the right table (ideal for auditing dropouts or missing forms).

5. Why must 99 not be replaced with NaN globally across an entire dataset?

99 is not always a missing code. In a 0-to-100 scale (such as Elaf’s wellbeing score or percentage grades), 99 is an authentic high value. Missing codes must only be replaced in columns where the study codebook defines them as missing sentinels.

Do it yourself

These tasks have no starter code and no answers. Write your own Python code, as you would for your thesis.

1. Which faculty has the largest proportion of students who moved away from their family (lives_away == "Yes"), and how does that proportion differ between Master’s and PhD students across faculties? Generate a grouped summary table.

2. Calculate each student’s burnout score as the mean of burnout_1..6, merge it with semester 1 records, and compare the mean burnout of students who have versus have not considered dropping out.

3. Inspect students.csv. Identify which variables contain missing values, compute the missingness rate for each column, and report which demographic variables are incomplete.

4. Write a comprehensive data cleaning function clean_survey() in Python that loads wellbeing_raw.xlsx, executes all cleaning steps, and returns clean DataFrames for students, questionnaire items, and longitudinal semester records.

Work on your own computer

NoteDownload the Chapter 3 project

The starter project contains the data and all exercises as executable Python scripts: starter exercises with blanks, solutions, and open tasks.

  • Download chapter03.zip, unzip it, and open the folder in VS Code.
  • Run pip install data2thesis pandas openpyxl in your Python environment.