Trendy DataFrames in Python: A Fingers-On Tutorial with Polars and DuckDB

If with Python for knowledge, you will have in all probability skilled the frustration of ready minutes for a Pandas operation to complete.

At first, all the pieces appears high-quality, however as your dataset grows and your workflows turn out to be extra complicated, your laptop computer immediately feels prefer it’s getting ready for lift-off.

A few months in the past, I labored on a venture analyzing e-commerce transactions with over 3 million rows of information.

It was a fairly attention-grabbing expertise, however more often than not, I watched easy groupby operations that usually ran in seconds immediately stretch into minutes.

At that time, I spotted Pandas is wonderful, however it’s not at all times sufficient.

This text explores fashionable alternate options to Pandas, together with Polars and DuckDB, and examines how they’ll simplify and enhance the dealing with of enormous datasets.

For readability, let me be upfront about a couple of issues earlier than we start.

This text just isn’t a deep dive into Rust reminiscence administration or a proclamation that Pandas is out of date.

As a substitute, it’s a sensible, hands-on information. You will notice actual examples, private experiences, and actionable insights into workflows that may prevent time and sanity.

Why Pandas Can Really feel Sluggish

Again once I was on the e-commerce venture, I keep in mind working with CSV information over two gigabytes, and each filter or aggregation in Pandas usually took a number of minutes to finish.

Throughout that point, I might stare on the display screen, wishing I may simply seize a espresso or binge a couple of episodes of a present whereas the code ran.

The primary ache factors I encountered have been pace, reminiscence, and workflow complexity.

Everyone knows how giant CSV information devour huge quantities of RAM, typically greater than what my laptop computer may comfortably deal with. On prime of that, chaining a number of transformations additionally made code tougher to take care of and slower to execute.

Polars and DuckDB tackle these challenges in several methods.

Polars, in-built Rust, makes use of multi-threaded execution to course of giant datasets effectively.

DuckDB, alternatively, is designed for analytics and executes SQL queries with no need you to load all the pieces into reminiscence.

Principally, every of them has its personal superpower. Polars is the speedster, and DuckDB is type of just like the reminiscence magician.

And one of the best half? Each combine seamlessly with Python, permitting you to boost your workflows with out a full rewrite.

Setting Up Your Atmosphere

Earlier than we begin coding, make certain your surroundings is prepared. For consistency, I used Pandas 2.2.0, Polars 0.20.0, and DuckDB 1.9.0.

Pinning variations can prevent complications when following tutorials or sharing code.

pip set up pandas==2.2.0 polars==0.20.0 duckdb==1.9.0

In Python, import the libraries:

import pandas as pd
import polars as pl
import duckdb
import warnings
warnings.filterwarnings("ignore")

For instance, I’ll use an e-commerce gross sales dataset with columns corresponding to order ID, product ID, area, nation, income, and date. You possibly can obtain comparable datasets from Kaggle or generate artificial knowledge.

Loading Knowledge

Loading knowledge effectively units the tone for the remainder of your workflow. I keep in mind a venture the place the CSV file had practically 5 million rows.

Pandas dealt with it, however the load occasions have been lengthy, and the repeated reloads throughout testing have been painful.

It was a kind of moments the place you would like your laptop computer had a “quick ahead” button.

Switching to Polars and DuckDB fully improved all the pieces, and immediately, I may entry and manipulate the info virtually immediately, which actually made the testing and iteration processes much more pleasing.

With Pandas:

df_pd = pd.read_csv("gross sales.csv")
print(df_pd.head(3))

With Polars:

df_pl = pl.read_csv("gross sales.csv")
print(df_pl.head(3))

With DuckDB:

con = duckdb.join()
df_duck = con.execute("SELECT * FROM 'gross sales.csv'").df()
print(df_duck.head(3))

DuckDB can question CSVs straight with out loading your complete datasets into reminiscence, making it a lot simpler to work with giant information.

Filtering Knowledge

The issue right here is that filtering in Pandas may be sluggish when coping with thousands and thousands of rows. I as soon as wanted to research European transactions in an enormous gross sales dataset. Pandas took minutes, which slowed down my evaluation.

With Pandas:

filtered_pd = df_pd[df_pd.region == "Europe"]

Polars is quicker and may course of a number of filters effectively:

filtered_pl = df_pl.filter(pl.col("area") == "Europe")

