DuckDB vs Pandas vs Polars For Python devs

[@Shawn-cr8ep]

DuckDB is the most underused and underrated Python library. I started using it a couple weeks ago and I'm blown away by the efficiency increase over Pandas. Plus SQL is easier and it forces you to think I'm vectorized operations rather than being tempted by Pandas built in loop methods that are super slow

[@porlando12]

I appreciate the nods to the R community going on in here. Great video!

[@matej6418]

all 5 of them.

[@MrRubix94]

Well I had just started to learn Polars, but your video and another one comparing DuckDB and Polars are making me doubt my choice… DuckDB seems MUCH faster. Besides, SQL knowledge can be leveraged for everything. Why one would use pandas or polars over DuckDB? Am I missing something?

[@mehdio]

I understand the doubt :) Apart from features there is the debate about DataFrame vs SQL approach.
While both Polars and DuckDB support DataFrame & SQL, DuckDB is primary designed to interface through SQL.
So if your a SQL lover, DuckDB is a no brainer. Polars has also a SQL interface but it's a pretty recent.

[@MrRubix94]

@@mehdio Hum, I’m not really a SQL lover, I just want to use what works best as a data scientist. Manipulating a DataFrame is really convenient when exploring data. Maybe DuckDB + Polars? But I like simplicity, I would rather use one tool only. Choices, choices…

[@incremental_failure]

Same here. Just finished a rewrite from Pandas to Polars and it's already out of date. Although I'll likely be using Polars for the in-memory stuff and DuckDB for out-of-memory persistent data. The differences in speed are not gigantic if you consider the bigger picture and Polars development is very active, they are getting faster with every minor version.

[@armeyavaidya3464]

Polars is best for continuous operation on columns,
Also it doesn't support indices so can't do (I at some point and j at some point)

[@incremental_failure]

@@armeyavaidya3464 Indexes can be simulated, using a column as an index.

[@Emotekofficial]

How about DUCKDB and SQLALCHEMY? Do they shake hands? Can I do ORM like this?

[@motherduckdb]

yep, here’s MotherDuck instructions for it: motherduck.com/docs/integrations/sqlalchemy
(though also works with vanilla OSS duckdb, with driver linked from there)

[@kpyoutuber4671]

Thank you, for this valuable content!!.
Can you also explain the parquet dataset?
I used to create partitioned Parquet datasets by using Pandas and Polars.
But I want to know how to read data from such partitioned parquet datasets directly to Polars lazy frame format (not to pandas as data size is larger than memory) to do some analytics.
import polars as pl
import pyarrow.parquet as pq
# Read data written to parquet dataset
pq_df = pq.read_table(r"C:\Users\test_pl",
schema=pd_df_schema,
)
pl_df = pl.from_pandas(pq_df.to_pandas()).lazy()
Is there any better way to do this

[@motherduckdb]

As per polars documentation, docs.pola.rs/py-polars/html/reference/api/polars.scan_pyarrow_dataset.html#polars.scan_pyarrow_dataset
You can use scan_pyarrow_dataset() to read from partitioned datasets.

[@HitAndMissLab]

what about DuckDB vs Dask?

[@user-fv1576]

Is DuckDb a query language, a real db like sqlite or both?

[@motherduckdb]

It's a real DB like sqlite! But it innovates a lot around SQL, read more here : duckdb.org/2022/05/04/friendlier-sql.html

[@allthingsdata]

I guess I'm stating the obvious but for anyone who doesn't use SQL for data operations DuckDB is second class. And I surely do not like to use SQL for transformations and such.

[@JOHNSMITH-ve3rq]

SQLite is faster yo

[@shogun8-9]

not for analysis. SQLite is OLTB, not OLAP.