3 Data Manipulation and Cleaning with Pandas

Raw data is never immediately suitable for scientific analysis. Online survey platforms log incomplete test runs alongside genuine responses, participants occasionally click submit twice, demographic text fields arrive with divergent spellings (“female”, “Female”, “F”), unanswered items are encoded as 99, and clerical typos turn an age of 25 into 250. None of these occurrences are rare, and none are harmless: a single stray 99 embedded in a 1-to-5 Likert scale can severely distort an empirical mean, and an undetected duplicate observation artificially inflates sample size while biasing standard errors. Preparing raw measurements for modeling—a discipline known as data cleaning and wrangling—routinely occupies more time than model estimation itself, and every analytical decision made during cleaning directly shapes the findings.

Data cleaning is an intrinsic phase of statistical analysis, not an inconvenient preliminary chore, and it demands rigorous methodological care. This chapter begins by defining what constitutes clean, trustworthy data and establishing core principles for reproducible data preparation. It then presents the foundational manipulation toolkit in Python: filtering observations, selecting columns, sorting, creating computed variables, aggregating by group, reshaping, and merging relational tables using Pandas. Finally, we apply these techniques step by step to sanitize the raw export of the wellbeing survey introduced in ?@sec-ch02 (wellbeing_raw.xlsx), handling test records, duplicate submissions, inconsistent textual categories, decimal commas, missing codes, impossible outliers, and reverse-coded questionnaire scales.

By the end of this chapter you will be able to

Define the five core qualities of clean data and maintain an immutable, auditable cleaning pipeline.
Explain the principles of tidy data (variables as columns, observations as rows) and structure data for analysis.
Chain Pandas transformations fluently using method chaining and lambda expressions.
Filter, subset, sort, compute new variables, and aggregate summaries across groups using .groupby() and .agg().
Reshape tabular data between wide and long representations using pd.melt() and .pivot().
Merge relational tables across primary and foreign keys using pd.merge(), and diagnose attrition with set filtering.
Clean an uncurated survey export: eliminate test and duplicate rows, standardize inconsistent categories, convert text to numbers, handle missing codes, and filter out-of-range values.
Compute multi-item composite scale scores, including reverse-coded Likert items, and write a transparent data-cleaning section for your thesis methodology.

Teaching & Practice: Interactive Lecture Slides · Browser Playground Exercises

3.1 What clean data means

Data can be considered clean when it faithfully represents the empirical phenomena under investigation. This standard encompasses five distinct qualities:

Validity: Every recorded value conforms to the permissible domain of its variable (e.g., chronological age cannot be 250 years, and daily sleep cannot exceed 24 hours).
Accuracy: Values capture what respondents actually intended, ensuring system codes (such as 99 for skipped items) are not treated as genuine quantitative answers.
Completeness: Data captures all eligible participants, and omitted observations are explicitly marked as missing (NaN) rather than masquerading as numbers.
Consistency: Categorical responses follow a single standardized nomenclature (e.g., "F", "female", and "Female " are normalized into "Female").
Uniqueness: Each participant appears exactly once per unit of analysis, with zero test records and zero duplicate submissions.

Violating these criteria distorts empirical conclusions. The impact of unhandled missing-data codes is immediately obvious. Suppose five students answer an item on a 1-to-5 scale, and one student leaves the question blank, which the web software logs as 99:

answers = pd.Series([4, 3, 5, 99, 2])
print("Uncleaned mean:", answers.mean())

clean_answers = answers.replace(99, np.nan)
print("Cleaned mean:", clean_answers.mean())

Uncleaned mean: 22.6
Cleaned mean: 3.5

Leaving 99 in the calculation produces a mean of 22.6, a mathematical impossibility on a 5-point scale. When 99 is properly converted to NaN, Pandas excludes the missing entry by default, yielding the true average of 3.5. In large datasets, such distortions are often smaller and harder to detect visually, making automated cleaning pipelines essential. Duplications exert a similar distorting effect: duplicating a participant gives them double weight in regressions and artificially narrows confidence intervals.

Three fundamental rules govern scientific data cleaning:

Raw files remain immutable: Never edit primary data files directly in Excel. Raw files must remain intact, exactly as received from instruments or survey platforms. All transformations are executed via code.
Every decision is documented: Your Python script serves as an auditable record of all exclusions, recodings, imputations, and composite score derivations.
Inspect results at every step: Validate table shapes, unique identifiers, and value distributions after each operation, because many data-cleaning bugs produce no syntax errors.

