2b, 2c - Exploring the 2027 large data set

The workbook describes authorities in two censuses

How can Gateshead's bus-travel share fall from about $17.4\%$ to $15.1\%$, while its number of bus travellers rises from $13723$ to $13909$? A percentage has a denominator as well as a numerator. This real census comparison will keep us honest throughout the lesson.

The prescribed H240 large data set contains travel-to-work and age-structure tables for $348$ aligned local authority areas in England and Wales, in the $2001$ and $2011$ censuses. It is the fixed H240 resource used for $2027$, rather than MEI's changing sequence of data sets. Open the census workbook, student notes and source information. The census data are from ONS, under Open Government Licence v3.0.

Read the Information sheet before choosing columns. In a travel table, the method is the one used for the longest part by distance of the usual journey to work, among people in employment in the week before the census. Someone recorded as using a train might also walk or drive for another stage. Use the employment count for a travel proportion. The age tables instead count usual residents; age is age at the last birthday, age bands contain counts, and mean and median age have units of years.

The mean of $348$ authority percentages gives each authority equal weight. Dividing total bus travellers by total employment weights authorities by their employment counts. These are different questions; neither calculation repairs a badly chosen denominator.

Try applying that sentence to a travel-method claim.

Metadata sets the limits of a comparison

Matching names is a risky way to join two census tables; matching their nine-character geography codes identifies the aligned areas directly.

The first three characters tell you the type of area. They are useful context, not a numerical measurement to average.

For example, County Durham's code E06000047 belongs to the first family. Some unitary authorities were formed by combining districts in $2009$; the workbook adds their $2001$ counts to match the later areas. County Durham combines seven former districts. You can compare the combined area across the two dates, but cannot recover those seven separate district results from their sum. That is lost geographical detail, not a spreadsheet error.

The age metadata matters too. In $2001$, dates of birth implying ages above $110$ were treated as invalid and age was imputed; in $2011$, the cut-off was $115$. An infant below one year is recorded as age $0$. The open-ended “Age 90 and over” group therefore has different effective upper limits across the two censuses. A midpoint estimate must state its choice; a change in an estimate may reflect that convention as well as population change.

Mean and median age were not retained in the $2001$ workbook. Grouped age counts cannot reveal exact individual ages, so they cannot reconstruct the exact mean or median. If exact means and population counts of all constituent districts were available, their population-weighted mean would recover the combined mean. Averaging district medians would still not recover the combined median: the combined middle position depends on the individual age distributions. Published rounded means would only give an approximate combined mean.

A comparison needs common definitions as well as matching areas.

Filtering and formulas need explicit denominators

A spreadsheet calculation becomes reproducible when another person can identify your columns, cleaning choices, selected rows and denominator.

Keep the source unchanged and make a working copy. On “Method of Travel by LA 2011”, select geography code, Region, authority name, employment and bus/minibus/coach. Copy these to columns A–E of a new sheet, with headers in row $1$, and put bus percent in F. For data row $2$, enter $\texttt{=100*E2/D2}$, then fill down. A cell is a column-and-row address, such as D2; filling down should change the row references, so the next formula uses E3 and D3.

Locate fields by header, not remembered position. Taxi is column L in $2001$, but I in $2011$. In the supplied $2011$ travel sheet P is blank and Q contains people not in employment; exclude that separate count from employment denominators. The $2001$ Isles of Scilly underground cell contains a dash. OCR's official notes recommend replacing this dash by zero in a working copy; record that documented cleaning decision. Do not generalise “dash means zero” to other data sets. Missing $2001$ mean and median ages remain unavailable.

Filtering Region to North East selects $12$ authorities. The first two give a quick check on the filled formula.

Change only the authority in Worked example 2. The census, variable, denominator rule and precision stay the same.

For the whole North East, copy the $12$ selected complete rows to a compact analysis range. Ordinary $\texttt{AVERAGE}$ and $\texttt{STDEV.P}$ still include hidden rows; a filter alone is not a safe range definition. In F2:F13, $\texttt{=COUNT(F2:F13)}$ checks the count, $\texttt{=AVERAGE(F2:F13)}$ gives $9.14925\ldots\%$, and $\texttt{=STDEV.P(F2:F13)}$ gives $3.955006\ldots$ percentage points. Here all $12$ selected authorities are the defined group, so the SD uses divisor $12$, consistent with H240's formula sheet.

The pooled percentage is instead

The mean of the percentages is $9.15\%$; the pooled percentage is $9.20\%$. Unequal employment counts explain the difference.

Now change the authority once more, then check that the spreadsheet has selected the intended group.

Graphs turn a question into inspectable evidence

State a question before choosing a graph: “Did the bus percentage fall between the two censuses in North East authorities?” calls for matched comparisons, not one pooled bar.

Join the two travel tables by geography code, keeping each year's employment denominator with its bus count. Select authority labels and the two computed percentage columns. A grouped bar chart puts both years beside each other on one percentage scale; the graph below uses all $12$ matched North East areas.

Every authority shown has a lower percentage in $2011$. That is a statement about these areas and dates, not a cause or a smooth trend during the intervening decade.

Use unrounded percentages for differences, even when the chart labels show rounded values.

Change only the authority to Darlington; keep the dates, measure and precision fixed.

Other questions need other displays. For an age-structure comparison, divide age-band counts by all usual residents before comparing differently sized authorities. To explore mean age against bus percentage, join the $2011$ age and travel tables by geography code and create an XY scatter diagram, one complete authority pair per point. Label mean age in years and bus use in percent. Inspect Region groups and unusual points before describing a pooled association; correlation alone would not identify a cause.

This final matched pair resolves the puzzle at the start.