E. Culminating Data Management Investigation · Culminating Data Management Investigation

Gathering, Cleaning and Organizing the Data

E1.3About 70 minutes

Learning goals

I can collect data by following my plan, and record the response rate and any problems.
I can build a codebook and enter data into a spreadsheet or database with one row per case.
I can clean data: find blanks, impossible values and contradictions, and decide what to do with them openly.
I can create new (derived) variables from my data.
I can organize numerical data into a frequency table with sensible intervals and draw a first graph.

Words to know

response rate
The number of usable responses received divided by the number of people asked, written as a percentage.

codebook
A table that describes every variable in a data set: its name, the question it came from, its type, its units or allowed values, and how missing values are recorded.

data cleaning
Checking a data set for errors, blanks, impossible values and contradictions, and fixing or excluding them by stated rules before analysis.

derived variable
A new variable calculated from others, such as "Job: yes or no" from hours worked, or "Meets guideline" from hours of sleep.

case
One individual (person, game, day, region) in a data set; usually one row of the spreadsheet.

class interval
A range of values used to group numerical data in a frequency table, such as 7.0 to less than 7.5 hours.

From paper slips to a data set

The surveys are back, the downloads are done, or the game has been played 300 times. Now you need to turn a pile of raw responses into a clean, organized data set that you can trust and analyse.

The curriculum asks you to gather data that will answer your question and organize it in a useful way, for example by using a survey, the Internet, a simulation, a database, or by establishing intervals. In this lesson Amira enters, checks and organizes her data, and you learn the routine that every analyst follows.

Step 1: Record what happened

Keep a collection log: dates, how many people were asked, how many responded, reminders sent, anything unusual (a snow day closed the school; the website changed). These details belong in the method section of your report and help readers judge your data.

Response rate = (number of responses ÷ number asked) × 100%.

Step 2: Build a codebook and enter the data

Use a spreadsheet (or database) with one row per case and one column per variable. Write a codebook first so data entry is consistent: for example, convert "7 h 45 min" to 7.75 hours every time, and leave blanks blank (never type 0 for a missing value: 0 means "zero", not "unknown").

Keep the raw file unchanged and do all cleaning in a copy.

Variable	From	Type	Units / values	Missing
ID	assigned at entry	label	1, 2, 3, ...	never missing
Grade12	Q1	categorical	Yes / No	blank
Work	Q2, Q3	numerical, discrete	hours per week (0 if Q2 = No)	blank
Sleep	Q4	numerical, continuous	hours, to nearest 0.25	blank
Evening1, Evening2	Q5	categorical	Homework, Sports, Work, Family, Screens, Other	blank
LateNights	Q6	numerical, discrete	0 to 5 nights	blank
Job (derived)	Work	categorical	Yes if Work > 0, else No	from Work
Meets (derived)	Sleep	categorical	Yes if Sleep ≥ 8, else No	from Sleep

Amira's codebook. The last two rows are derived variables calculated from earlier columns.

Step 3: Clean the data

Scan every column. Sort it, or use a filter, to find:

blanks in key variables;
impossible values (sleep of 25 hours; a percentage over 100);
contradictions between answers ("no job" but 12 hours of work);
entry errors (75 typed for 7.5).

For each problem, either fix it (only if the correct value is certain, such as a typing error you can check against the paper form) or exclude that case from the analyses that need it. Write down a rule and apply it the same way to everyone. Never delete a case because its value is inconvenient for your hypothesis: unusual but possible values (an outlier) stay in, and you discuss them in the analysis.

Amira's data collection and cleaning, start to finish. Every excluded case is listed with a reason in her report.

Worked example

The model investigation, Stage 7: collecting and cleaning

Of the 48 surveys handed out, 43 were returned. Amira finds three problem responses: one with Q4 blank; one with Q2 = No but Q3 = 12 hours; one with sleep written as "15" hours and no minutes, from a student who works 20 hours a week. What should she do, and what is her final sample?

