GOV.UK people data

This project extracts data on individuals (identifiers and details of appointments) from GOV.UK and matches it to the IfG Ministers Database, producing SQL scripts to update the database.

Related repositories

• 🔒 IfG Ministers Database - private repo: Holds the majority of code and resources for the IfG Ministers Database project (held privately). GOV.UK appointment data collated here is matched to IfG Ministers Database appointments data and scripts created that update the data held in the IfG Ministers Database. A package - ifg_ministers_database_utils - stored in this repo is also a dependency used in one of the steps of the GOV.UK people data pipeline

• 🔓 IfG Ministers Database - public repo: The public part of the IfG Ministers Database project

• 🔒 Data App: An early prototype of an IfG data app. Can be used for manual review of possible matches of appointment data between GOV.UK data and the IfG Ministers Database

  NB: Not to be confused with the similarly named 🔓 IfG Data App, which serves the IfG's analytics dashboard

Project structure

├── govuk_data/
|   ├── appointments/
|   |   ├── clean_govuk_person_roles.py
|   |   ├── extract_govuk_person_pages.py
|   |   ├── match_govuk_appointments.py
|   |   ├── personpage_createanalysistable.sql
|   |   ├── personpage_qa_analysis.sql
|   |   ├── personpage_qa_source.sql
|   |   ├── personpage_reviewmatchoutput.sql
|   |   ├── write_add_post_relationships_script.py
|   |   ├── write_add_posts_script.py
|   |   ├── write_update_appointments_script.py
|   |   ├── data/
|   |   │   └── df_person_page.pkl_<datestamp>
|   |   └── utils/
|   |       ├── create_post_count.sql
|   |       ├── create_post_relationship_count.sql
|   |       ├── create_post_relationship.sql
|   |       ├── create_post.sql
|   |       ├── identify_appointments_to_edit.sql
|   |       ├── identify_post_relationships_to_add.sql
|   |       ├── identify_posts_to_add.sql
|   |       ├── update_appointment_count.sql
|   |       ├── update_appointment.sql
|   |       └── utils.py
|   ├── people_strings/
|       ├── build_people_strings_reference.sql
|       ├── load_govuk_people_matches.py
|       ├── match_govuk_people.py
|       ├── scrape_govuk_people.py
|       └── data/
|           └── match_<datestamp>.xlsx
.gitignore
.pre-commit-config.yaml
README.md

Installation

pip install -r requirements.txt

Database schemas

Schema	Role
core	Core tables that make up the IfG Ministers Database — person, appointment, post, organisation, etc. Read-only from this pipeline's perspective
source	Raw data as scraped or extracted from GOV.UK, written by people_strings/scrape_govuk_people.py and appointments/extract_govuk_person_pages.py
analysis	Processed and intermediate tables produced during matching and cleaning
reference	Consolidated lookup tables used across runs, built up by build_people_strings_reference.sql
workflow	Ephemeral per-run matching tables written by match_govuk_appointments.py; a new UUID-named table is created each run

People strings pipeline

Run once per data pull to build up the reference.[ukgovt.govuk_strings_people] lookup, which maps IfG minister IDs to their GOV.UK people string (e.g. matthew-hancock). Only ministers not already in the reference table need to be matched each run.

Step	Script	Inputs	Outputs
1	scrape_govuk_people.py	Web: gov.uk/government/people (paginated)	source.[ukgovt.govuk_strings_people_<datestamp>]
2	match_govuk_people.py	core.person core.appointment reference.[ukgovt.govuk_strings_people] source.[ukgovt.govuk_strings_people_<datestamp>]	data/match_<datestamp>.xlsx
3	load_govuk_people_matches.py	data/match_<datestamp>.xlsx — following manual review	analysis.[ukgovt.minister_ids_govuk_strings_<datestamp>]
4	build_people_strings_reference.sql	analysis.[ukgovt.minister_ids_govuk_strings_<datestamp>] (one table per historical run, unioned)	reference.[ukgovt.govuk_strings_people]

