K1, L4, 3.21 - Using technology to investigate real data

Turn a real question into an investigation

Can a small random sample have a higher mean CO2 than the whole group it came from, even when every calculation is right? We will follow real car records from the AQA workbook to find out.

Start with a question that sounds tempting: “Did newer cars cause lower carbon dioxide emissions?” The workbook records car characteristics; it did not randomly assign cars to years. A comparison can describe the recorded groups, but it cannot isolate registration year as a cause.

A useful descriptive question is: “Among FORD petrol cars with current keepers in the South West in this extract, how do recorded CO2 values compare between first-registration years $2002$ and $2016$?” This specifies a measured quantity, two groups and a defined eligible set.

Choose the fields before pressing a calculator button. The response variable is CO2; the grouping variable is YearRegistered. Keep Make equal to FORD, PropulsionTypeId equal to $1$ and GovRegion equal to South West. Those filters hold some characteristics fixed, while engine capacities and body types can still differ.

The cycle is not a button sequence with an inevitable conclusion. An unexpected value may send you back to the dictionary; a tiny filtered group may make the planned comparison unhelpful. Record those changes rather than quietly replacing the question after seeing the result.

Choose a representation that suits the variables. Separate plots on the same scale can compare CO2 in two year groups. A scatter plot is useful for two quantitative variables, such as EngineSize and CO2. The numerical codes for fuel categories do not form a meaningful measured horizontal axis. Later lessons develop displays and measures of spread in detail.

The next options differ in how clearly they define a question and how far they extend the evidence.

Extract and sample without separating a record's fields

A reproducible extraction keeps the source, selection rules and record identifiers together.

Download the unchanged official workbook or raw CSV extraction. Read Introduction, Definition of fields and Field Values first. Work on an analysis copy and leave the source unchanged.

Apply all filters to the whole table, retaining ReferenceNumber. FORD, Petrol, South West and YearRegistered $2016$ gives $95$ eligible records. A filter can hide rows without removing them from an ordinary formula's calculation range. Copy the visible eligible records to a separate analysis range, or use an appropriate filtered-data function and verify its count. Do not assume that an AVERAGE over a whole column automatically uses only visible rows.

For a random sample, attach independent random helpers to eligible rows, freeze them as values, sort whole rows and take the required number. Resolve ties randomly. The workbook contains saved cached Random number values. Using those saved values, rather than newly recalculated random functions, reproduces the particular ordering used here.

The lowest six full-precision helpers in the $95$-record eligible group give the following authentic records. Helpers are rounded to six decimal places for display only; sorting used the full saved values.

Retain the full helpers and identifiers in your saved analysis. An emission list alone cannot establish which records were selected or whether the other filters were respected.

Change only the requested sample size from three to six. Keep the filters and saved random ordering fixed.

Freezing saves the selection after random generation; it does not make the sample represent excluded makes or regions.

Try the same selection rule on a different authentic year group.

Clean the required field and report its denominator

Clean the field your question needs and report the number of usable values, rather than deleting every unusual row from every analysis.

Across the whole extract, $92$ Mass values are zero. Suspending these impossible masses leaves $3827-92=3735$ positive masses for a provisional mass summary. A car with unusable Mass may still have usable CO2; its row should remain in the source and can remain eligible for another field.

These four genuine records were deliberately chosen to show data-quality issues. They are not a random sample and cannot estimate how common those issues are. “Blank” means an empty source cell.

Mass here includes the dictionary's $75\,\mathrm{kg}$ average-driver allowance. The following calculation concerns that recorded Mass, so no allowance is subtracted.

Change only the analysed field from Mass to CO2 for the same four records. The appropriate exclusions now change because zero means something different in this field.

Blank NOX is not zero NOX. Here there are two recorded entries and two blanks, so a recorded-value NOX mean needs denominator two. If missingness is associated with car type, that mean may fail to represent all four cars.

The particulate dictionary says diesel-only, while the workbook contains some petrol rows with particulate entries. Preserve that discrepancy for checking. An unusually high emission is also not automatically an error: inspect the identified car before deciding whether an exclusion is justified. Mean calculations use a recall result, not a supplied formula-booklet formula.

Use the same records to check how a third field changes the denominator.