04-01: Survey Data Organizer & Codebook

Home  |  All Projects  |  Notes  |  Exercises  |  Quiz Hub

Turn a deliberately messy survey export into a clean, documented, analysis-ready data set — the step that every later project silently assumes has been done.

Chapters applied: 01-01 · 01-02

Difficulty: ⭐ Beginner

---

The Data

data/survey_raw.csv — 61 rows of student survey responses, containing every problem you will meet in real life:

Column	Intended level	Planted problems
student_id	Nominal (key)	One duplicated ID
gender	Nominal	F, f, " F", Female, M, m, male, "M ", X, blank
major	Nominal	Trailing/leading spaces, inconsistent case
age	Ratio	Clean
study_hours	Ratio	Some cells stored as text ("14.5 hrs", " 9.2")
midterm	Ratio	Blanks, and one impossible value of 105
satisfaction	Ordinal (1–5)	Stored as a bare number — easy to average by mistake

---

What You Produce

1. A cleaned data set with consistent categories and genuinely numeric columns
2. A codebook documenting every variable, its level of measurement, and its allowed values
3. A quality report: row count, duplicates, missing values per column, range checks, distinct categories
4. Derived columns: a gender dummy, an age_group band, and major dummies

---

Excel Route

Step 1 — Load and make it a Table

Data ▸ From Text/CSV ▸ survey_raw.csv ▸ Load, then select the block and press Ctrl+T ("My table has headers"). You now get filter buttons and structured references.

Step 2 — Quality report

Build this block on a new sheet before changing anything:

=COUNTA(survey[student_id])                                   ' rows            -> 61
=COUNTA(survey[student_id])-COUNTA(UNIQUE(survey[student_id])) ' duplicate IDs  -> 1
=SUMPRODUCT(--ISTEXT(survey[study_hours]))                    ' numbers as text -> >0
=COUNTBLANK(survey[midterm])                                  ' missing midterms
=MIN(survey[midterm]) & " to " & MAX(survey[midterm])          ' range check -> exposes 105
=UNIQUE(survey[gender])                                       ' shows F / f / " F" / Female …
=UNIQUE(survey[major])                                        ' shows the space/case drift
=COUNTIF(survey[age],">100")+COUNTIF(survey[age],"<15")        ' impossible ages -> 0

Tip

Find the duplicate: =IF(COUNTIF($A$2:$A$62,A2)>1,"DUPLICATE","") filled down, then filter for DUPLICATE.

Step 3 — Clean

Add helper columns to the right of the table:

' gender  →  exactly F / M / X
=IFS(LEFT(UPPER(TRIM([@gender])),1)="F","F",
     LEFT(UPPER(TRIM([@gender])),1)="M","M",
     TRUE,"X")

' major  →  trimmed and proper-cased
=PROPER(TRIM([@major]))

' study_hours  →  a real number, stripping any text
=IFERROR(VALUE(SUBSTITUTE(TRIM([@study_hours])," hrs","")), "")

' midterm  →  blank out the impossible value rather than guessing
=IF(OR([@midterm]="", [@midterm]>100, [@midterm]<0), "", [@midterm])

Then Data ▸ Data Validation ▸ List on the cleaned gender column with source F,M,X so the drift cannot come back.

Step 4 — Derive

=IF([@gender_clean]="F", 1, 0)                                  ' dummy
=IFS([@age]<25,"Under 25", [@age]<40,"25-39", TRUE,"40+")        ' age band
=IF([@major_clean]="Business",1,0)                              ' major dummies:
=IF([@major_clean]="Cs",1,0)                                    '   3 columns for
=IF([@major_clean]="Nursing",1,0)                               '   4 levels

Step 5 — Summarise

Insert ▸ PivotTable: rows = major_clean, values = Count of student_id, plus Average of study_hours. Right-click a value ▸ Show Values As ▸ % of Grand Total for the relative frequencies.

Finally, run Data ▸ Data Analysis ▸ Descriptive Statistics on age, study_hours, and midterm — but not on satisfaction, which is ordinal.

Step 6 — Codebook sheet

variable	type	level	allowed values	notes
student_id	text	nominal	S001–S060	unique key; 1 duplicate removed
gender	text	nominal	F, M, X	normalized from 10 raw spellings
major	text	nominal	Biology, Business, Cs, Nursing	trimmed and proper-cased
age	integer	ratio	18–46	—
study_hours	decimal	ratio	0.0–40.0	text suffixes stripped
midterm	integer	ratio	0–100	blank = absent; 105 voided as impossible
satisfaction	integer	ordinal	1–5	do not average — report the median

---

R Route

Rscript r/analysis.R

Produces the quality report, the cleaned data frame, the codebook, and a written summary in the console. See r/analysis.R.

Python Route

python python/analysis.py

Same outputs via pandas. See python/analysis.py.

---

Checkpoints

• The duplicate student_id is found and dealt with (documented, not silently dropped)
• gender has exactly 3 distinct values; major exactly 4
• study_hours is numeric in every row (ISTEXT count is 0)
• The midterm of 105 is voided, with the reason recorded
• satisfaction is declared ordinal and is never averaged
• The codebook lists every variable with its level of measurement

---

Extend It

• Add a completeness column: the percentage of non-missing fields per row
• Write the cleaned data to a new CSV / a second worksheet, leaving the raw file untouched
• Compare the mean midterm with and without the voided 105, and say how much it moved
• Reshape the quiz-style columns to long format for a grouped boxplot (02-02)