Computational & Data Journalism @ Cardiff

Reporting. Building. Designing. Informing

  • Blog
  • About Us
  • Local
  • Guests
You are here: Home / Archives for SQL

Finding donors to Truss leadership campaign, via Datasette

8th October 2022 by Aidan O'Donnell

Liz Truss now has the job of leading the Conservative Party and of running the country. So who gave her the money for her campaign?

MPs in Britain have to declare any money they receive, via donations or second jobs for example, and this is listed in the Register of Members’ Financial Interests.

The website explains that the Register exists to provide information about “any financial interest which a Member has, or any benefit which he or she receives” and the reason that this matters is that, according to the website, “others might reasonably consider” this money could influence the MP.

The Register is updated regularly but the data is laid out as text as a web page and only the line breaks serve to distinguish one field from another. This makes it very difficult to scrape the data or interrogate it for trends.

Datasette

This is where we turn to Datasette, a lovely tool for “exploring and publishing data”, built and maintained by Simon Willison and which allows SQL queries. Happily, there is already an example in place for the Register of Members’ Interests.

Querying

There are four main tables in this instance of Datasette: categories, items, members, and people. A query of the members table (using “View and edit SQL”) returns two ids we can use to look up Liz Truss in the main items table: a member number (40560) in the “id” column and a person number (24941) in the “person_id” column.

SELECT * FROM members WHERE name = ‘Elizabeth Truss’

The table with the crucial information, items, has close to two million entries. But, as Simon Willison explains, its members field seems to stop around 2015, so the person field is a better choice. Querying Truss in items via her “person_id”:

SELECT * FROM items WHERE person_id = ‘uk.org.publicwhip/person/24941’ ORDER BY date DESC

returns just over 950 entries, from 2010 to 2022.

But if you just want the 2022 donations:

SELECT * FROM items WHERE person_id = ‘uk.org.publicwhip/person/24941’ AND date LIKE ‘2022%’ ORDER BY date DESC

or more precisely again, just the donation descriptions that mention the word “campaign”:

SELECT * FROM items WHERE person_id = ‘uk.org.publicwhip/person/24941’ AND item LIKE ‘%campaign%’

This last query returns 48 donations, which you can then download as a csv or json from Datasette. Here is that data as a csv, after some further cleaning.

truss_donations2-1Download

Answers

Some initial observations on the donations are that:

£120,000 came from six companies: Big Bang Films, JC Bamford Excavators, Grolar Developments, SJJ Contracts, Smoked Salmon and Tungsten West. JC Bamford is the only one to have also donated to the wider Tory party in the last two years.

A little over £700,000 came from 13 people: Natasha Barnaba, Linda Edwards, Clara Freeman, Alison Frost, Fitriani Hay, Phillip Jeans, Gary Mond, Jon Moynihan, Sheila Noakes, Gordon Phillips, Howard Shore, Michael Spencer and Barbara Yerolemou.

A further £85,000 appeared to be help with transport, from Graham Edwards, Tony Gallagher, Greville Howard, Andrew Law and Nigel Vinson.

These initial observations however are just a starting point.

Filed Under: Blog Tagged With: data, politics, SQL

SELECT * FROM a day of SQL…

6th March 2020 by Aidan O'Donnell

This month our students survived a full-day workshop on SQL, moving from the very basics of the syntax to querying datasets or working through some of the better tutorials.

First up was the excellent Select Star tutorial by Zi Chong Kao, which is based on a dataset of US prisoners executed since 1976.

We then looked for newslines in a sqlite database of US babynames (via the command line) and wrote queries in Carto to map a dataset of protected Welsh monuments.

There was more sqlite with a database of shooting incidents involving Dallas police officers, this time via a notebook. And we finished with the Knight Center’s fine SQL-based murder mystery.

Enough there to get you started (or refreshed) with your SQL syntax.

Filed Under: Blog Tagged With: coding, data, education, investigation, SQL, tools

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in