3.2 Tidy data principles in Python

Tabular data should be organized to make analysis straightforward. The standard architecture in modern data science is tidy data (Wickham 2014): 1. Each variable forms a column. 2. Each observation forms a row. 3. Each type of observational unit forms a table.

Consider Table 3.1, which records two semesters of wellbeing scores for three students side by side, as spreadsheet exports often do.

Table 3.1: Wellbeing in wide format: the variable ‘wellbeing’ is spread over multiple columns

student	wellbeing_s1	wellbeing_s2
S1	58	63
S2	71	70
S3	64	69

While compact for human reading, this layout spreads a single construct (wellbeing) across multiple columns, while embedding a second variable (semester) inside column headers. In contrast, Table 3.2 presents the identical measurements in tidy (long) format.

Table 3.2: The same data in tidy (long) format: one column per variable, one row per observation

student	semester	wellbeing
S1	1	58
S1	2	63
S2	1	71
S2	2	70
S3	1	64
S3	2	69

In tidy format, each measurement occupies its own row, and computing grouped summaries—such as mean wellbeing per semester—becomes straightforward. The students table in data2thesis is tidy at the participant level (\(N = 600\)), whereas semesters is tidy at the semester observation level (\(N = 2,326\)). Uncurated survey exports, by contrast, frequently pack longitudinal observations side by side. Much of data preparation consists of reshaping data into tidy tables.

3.3 The Pandas manipulation toolkit

In Python, tabular manipulation centers on Pandas. To keep transformations readable and eliminate intermediate variables, we use method chaining: calling sequential methods on a DataFrame enclosed within parentheses (...).

Let’s illustrate on a small pilot cohort:

pilot = pd.DataFrame({
    "student": ["S1", "S2", "S3", "S4", "S5"],
    "faculty": ["Education", "Humanities", "Education", "Health Sciences", "Humanities"],
    "sleep":   [6.5, 7.5, 5.5, 8.0, 6.0],
    "stress":  [3.2, 2.1, 4.5, 1.8, 3.9]
})
pilot

	student	faculty	sleep	stress
0	S1	Education	6.5	3.2
1	S2	Humanities	7.5	2.1
2	S3	Education	5.5	4.5
3	S4	Health Sciences	8.0	1.8
4	S5	Humanities	6.0	3.9

3.3.1 Filtering observations

Most analyses focus on a defined subset of cases: participants in a specific faculty, students observed in semester 1, or those who completed a treatment protocol. Observations are filtered using boolean indexing or the .query() method:

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

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

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

	student	faculty	sleep	stress
0	S1	Education	6.5	3.2
1	S2	Humanities	7.5	2.1
2	S3	Education	5.5	4.5
4	S5	Humanities	6.0	3.9

Alternatively, .query() offers a concise syntax using expression strings:

pilot.query("sleep < 7 and faculty == 'Education'")

	student	faculty	sleep	stress
0	S1	Education	6.5	3.2
2	S3	Education	5.5	4.5

Every filtering step represents an explicit sampling decision that must be reported in your thesis methodology: an analysis restricted to full-time students answers a fundamentally different question than one encompassing the entire cohort.

3.3.2 Selecting and dropping columns

Real-world datasets often contain dozens of administrative or technical columns irrelevant to a given hypothesis. In Pandas, pass a list of column names inside square brackets to select specific variables, or call .drop() to remove unneeded columns:

# Keep only student ID and sleep hours
pilot[["student", "sleep"]]

# Drop the faculty column
pilot.drop(columns=["faculty"])

	student	sleep	stress
0	S1	6.5	3.2
1	S2	7.5	2.1
2	S3	5.5	4.5
3	S4	8.0	1.8
4	S5	6.0	3.9

3.3.3 Sorting cases

Sorting helps inspect distributions, identify extreme values, or prepare multi-wave data for lagged calculations. Use .sort_values():

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

	student	faculty	sleep	stress
2	S3	Education	5.5	4.5
4	S5	Humanities	6.0	3.9
0	S1	Education	6.5	3.2
1	S2	Humanities	7.5	2.1
3	S4	Health Sciences	8.0	1.8

3.3.4 Creating and transforming variables

