Computational & Data Journalism @ Cardiff

Reporting. Building. Designing. Informing

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

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

Election data — the UK’s December vote

13th December 2019 by Aidan O'Donnell

Elections are a special meeting of journalism and data. They generate lots of both! So the morning after the long night of vote counting, we got this year’s students working on the results for a full day. Four student groups were each given one of the four UK nations. Each group also got a Welsh constituency to analyse; after consulting with our pol corrs on the MA-News programme we decided the interesting Welsh battles would be in Cardiff North, Ceredigion, Caerphilly and Vale of Glamorgan.

The main difficulty with analysing the results was not having the XML feed from PA that UK news organisations had been relying on (and had been testing for weeks). We didn’t have the raw data flowing in as soon as a count was announced. But that’s where the BBC came in — they published results for each constituency in a standardised url, supplying 650 webpages for the UK’s 650 constituencies.

This meant that it was enough to draw up a few lines to grab each page and the corresponding batch of results. If only all large-scale scraping was as clean and consistent!

We were able to publish csv files with full results for the four nations by the end of the day. Now of course you can get them from lots of sources but right after the election, and with results still being declared throughout Friday, we were able to get started on analysing the results once we had these tables.

The people at Flourish provided very helpful templates ahead of the vote. So hex maps, animated bar charts and Sankey diagrams were all ready and waiting for numbers.

Filed Under: Blog Tagged With: hackday, politics, scraping, students, voterpower

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