Did you know? Two school surveys can contain a column called ‘reading time’ and still be measuring completely different things. One might ask about yesterday’s reading in minutes; the other might ask about last week’s reading in hours. Combining those figures without reading their definitions can create a very tidy spreadsheet and a very wrong conclusion.
A data dictionary is a small reference that defines each field in a dataset: what it means, what its values and units represent, when they were measured, which values mean missing or not applicable, and how each row should be interpreted. Before combining classroom survey files, students should compare the dictionaries, confirm a legitimate common key and preserve differences that cannot be resolved.
The core educational aim is to help students ask a better question before pressing Merge: Do these two numbers mean the same thing, and does each row refer to the same kind of observation?
A column name is not an explanation
A spreadsheet heading such as score, hours, participation or reading_time looks informative, but it may hide a unit, reference period or coding rule. A numeric 1 could mean one hour, ‘yes’, ‘first choice’ or the lowest point on a rating scale. Even identical values are not necessarily comparable.
The United States Geological Survey’s guidance on data dictionaries describes documenting names, types, definitions and allowed values. The UK Office for National Statistics’ published data standards emphasise consistent representation, definitions and units when data are exchanged. A school project needs a simpler version of the same discipline.
A fully worked example using invented pupil IDs
Imagine two fictional surveys undertaken with appropriate permission and using non-identifying example codes. These are invented records, not real pupil data or outcomes. File A contains a Monday reading diary. File B contains a weekly reading estimate. Both cover the same three example IDs, but they do not ask the same question.
- File A definition: student_key = fictional respondent code; reading_time = minutes personally reported on Monday; one row per respondent. Rows: P01 = 15 min, P02 = 20 min, P03 = 10 min.
- File B definition: student_key = the same fictional respondent code; reading_time = estimated hours during the preceding seven days; one row per respondent. Rows: P01 = 2 h/week, P02 = 3 h/week, P03 = 1 h/week.
- Common row meaning: P01 in File A and P01 in File B refer to the same example respondent, not two separate pupils.
- Important qualification: a Monday diary value and a seven-day estimate reflect different observation periods and potentially different recall quality.
The first task is to rename or label the fields by their meanings: monday_reading_min and reported_week_reading_h. The files should not be combined into a single generic reading_time column.
Check the arithmetic — and the comparison’s limits
The mean Monday reading time is (15 + 20 + 10) ÷ 3 = 15 minutes for that Monday. The mean self-reported weekly time is (2 + 3 + 1) ÷ 3 = 2 hours per respondent for the week. They are not two alternative estimates of the same period.
You may express the weekly values in minutes: 2 × 60 = 120, 3 × 60 = 180 and 1 × 60 = 60 minutes per week. Their mean is (120 + 180 + 60) ÷ 3 = 120 minutes per respondent per week. This unit conversion is correct, but it does not turn a weekly total into the amount read on Monday.
If you deliberately divide weekly totals by seven, you obtain weekly daily averages of about 17.14, 25.71 and 8.57 minutes per day. That is a mathematical summary based on seven calendar days, not evidence that these respondents read exactly those amounts on any particular day. Comparing such an average with Monday’s diary values would require careful explanation of the period, measurement method and limitations.
Check the row count before declaring a class total
Each file has three rows, and each has one unique record per matching example ID. A one-to-one merge on a properly authorised common key therefore produces three matched respondent rows with two separate reading columns. It does not produce six independent pupils. Appending the files would create six observations from two different measures and could mislead anyone who later counts rows as people.
Build a small data dictionary before merging
For a school task, a compact dictionary is enough. Each column can carry a short entry that another pupil could understand without consulting the file’s creator:
- Field name: a stable identifier such as monday_reading_min.
- Plain-language definition: what question the field answers, including the group and reference period.
- Unit and data type: minutes, hours, percentage, category label, text identifier, date or another precise type.
- Allowed values: a range or codebook, such as 1 = never through 5 = very often, with the response direction spelled out.
- Missing-value rule: distinguish no response, not asked, not applicable and a genuine value of zero.
- Row grain: one row per student, per session, per question, or per day; this is essential for interpreting duplicate keys.
- Source and permission: who created the dataset, when, what consent applies, and which fields should not be shared.
The phrase row grain simply means what a single row represents. If one file records each pupil once but another records the same pupil on five different days, matching the student ID alone creates a one-to-many join. That can be perfectly legitimate for a day-by-day analysis, but misleading if someone then treats all five rows as five different children.
A diagnosis-first classroom activity
Give students two tiny tables with a shared heading rating. Tell them that one file uses 1 = strongly agree, 5 = strongly disagree, while the second uses 1 = strongly disagree, 5 = strongly agree. Ask: ‘Would the average of all ratings mean more agreement or less?’ A careful pupil should refuse to average until the coding direction has been reconciled.
That refusal is evidence of understanding. A less careful pupil may add every number, divide by the row count and announce a precise answer to an ill-defined question. The teacher’s job is to distinguish arithmetic fluency from data meaning.
- Confuses a field name with a definition: ask for the actual survey question and response choices.
- Treats 0 as missing automatically: ask whether zero was an allowed genuine response.
- Counts merged records as independent people: ask what one row represents in each source.
- Converts hours to minutes but ignores the time period: ask whether the figures describe the same day, week or month.
- Matches by names or unverified identifiers: ask whether record linkage is authorised, necessary and accurate.
Progressive practice that ends with independent checking
Guided: identify the hidden definitions
Start with two four-row fictional tables. Let pupils circle the headings and annotate units, response direction and reference period. Their first goal is to spot at least one incompatible column without being told which it is.
Supported: repair the schema without changing the evidence
Have students rename ambiguous headings, make a four-column dictionary (name, definition, unit, allowed values), and leave unsupported conversions unresolved. Let them calculate a valid total or average only after documenting its denominator and unit.
Independent: merge a new pair of anonymised records
Give a fresh fictional file A with four unique respondent keys and a file B with three matching keys and one deliberately missing match. Ask students to predict the matched count, inspect the actual match results and label the unmatched row. The correct decision about what to do with the missing record depends on the analysis and consent; it is not automatically a zero or an invitation to manufacture an answer.
A useful progress test is whether the pupil can explain, without prompts, which fields can be compared, which can only be displayed side by side, and which cannot be joined safely at all. A tidy dashboard is not a substitute for that reasoning.
What students, parents and teachers should do
- Student: submit a dictionary alongside the chart and a one-sentence note of each important conversion or exclusion.
- Teacher: inspect a sample of original rows, ask pupils to justify join keys, and check whether the conclusion uses the actual row grain.
- Parent: try the question ‘If one survey asked about yesterday and the other about last month, would the same column title make them equal?’
- School team: protect children’s privacy. Use synthetic or appropriately anonymised samples for practice, and avoid collecting or joining identifiable pupil information without legitimate authorisation.
What a data dictionary cannot solve
A dictionary tells you what the data purport to mean; it cannot make an unreliable survey reliable, make biased responses representative, or supply missing observations. Two columns can match in units and still differ in wording, method or population. A legitimate data link can also be inaccurate if the key was mistyped or reused. Keep uncertainties visible and do not equate identical labels with identical evidence.
Conversely, different headings need not imply different meanings: two teams may use minutes_read and reading_duration_minutes for the same well-defined quantity. The dictionary helps verify that equivalence; it is not an order to force all datasets into identical names.
Frequently asked questions
Should I merge on student name or row number?
Not automatically. Row number often changes when sorting. Personal names pose privacy and matching problems. For authorised practice, use stable fictional keys; for real school data, follow applicable permissions and approved data-management methods.
Can I add 15 minutes to 2 hours?
You can add compatible quantities after converting them to the same unit if the question genuinely asks for their combined total and they represent non-overlapping periods. That is different from averaging Monday reading with an entire week that may already include Monday.
Is a blank cell the same as zero?
No. Blank might mean missing, skipped, not applicable or a technical import problem. The dictionary and original survey rules must determine what it represents.
Is a data dictionary needed for only ten rows?
A small one may be particularly useful for learning. It prevents children from building an error into their earliest analysis habits and makes later corrections traceable.
The core aim is to give numbers honest meanings
Related Core Aim lessons on duplicate ID lookups and preserving leading zeros help protect record identity. Our Education Hub and diagnosis-first teaching reference offer the wider sequence: explain, practise, independently check.
If a pupil can defend a column’s definition, the unit, the period and the row it belongs to, that pupil is learning something much more durable than clicking Merge. Good evidence starts with knowing what was actually counted.