DuckDB makes use of SQL syntax:

filtered_duck = con.execute("""
    SELECT *
    FROM 'gross sales.csv'
    WHERE area = 'Europe'
""").df()

Now you may filter via giant datasets in seconds as an alternative of minutes, leaving you extra time to deal with the insights that actually matter.

Aggregating Giant Datasets Shortly

Aggregation is usually the place Pandas begins to really feel sluggish. Think about calculating whole income per nation for a advertising and marketing report.

In Pandas:

agg_pd = df_pd.groupby("nation")["revenue"].sum().reset_index()

In Polars:

agg_pl = df_pl.groupby("nation").agg(pl.col("income").sum())

In DuckDB:

agg_duck = con.execute("""
    SELECT nation, SUM(income) AS total_revenue
    FROM 'gross sales.csv'
    GROUP BY nation
""").df()

I keep in mind working this aggregation on a ten million-row dataset. In Pandas, it took practically half an hour. Polars accomplished the identical operation in beneath a minute.

The sense of reduction was virtually like ending a marathon and realizing your legs nonetheless work.

Becoming a member of Datasets at Scale

Becoming a member of datasets is a kind of issues that sounds easy till you might be truly knee-deep within the knowledge.

In actual tasks, your knowledge normally lives in a number of sources, so it’s important to mix them utilizing shared columns like buyer IDs.

I discovered this the laborious manner whereas engaged on a venture that required combining thousands and thousands of buyer orders with an equally giant demographic dataset.

Every file was large enough by itself, however merging them felt like attempting to pressure two puzzle items collectively whereas your laptop computer begged for mercy.

Pandas took so lengthy that I started timing the joins the identical manner individuals time how lengthy it takes their microwave popcorn to complete.

Spoiler: the popcorn received each time.

Polars and DuckDB gave me a manner out.

With Pandas:

merged_pd = df_pd.merge(pop_df_pd, on="nation", how="left")

Polars:

merged_pl = df_pl.be a part of(pop_df_pl, on="nation", how="left")

DuckDB:

merged_duck = con.execute("""
    SELECT *
    FROM 'gross sales.csv' s
    LEFT JOIN 'pop.csv' p
    USING (nation)
""").df()

Joins on giant datasets that used to freeze your workflow now run easily and effectively.

Lazy Analysis in Polars

One factor I didn’t admire early in my knowledge science journey was how a lot time will get wasted whereas working transformations line by line.

Polars approaches this in a different way.

It makes use of a way known as lazy analysis, which basically waits till you will have accomplished defining your transformations earlier than executing any operations.

It examines your complete pipeline, determines probably the most environment friendly path, and executes all the pieces concurrently.

It’s like having a good friend who listens to your complete order earlier than strolling to the kitchen, as an alternative of 1 who takes every instruction individually and retains going backwards and forwards.

This TDS article indepthly explains lazy analysis.

Right here’s what the stream seems to be like:

Pandas:

df = df[df["amount"] > 100]
df = df.groupby("section").agg({"quantity": "imply"})
df = df.sort_values("quantity")

Polars Lazy Mode:

import polars as pl

df_lazy = (
    pl.scan_csv("gross sales.csv")
      .filter(pl.col("quantity") > 100)
      .groupby("section")
      .agg(pl.col("quantity").imply())
      .type("quantity")
)

outcome = df_lazy.accumulate()

The primary time I used lazy mode, it felt unusual not seeing prompt outcomes. However as soon as I ran the ultimate .accumulate(), the pace distinction was apparent.

Lazy analysis received’t magically resolve each efficiency challenge, however it brings a stage of effectivity that Pandas wasn’t designed for.

Conclusion and takeaways

Working with giant datasets doesn’t must really feel like wrestling along with your instruments.

Utilizing Polars and DuckDB confirmed me that the issue wasn’t at all times the info. Typically, it was the software I used to be utilizing to deal with it.

If there may be one factor you’re taking away from this tutorial, let or not it’s this: you don’t must abandon Pandas, however you may attain for one thing higher when your datasets begin pushing their limits.

Polars provides you pace in addition to smarter execution, then DuckDB helps you to question enormous information like they’re tiny. Collectively, they make working with giant knowledge really feel extra manageable and fewer tiring.

If you wish to go deeper into the concepts explored on this tutorial, the official documentation of Polars and DuckDB are good locations to begin.