Appointments pipeline

Run once per data pull to match GOV.UK appointment records to the IfG Ministers Database and generate SQL scripts to update it. Note, the scripts only update the post name an appointment is associated with - other updates that are required (e.g. to start/end dates) must be made manually in the database.

Step	Script	Inputs	Outputs
1	extract_govuk_person_pages.py	reference.[ukgovt.govuk_strings_people] GOV.UK Content API	data/df_person_page.pkl_<datestamp> (checkpoint) source.[ukgovt.minister_govuk_people_page_content_<datestamp>]
2	personpage_qa_source.sql	source.[ukgovt.minister_govuk_people_page_content_<datestamp>]	(exploratory — no outputs; findings inform filters in step 3)
3	personpage_createanalysistable.sql	source.[ukgovt.minister_govuk_people_page_content_<datestamp>]	analysis.[ukgovt.minister_govuk_people_page_content_<datestamp>]
4	personpage_qa_analysis.sql	analysis.[ukgovt.minister_govuk_people_page_content_<datestamp>]	(QA — no outputs)
5	clean_govuk_person_roles.py	analysis.[ukgovt.minister_govuk_people_page_content_<datestamp>]	analysis.[ukgovt.minister_govuk_people_page_content_<datestamp>] (updated in place)
6	match_govuk_appointments.py	analysis.[ukgovt.minister_govuk_people_page_content_<datestamp>] core.person core.appointment core.appointment_characteristics core.post core.organisation	workflow.<uuid> (UUID printed to console — substitute into steps 7–9)
7	personpage_reviewmatchoutput.sql	workflow.<uuid>	(manual review — no outputs; update reviewed/match_accepted/replace_post_name flags in the workflow table)
8	write_add_posts_script.py	workflow.<uuid> (via utils/identify_posts_to_add.sql)	stdout: SQL to insert new records into core.post — copy and run manually in the IfG Ministers Database
9	write_add_post_relationships_script.py	workflow.<uuid> (via utils/identify_post_relationships_to_add.sql)	stdout: SQL to insert new records into core.post_relationships — copy and run manually in the IfG Ministers Database
10	write_update_appointments_script.py	workflow.<uuid> (via utils/identify_appointments_to_edit.sql)	stdout: SQL to update core.appointment post IDs — copy and run manually in the IfG Ministers Database

Run history

Run date	Scope	Appointment details workflow table - input	Appointment details workflow table - output	Notes
2021-10-09	People strings only
2022-07-02	People strings only
2024-05-03	People strings and appointment details	workflow.[150d6d94-c423-446a-9594-bf8810899a6e]	workflow.[c0fbaa62-7e6b-4652-9ee8-bef5bd1baa3a]
2026-04-28	People strings and appointment details	workflow.[9e918c56-ece0-4f78-8571-d141addbdc9d]	workflow.[4c930166-fa81-49ca-bb9c-b9d0a4890ab4]	Unintentionally excluded ongoing appointments
2026-05-13	People strings and appointment details	workflow.[6f4b5295-0d68-4f11-b0cf-624bacd30c28]	workflow.[5a5df950-6520-4553-b93e-8fa8ce754599]

Environment variables

The following environment variables must be set before running any scripts:

Variable	Description
ODBC_DRIVER	ODBC driver name
ODBC_SERVER	SQL Server hostname
ODBC_DATABASE	Database name
ODBC_AUTHENTICATION	Authentication method
AZURE_CLIENT_ID	Azure service principal client ID used for database authentication
AZURE_CLIENT_SECRET	Azure service principal client secret used for database authentication

Contributing

This project uses pre-commit hooks to ensure code quality. To set up:

1. Install pre-commit on your system if you don't already have it:

   pip install pre-commit

2. Set up pre-commit in your copy of this project. In the project directory, run:

   pre-commit install

Rules that are applied can be found in .pre-commit-config.yaml.

The hooks run automatically on commit, or manually with pre-commit run --all-files.