BW #69: Election participation (solution)
[Another late issue! Lots of training and meetings about training today. Also, this issue took a lot of time to write and analyze. But if you’ve always been curious about DuckDB, how its queries stack up to Pandas, and where it’s faster and slower — I think that the wait was worthwhile, with the longest analysis and description section I’ve ever written, at 4,500 words. Thanks, as always, for your support!]
The last week has been full of election news, from Donald Trump’s new status as a convicted felon, to the upcoming elections in the United Kingdom, to the election of a new president of Mexico, to weaker support in India for Narendra Modi in what will likely be his third term as prime minister.
Rather than look at one particular country’s election data, I thought it might be appropriate to analyze data across a large number of countries. Moreover, I decided that this would be a good time to look into DuckDB, an in-memory analytical database that has been growing steadily in popularity over the last few years. It was mentioned on a number of occasions at PyCon US, and I thought that this might give us a good opportunity to look at DuckDB, comparing its syntax, capabilities, and speed with what we have in Pandas.
Traditional relational databases divide tasks between a server (where the data is stored, and where queries are executed) and a client (where the queries are formulated). In the simplest case, you have two processes, one server and one client. However, you can have multiple clients submitting queries to a single server. And it’s now common to have multiple servers working together. You can thus have a large number of processes, often located on different computers, communicating with one another.
By contrast, DuckDB doesn’t even consume one process. It’s an in-process database, meaning that it is loaded as a library into whatever program wants to use it, without any external connections. Granted, that process will then grow in size to accommodate DuckDB’s functionality, as well as the data that you’re analyzing. But you won’t have the overhead of having to transfer data across processes.
Moreover, DuckDB works with a variety of in-memory data structures, including Pandas data frames. You can thus load a CSV or Excel file into DuckDB — but you can also perform SQL “select” queries directly from a data frame.
A quick tutorial on DuckDB is here: https://duckdb.org/2021/05/14/sql-on-pandas.html. If you have any experience with SQLite or PostgreSQL, you’ll likely feel at home with DuckDB’s functionality and syntax. And if you haven’t ever used SQL before, then DuckDB isn’t a bad way to start.
Data and five questions
This week's data comes from International IDEA's voter turnout database, whose home page is here:
https://www.idea.int/data-tools/data/voter-turnout-database
Go to that page, and click on the "Export data" button. This will download an Excel file. We're interested in the first ("All") sheet in the document.
I gave you five questions and tasks this week, many of which asked you to perform the same task twice — once with Pandas, and a second time with DuckDB. The point was both to compare the syntax and to get a sense of whether DuckDB would offer any performance help. To some degree, I knew going into these tests that they would be unfair, because the data set is relatively small; I will soon be doing some tests on a much larger data set, to see how much that is affected.
Here are my questions for this week. As always, a link to my Jupyter notebook is at the end of the newsletter.
Read the Excel file into Pandas as a data frame. However, turn the "Year" column into a datetime column, the numeric columns into float (removing "%" and "," characters), and the "Election type" and "Compulsory voting" columns into categories. How much memory do we save in making such changes?
Let’s start off by loading Pandas:
import pandas as pdNext, because it’s an Excel file, I’ll use “read_excel” to load it into Pandas:
filename = 'idea_export_voter_turnout_database_region.xlsx'
df = pd.read_excel(filename, sheet_name='All')Note that I pass the “sheet_name” keyword argument, allowing me to indicate which sheet (or multiple sheets, if I want) I would like to get back as a data frame. It might seem like I’m somehow asking for all of the sheets in the Excel file to be returned as a data frame, but actually, the first sheet has a name of “All”. We thus get back a data frame containing 3,649 rows and 13 columns.
How much memory does this take up? We can find out by invoking “memory_usage” on our data frame, which returns the number of bytes used by each column. We can then use “sum” to find the total amount of memory used by the data frame.
Note that we need to pass the “deep=True” keyword argument in order to ensure that Python-object columns are calculated correctly:
df.memory_usage(deep=True).sum()The result? 2,622,423 bytes, or about 2.5 MB.
I’ve found that data read from a CSV file usually needs some help and hints to determine the dtype of at least a few columns. Excel files, by contrast, support a rich set of data types, most of which map quite naturally to the dtypes in Pandas. Let’s check to see what dtypes we got:
Country object
ISO2 object
ISO3 object
Election Type object
Year object
Voter Turnout object
Total vote object
Registration object
VAP Turnout object
Voting age population object
Population object
Invalid votes object
Compulsory voting object
dtype: objectAh, I see — they are all of “object” dtypes, meaning that they were all imported as strings. That’s unusual, and I’m guessing that this is because the data was originally in a textual format (e.g., CSV), and was imported into Excel without doing any cleaning or setting of types.
It’s tempting to say that we can resolve this situation by just passing a few arguments to “read_excel”, telling it to parse the “Year” column as a date, and many of the rest as floats. But that’s sadly impossible, because so many of the number-wannabe columns contain dollar signs and commas, which aren’t legal there.
So we’ll have to go through a bunch of columns, removing non-digit characters, and then turning them into float columns. (Why float? Because they might well contain NaN values. Although as we saw last week, we could use nullable types…) That’s annoying, but it works.
I’ll thus go back to loading the Excel file, but with an important addition to our call to “read_excel”:
df = pd.read_excel(filename,
sheet_name='All',
parse_dates=['Year'])Next, I’ll iterate over a bunch of columns, invoking “str.replace” on each to remove commas and percent signs, followed by “astype(‘int’)”, then assigned back to the original variable.
for one_colname in convert_to_float:
df[one_colname] = (df
[one_colname]
.str.replace(r'[,%]', '', regex=True)
.astype('float')
)Finally, I took two columns that contained (repeated) text, and made each a bit more efficient by turning them into Pandas categories.
convert_to_category = ['Election Type', 'Compulsory voting']
for one_colname in convert_to_category:
df[one_colname] = df[one_colname].astype('category')After all this, which made the data frame more robust, have we also managed to make it smaller? I again run
df.memory_usage(deep=True).sum()The new size is 829,452 bytes, or about 31 percent of the original size. Which means that getting our data into good dtypes not only ensures we can perform the calculations we want and need, but that we’ve saved a lot of memory, too.
Here are the final dtypes:
Country object
ISO2 object
ISO3 object
Election Type category
Year datetime64[ns]
Voter Turnout float64
Total vote float64
Registration float64
VAP Turnout float64
Voting age population float64
Population float64
Invalid votes float64
Compulsory voting category
dtype: objectUsing Pandas, which five countries with non-compulsory voting has the highest mean VAP turnout? Now use DuckDB to calculate the same thing. How long did each query take?
I’ve often heard people complain that too few people vote in elections. I was thus curious to know in which countries the greatest proportion of voters do actually vote. However, I had to remove countries in which there is mandatory voting, since that would clearly skew the numbers.
To calculate this in Pandas, we’ll first remove rows referring to countries in which there is compulsory voting. I did this using a combination of “loc” and “lambda”, looking for rows in which the “Compulsory voting” column had a “No” value:
(
df
.loc[lambda df_: df_['Compulsory voting'] == 'No']
)Next, I performed a “groupby” operation, asking for the mean value of “VAP Turnout” for each unique value in the “Country” column:
(
df
.loc[lambda df_: df_['Compulsory voting'] == 'No']
.groupby('Country')['VAP Turnout'].mean()
)This produced a series of 185 elements, with country names as the index and the mean voting percentage for each country as the values. However, I was only interested in the five countries with the greatest percentage. One option would be to use “nlargest”, but I decided here to use a combination of “sort_values” and “head”. Note that I had to indicate that I wanted the sort to be in descending order:
(
df
.loc[lambda df_: df_['Compulsory voting'] == 'No']
.groupby('Country')['VAP Turnout'].mean()
.sort_values(ascending=False)
.head(5)
)The result was:
Country
Croatia 315.038800
North Macedonia, Republic of 293.369565
Somalia 129.466667
Cook Islands 100.640000
Viet Nam 96.898000
Name: VAP Turnout, dtype: float64Um, wait a second. How can there be 315 percent voter turnout? We might be aiming for high turnout, but that seems a bit … high, no?
The FAQ at https://www.idea.int/data-tools/data/voter-turnout-database indicates that VAP (“voting age population”), along with turnout and other numbers, are often estimates, and that they aren’t always updated in sync. That said, we can probably assume that voter turnout in Croatia and Maceconia is quite high, even if not quite 315 percent.
What about the next three? Well, Somalia isn’t exactly a bastion of open democracy. The Cook Islands have a population of 15,000 people, so it seems likely to me that they have high turnout rates. And Vietnam … it’s a one-party state, so I’m not sure who people are voting for, but it’s good to know (I guess) that they’re voting in high percentages.
Bottom line, having a very high percentage of the public coming to vote doesn’t necessarily mean that you’re a model democracy.
How long did it take Pandas to perform this query? I use the “%%timeit” magic command in Jupyter, and found that it took, on average, 1.04 ms to execute my query.
What about DuckDB? How do we even use DuckDB, for that matter?
DuckDB is, as I wrote above, an in-memory relational database that uses SQL. It uses standard SQL queries to create tables, update them, and retrieve from them. However, the tables all reside in memory. Moreover, it’s a columnar database, meaning that (like Pandas) it is structured primarily along columns, rather than along rows, as traditional databases did. It’s not meant for high-speed transactions; rather, DuckDB is designed for high-performance, in-process analysis of data.
Because DuckDB is in the same process, it has access to all of the objects in memory. A nice side effect, particularly useful to us, is that it can thus query a Pandas data frame as if it were a database table. Pandas queries are often similar to SQL, and I’m sure that some database veterans have long wished that they could use SQL to query their data frames. Well, your wish has come true!
You’ll first have to install DuckDB on your computer. On my Mac, I use Homebrew for open-source installations. I thus added it with
brew install duckdbHowever, I also needed to install the Python bindings for it. That was equally easy:
pip install duckdbInside of my Jupyter notebook, I then wrote:
import duckdbOnce that was done, I was able to query my data frame:
duckdb.query('''SELECT Country, mean("VAP Turnout") as mvt
FROM df
WHERE "Compulsory voting" = 'No'
GROUP BY Country
ORDER BY mvt DESC
LIMIT 5''')The above SQL query basically does what my Pandas query did:
- I start with SELECT, indicating what columns and values I want to get back. I asked for the country name and the mean of the “VAP Turnout” column, aliased to the name “mvt”. The alias will make it easier to order by that column.
- I indicate that I want to retrieve from “df”. That’s right; I just name the variable in which my data frame is located, and DuckDB takes care of the rest. Wow!
- I add a condition, indicating that I only want rows without compulsory voting. Notice that SQL, unlike Python, distinguishes between single quotes and double quotes: Single quotes are for strings, whereas double quotes allow us to reference tables and columns whose names contain spaces.
- We then group by the country, indicating that we want a result for each unique value in the Country column
- We then order the results by the “mvt” alias we previously created, in descending order
- We only want the top 5 results.
Those results are:
┌──────────────────────────────┬────────────────────┐
│ Country │ mvt │
│ varchar │ double │
├──────────────────────────────┼────────────────────┤
│ Croatia │ 315.03880000000004 │
│ North Macedonia, Republic of │ 293.3695652173913 │
│ Somalia │ 129.46666666666667 │
│ Cook Islands │ 100.64 │
│ Viet Nam │ 96.898 │
└──────────────────────────────┴────────────────────┘We basically got the same results, although we are seeing some slight differences in the float precision. Notice that we get the results back as a DuckDB table, as in all databases. Want to get back a Pandas data frame? No problem; just call “to_df”:
Country mvt
0 Croatia 315.038800
1 North Macedonia, Republic of 293.369565
2 Somalia 129.466667
3 Cook Islands 100.640000
4 Viet Nam 96.898000And how long does DuckDB take to do this calculation? 2.22 ms on average, about twice as long as Pandas.