New variables are derived from existing ones: converting measurement units, calculating composite indices, or binning continuous metrics into clinical categories. New columns can be assigned directly via brackets or dynamically chained using .assign():

pilot = pilot.assign(
    sleep_minutes = pilot["sleep"] * 60,
    short_sleep   = pilot["sleep"] < 7
)
pilot[["student", "sleep", "sleep_minutes", "short_sleep"]]

	student	sleep	sleep_minutes	short_sleep
0	S1	6.5	390.0	True
1	S2	7.5	450.0	False
2	S3	5.5	330.0	True
3	S4	8.0	480.0	False
4	S5	6.0	360.0	True

To categorize a continuous metric into ordinal groups, np.select() evaluates conditions in order, applying the corresponding label from a list of choices:

conditions = [
    pilot["stress"] >= 4.0,
    pilot["stress"] >= 2.5
]
choices = ["High", "Medium"]

pilot["stress_level"] = np.select(conditions, choices, default="Low")
pilot[["student", "stress", "stress_level"]]

	student	stress	stress_level
0	S1	3.2	Medium
1	S2	2.1	Low
2	S3	4.5	High
3	S4	1.8	Low
4	S5	3.9	Medium

[!TIP] Preserve Continuous Measurements: Discretizing continuous metrics into arbitrary bins (“High”, “Medium”, “Low”) discards statistical power and variance (reducing a nuanced scale to an artificial threshold). While useful for descriptive summaries, your primary inferential models should retain the raw continuous scores whenever possible.

3.3.5 Summarizing by group

Scientific questions frequently compare groups: Do education students sleep longer than science students? Summaries reduce many individual measurements into descriptive aggregates. In Pandas, grouped aggregation is achieved with .groupby() paired with .agg():

# Overall cohort summary
print("Mean sleep across all pilot cases:", pilot["sleep"].mean())

# Grouped summary by faculty
faculty_summary = (
    pilot
    .groupby("faculty")["sleep"]
    .agg(mean_sleep="mean", count="count")
    .reset_index()
)
faculty_summary

Mean sleep across all pilot cases: 6.7

	faculty	mean_sleep	count
0	Education	6.00	2
1	Health Sciences	8.00	1
2	Humanities	6.75	2

For quick frequency counts of discrete categories, use .value_counts():

pilot["faculty"].value_counts().reset_index()

	faculty	count
0	Education	2
1	Humanities	2
2	Health Sciences	1

3.3.6 Combining operations into an analytical pipeline

Because Pandas methods return modified DataFrames, we can construct fluent pipelines that answer substantive research questions. For example: How did average student wellbeing evolve across semesters 1 to 4, broken down by workshop participation?

To answer this, we join the longitudinal records (semesters) with baseline workshop assignments (students), group by semester and workshop status, calculate mean wellbeing and sample size, and sort the output:

wellbeing_trajectory = (
    semesters
    .merge(students[["student_id", "workshop"]], on="student_id", how="left")
    .groupby(["semester", "workshop"])["wellbeing"]
    .agg(mean_wellbeing="mean", n_students="count")
    .reset_index()
    .sort_values(["semester", "workshop"])
)
wellbeing_trajectory

	semester	workshop	mean_wellbeing	n_students
0	1	Invited	60.646667	300
1	1	Not invited	60.266667	300
2	2	Invited	65.470000	300
3	2	Not invited	60.220000	300
4	3	Invited	63.222615	283
5	3	Not invited	59.228571	280
6	4	Invited	60.932862	283
7	4	Not invited	58.425000	280

Notice the pattern: in Semester 1 (prior to the intervention), wellbeing scores between invited and uninvited students were virtually identical (~63.5 vs ~63.7). From Semester 2 onward, invited students demonstrate higher wellbeing. In ?@sec-ch07, we use inferential hypothesis tests to evaluate whether this observed difference exceeds chance expectation.

3.4 Reshaping data between wide and long

Data is often collected in wide format but analyzed in long format. In wide format, repeated measurements appear side by side across columns. In long format, each measurement occupies its own row.

Consider the wide table from Table 3.1:

wide = pd.DataFrame({
    "student":     ["S1", "S2", "S3"],
    "wellbeing_1": [58, 71, 64],
    "wellbeing_2": [63, 70, 69]
})
wide

	student	wellbeing_1	wellbeing_2