Response rate: 43 ÷ 48 = 0.8958..., about 89.6%. This is high, so non-response bias is less of a worry, although the 5 non-responders might still differ (for example, they could be students who work the most).
Blank sleep: the response variable is missing, so this case cannot be used in any sleep analysis. Exclude.
Contradiction (Q2 No, Q3 12): she cannot tell which answer is right, and the survey is anonymous, so she cannot ask. Exclude, rather than guess.
Sleep of 15 hours on a school night: physically possible only in unusual cases, and this student works 20 hours a week. It may be a slip (perhaps 5 hours, or 7.5), but she cannot know. Her written rule ("school-night sleep above 13 hours is treated as an entry error") was set before entry, so she excludes it and reports it.
Final data set: 43 − 3 = 40 valid responses: 22 students with jobs and 18 without.
She also converts every sleep time to decimal hours (7 h 15 min = 7.25) and checks a random 5 rows against the paper forms for typing errors.

Response rate 89.6%; 3 cases excluded by stated rules; 40 valid responses (22 with jobs, 18 without). All decisions are recorded for the report.

ID	Work (h/week)	Sleep (h)	ID	Work (h/week)	Sleep (h)
1	0	8.50	21	0	8.75
2	6	8.75	22	25	6.25
3	9	8.00	23	0	7.50
4	0	8.00	24	0	7.75
5	14	7.25	25	10	7.00
6	16	7.50	26	22	7.50
7	0	8.25	27	0	9.00
8	18	7.75	28	15	7.00
9	0	8.25	29	0	8.75
10	5	8.75	30	24	7.25
11	15	7.75	31	10	6.75
12	0	9.25	32	0	9.75
13	0	9.50	33	0	8.50
14	6	7.50	34	4	8.00
15	20	6.25	35	0	9.00
16	12	8.00	36	12	7.00
17	8	7.25	37	0	8.75
18	12	7.50	38	0	8.25
19	0	8.00	39	20	6.50
20	8	8.00	40	0	8.50

Example data set (made up for teaching): the 40 valid responses in Amira's survey of Grade 12 students at the fictional Pinecrest Secondary School. Work = usual hours of paid work per week (0 = no job). Sleep = usual school-night sleep in hours.

Step 4: Derive variables and group the data

Spreadsheet formulas create derived variables quickly. If Work is in column B and Sleep in column C:

Job: =IF(B2>0,"Yes","No")
Meets the 8-hour guideline: =IF(C2>=8,"Yes","No")

To establish intervals for a frequency table, choose about 5 to 10 equal-width intervals that cover all values without overlapping, with boundaries that are easy to read. Amira's sleep data run from 6.25 to 9.75 hours and were recorded to the nearest quarter hour, so half-hour intervals starting at 6.0 work well: each interval holds exactly two possible recorded values. A function such as =COUNTIFS(C:C,">=7",C:C,"<7.5") counts one interval.

Worked example

The model investigation, Stage 8: a frequency table

Organize the sleep data into a frequency table with intervals of width 0.5 h, for all 40 students and for each group.

Intervals: 6.0 to less than 6.5, 6.5 to less than 7.0, ..., 9.5 to less than 10.0 (8 intervals).
Count each group in each interval (sorting the Sleep column makes this quick).
Check the totals: job group 22, no-job group 18, all 40.

See the table below. Even before any calculation, the job group's values pile up between 7.0 and 8.0 hours, while the no-job group's values pile up between 8.0 and 9.0 hours.

Sleep (h)	Job	No job	All
6.0 to < 6.5	2	0	2
6.5 to < 7.0	2	0	2
7.0 to < 7.5	6	0	6
7.5 to < 8.0	6	2	8
8.0 to < 8.5	4	5	9
8.5 to < 9.0	2	6	8
9.0 to < 9.5	0	3	3
9.5 to < 10.0	0	2	2
Total	22	18	40

Frequency table of the example data set (made up for teaching).

Histogram of all 40 sleep values, with each bar split into students with jobs (lower part) and without jobs (upper part). The two groups overlap but are centred in different places.

