Collecting and cleaning data: from questionnaire to clean dataset
AdvancedWrite clear, non-leading questions; clean raw data (duplicates, errors, missing values) before analysing; collect the minimum personal data and anonymise when presenting.
- Write clear questions with complete options and no leading wording.
- Clean before analysing: remove duplicates, check errors, handle missing values.
- Sorting and MAX/MIN are the quickest ways to spot outliers.
文體小知識：數據素養課講如何負責任地處理資料 — 由收集、清理到保護私隱，都是做專題研習的實用基本功。
§Text Passage
1For project work, questionnaires and interviews are the usual ways to collect data. Questions must be clear with complete options: 'How many hours a week do you spend on your phone?' works best with range choices (0-1, 2-3, 4 or more) — easy to answer and tidy to analyse — and leading wording must be avoided.
2Collected raw data is usually messy and 'dirty': duplicate submissions (one person handing in twice), obvious errors (an age of 200), and blanks from skipped questions. Analysing it directly leads to wrong conclusions, so data cleaning comes first.
3Three basic cleaning steps: first, remove duplicates so each person counts once; second, check for error values — numbers like age 200 or height 5 m are clearly implausible and need verifying or removing; third, handle missing values by re-asking, dropping the record, or stating it clearly in the report.
4Data about real people means privacy. Before collecting personal data, explain the purpose and collect only the minimum needed; when presenting, replace names with codes (such as A01, A02) to protect classmates — in line with the spirit of Hong Kong's Personal Data (Privacy) Ordinance.
1Summary 重點摘要
Write clear, non-leading questions; clean raw data (duplicates, errors, missing values) before analysing; collect the minimum personal data and anonymise when presenting.
2Vocabulary cards Vocabulary
A set of prepared questions for collecting data from many people; wording and options must be clear.
Freshly collected, unprocessed data, usually containing duplicates, errors and gaps.
Fixing data before analysis: removing duplicates, checking errors, handling missing values.
The same person recorded more than once, double-counting their views; keep only one copy.
An implausible number, e.g. age 200 or height 5 m; verify or remove it.
A blank left by a respondent; re-ask, drop it or state it — never invent a number.
Replacing names with codes so data cannot be traced back to an individual.
Data identifying an individual, e.g. name, phone number, address, photo.
Explaining the purpose to respondents first so they voluntarily agree to take part.
3Step by step Steps
- Design the questionnaireA questionnaire is the most common data-collection tool for project work, and its design decides how easy the statistics will be. For a survey of the class's sleep, a closed question like 'How many hours do you sleep? under 6 / 6-7 / 8+' offers fixed options that are easy to answer and tidy to count, while open questions produce unclassifiable variety. Leading questions such as 'You agree late nights are harmful, right?' push respondents towards an answer and distort the data.
- Collect and recordCollected data must be recorded systematically, usually in a spreadsheet: one row per student and one column per question, so that cleaning later is straightforward. Keep units and formats uniform across the whole table — height always in centimetres, never mixing 1.6 m with 160 cm — and use one consistent date style. Enter carefully and check as you go, because an error at the source contaminates every statistic calculated afterwards.
- Spot the duplicatesDuplicates are the most common defect in raw data, usually because one person submitted twice. If code A07 appears twice with identical answers, treat it as a double submission and keep one; if the two differ, verify which is genuine, for example by asking the student. Leaving duplicates in place counts that person's opinion twice and biases every class average and count that follows.
- Check error valuesError values are numbers that are plainly implausible, such as an age of 200 or a height of 5, usually from careless answers or typing slips. Checking is straightforward: run =MAX(B2:B41) on the column and a result of 200 reveals the problem, then run MIN for the smallest values. Verify what can be verified and correct it; remove clearly fabricated entries that cannot, and state in the report how many were removed and why.
- Handle missing valuesMissing values are questions a respondent skipped, such as 3 of 40 questionnaires leaving out 'daily exercise time'. Three options exist: re-ask if possible; exclude those 3 from this question; or state clearly in the report that 'the valid sample for this question is 37'. Never invent a number to fill the gap — that is fabrication — and never silently treat blanks as zero, because a skipped answer is not a zero. Always report how missing values were handled so readers can judge the reliability.
- Sort to scan quicklySorting is one of the quickest checks available: sort the age column from small to large and any odd values at the two ends — 0 or 999, say — stand out immediately, pinpointing the problem records. Sorting combined with the MAX and MIN functions forms the standard toolkit for spotting outliers: scan once after sorting, then confirm with the functions. There is no need to inspect all 40 rows cell by cell; these tools clean-scan the dataset in minutes before any statistics begin.
- Present anonymouslyWhen data describes real people, presentation must protect privacy. Replace names with codes such as A01 and A02, show whole-class averages rather than identifiable details, and treat grades, addresses and health information with extra care — better not shown than shown. This protects the respondents and follows the spirit of Hong Kong's Personal Data (Privacy) Ordinance: collect the minimum and restrict the use. Before publishing, imagine being one of the respondents and check whether anything could identify them.
4Diagrams Diagrams
5Worked example Example
Example: among 40 questionnaires there are 2 duplicates, 1 with age 150, and 3 that skipped question 3. What is the valid sample size for question 3?
- Remove the 2 duplicate records first so that each person's opinion counts only once: 40 − 2 = 38 valid questionnaires.
- Age 150 is an obviously implausible error value; verification fails, so it is removed as the rules require: 38 − 1 = 37.
- The 3 questionnaires that skipped question 3 do not count as valid for that question: 37 − 3 = 34.
- The report must state 'a valid sample of 34 for question 3' and explain how many were removed as duplicates, errors and missing values — never a blanket 40.
- Answer: The valid sample for question 3 is 34 (40 − 2 duplicates − 1 error − 3 missing). Every removal during data cleaning needs a reason, and the report must state them honestly so that readers can judge how far the statistics can be trusted.
6Common mistakes Mistakes
7Quick check Quick check
Q1Two identical records appear in the raw data. What should you do?
Q2An age of '200' appears in the column. What type of problem is this and what should be done?
Q35 of 50 questionnaires skip one question. What is the valid sample for it, and how should the report state it?
Q4Why replace names with codes when presenting questionnaire results?
Q5Which question is better designed? A. 'Don't you agree late nights harm health?' B. 'When did you sleep last night? □ before 10 □ 10-11 □ 11-12 □ after 12'
8Recap Recap
- Write clear questions with complete options and no leading wording.
- Clean before analysing: remove duplicates, check errors, handle missing values.
- Sorting and MAX/MIN are the quickest ways to spot outliers.
- Missing values: re-ask, exclude or disclose — never invent.
- Collect the minimum personal data and anonymise when presenting.