By the end of today you can

• explain the five qualities of clean data, and why decisions must be scripted;
• structure tabular data according to tidy data principles;
• chain analytical operations fluently in Pandas;
• filter, select, sort, create variables, and aggregate by group;
• reshape tables between wide and long representations;
• merge relational tables across keys and identify participant attrition;
• clean an uncurated survey export from start to finish;
• compute composite scale scores with reverse-coded items;
• write an auditable data preparation section for a thesis.

Raw data is never immediately ready

• test submissions sit alongside real participants;
• the same web form is submitted multiple times;
• one respondent enters “female”, another “F”, another “Female”;
• skipped survey answers are logged as 99 or -9;
• a clerical slip turns an age of 25 into 250.

None of this is rare, and none of it is harmless.

The five qualities of clean data

The missing-code trap

import pandas as pd
import numpy as np

answers = pd.Series([4, 3, 5, 99, 2])
print(answers.mean())
# 22.6

clean = answers.replace(99, np.nan)
print(clean.mean())
# 3.5

With 99 included, the mean is impossible on a 1-to-5 Likert scale.

In large datasets, minor distortions are subtle and easily missed without automated checks.

Three rules for scientific data cleaning

flowchart LR
    A["Raw primary file<br>never edited"] --> B["Python script executes<br>every transformation"] --> C["Audit shapes & values<br>after every step"]

Document every exclusion and recode in your Python script.

Many data-cleaning bugs produce no error messages or exceptions.

Tidy data principles

Each variable forms a column, each observation forms a row, and each value sits in its own cell.

When data is tidy, vectorized Pandas operations operate seamlessly.

An untidy table

A single construct (wellbeing) is spread over two columns, and a second variable (semester) is embedded in column headers.

The same data in tidy format

Each row represents one observation: one student in one semester.

Method chaining in Pandas

Operations can be chained inside parentheses (...):

# Fluent, readable analytical pipeline:
result = (
    students
    .query("sleep_hours < 7")
    .sort_values("gpa", ascending=False)
    .assign(sleep_minutes=lambda df: df["sleep_hours"] * 60)
)

Chaining eliminates unnecessary intermediate variables and creates an auditable workflow.

Filtering observations

# Boolean indexing:
pilot[pilot["sleep"] < 7]

# Multiple conditions combined with & (AND):
pilot[(pilot["sleep"] < 7) & (pilot["faculty"] == "Education")]

# Matching against a list:
pilot[pilot["faculty"].isin(["Education", "Humanities"])]

# Query syntax:
pilot.query("sleep < 7 and faculty == 'Education'")

Every filtering step represents an explicit sampling decision.

Selecting and sorting columns

# Keep specific columns:
pilot[["student", "sleep"]]

# Drop unneeded columns:
pilot.drop(columns=["faculty"])

# Sort by a variable in descending order:
pilot.sort_values("stress", ascending=False)

Creating and deriving variables

# Assign new calculated columns:
pilot = pilot.assign(
    sleep_minutes = pilot["sleep"] * 60,
    short_sleep   = pilot["sleep"] < 7
)

# Multi-condition categorization with np.select:
conditions = [pilot["stress"] >= 4.0, pilot["stress"] >= 2.5]
choices    = ["High", "Medium"]
pilot["stress_level"] = np.select(conditions, choices, default="Low")

Continuous measurements should generally remain continuous in primary models.

Grouped summaries: .groupby().agg()

# Calculate mean and sample size by group:
faculty_summary = (
    pilot
    .groupby("faculty")["sleep"]
    .agg(mean_sleep="mean", count="count")
    .reset_index()
)

.groupby() partitions data by factor levels, and .agg() applies summary functions to each subset.

Reshaping: Wide to Long with pd.melt()

long = pd.melt(
    wide,
    id_vars=["student"],
    value_vars=["wellbeing_1", "wellbeing_2"],
    var_name="semester",
    value_name="wellbeing"
)

Unpivots multiple columns into rows for panel modeling and visualization.

Reshaping: Long to Wide with .pivot()

wide_again = long.pivot(
    index="student",
    columns="semester",
    values="wellbeing"
).reset_index()

Ideal for structuring summary tables in thesis chapters.

Joining relational tables with pd.merge()

# Left join brings supervisor rank into student records:
students.merge(
    supervisors[["supervisor_id", "rank"]],
    on="supervisor_id",
    how="left"
)

Preserves all participant records while appending matching secondary attributes.

Detecting study attrition (Anti-joins)

# Find students who did not complete Semester 3:
sem3_students = semesters[semesters["semester"] == 3]["student_id"]
left_cohort = students[~students["student_id"].isin(sem3_students)]

print(len(left_cohort))  # 37 students
print(left_cohort["considering_dropout"].value_counts())

Attrition is rarely random: tracking departures prevents survivorship bias.

Cleaning a survey export: Step by Step

1. Audit: Inspect table dimensions and data types (raw.shape).
2. De-duplicate: Exclude test accounts ("TEST") and drop duplicates (.drop_duplicates()).
3. Rename: Map cryptic headers to snake_case (.rename(columns=...)).
4. Normalize: Strip whitespace and resolve category spellings (np.where, np.select).
5. Recode: Replace sentinel codes (99, -9) with np.nan.
6. Clean strings: Standardize decimal commas and extract numeric digits.
7. Screen: Recode impossible values to np.nan.
8. Composite scores: Reverse-code inverted items and compute scale averages.
9. Reshape: Unpivot repeated longitudinal waves into long panel format.
10. Validate: Verify row counts and clean distributions against study protocol.

Reverse-coding Likert scale items

# Item stress_4 is worded inversely:
# (1 = Strongly Disagree to 5 = Strongly Agree)
reversed_stress_4 = 6 - questionnaire["stress_4"]

# Compute composite scale score as row-wise mean:
scores["stress_score"] = pd.concat([
    questionnaire[["stress_1", "stress_2", "stress_3", "stress_5", "stress_6"]],
    reversed_stress_4.rename("stress_4")
], axis=1).mean(axis=1)

On a 1-to-5 scale, \(6 - x\) maps \(1 \to 5\) and \(5 \to 1\).

Writing up data preparation in your thesis

“The institutional survey export contained 615 submissions. Removing 3 test entries and 12 duplicate submissions left 600 students. Categorical variables were standardized, and sentinel missing-data codes (99, −9) were converted to missing values. Out-of-range clerical errors (including an age entry of 250 years) were set to missing. Reverse-worded item stress_4 was recoded before computing composite scale scores. Multi-wave records were unpivoted into longitudinal panel format (\(N = 2,326\) student-semesters), excluding unobserved waves resulting from participant attrition.”

Practical lab: Chapter 3 Playground

Practice these concepts interactively in your browser with Pyodide:

• Filter and summarize grouped cohorts
• Re-pivot multi-wave datasets
• Clean uncurated text and numerical formats
• Download chapter03.zip to run directly in VS Code

Summary

Data cleaning is not a preliminary chore; it is an analytical foundation. Using Pandas method chaining, tidy reshaping, and auditable pipelines, every empirical table in your thesis remains reproducible and defensible.

Next: Chapter 4

In Chapter 4, we explore publication-quality data visualization:

• The grammar of graphics in Python with Seaborn and Matplotlib;
• Visualizing distributions: histograms, KDE density plots, and box plots;
• Visualizing associations: scatter plots with regression trend lines;
• Visualizing trajectories: longitudinal lines across study waves;
• Customizing themes, colorblind-friendly palettes, and export settings.