Secondary data and simulations need organizing too

Downloaded data: remove extra header rows and footnotes, check units and symbols (for example, values marked as unreliable), put the data in long (tidy) form, and keep the citation with the file. Simulations or games: record every trial in a row (trial number, outcome, payout) rather than just a running tally, so you can recount, check and graph later.

Just for fun

Data cleaning is like tidying your room: it takes longer than you expect, you find things you didn't know were there, and somebody always asks why there is a 15 in the middle of everything.

Hands-on activity

Your investigation, step 5: collect, log and organize

You need: your approved survey (or data source or game), a spreadsheet program, and your project log.

Collect your data exactly as planned. Keep a dated collection log (how many asked, how many responded, reminders, problems).
Write a codebook for every variable before you enter anything.
Enter the data, one row per case. Save the raw file; make a working copy.
Clean the copy: sort each column, look for blanks, impossible values and contradictions. Write your rules and list every change or exclusion.
Create your derived variables with formulas.
Make a frequency table with sensible intervals for your main numerical variable, and draw a first histogram or bar graph.
If you are doing a probability project: play or simulate your game at least 100 times (dice, cards or a spreadsheet with RANDBETWEEN), recording each trial in its own row.

What to look for: a tidy file in which a stranger could understand every column; a log that explains the response rate and every exclusion; intervals that do not overlap and cover every value.

Your turn

A survey was given to 120 people and 87 returned it. Find the response rate.

Why should a missing answer be left blank rather than entered as 0?

Convert 6 h 45 min and 8 h 15 min to decimal hours.

Write a spreadsheet formula for a derived variable that is "Long" when work hours (column B) are 15 or more and "Short" otherwise.

Using the example data table, how many students sleep 9 hours or more?

Why didn't Amira simply change the contradictory response to match one of its answers?

More practice: warm-up

How many valid responses are in the example data set?

How many students in the example have jobs?

What should one row of a data set represent?

Should you edit the raw data file?

What is a derived variable? Give Amira's examples.

More practice: on your own

Using the example data table, list the IDs of the students with jobs who sleep at least 8 hours.

A student's data on daily temperatures run from −24.6 °C to 3.1 °C. Suggest intervals for a frequency table.

In a game simulation, a student recorded only "wins: 27, losses: 73". Explain why recording each trial in its own row would be better.

One respondent reports 0.5 hours of sleep on a school night. Should it be excluded? Explain.

Using the frequency table, what percentage of the no-job group sleeps less than 8 hours?

Challenge

Explain why Amira's half-hour intervals are a good fit for data recorded to the nearest quarter hour, and what goes wrong with intervals of width 0.4 h starting at 6.0.

The 5 students who did not return surveys might all be students who work long hours. If each had a job and slept 6.5 hours, how would the job group's mean change? (Its current total is 163.5 hours for 22 students.)

Two classmates enter the same 40 surveys independently and compare files. Explain how this "double entry" improves data quality, and what to do when the files disagree.

Check your understanding

Quiz

A survey went to 60 people and 51 returned it. The response rate is:

Which is the best way to record a respondent who left the sleep question blank?

Which pair of intervals is acceptable for a frequency table?

In the example data set, how many valid responses came from students without jobs?

A codebook is:

An unusual but possible value (such as 9.75 hours of sleep) should be:

For parents and teachers

Big idea (E1.3): raw responses must be logged, entered consistently, cleaned by stated rules and organized before analysis. Honest records of every decision make the results trustworthy.

How to help: Offer to read the paper forms aloud while your teen types (or to double-check a random sample of rows). Ask, "What will you do with blanks? With impossible answers? Did you write your rules down?" Encourage keeping the raw file untouched.

About the example data: the 40 responses shown are made up for teaching; they are used in every following lesson, so your teen can check each calculation.

Common difficulties:

Entering missing values as 0.
Deleting real but unusual values to make results look cleaner.
Overlapping or uneven intervals.
Not recording the response rate or the reasons for exclusions.