0	S1	58	63
1	S2	71	70
2	S3	64	69

To convert wide columns into tidy rows, use pd.melt():

long = pd.melt(
    wide,
    id_vars=["student"],
    value_vars=["wellbeing_1", "wellbeing_2"],
    var_name="semester",
    value_name="wellbeing"
)
# Clean the semester column to extract the integer number
long["semester"] = long["semester"].str.replace("wellbeing_", "").astype(int)
long = long.sort_values(["student", "semester"]).reset_index(drop=True)
long

	student	semester	wellbeing
0	S1	1	58
1	S1	2	63
2	S2	1	71
3	S2	2	70
4	S3	1	64
5	S3	2	69

The arguments specify: - id_vars: Columns to preserve as identifiers (student). - value_vars: Columns to unpivot into rows. - var_name: Name for the new column storing original column headers (semester). - value_name: Name for the new column storing the data values (wellbeing).

Conversely, when formatting summary tables for a thesis chapter, wide presentation is often clearer for human readers. Use .pivot() to spread long data across columns:

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

semester	student	1	2
0	S1	58	63
1	S2	71	70
2	S3	64	69

We can apply this to our longitudinal trajectory summary, spreading the workshop groups into adjacent columns:

(wellbeing_trajectory
 .assign(mean_wellbeing=lambda df: df["mean_wellbeing"].round(1))
 .pivot(index="semester", columns="workshop", values="mean_wellbeing")
 .reset_index()
)

workshop	semester	Invited	Not invited
0	1	60.6	60.3
1	2	65.5	60.2
2	3	63.2	59.2
3	4	60.9	58.4

3.5 Joining relational tables

In empirical research, data is frequently split across multiple tables sharing a common key: participant demographics in one file, longitudinal outcomes in another, and supervisor attributes in a third. In Pandas, relational tables are combined using pd.merge().

Consider two simple tables:

people = pd.DataFrame({
    "student": ["S1", "S2", "S3"],
    "faculty": ["Education", "Humanities", "Education"]
})

scores = pd.DataFrame({
    "student":   ["S1", "S1", "S2", "S4"],
    "wellbeing": [58, 63, 71, 66]
})

A left join (how="left") preserves all rows from the primary table, matching attributes from the secondary table where the keys align:

scores.merge(people, on="student", how="left")

	student	wellbeing	faculty
0	S1	58	Education
1	S1	63	Education
2	S2	71	Humanities
3	S4	66	NaN

Student S4 has a score but no corresponding demographic record in people, so their faculty is assigned NaN.

To find cases that have no matching counterpart in another table (an anti-join), use boolean negation ~ with .isin():

# Find students in 'people' who have no records in 'scores'
missing_scores = people[~people["student"].isin(scores["student"])]
missing_scores

	student	faculty
2	S3	Education

Student S3 enrolled in the study but contributed zero scores.

In Elaf’s research cohort, merging connects supervisory rank (from supervisors) to student dropout intentions (from students):

supervisors = dt.supervisors

by_rank = (
    students
    .merge(supervisors[["supervisor_id", "rank"]], on="supervisor_id", how="left")
    .assign(dropout_flag = lambda df: (df["considering_dropout"] == "Yes").astype(int))
    .groupby("rank")
    .agg(
        dropout_rate=("dropout_flag", "mean"),
        n_students=("student_id", "count")
    )
    .reset_index()
)
by_rank

	rank	dropout_rate	n_students
0	Assistant Professor	0.175299	251
1	Lecturer	0.136752	234
2	Professor	0.121739	115

Similarly, set filtering identifies students who dropped out of the longitudinal study by Semester 3:

sem3_students = semesters[semesters["semester"] == 3]["student_id"]
attrition_students = students[~students["student_id"].isin(sem3_students)]

print("Students who left before Semester 3:", len(attrition_students))
print("\nDropout thoughts among those who left:")
print(attrition_students["considering_dropout"].value_counts())

Students who left before Semester 3: 37

Dropout thoughts among those who left:
considering_dropout
Yes    22
No     15
Name: count, dtype: int64

Of the 37 students who departed prior to Semester 3, the vast majority had indicated on their baseline questionnaire that they were considering discontinuing their studies. Participant attrition is rarely random; tracking who dropped out is essential context for interpreting later semesters (?@sec-ch06).

3.6 Cleaning an uncurated survey export

