Mar 9, 2021

Vactrack: Tweeting Covid Vaccine Rollout in Spain

How quickly are people getting vaccinated?

Unless you’ve been living on a literal desert island for the last year you’ve been impacted by Covid 19. Well my girlfriend and I have been curious about the vaccine rollout and how much progress is being made so things can get back to normal.

While browsing twitter my other half stumbled upon a twitter bot called @ukvacprogress. The bot consumes vaccine information published by the UK government and tweets out a little progress bar.

The Bot is really neat and better yet it is open source! Thanks to Mark Williams-Cook for publishing the code. You can and should read Mark’s blog post here.

Well, my other half is from Barcelona so we’re also interested in the progress being made by the Spanish Government. “Do you think we could make a Spanish version?” She asked. And we were off to the races! :-)

First off we forked and had a dig through the code. To make the code work as is we need to find some data!

Finding some data…

To calculate the percentage of the population that has been vaccinated we need to know the population of Spain. A quick google later and we’d found that the population at the start of 2020 was 47,332,614 according to Instituto Nacional de Estadística. Great!

Now we need to find if / where the government is publishing regular vaccination numbers. Fortunately they are! You can find the latest vaccine distribution data on mscbs.gob.es. The numbers seem to be updated every weekday after 6pm local time.

Furthermore the daily data is uploaded as a spreadsheet, for example Informe_Comunicacion_20210305.ods is a timestamped url, you can change that date and get the info for a specific date until the records began. 🥳The spreadsheet gives a pretty thourgh breakdown.

We have all the data we’re going to need for us to publish daily updates. the spreadsheet is a .ods and the python script we forked expects a .csv but we can probably work around that.

Getting up and running

First things first, I’d like to get the code running locally, as is so I can poke around and quickly get feedback on any changes I’m making. The code is a small python script. I have tinkered with python a bit in the past and it is normally quite fun. Python is a very high level language so one can achieve a lot with very little code.

The project has a requirements.txt but when I try to run pip install -r requirements.txt I get an error. So… I don’t have a working pip installation but I can see pip3 in my path so maybe if I run pip3 install -r requirements.txt???

Some considerable time later: ERROR: Failed building wheel for pandas

Okay, that error message took way to long to materialise. I think it was building things from source… I’m going to grab a Docker image with a working pip installation! On autopilot I go to docker-hub and pull an alpine image with python on it.

I spin up a container running bash and once again try pip install... Okay cool, lets do this!

Another loooong wait: ERROR: Failed building wheel for pandas

(╯°□°)╯︵ ┻━┻

Hmmm this isn’t off to a great start. A little googling later I stumble onto a blog post from Seb IT about why, despite having smaller base images, Alpine probably isn’t the best choice for building Python apps. Turns out wheels, a python packaging format aren’t published for Alpine so everything needs to be built from source.

Well, if it’ll get me up and running faster then I’ll go with the suggested Debian slim image…

~> docker run --rm -ti -v $(pwd):/workspace -w /workspace python:3.9-slim bash
root@2f70afd90efd:/workspace# pip install -r requirements.txt

lots of installing here!!!

Successfully installed PySocks-1.7.1 … more packages here

Amazing! 🎉🎉🎉

Perfect, I can run the code locally and get quick feedback when I change things. I can also create a small Dockerfile so I don’t need to manually perform installations every-time.

FROM python:3.9-slim

COPY requirements.txt .

RUN pip install --upgrade pip

RUN pip install -r requirements.txt

Fab.

Time for some coding

Now to make the code work for our use case… Mark did a great job with the original code so it is super easy to hack on. I’ve tweaked it a little but it does the same thing as the original. Lets take a look!

I’m going to refactor the function that builds a string representing the loading bar from the original a little. First things first, declaring some constants, like the population of Spain and the size of the loading bar. We’re Keeping the exact progress bar layout as the original cause it looks great!

def BuildLoadingBars(vaccines_given):
    population_of_spain = 47332614
    # How many blocks in the progress bar
    # 15 works well with Twitter ▓▓▓▓▓░░░░░░░░░░
    bar_total = 15
    perc_per_bar = 100 / bar_total

    perc_rounded = round(((vaccines_given / population_of_spain) * 100), 2)

    # floor / integer division you can't
    # have .5 of a char based loading bar
    solid_bars_to_print = int(perc_rounded // perc_per_bar)
    empty_bars_to_print = int(bar_total - solid_bars_to_print)

    # python lets you multiply chars and I think it is cute
    dataToAdd =  '▓' * solid_bars_to_print
    dataToAdd += '░' * empty_bars_to_print
    dataToAdd += ' ' + str(perc_rounded) + '%\n\n'
    return dataToAdd

Now let’s grab today’s date and build a url to fetch the latest data. The .ods file is timestamped, 20210308 would be the eighth of March 2021 for example.

# calculate today's date and format it to match
# the ods file url from the .gob website
from datetime import date, timedelta
date_to_check = date.today()
today = date_to_check.strftime('%Y%m%d')
baseurl = 'https://www.mscbs.gob.es/......'
full_url = baseurl + today + '.ods'

Fetching and parsing the data… Pandas has a read CSV file which built in which worked in the original as the UK government is publishing CSVs but I’ve pulled in an extension library to read ODS format.

# download and read today's data
# into a pandas data frame 
import pandas
from pandas_ods_reader import read_ods

dataFile = 'data.ods'
urllib.request.urlretrieve(full_url, dataFile)
# Comunicación is a specifc sheet in the .ods dataFile
# This is quite brittle and if anything is going to break...
df = read_ods(dataFile, "Comunicación")

Now lets extract the data we want from the file.

# read the data from the downloaded file also brittle
number_of_vaccines_given = df.loc[19,'Dosis administradas (2)'] 
number_of_people_given_2_doses = df.loc[19,'Nº Personas vacunadas\n(pauta completada)']

number_of_people_given_1_dose = number_of_vaccines_given - number_of_people_given_2_doses

Now lets pull it all together and build a string representing our tweet! The += part feels a bit repetitive but works just fine.

tweet = 'A día '+ str(date_to_check.isoformat())+ '\n\n'
tweet += 'Personas que han recibido la 1ª vacuna: \n'
tweet += BuildLoadingBars(number_of_people_given_1_dose)
tweet += 'Personas que han recibido la 2ª vacuna: \n'
tweet += BuildLoadingBars(number_of_people_given_2_doses)
tweet += 'Según datos de @sanidadgob\n'
tweet += '#VacunaCOVID19 #YoMeVacuno\n'
tweet += ‘https://www.mscbs.gob.es/…'
print(stringToTweet)

Running everything we have put together we get the following output:

A día 2021-03-08

Personas que han recibido la 1ª vacuna: 
▓░░░░░░░░░░░░░░ 7.03%

Personas que han recibido la 2ª vacuna: 
░░░░░░░░░░░░░░░ 2.92%

Según datos de @sanidadgob
#VacunaCOVID19 #YoMeVacuno
https://www.mscbs.gob.es/profesi...

Awesome! I think I’m going to leave it there for now. You can see the twitter account which is giving weekday updates at @VacunaCovidES. I still need to automate it but I’ve applied for a twitter application / developer account and been approved so more fun for later. :-)

Thanks again to Mark Williams-Cook for the original code. Thanks to Lidia Infante for the Spanish language and data hunting help! And thank you for reading! :D

See Also