> ## Content Index > Fetch the complete content index at: https://www.bambooweekly.com/llms.txt > Use this file to discover other available public pages before exploring further. # Bamboo Weekly #69: Election participation (solutions) - URL: https://www.bambooweekly.com/bw-69-election-participation-solution/ - Published: 2024-06-18T10:02:19.000Z - Updated: 2026-09-02T14:02:26.000Z - Description: Get better at: Excel, DuckDB, grouping, speed optimization, pivot tables, and window functions. - Author: Reuven M. Lerner - Tags: excel, duckdb, grouping, speed-optimization, pivot-table, window-functions *\[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](https://duckdb.org/2021/05/14/sql-on-pandas.html?ref=bambooweekly.com). 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](https://www.idea.int/data-tools/data/voter-turnout-database?ref=bambooweekly.com) 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 pd ``` Next, because it’s an Excel file, I’ll use “[read\_excel](https://www.bambooweekly.com/pandas-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](https://www.bambooweekly.com/pandas-memory-usage/)” on our data frame, which returns the number of bytes used by each column. We can then use “[sum](https://www.bambooweekly.com/pandas-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: object ``` Ah, 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](https://www.bambooweekly.com/pandas-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: object ``` ### Using 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](https://www.bambooweekly.com/pandas-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](https://www.bambooweekly.com/pandas-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](https://www.bambooweekly.com/pandas-nlargest/)”, but I decided here to use a combination of “[sort\_values](https://www.bambooweekly.com/pandas-sort-values/)” and “[head](https://www.bambooweekly.com/pandas-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: float64 ``` Um, 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](https://www.idea.int/data-tools/data/voter-turnout-database?ref=bambooweekly.com) 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](https://ipython.readthedocs.io/en/stable/interactive/magics.html?ref=bambooweekly.com#magic-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 duckdb ``` However, I also needed to install the Python bindings for it. That was equally easy: ``` pip install duckdb ``` Inside of my Jupyter notebook, I then wrote: ``` import duckdb ``` Once 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.898000 ``` And how long does DuckDB take to do this calculation? 2.22 ms on average, about twice as long as Pandas. ### Using Pandas, calculate the mean and median percentage of invalid votes in countries with compulsory vs. non-compulsory voting policies. Do the same with DuckDB. How long did each query take? I wondered whether compulsory voting raises the number of invalid votes. After all, they might force me to go to the voting booth, but they can’t force (I think) to vote for a particular candidate. What if I completely deface it while I’m behind the partition? I’ll count as having voted, but no one will know that I didn’t. I asked to find both the mean and median, since we know that a large outlier can skew the mean, but can’t do that to the median. To make this calculation, we’ll need to use “groupby” again. This time, we want one result for each unique value in “Compulsory voting”, which is normally “Yes” or “No”. I’ll want to perform my calculations on the “Invalid votes” column. And because I want to calculate both mean and median, I’ll need to use “[agg](https://www.bambooweekly.com/pandas-agg/)” to name two different aggregation methods: ``` ( df .groupby('Compulsory voting') ['Invalid votes'] .agg(['mean', 'median']) ) ``` I got a warning from Pandas telling me that the current default of “observed=False” is changing to be “observed=True”, and that I should state what I want to avoid getting a warning. It didn’t make a difference in my calculations, but I added the keyword argument anyway: ``` ( df .groupby('Compulsory voting', observed=True) ['Invalid votes'] .agg(['mean', 'median']) ) ``` I got the following results: ``` mean median Compulsory voting No 2.907208 1.80 Yes 5.933224 3.86 ``` In other words, countries with compulsory voting have more than twice the number of invalid ballots as those without. It took Pandas 688 µs to perform this calculation. What about DuckDB? Here was my query: ``` duckdb.query('''SELECT "Compulsory voting", MEAN("Invalid Votes"), MEDIAN("Invalid Votes") FROM df GROUP BY "Compulsory voting"''') ``` We can put however many things we want in an SQL SELECT statement. Here, I asked for the Compulsory voting (yes/no) value, mandatory because it’s in the GROUP BY statement. I then asked for the MEAN and MEDIAN of the “Invalid Votes” column. I got the following results: ``` ┌───────────────────┬───────────────────────┬─────────────────────────┐ │ Compulsory voting │ mean("Invalid Votes") │ median("Invalid Votes") │ │ enum('no', 'yes') │ double │ double │ ├───────────────────┼───────────────────────┼─────────────────────────┤ │ NULL │ 5.498333333333332 │ 4.87 │ │ No │ 2.907207977207976 │ 1.8 │ │ Yes │ 5.933223965763203 │ 3.86 │ └───────────────────┴───────────────────────┴─────────────────────────┘ ``` Notice that in this version, I got a third row, for NULL values. I believe that’s because Pandas aggregation methods remove NA/NaN values by default. How fast did DuckDB process these? It took 2.18 ms. Given that 1 ms is 1,000 µs, this shows that it took DuckDB more than three times as long to perform this calculation. Are you getting the sense that DuckDB is very slow? I certainly had that feeling, at least to some degree. But remember that this is a very small data frame, and we’re doing simple calculations. The overhead associated with a DuckDB query might be much bigger than the time needed to do the actual calculation. I’ll be doing some experiments and comparisons with DuckDB and a much larger data set to investigate this further. ### For countries that vote in the EU Parliament, in how many countries is there higher mean VAP turnout in EU elections than parliamentary elections? Calculate this in both Pandas and DuckDB. Which is faster? Right now, as I write this, [the European Union is holding its parliamentary elections](https://en.wikipedia.org/wiki/2024%5FEuropean%5FParliament%5Felection?ref=bambooweekly.com). In the United States, more people vote in federal elections than in state and local elections. Is it possible that in the European Union, there are countries where more people vote for the EU parliament than for their own countries’ parliamentary elections? To do this, we’ll first need to get the data into a form where we have country names along the rows, election types along the columns, and the mean VAP turnout for each country-election combination in each cell. This sounds like a pivot table, and we can indeed create one: ``` ( df .pivot_table(index='Country', columns='Election Type', values='VAP Turnout', observed=False) ) ``` This gives us a data frame with 201 rows (one for each country) and 3 columns (one for each type of election). I then use “[drop](https://www.bambooweekly.com/pandas-drop/)” to get rid of the presidential election column: ``` ( df .pivot_table(index='Country', columns='Election Type', values='VAP Turnout', observed=False) .drop('Presidential', axis='columns') ) ``` I then use “[dropna](https://www.bambooweekly.com/pandas-dropna/)” to remove rows in which “EU Parliament” has NA/NaN values, since we only care about European countries in this query: ```python ( df .pivot_table(index='Country', columns='Election Type', values='VAP Turnout', observed=False) .drop('Presidential', axis='columns') .dropna(subset='EU Parliament') ) ``` I’m now left with two columns. I use “[diff](https://www.bambooweekly.com/pandas-diff/)” to subtract the left column from the right one, leaving me with the difference under “Parliamentary”, which I then select with \[\]: ``` ( df .pivot_table(index='Country', columns='Election Type', values='VAP Turnout', observed=False) .drop('Presidential', axis='columns') .dropna(subset='EU Parliament') .diff(axis='columns') ['Parliamentary'] ) ``` Finally, I invoke “[lt](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.lt.html?ref=bambooweekly.com)” to find the rows that are less than 0, and calculate their mean (since True is 1 and False is 0): ``` ( df .pivot_table(index='Country', columns='Election Type', values='VAP Turnout', observed=False) .drop('Presidential', axis='columns') .dropna(subset='EU Parliament') .diff(axis='columns') ['Parliamentary'] .lt(0) .mean() ) ``` The result? 0! In zero countries we have a higher voting percentage for EU parliamentary elections than national parliamentary elections. Which isn’t a huge surprise. This query took Pandas 5.39 ms to calculate. What about DuckDB? It’s a bit complex, but it works. I’m a big fan of CTEs (common table expressions), similar in some ways to the “[assign](https://www.bambooweekly.com/pandas-assign/)” method as used in Pandas method chaining. The idea is that you issue a query with SELECT, and put the results into a temporary, named table that disappears when the query is over. But before the query ends, you perform another query that has access to the previous one — either another CTE, or a regular ol’ SELECT that can retrieve from one or more of the previous tables. We start by calculating the mean turnout for EU and parliamentary elections: ``` sql = ''' SELECT Country, "Election Type", AVG("VAP Turnout") AS mean_vap_turnout FROM df WHERE "Election Type" IN ('EU Parliament', 'Parliamentary') GROUP BY Country, "Election Type"''' duckdb.query(sql) ``` We can then wrap that in a CTE using the WITH keyword. Notice how I can then use SELECT \* to retrieve from this newly created, temporary table. Also notice that I’m grouping by two columns, giving me something akin to a two-level multi-index, which can be rejiggered into a pivot table: ``` sql = ''' WITH mean_turnout AS ( SELECT Country, "Election Type", AVG("VAP Turnout") AS mean_vap_turnout FROM df WHERE "Election Type" IN ('EU Parliament', 'Parliamentary') GROUP BY Country, "Election Type" ) SELECT * FROM mean_turnout ''' duckdb.query(sql) ``` Now let’s use a second CTE (“pivoted”) based on the first one (“mean\_turnout”): ``` sql = ''' WITH mean_turnout AS ( SELECT Country, "Election Type", AVG("VAP Turnout") AS mean_vap_turnout FROM df WHERE "Election Type" IN ('EU Parliament', 'Parliamentary') GROUP BY Country, "Election Type" ), pivoted AS ( SELECT Country, MAX(CASE WHEN "Election Type" = 'EU Parliament' THEN mean_vap_turnout END) AS mean_eu_parliament, MAX(CASE WHEN "Election Type" = 'Parliamentary' THEN mean_vap_turnout END) AS mean_parliamentary FROM mean_turnout GROUP BY Country ) SELECT * FROM pivoted ''' duckdb.query(sql) ``` Once we have our “mean\_turnout” CTE defined, we can create our second one. In this, we run a GROUP BY query per country, getting (for each country) the EU and national parliamentary values. The result is a three-column table, in which we have each country, its EU participation, and its national participation — a pivot table. Finally, we count all of the rows in which mean\_eu\_parliament is greater than mean\_parliamentary: ``` sql = ''' WITH mean_turnout AS ( SELECT Country, "Election Type", AVG("VAP Turnout") AS mean_vap_turnout FROM df WHERE "Election Type" IN ('EU Parliament', 'Parliamentary') GROUP BY Country, "Election Type" ), pivoted AS ( SELECT Country, MAX(CASE WHEN "Election Type" = 'EU Parliament' THEN mean_vap_turnout END) AS mean_eu_parliament, MAX(CASE WHEN "Election Type" = 'Parliamentary' THEN mean_vap_turnout END) AS mean_parliamentary FROM mean_turnout GROUP BY Country ) SELECT COUNT(*) AS num_countries_higher_eu_turnout FROM pivoted WHERE mean_eu_parliament > mean_parliamentary; ''' duckdb.query(sql) ``` The result? Zero, just as with Pandas. And the timing? 2.3 ms, about half the time that Pandas took. In other words: When we have a complex query that involves a bunch of chaining and grouping, DuckDB does pretty well, even on a small dataset. ### Has the number of countries with mandatory voting risen or fallen over the years? Calculate the percentage change for each decade. How does Pandas compare with DuckDB in performance? Finally, I wanted to know if the number of countries with mandatory voting has risen or fallen, when we look at the numbers per decade. For starters, I grabbed only those countries with compulsory voting: ``` ( df .loc[lambda df_: df_['Compulsory voting'] == 'Yes'] ) ``` I then used my new best friend, “[Grouper](https://www.bambooweekly.com/pandas-grouper/)”, indicating that I wanted to group by “10YE” — every 10 year-end periods — looking at the “Year” column. I wanted to count the number of results in the “Compulsory voting” column: ``` ( df .loc[lambda df_: df_['Compulsory voting'] == 'Yes'] .groupby(pd.Grouper(key='Year', freq='10YE'))['Compulsory voting'].count() ) ``` This gave me the raw numbers. I wanted to know the percentage change. For that, I invoked “[pct\_change](https://www.bambooweekly.com/pandas-pct-change/)”: ``` ( df .loc[lambda df_: df_['Compulsory voting'] == 'Yes'] .groupby(pd.Grouper(key='Year', freq='10YE'))['Compulsory voting'].count() .pct_change() ) ``` The results: ``` Year 1945-12-31 NaN 1955-12-31 11.833333 1965-12-31 0.194805 1975-12-31 -0.217391 1985-12-31 0.444444 1995-12-31 0.076923 2005-12-31 0.071429 2015-12-31 0.083333 2025-12-31 -0.200000 Freq: 10YE-DEC, Name: Compulsory voting, dtype: float64 ``` We see a small increase in the number of countries mandating voting in each decade, It looks like the 1960s through the 1980s were the heyday for mandatory-voting laws to be put on the books. It stayed somewhat stagnant for another few decades before falling by 20 percent in the last decade or so. It took Pandas 4.35 ms to perform this query. How would we do this in DuckDB? I was particularly curious (or masochistic), because the time-based grouping and window functions are quite powerful and succinct in Pandas. Let’s start with a first CTE, keeping only those rows where mandatory voting is “Yes”: ``` sql = ''' WITH filtered_data AS ( SELECT * FROM df WHERE "Compulsory voting" = 'Yes' ) SELECT * from filtered_data ''' duckdb.query(sql) ``` Now that we’ve filtered them, let’s count how many countries had mandatory voting per decade: ``` sql = ''' WITH filtered_data AS ( SELECT * FROM df WHERE "Compulsory voting" = 'Yes' ), grouped_data AS ( SELECT DATE_TRUNC('decade', "Year") AS decade, COUNT(*) AS count FROM filtered_data GROUP BY decade ) SELECT * from grouped_data ''' duckdb.query(sql) ``` We use [DATE\_TRUNC](https://duckdb.org/docs/sql/functions/date?ref=bambooweekly.com#date%5Ftruncpart-date) here, one of DuckDB’s many datetime functions, to retrieve the decade from the “Year” column. Note that this means the results will be slightly different than what we got with Pandas. That’s because Grouper in Pandas starts with the first value we have, and doesn’t support a “decade” frequency code. So the Pandas data will start in 1945, whereas the DuckDB data will start with 1950. We get the following results: ``` ┌────────────┬───────┐ │ decade │ count │ │ date │ int64 │ ├────────────┼───────┤ │ 2000-01-01 │ 131 │ │ 2020-01-01 │ 53 │ │ 1960-01-01 │ 77 │ │ 1940-01-01 │ 37 │ │ 1990-01-01 │ 113 │ │ 1980-01-01 │ 112 │ │ 2010-01-01 │ 121 │ │ 1950-01-01 │ 87 │ │ 1970-01-01 │ 86 │ └────────────┴───────┘ ``` Right, but I didn’t want the numbers. I wanted the percentage change. For that, we’ll use the [LAG](https://duckdb.org/docs/sql/window%5Ffunctions?ref=bambooweekly.com#lagexpr-offset-default-ignore-nulls) window function, which lets us add a new column to our table, the value in the previous row: ``` sql = ''' WITH filtered_data AS ( SELECT * FROM df WHERE "Compulsory voting" = 'Yes' ), grouped_data AS ( SELECT DATE_TRUNC('decade', "Year") AS decade, COUNT(*) AS count FROM filtered_data GROUP BY decade ), count_vs_previous AS ( SELECT decade, count, LAG(count) OVER (ORDER BY decade) AS prev_count FROM grouped_data ) SELECT * from count_vs_previous ''' duckdb.query(sql) ``` We get the following: ``` ┌────────────┬───────┬────────────┐ │ decade │ count │ prev_count │ │ date │ int64 │ int64 │ ├────────────┼───────┼────────────┤ │ 1940-01-01 │ 37 │ NULL │ │ 1950-01-01 │ 87 │ 37 │ │ 1960-01-01 │ 77 │ 87 │ │ 1970-01-01 │ 86 │ 77 │ │ 1980-01-01 │ 112 │ 86 │ │ 1990-01-01 │ 113 │ 112 │ │ 2000-01-01 │ 131 │ 113 │ │ 2010-01-01 │ 121 │ 131 │ │ 2020-01-01 │ 53 │ 121 │ └────────────┴───────┴────────────┘ ``` Now we’ll calculate the difference: ``` # %%timeit sql = ''' WITH filtered_data AS ( SELECT * FROM df WHERE "Compulsory voting" = 'Yes' ), grouped_data AS ( SELECT DATE_TRUNC('decade', "Year") AS decade, COUNT(*) AS count FROM filtered_data GROUP BY decade ), count_vs_previous AS ( SELECT decade, count, LAG(count) OVER (ORDER BY decade) AS prev_count FROM grouped_data ) SELECT decade, (count - prev_count) / prev_count AS pct_change FROM count_vs_previous WHERE prev_count IS NOT NULL; ''' duckdb.query(sql) ``` And we can get our percentage change: ``` ┌────────────┬──────────────────────┐ │ decade │ pct_change │ │ date │ double │ ├────────────┼──────────────────────┤ │ 1950-01-01 │ 1.3513513513513513 │ │ 1960-01-01 │ -0.11494252873563218 │ │ 1970-01-01 │ 0.11688311688311688 │ │ 1980-01-01 │ 0.3023255813953488 │ │ 1990-01-01 │ 0.008928571428571428 │ │ 2000-01-01 │ 0.1592920353982301 │ │ 2010-01-01 │ -0.07633587786259542 │ │ 2020-01-01 │ -0.5619834710743802 │ └────────────┴──────────────────────┘ ``` The data is slightly different, but we can see that indeed there was (mostly) steady growth in the 1950s, 1970s, and 1980s, with a bit of a backslide in the 1960s that wasn’t reflected in the Pandas data. But then there was a bit of stagnation and (again) a backslide in the last few years in mandatory voting. How long did it take to get this output? 3.2 ms on average, about 25 percent faster than Pandas. I must admit that as I finished the analysis here, my queries didn’t quite match what I had asked for. That’s because I had asked how many *countries* had mandatory voting in each decade. Instead, I ended up querying how many *elections* had mandatory voting in each decade, which is similar but not the same. That is: A country that has elections every two years would add 5 to the count in a decade, whereas a country that has elections every five years would add 2. However, this still might be a useful metric, and upon deeper thought, calculating the number of countries with mandatory voting per decade might not be possible with the data we have in hand. So, did we learn? - DuckDB integrates seamlessly into Pandas. Querying a Pandas data frame as if it were an SQL table is surprisingly natural. - Even on small data sets, it has faster performance on complex queries. - Pandas is better at simple queries, including grouping, on small data sets. I have to check it with larger ones to see how they compare. - Even when the SQL is longer and more complex than the Pandas methods, DuckDB can still be faster. - Pandas query syntax is, in my opinion, both more compact and richer. - The future of both Pandas and DuckDB is pretty bright! What did you think? The Jupyter notebook I used is here: [https://drive.google.com/file/d/1FvN1OPd-IA6cENENlM\_3z0m1du1IsINc/view?usp=sharing](https://drive.google.com/file/d/1FvN1OPd-IA6cENENlM%5F3z0m1du1IsINc/view?usp=sharing&ref=bambooweekly.com) I’ll be back next week with more Pandas puzzles based on current events. Reuven