Messing around with Elections NI data

Sources:

• Live Data (for 2023)
• 2022 Assembly Elections

Creating your own Google Sheet and referencing the crowdsourced data

The above linked spreadsheets are naturally not editable by everyone; this is great for reliable data but isn’t so great when you want to make pretty graphs.

Google Sheets supports the live referencing of external sheets in your own sheets, so you can ‘import’ the data from the read-only sheets as they evolve over the count, and then reference those data in your own visualisations.

This is done using the IMPORTRANGE function in Google Sheets, so like this;

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AazeIZwfflJJoTiYNil3RprIOXcNy8yyzfZ4ImVlETA/edit#gid=372848906","Belfast South/West!A2:Q24")

Using this and the Google QUERY language, you can easily create some pretty dynamic graphs in a couple of lines/cells across your own sheets, all while being ‘fed’ by the main collaborative work.

For instance, this is generated from the following formula;

=query('Basic IMPORTRANGE'!A2:Q16, "select B, sum(C) group by B order by sum(C) desc");

In this case the first argument to the query is a reference to my own sheet that just has ‘IMPORTRANGE’ in it; the interesting bit is the second argument which gives a list of the values in column B (The Party Names in the count sheet) sorted by the sum of the matching rows in column C (the first preference votes), with that ‘sum’ being defined across the groups with the same values in column B, and finally, these all sorted in a descending fashion by the total of those first preference votes.

So now we’ve easily set up a aggregation with two ‘cells’ of formula and a fairly basic chart.

Doing the real work with Python

Python is really powerful for this kind of stuff, particularly the pandas data management library; we can get the above referenced sheet into a ‘raw’ python format with just a ‘few lines of code’.

import pandas as pd
from urllib.parse import quote

sheet_id = "1AazeIZwfflJJoTiYNil3RprIOXcNy8yyzfZ4ImVlETA" # This is the bit taken from the URL above, like IMPORTRANGE above
tab_id = 372848906
# https://docs.google.com/spreadsheets/d/1AazeIZwfflJJoTiYNil3RprIOXcNy8yyzfZ4ImVlETA/edit#gid=372848906

url = f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv&gid={tab_id}"
pd.read_csv(url)

This is a little bit more complicated than other google foo would have you believe but it looks like Google updated their API’s over the years to remove the ‘happy path’ for this call.

Additionally, note that as in the IMPORTRANGE example, there are no usable ‘headers’ in the underlying data so we may have to create these ourselves for more complex analysis.

Also, we have to manually ‘tidy up’ the ‘range’ ourselves, as the Belfast South range only goes to row 24, and then Belfast West appears.

While we could get fancy, for simplicity, this is a manual example. And we’ll also exclude the ‘metadata’ such as the Turnout statistics and Quota from the bottom of the section, so in this case trimming the data from to run between rows 3 and 17 for just candidates and transfer statistics.

df = pd.read_csv(url)
df

df.iloc[1:16]

There are a few ways to tidy up this stage/transfer setup, so for simplicity we’ll take the ‘index’ off the left of the table (consisting of the candidate and party names) and try and construct a new column index based on those.

Sounds fancy.

df.iloc[1:16]

_table = df.iloc[1:16]
_table = _table.dropna(how='all', axis=1)
_table = _table.set_index(_table.columns.tolist()[0:2])
_table = _table.fillna(0)
_table = _table.astype(float)
_table.index=_table.index.set_names(['Candidate','Party'])
_table

Now to do the same thing with the stage counts and transfers;

There are many ways to do this, either leaving it as is and just naming the columns by Stage and Transfer, but a ‘better’ way to do it is to create a multiindex on the column. Which probably means nothing.

_table.columns

Index(['Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6',
       'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10', 'Unnamed: 11',
       'Unnamed: 12', 'Unnamed: 13', 'Unnamed: 14', 'Unnamed: 15',
       'Unnamed: 16'],
      dtype='object')

stage = ['Count','Transfers']
stages = range(1,_table.shape[1]//2+2)
pd.MultiIndex.from_product([stages,stage])[:-1]

MultiIndex([(1,     'Count'),
            (1, 'Transfers'),
            (2,     'Count'),
            (2, 'Transfers'),
            (3,     'Count'),
            (3, 'Transfers'),
            (4,     'Count'),
            (4, 'Transfers'),
            (5,     'Count'),
            (5, 'Transfers'),
            (6,     'Count'),
            (6, 'Transfers'),
            (7,     'Count'),
            (7, 'Transfers'),
            (8,     'Count')],
           )

_table.columns=pd.MultiIndex.from_product([stages,stage], names=['Stage','Step'])[:-1]

_table

Now we can do some interesting queries really easily;

_table.xs("Count", level='Step', axis=1)

This makes plotting quite simple; which then makes the queries you can express much more complex…

_table.xs("Count", level='Step', axis=1).groupby('Party').sum().T.plot()

<Axes: xlabel='Stage'>

_table.xs("Transfers", level='Step', axis=1)\
    .groupby('Party').sum().cumsum().T.plot(
        title='Net Cumulative Transfers'
    )\
    .legend(loc='right', bbox_to_anchor=(1.6,0.5))

<matplotlib.legend.Legend at 0x17b555a50>

Conclusion

Considering it’s election day today in Northern Ireland, I wanted to bash this out to help other election observers have a play with the fantastic work the likes of @colm_burns and the rest of the @electionsni team are doing.

More than happy to help anyone else answer interesting electoral questions, and remember; vote early, vote often, and #votetillyouboke