We are now equipped to sanitize the raw wellbeing export (wellbeing_raw.xlsx), resolving the real-world data issues introduced in ?@sec-ch02.

3.6.1 Inspecting raw survey responses

Data cleaning begins with diagnostic inspection:

raw = pd.read_excel(dt.example_file("wellbeing_raw.xlsx"))
print("Raw table shape (rows, columns):", raw.shape)

Raw table shape (rows, columns): (615, 64)

The raw export contains 615 rows, even though Elaf’s study enrolled exactly 600 students. Examining key demographic columns reveals multiple data anomalies:

print(raw["Q3_Gender"].value_counts(dropna=False).head(8))
print("\nSample test records:")
print(raw[raw["Q1_Student ID"].str.upper().str.startswith("TEST", na=False)][["Q1_Student ID", "Q2_Age", "Q3_Gender"]].head(3))

Q3_Gender
Female     203
Male       152
male        78
female      56
M           46
Female      30
F           28
Male        21
Name: count, dtype: int64

Sample test records:
  Q1_Student ID  Q2_Age Q3_Gender
0          TEST    99.0    Female
1          test     NaN       NaN
2         TEST2    30.0      Male

The gender field contains divergent spellings ("F", "female", "Female", "Male", and trailing spaces), test entries ("TEST", "TEST2") are mixed in with student records, column names contain special characters and spaces, and numeric metrics contain text.

3.6.2 Removing test records and duplicate submissions

Test submissions are not empirical observations, and duplicate survey submissions inflate sample size. Both must be excised first:

# Filter out test accounts
no_test = raw[~raw["Q1_Student ID"].str.upper().str.startswith("TEST", na=False)]

# Drop technical response ID and eliminate duplicate submissions
responses = no_test.drop(columns=["Response ID"]).drop_duplicates()
print("Cleaned participant count:", len(responses))

Cleaned participant count: 600

Filtering removes 3 test records and 12 duplicate submissions, leaving exactly 600 unique students.

3.6.3 Renaming variables to standardized identifiers

Verbose survey headers like Q10_How worried are you about money? (1-5) are error-prone in scripts. We map them to concise, standardized snake_case identifiers:

# Construct questionnaire item names: stress_1..6, burnout_1..6, support_1..6, satisfaction_1..4
item_names = (
    [f"stress_{i}" for i in range(1, 7)] +
    [f"burnout_{i}" for i in range(1, 7)] +
    [f"support_{i}" for i in range(1, 7)] +
    [f"satisfaction_{i}" for i in range(1, 5)]
)

rename_dict = {
    "Q1_Student ID": "student_id",
    "Q2_Age": "age",
    "Q3_Gender": "gender",
    "Q4_Faculty": "faculty",
    "Q5_Programme": "programme",
    "Q6_Study mode": "study_mode",
    "Q7_Paid work": "employment",
    "Q8_Children": "has_children",
    "Q9_Moved away from family": "lives_away",
    "Q10_How worried are you about money? (1-5)": "financial_worry",
    "Workshop group": "workshop",
    "Workshop sessions attended": "workshop_sessions",
    "Y1_Considered leaving?": "considering_dropout"
}

# Add item column mappings Q11_1 through Q11_22
for i, name in enumerate(item_names, 1):
    rename_dict[f"Q11_{i}"] = name

responses = responses.rename(columns=rename_dict)

The original survey wording is not discarded; it is documented in the project codebook (?@sec-ch02).

3.6.4 Standardizing categorical variables

We normalize categories by stripping whitespace, converting to lowercase, and mapping variations to canonical factor levels:

# Standardize gender
responses["gender"] = np.where(
    responses["gender"].astype(str).str.strip().str.lower().str.startswith("f"),
    "Female",
    "Male"
)

# Standardize faculty using regex classification
fac_lower = responses["faculty"].astype(str).str.lower()
responses["faculty"] = np.select(
    [
        fac_lower.str.contains("educ"),
        fac_lower.str.contains("health"),
        fac_lower.str.contains("humanit"),
        fac_lower.str.contains("social"),
        fac_lower.str.contains("natural|^sciences?$")
    ],
    [
        "Education",
        "Health Sciences",
        "Humanities",
        "Social Sciences",
        "Natural Sciences"
    ],
    default="Other"
)

