APG5642 Chap.3 Finding and Structuring Data
Finding and Structuring Data
Data journalism starts before analysis. The reporter first asks what record exists, who created it, which unit each row represents and whether the fields can answer the reporting question. A large file is not automatically useful evidence. It becomes reportable only when its provenance, scope and limitations are understood.
Preserve the original file and create a working copy.
Record the download location, date, stated coverage, definitions and any documentation. Inspect column names, data types, duplicate identifiers, missing values and category spellings before calculating anything. These checks often reveal that apparent patterns are coding differences, incomplete periods or changes in collection practice.
Structure should follow the question.
If one row mixes several events, split or aggregate carefully; if a total combines incomparable categories, retain the distinctions needed for fair analysis. Keep transformations reproducible and maintain a record from source field to cleaned field so another reporter can trace the published result back to the original material.
What this chapter covers
- 01
Reporting questions that can be answered with records
- 02
Dataset provenance, scope and documentation
- 03
The unit of observation represented by one row
- 04
Identifiers, duplicates and repeated events
- 05
Missing values and unknown categories
- 06
Field definitions, coding changes and date coverage
- 07
Tidy reporting tables built around the question
- 08
Transformation logs and reproducible checks
Audit a small inspection table
- 1Confirm that one row is intended to represent one inspection and check whether the repeated identifier is a duplicate or a legitimate revision.
- 1Preserve the blank outcome as unknown until documentation or the record holder explains it; do not silently label it a pass.
- 1Convert the text date with an explicit rule and flag any value that fails parsing rather than dropping it unnoticed.
- 1Standardise outcome labels only after checking that spelling variants have the same operational meaning.
- 1Count failures on the verified unique inspection records and report the number of unresolved records beside the result.
- 1Save a transformation log that links each cleaning decision to the source field and reason.
Key terms
- Data provenance
- The origin, custody, coverage and transformation history that connects a dataset to a published claim.
- Observation unit
- The real-world entity or event represented by one row, such as one inspection, person, application or transaction.
- Unique identifier
- A field or field combination intended to distinguish one observation reliably from every other observation.
- Missing value
- A field with no recorded value, which may reflect absence, non-response, inapplicability or collection failure rather than zero.
- Data dictionary
- Documentation that defines fields, codes, units, formats and other rules needed to interpret a dataset correctly.
- Transformation log
- A reproducible record of cleaning, recoding, joining and calculation decisions applied to the original data.
Finding and Structuring Data FAQ
What should be checked before analysing a dataset?
Confirm origin, coverage, row meaning, field definitions, identifiers, duplicates, missing values, category consistency and date formats. Compare the file with its documentation and preserve an untouched original before making changes.
Why does the unit of observation matter?
A count is meaningful only when you know what one row represents. Multiple rows may describe one person, one case may contain several events, or a summary row may be mixed with detail, causing serious overcounting.
Can missing values be treated as zero?
Only when documentation explicitly defines the blank that way. Missing data can mean unknown, not applicable, withheld or uncollected. Converting every blank to zero invents information and can reverse the reported pattern.
How can cleaning remain auditable?
Keep the original file, use a working copy, document every transformation and retain before-and-after counts. A colleague should be able to trace a published figure through the cleaned field to its source definition.
What should be checked when two tables are joined?
Predict the relationship and row count before joining, test key uniqueness, inspect unmatched records and check whether a one-to-many match multiplied observations. Document the join rule because unnoticed duplication can distort every total derived from the combined table.
Assessment move
Use a fixed intake routine whenever you open a dataset. Write the reporting question, source, coverage period, observation unit and important definitions before touching the values. Then inspect identifiers, duplicates, missingness, categories and dates. Make a small frequency table for every categorical field and a range check for every numeric field. Keep an explicit log of decisions and rerun the checks after cleaning.
Practise explaining the dataset in plain language: what it records, what it omits and which conclusions it cannot support. If that explanation is unclear, the analysis is not ready. Create a miniature audit table from an invented administrative process and deliberately insert a duplicate, blank, invalid date and category variant.
Exchange it with a partner, compare the cleaning decisions and discuss which choices require documentation from the record holder. Finish by writing a provenance note and a reproducibility checklist. This exercise shows how reasonable but different assumptions can change a total and why transformations must be recorded before the result enters a story.
