Census data quality

Author

(insert name here)

Published

April 2, 2025

Census data quality

Introduction

Add some introductory test stating the purpose and context of the data as well as introducing the report.

The dimensions of data quality listed below are explained on more detail in the DQHub website.

Any additional information about processes that will resolve issues reported in this document can go here at a high-level. Specific actions to address particular issues may be better placed with the analysis reported below.

It is a good idea to reference organsiational goals at this point. Use visualisations to relate the quality of the data being reported on back to those goals. These don’t have to be charts, but make use of being able to include pngs, or generate mermaid diagrams. The example below is bespoke, but there are examples of others at mermaid.js.org.

flowchart LR
read["PRREAD (GRADE)"]
pread["KS1_PSREAD"]
readps[KS1READPS]
readps_p[KS1READPS_P]
ks1av[KS1AVERAGEPS]
ks1grp["KS1GROUP (for prior attainment LMH)"]
ks1avgrp["KS1AVERAGE_GRP_P (for progress)"]
sln["SCHRES
LARES
NATRES"]
eks["ENDKS
DISC3"]

read-->|lookup|readps-->readps_p-->ks1av-->ks1grp
eks-->readps
ks1av-->ks1avgrp
pread-->|lookup|readps_p
eks-->readps_p
sln-->ks1av
eks-->ks1av
eks-->ks1avgrp

Improve the skills pipeline Level up education standards Support the most disadvantaged and vulnerable children High quality early education and childcare

Data Quality Reporting

Completeness

Completeness describes the degree to which records are present.

Code 
chartdata = pd.DataFrame([
    {'year':'this_year', 'record count':df.shape[0]},{'year':'last_year', 'record count':reference.shape[0]}
    ])
barchart(chartdata = chartdata, cats = 'year',values = 'record count', groups = 'year')
Figure 1: Completeness of data: comparison with data from the previous year

Uniqueness

Uniqueness describes the degree to which there is no duplication in records. This means that the data contains only one record for each entity it represents, and each value is stored once.

Code 
chartdata = pd.DataFrame([
    {'year':'this_year', 'proportion unique':df['UPN'].nunique() / df.shape[0]},
    {'year':'last_year', 'proportion unique':reference['UPN'].nunique() / reference.shape[0]}
    ])
barchart(chartdata, cats = 'year',values = 'proportion unique', groups = 'year')
Figure 2: Uniqueness of data: comparison with data from the previous year

It may be more helpful to see the number of records that aren’t unique.

Code 
chartdata = fd([
    df.groupby('UPN')['UPN'].count(),
    reference.groupby('UPN')['UPN'].count()
    ], ids=['this','last'], long=1).rename(columns={'value': 'number of duplications'})

barchart(chartdata[chartdata['number of duplications']>1], cats = 'number of duplications',values = 'count',groups='group')
Figure 3

Consistency

Consistency describes the degree to which values in a data set do not contradict other values representing the same entity. For example, a mother’s date of birth should be before her child’s.

In this example we are looking for consistency within the same dataset, but if we were doing this for real we could include previous year or mutliple census in the same year, as well as other datasets entirely where the same data items were collected - National Curriculum assessments for example.

PupilMatchingRefAnonymous (PMRA) and UPN should both relate to the same pupil. Let’s see if that’s the case…

Code 
chartdata = fd([
    df.groupby('PupilMatchingRefAnonymous')['UPN'].nunique(),
    reference.groupby('PupilMatchingRefAnonymous')['UPN'].nunique()
    ], ids=['this','last'], long=1).rename(columns={'value':'number of UPNs'})

barchart(chartdata[chartdata['number of UPNs']>1], cats = 'number of UPNs',values = 'count',groups='group')
Figure 4: Number of UPNs associated with a single PMRA

If they are truly the same pupils, then the dates of birth should be consistent too.

Code 
chartdata = fd([
    df.groupby('PupilMatchingRefAnonymous')['DOB'].nunique(),
    reference.groupby('PupilMatchingRefAnonymous')['DOB'].nunique()
    ], ids=['this','last'], long=1).rename(columns={'value':'number of DOBs'})

barchart(chartdata[chartdata['number of DOBs']>1], cats = 'number of DOBs',values = 'count',groups='group')
Figure 5: Number of DOBs associated with a single PMRA

Those 18 instances of PMRs associated with 2 records with different dates of birth would certainly be worth following up.

Timeliness

Timeliness describes the degree to which the data is an accurate reflection of the period that they represent, and that the data and its values are up to date.

This dataset refers to a census where the data reflects a point in time - even if those variables change in real time, the census values should not change. Other datasets will benefit from analyses to monitor change or reference datasets collected at different times.

Validity

Validity describes the degree to which the data is in the range and format expected. For example, date of birth does not exceed the present day and is within a reasonable range.

This could easily be the biggest section of the report if handled badly. Ideally this section should report by exception - the failures, or an overview (all 500 columns contain values within specified range) and refer to appendices for detail.

For example we might check that UPNs are valid, or that names are valid. We wouldn’t report all passes here for every field, but might report by exception and include data for all columns in an appendix.

Code 
pd.DataFrame(df['UPN'].apply(valid_upn).value_counts())
Table 1: UPN validity checks
count
UPN
True 598157
False 4
Code 
pd.DataFrame(df['Forename'].str.match(pat=valid_name_regex).value_counts())
Table 2: Forename validity checks
count
Forename
True 598141
False 20
Code 
pd.DataFrame(df['Forename'].str.strip().str.match(pat=valid_name_regex).value_counts())
Table 3: Forename validity checks (without trailing spaces)
count
Forename
True 598152
False 9

Accuracy

Accuracy describes the degree to which data matches reality.

In the case of a census it may be difficult to determine accuracy beyond validity and consistency. One approach to gaining some measure of assurance of accuracy is to consider what an aggregate measure might look like if the data were accurate or inaccurate.

For example, our dates of birth might all be valid and consistent, but if they were all in January we would be concerned over the accuracy of the data, as might happen if there were an issue recording the correct month in the data collection.

In a dataset covering most children in the country of a certain age, we would expect there to be similar numbers of pupils born in each month of the year, with a few more in months with 31 days and a few less in february. It doesn’t quite work out that way, so using the previous year as a comparison is better. Some variables might have a uniform distribution and can be presented along with a straight line to show where the expected distribution would lie, but other variables are better presented with a reference dataset.

Code 
dob_data = fd([df['DOB'].dt.month.astype(int), reference['DOB'].dt.month.astype(int)], ids=['this year', 'last year'], long=True)
barchart(dob_data, cats='value', values='count', groups='group')
Figure 6: Month of birth: comparison with data from the previous year

User needs and trade-offs

Are there any hot-topics or issues which have occurred in the past which require monitoring?

Interpretation, conclusion and recommendation

In summary, what does it all mean? What purposes can the data be used for?

Appendices

Lists of columns, tables, metadata, etc.

column names in table

Code 
pd.DataFrame(get_table_metadata(conn = get_default_conn(), tablename='tier0.CensusSeasonSSA_MasterView')['columns']).style.set_properties(**{'text-align': 'left'}).hide()
Table 4: Metadata for all columns in CensusSeasonSSA_MasterView
name type nullable default autoincrement comment order
AcademicYear BIGINT True None False None 0
PupilMatchingRefAnonymous CHAR(18) COLLATE "Latin1_General_CI_AS" True None False None 1
AddressLine1 VARCHAR(40) COLLATE "Latin1_General_CI_AS" True None False None 2
AddressLine2 VARCHAR(40) COLLATE "Latin1_General_CI_AS" True None False None 3
AddressLine3 VARCHAR(40) COLLATE "Latin1_General_CI_AS" True None False None 4
AddressLine4 VARCHAR(40) COLLATE "Latin1_General_CI_AS" True None False None 5
AddressLine5 VARCHAR(40) COLLATE "Latin1_General_CI_AS" True None False None 6
AdministrativeArea VARCHAR(30) COLLATE "Latin1_General_CI_AS" True None False None 7
AdoptedFromCareFrom2005 CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 8
AgeAtStartOfAcademicYear TINYINT True None False None 9
Alevel TINYINT True None False None 10
AnnualSessionsAuthorised SMALLINT True None False None 11
AnnualSessionsPossible SMALLINT True None False None 12
AnnualSessionsUnauthorised SMALLINT True None False None 13
Boarder VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 14
CareAuthority VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 15
CensusDate DATETIME True None False None 16
CensusTerm VARCHAR(6) COLLATE "Latin1_General_CI_AS" True None False None 17
CensusTermKey INTEGER False None False None 18
ClassType VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 19
Connexions VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 20
Contribution VARCHAR(20) COLLATE "Latin1_General_CI_AS" True None False None 21
DataSourceHistoryId BIGINT True None False None 22
DataSourcePath VARCHAR(2000) COLLATE "Latin1_General_CI_AS" True None False None 23
DistCurrSch DECIMAL(5, 2) True None False None 24
DistNearSch DECIMAL(5, 2) True None False None 25
DOB DATETIME True None False None 26
EnrolStatus VARCHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 27
EntryDate DATETIME True None False None 28
Estab CHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 29
EthnicGroup VARCHAR(30) COLLATE "Latin1_General_CI_AS" True None False None 30
EthnicGroupMajor CHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 31
EthnicGroupMinor VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 32
Ethnicity VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 33
EthnicitySource VARCHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 34
EVERFSM3 INTEGER True None False None 35
EVERFSM6 INTEGER True None False None 36
EVERFSMALL INTEGER True None False None 37
EYPPBF VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 38
EYPPreason VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 39
EYPPE CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 40
EYPPeligible CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 41
FirstLanguage VARCHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 42
Forename VARCHAR(60) COLLATE "Latin1_General_CI_AS" True None False None 43
FormerSurname VARCHAR(60) COLLATE "Latin1_General_CI_AS" True None False None 44
FormerUPN VARCHAR(13) COLLATE "Latin1_General_CI_AS" True None False None 45
FSMeligible VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 46
FSMeligibleFFD INTEGER True None False None 47
FTEmp TINYINT True None False None 48
FundedHours DECIMAL(5, 1) True None False None 49
GandTindicator VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 50
GCSE TINYINT True None False None 51
Gender VARCHAR(6) COLLATE "Latin1_General_CI_AS" True None False None 52
GNVQ TINYINT True None False None 53
HomeLA VARCHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 54
HOMELA9CODE VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 55
HoursAtSetting DECIMAL(5, 1) True None False None 56
IDACIR DECIMAL(5, 0) True None False None 57
IDACIR10 SMALLINT True None False None 58
IDACIR15 INTEGER True None False None 59
IDACIS DECIMAL(18, 17) True None False None 60
IDACIS10 DECIMAL(8, 7) True None False None 61
IDACIS15 DECIMAL(8, 7) True None False None 62
InCare VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 63
InCareAtCurrentSchool VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 64
LA CHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 65
LA9CODE VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 66
LAEstab CHAR(7) COLLATE "Latin1_General_CI_AS" True None False None 67
LALGR CHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 68
Language VARCHAR(7) COLLATE "Latin1_General_CI_AS" True None False None 69
LanguageGroup CHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 70
LanguageGroupMajor VARCHAR(6) COLLATE "Latin1_General_CI_AS" True None False None 71
LanguageGroupMinor CHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 72
LeavingDate DATETIME True None False None 73
LinkUniqueRowNumber BIGINT True None False None 74
Locality VARCHAR(35) COLLATE "Latin1_General_CI_AS" True None False None 75
LSOA01 VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 76
LSOA11 VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 77
MiddleNames VARCHAR(60) COLLATE "Latin1_General_CI_AS" True None False None 78
MobilityInd TINYINT True None False None 79
ModeOfTravel VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 80
MonthOfBirth TINYINT True None False None 81
MonthPartOfAgeAtStartOfAcademicYear TINYINT True None False None 82
NCYearActual VARCHAR(11) COLLATE "Latin1_General_CI_AS" True None False None 83
NCYearLeaving VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 84
NearSchLAEstab CHAR(7) COLLATE "Latin1_General_CI_AS" True None False None 85
NonQualHrs SMALLINT True None False None 86
NonQualHrsPrev SMALLINT True None False None 87
NVQ TINYINT True None False None 88
OA01 VARCHAR(10) COLLATE "Latin1_General_CI_AS" True None False None 89
OA11 VARCHAR(10) COLLATE "Latin1_General_CI_AS" True None False None 90
OACODE VARCHAR(10) COLLATE "Latin1_General_CI_AS" True None False None 91
OnRoll TINYINT True None False None 92
Other VARCHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 93
PAON VARCHAR(100) COLLATE "Latin1_General_CI_AS" True None False None 94
PartitionName VARCHAR(2000) COLLATE "Latin1_General_CI_AS" True None False None 95
PartTime VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 96
Phase VARCHAR(3) COLLATE "Latin1_General_CI_AS" True None False None 97
PLAA VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 98
PostAdvanced VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 99
Postcode NVARCHAR(8) COLLATE "Latin1_General_CI_AS" True None False None 100
PostTown VARCHAR(30) COLLATE "Latin1_General_CI_AS" True None False None 101
PPeligible TINYINT True None False None 102
PreferredSurname VARCHAR(60) COLLATE "Latin1_General_CI_AS" True None False None 103
PreGNVQ TINYINT True None False None 104
PrimarySENtype VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 105
PupilOrderSeqColumn INTEGER True None False None 106
PupilTableID CHAR(36) COLLATE "Latin1_General_CI_AS" True None False None 107
QualHrs SMALLINT True None False None 108
QualHrsPrev SMALLINT True None False None 109
RecordStatus INTEGER True None False None 110
ResourcedProvisionIndicator TINYINT True None False None 111
RMConsortiumID INTEGER True None False None 112
SAON VARCHAR(100) COLLATE "Latin1_General_CI_AS" True None False None 113
SchoolCensusTableID CHAR(36) COLLATE "Latin1_General_CI_AS" True None False None 114
SchoolLunchTaken TINYINT True None False None 115
SecondarySENtype VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 116
SENprovision VARCHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 117
SENprovisionMajor VARCHAR(6) COLLATE "Latin1_General_CI_AS" True None False None 118
SENUnitIndicator TINYINT True None False None 119
ServiceChild CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 120
ServiceChildSource VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 121
SOA1 VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 122
SourceAcademicYear VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 123
SourceID INTEGER True None False None 124
SourceTable VARCHAR(30) COLLATE "Latin1_General_CI_AS" True None False None 125
SpecialProvisionIndicator VARCHAR(5) COLLATE "Latin1_General_CI_AS" True None False None 126
Street VARCHAR(100) COLLATE "Latin1_General_CI_AS" True None False None 127
Surname VARCHAR(60) COLLATE "Latin1_General_CI_AS" True None False None 128
SurveyPupilID INTEGER True None False None 129
TermlyReasonC SMALLINT True None False None 130
TermlyReasonE SMALLINT True None False None 131
TermlyReasonF SMALLINT True None False None 132
TermlyReasonG SMALLINT True None False None 133
TermlyReasonH SMALLINT True None False None 134
TermlyReasonI SMALLINT True None False None 135
TermlyReasonM SMALLINT True None False None 136
TermlyReasonN SMALLINT True None False None 137
TermlyReasonO SMALLINT True None False None 138
TermlyReasonR SMALLINT True None False None 139
TermlyReasonS SMALLINT True None False None 140
TermlyReasonT SMALLINT True None False None 141
TermlyReasonU SMALLINT True None False None 142
TermlySessionsAuthorised SMALLINT True None False None 143
TermlySessionsPossible SMALLINT True None False None 144
TermlySessionsUnauthorised SMALLINT True None False None 145
TopUpFunding TINYINT True None False None 146
Town VARCHAR(30) COLLATE "Latin1_General_CI_AS" True None False None 147
TypeOfClass VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 148
ULNCensus VARCHAR(50) COLLATE "Latin1_General_CI_AS" True None False None 149
ULNLRS VARCHAR(10) COLLATE "Latin1_General_CI_AS" True None False None 150
UniqueRowNumber INTEGER True None False None 151
UnitContactTime TINYINT True None False None 152
UPN VARCHAR(13) COLLATE "Latin1_General_CI_AS" True None False None 153
URN VARCHAR(6) COLLATE "Latin1_General_CI_AS" True None False None 154
YearOfBirth SMALLINT True None False None 155
YSSA VARCHAR(4) COLLATE "Latin1_General_CI_AS" True None False None 156
EVERFSM6P INTEGER True None False None 157
FundingBasisLAA TINYINT True None False None 158
DAFIndicator TINYINT True None False None 159
EYEEntitlement DECIMAL(8, 6) True None False None 160
EYUEntitlement DECIMAL(8, 6) True None False None 161
FundingBasisECO TINYINT True None False None 162
FundingBasisHSD TINYINT True None False None 163
PPEntitlement DECIMAL(8, 6) True None False None 164
SBEntitlement DECIMAL(8, 6) True None False None 165
ExtendedHours DECIMAL(5, 1) True None False None 166
ThirtyHourCode VARCHAR(11) COLLATE "Latin1_General_CI_AS" True None False None 167
FSMProtected TINYINT True None False None 168
MathsGCSEHighestPriorAttainment VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 169
MathsGCSEPriorAttainmentYearGroup CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 170
EnglishGCSEHighestPriorAttainment VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 171
EnglishGCSEPriorAttainmentYearGroup CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 172
MathsGCSEFundingExemption CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 173
EnglishGCSEFundingExemption CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 174
NonqualHrsPreviousYear SMALLINT True None False None 175
QualHrsPreviousYear SMALLINT True None False None 176
EYPPR CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 177
EnglishGCSEHighestPriorAttainmentPreviousYear VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 178
MathsGCSEHighestPriorAttainmentPreviousYear VARCHAR(2) COLLATE "Latin1_General_CI_AS" True None False None 179
IDACIR19 INTEGER True None False None 180
IDACIS19 DECIMAL(8, 7) True None False None 181
Census Output Area (Census 2011) VARCHAR(10) COLLATE "Latin1_General_CI_AS" True None False None 182
Census Output Area VARCHAR(10) COLLATE "Latin1_General_CI_AS" True None False None 183
Eastings INTEGER True None False None 184
Electoral Ward VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 185
Government Office Region VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 186
Local Authority (Upper Tier) VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 187
Local Authority District (Lower Tier) VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 188
Lower Level Super Output Area (Census 2011) VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 189
Lower Level Super Output Area VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 190
Middle Level Super Output Area (Census 2011) VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 191
Middle Level Super Output Area VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 192
Northings INTEGER True None False None 193
Parliamentary Constituency VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 194
TLevelNonqualHrs SMALLINT True None False None 195
TLevelQualHrs SMALLINT True None False None 196
IDACID19 TINYINT True None False None 197
EnglishGCSEPriorAttainmentYear11 CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 198
MathsGCSEPriorAttainmentYear11 CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 199
LanguageOriginal VARCHAR(7) COLLATE "Latin1_General_CI_AS" True None False None 200
FSMEligibleFFDSummer INTEGER True None False None 201
LSOA21 VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 202
OA21 VARCHAR(9) COLLATE "Latin1_General_CI_AS" True None False None 203
YoungCarer CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 204
Sex CHAR(1) COLLATE "Latin1_General_CI_AS" True None False None 205
ExpandedHours DECIMAL(5, 2) True None False None 206
EligibilityCode VARCHAR(11) COLLATE "Latin1_General_CI_AS" True None False None 207