# Standardize degree programme and study mode
responses["programme"] = np.where(
    responses["programme"].astype(str).str.lower().str.contains("ph"),
    "PhD",
    "Master's"
)
responses["study_mode"] = np.where(
    responses["study_mode"].astype(str).str.lower().str.contains("part"),
    "Part-time",
    "Full-time"
)

# Standardize binary Yes/No indicators
for col in ["has_children", "lives_away", "considering_dropout"]:
    responses[col] = np.where(
        responses[col].astype(str).str.strip().str.lower().str.startswith("y"),
        "Yes",
        "No"
    )

print("Standardized gender counts:")
print(responses["gender"].value_counts())
print("\nStandardized faculty counts:")
print(responses["faculty"].value_counts())

Standardized gender counts:
gender
Female    312
Male      288
Name: count, dtype: int64

Standardized faculty counts:
faculty
Health Sciences     154
Education           148
Social Sciences     116
Humanities           95
Natural Sciences     87
Name: count, dtype: int64

3.6.5 Recoding missing value codes and text numbers

Online forms frequently code skipped questions as 99 or -9. We replace these sentinel codes with np.nan before converting columns to numeric dtypes:

# Replace missing codes (99, -9) in survey items
for col in item_names + ["financial_worry"]:
    responses[col] = responses[col].replace([99, -9, "99", "-9"], np.nan)
    responses[col] = pd.to_numeric(responses[col], errors="coerce").astype("Int64")

responses["workshop_sessions"] = pd.to_numeric(responses["workshop_sessions"], errors="coerce").astype("Int64")

[!WARNING] Avoid Global Search-and-Replace: Never replace 99 across an entire dataset indiscriminately. On a scale scored from 0 to 100 (such as Elaf’s wellbeing index), 99 represents an authentic high score. Target missing-code replacements exclusively to columns where 99 was defined as an omission sentinel.

Text columns also contain embedded characters (e.g. respondents typing "7 hrs" or using decimal commas "6,5"). In Python, we clean decimal commas and extract digits:

sample_text = pd.Series(["7 hrs", "6.5", "6,5"])
cleaned_numbers = sample_text.str.replace(",", ".").str.extract(r"([\d\.]+)", expand=False).astype(float)
print(cleaned_numbers.tolist())

[7.0, 6.5, 6.5]

3.6.6 Eliminating impossible values

Clerical errors can introduce biologically or chronologically impossible values (such as an age of 250). Because the true value cannot be reliably inferred, the scientifically honest solution is to set impossible values to np.nan:

# Parse numeric age and set impossible values (> 100) to NaN
responses["age"] = pd.to_numeric(
    responses["age"].astype(str).str.extract(r"([\d\.]+)", expand=False),
    errors="coerce"
)
responses.loc[responses["age"] > 100, "age"] = np.nan

print("Participants with age > 100:", (responses["age"] > 100).sum())
print("Missing age count:", responses["age"].isna().sum())

Participants with age > 100: 0
Missing age count: 1

This single clerical outlier accounts for the missing age observation noted in ?@sec-ch01. Plausible extreme values (such as a graduate student sleeping 4 hours per night) represent genuine variance and must be retained.

3.6.7 Computing composite questionnaire scale scores

In psychometric research, psychological constructs are measured using multi-item scales whose items are averaged. Before averaging, all items must be aligned in a uniform direction.

Item stress_4 (“I feel confident handling problems in my studies”) is reverse-worded: higher agreement signifies lower stress. If averaged directly, it would cancel out the other five items. On a 1-to-5 Likert scale, reverse-coding is achieved by subtracting from 6 (\(6 - x\) maps \(1 \to 5\), \(2 \to 4\), \(3 \to 3\), \(4 \to 2\), and \(5 \to 1\)):

questionnaire_clean = responses[["student_id"] + item_names].copy()

# Create scale scores
scores = pd.DataFrame({"student_id": questionnaire_clean["student_id"]})

# Reverse-code stress_4
reversed_stress_4 = 6 - questionnaire_clean["stress_4"]

# Compute row-wise means across scale items (skipping missing items)
scores["stress_score"] = pd.concat([
    questionnaire_clean[["stress_1", "stress_2", "stress_3", "stress_5", "stress_6"]],
    reversed_stress_4.rename("stress_4")
], axis=1).mean(axis=1)

scores["burnout_score"] = questionnaire_clean[[f"burnout_{i}" for i in range(1, 7)]].mean(axis=1)
scores["support_score"] = questionnaire_clean[[f"support_{i}" for i in range(1, 7)]].mean(axis=1)
scores["satisfaction_score"] = questionnaire_clean[[f"satisfaction_{i}" for i in range(1, 5)]].mean(axis=1)

scores.head()

	student_id	stress_score	burnout_score	support_score	satisfaction_score
3	S0191	2.833333	1.5	3.833333	3.333333
4	S0015	4.5	3.833333	1.666667	3.0
5	S0474	3.5	4.5	1.4	2.75
6	S0418	3.166667	3.0	1.666667	3.75
7	S0049	3.0	3.5	3.166667	3.333333

Individual item responses must always be preserved in the archive (questionnaire_clean) alongside composite scale scores (scores), enabling scale reliability and factor structure verification (?@sec-ch09).

3.6.8 Reshaping repeated semester measurements

The survey export stores 28 longitudinal columns side by side: GPA_S1, Sleep_S1, …, Wellbeing_S4. To make the data tidy, we reshape these columns from wide to long using pd.wide_to_long():

import re

sem_cols = [c for c in responses.columns if re.search(r"_S[1-4]$", c)]
sem_wide = responses[["student_id"] + sem_cols]

# Reshape wide columns into tidy rows
sem_long = pd.wide_to_long(
    sem_wide,
    stubnames=["GPA", "Sleep", "Study", "Exercise", "Caffeine", "Meetings", "Wellbeing"],
    i="student_id",
    j="semester",
    sep="_S"
).reset_index()

sem_long = sem_long.rename(columns={
    "GPA": "gpa",
    "Sleep": "sleep_hours",
    "Study": "study_hours",
    "Exercise": "exercise_days",
    "Caffeine": "caffeine_mg",
    "Meetings": "supervisor_meetings",
    "Wellbeing": "wellbeing"
})

# Standardize numeric dtypes and decimal commas
sem_long["sleep_hours"] = (
    sem_long["sleep_hours"]
    .astype(str)
    .str.replace(",", ".", regex=False)
    .str.extract(r"([\d\.]+)", expand=False)
    .astype(float)
)

for col in ["gpa", "study_hours", "exercise_days", "caffeine_mg", "supervisor_meetings", "wellbeing"]:
    sem_long[col] = pd.to_numeric(
        sem_long[col].astype(str).str.extract(r"([\d\.]+)", expand=False),
        errors="coerce"
    )

# Filter out impossible measurement values
sem_long.loc[sem_long["sleep_hours"] > 24, "sleep_hours"] = np.nan
sem_long.loc[sem_long["study_hours"] > 168, "study_hours"] = np.nan
sem_long.loc[sem_long["gpa"] > 4.0, "gpa"] = np.nan

# Drop unobserved rows where all semester measurements are missing (attrition)
measure_cols = ["gpa", "sleep_hours", "study_hours", "exercise_days", "caffeine_mg", "supervisor_meetings", "wellbeing"]
semesters_clean = sem_long.dropna(subset=measure_cols, how="all").copy()
semesters_clean = semesters_clean.sort_values(["student_id", "semester"]).reset_index(drop=True)

print("Cleaned longitudinal semester observations:", len(semesters_clean))

Cleaned longitudinal semester observations: 2326

3.6.9 Validating cleaned tables against canonical datasets

To ensure total accuracy, we compare our cleaned tables against the canonical datasets bundled in data2thesis:

# Verify questionnaire items match canonical data exactly
canonical_q = dt.questionnaire.sort_values("student_id").reset_index(drop=True)
q_test = questionnaire_clean.sort_values("student_id").reset_index(drop=True)

diffs = sum(
    not (q_test[c].fillna(-999).astype(int) == canonical_q[c].fillna(-999).astype(int)).all()
    for c in item_names
)
print("Questionnaire item discrepancies:", diffs)

# Verify semesters records match canonical data exactly
canonical_sem = dt.semesters.sort_values(["student_id", "semester"]).reset_index(drop=True)
max_sem_diff = max(
    (semesters_clean[c] - canonical_sem[c]).abs().max()
    for c in measure_cols
)
print("Maximum absolute difference across semester measurements:", max_sem_diff)

Questionnaire item discrepancies: 0
Maximum absolute difference across semester measurements: 0.0

Both tests confirm an exact numerical match: zero discrepancies.

Writing Up Data Preparation in Your Methodology

When drafting your thesis methods section, summarize cleaning procedures transparently:

“The raw institutional survey export contained 615 submissions. Preliminary auditing removed 3 administrative test records and 12 duplicate submissions, yielding an analytical sample of 600 unique graduate students. Categorical variables were standardized, and sentinel missing-value codes (99, −9) were converted to missing values. Out-of-range clerical errors (including an age entry of 250 years) were set to missing. The reverse-worded item stress_4 was recoded prior to computing composite scale scores as the mean of completed items. Multi-wave records were unpivoted into longitudinal panel format (\(N = 2,326\) student-semesters), excluding unobserved waves resulting from participant attrition.”

In your field: public health and environmental monitoring

The built-in airquality dataset illustrates missingness in environmental surveillance:

# Load airquality via data2thesis or build sample dataframe
air_sample = pd.DataFrame({
    "Ozone": [41.0, 36.0, 12.0, 18.0, np.nan, 28.0, 23.0, 19.0, 8.0, np.nan],
    "Month": [5, 5, 5, 5, 5, 5, 5, 5, 5, 5],
    "Temp": [67, 72, 74, 62, 56, 66, 65, 59, 61, 69]
})

air_summary = (
    air_sample
    .groupby("Month")
    .agg(
        mean_ozone=("Ozone", "mean"),
        missing_days=("Ozone", lambda s: s.isna().sum())
    )
    .reset_index()
)
air_summary

	Month	mean_ozone	missing_days
0	5	23.125	2

Checking missing data mechanisms ensures environmental policy is not biased by missing hot summer days.

3.7 Chapter review

3.7.1 Summary

Clean data satisfies five properties: validity, accuracy, completeness, consistency, and uniqueness.
Tidy data principles require that every variable forms a column, every observation forms a row, and every observational unit forms a distinct table.
Fluent data manipulation in Pandas relies on method chaining: sequentially connecting .query(), [], .assign(), .sort_values(), and .groupby().agg().
Reshaping is performed with pd.melt() (wide to long) and .pivot() (long to wide).
Relational tables are merged across common keys via pd.merge(), while missing counterparts (attrition) are identified using set exclusion ~df["key"].isin(...).
Psychometric scale derivation requires identifying reverse-worded items (\(6 - x\) on a 1–5 scale) before aggregating item averages across rows.

3.7.2 Key terms

Data cleaning, tidy data, method chaining, vectorization, boolean indexing, .query(), .assign(), .groupby(), .agg(), wide format, long format, pd.melt(), .pivot(), left join, anti-join, missing codes, reverse-coding, composite scale score.

3.8 Exercises

Complete these exercises in your browser using the Chapter 3 Playground or download the starter project for VS Code.

Using students, calculate the average chronological age for each faculty, sorted from oldest to youngest (handling missing values with default skipna=True).
Add a categorical column sleep_category to semesters that assigns "Short" when sleep_hours < 6.0, "Recommended" when sleep_hours >= 7.0, and "Moderate" otherwise. Count records per category.
Using semesters, create a wide summary table displaying mean GPA with one row per student and one column per semester (Semesters 1 to 4) using .pivot().
Merge semesters with students and compare average semester GPA between full-time and part-time students.
Explain why item stress_4 must be reverse-coded before computing stress_score, and describe what would happen to empirical correlations if reverse-coding were omitted.
For each of the five qualities of clean data, describe a specific data-quality challenge from your own academic discipline and the corresponding Python cleaning step required.

3.9 Further reading

Python for Data Analysis (3rd ed.) by Wes McKinney (McKinney 2022): chapters on “Data Cleaning and Preparation” and “Data Wrangling: Join, Combine, and Reshape”.
“Tidy Data” (Wickham 2014): the seminal paper formalizing tabular data organization.
Official Pandas guide to Reshaping and Pivot Tables.

References

McKinney, Wes. 2022. Python for Data Analysis: Data Wrangling with Pandas, NumPy, and Jupyter. 3rd ed. O’Reilly Media. https://wesmckinney.com/book/.

Wickham, Hadley. 2014. “Tidy Data.” Journal of Statistical Software 59 (10): 1–23. https://doi.org/10.18637/jss.v059.i10.