3 ms·

> working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the

> working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.

wenc 4y ago
SQL is much nicer for anything non-trivial. Pandas methods get unwieldy for complex aggregations. Also Pandas methods are imperative so cannot be optimized. SQL is declarative so it can be optimized to the hilt and DuckDB is faster than Pandas in almost all cases, even on Pandas data frames themselves! (partly due to vectorization).
camgunz 4y ago
Databases are just much, much faster than Pandas, and that's before you start factoring the extraction and loading of data. I treat Pandas as a last resort when I can't do something in SQL, generally this is something like integrating with external services or running recordlinkage.
- RobinL 4y ago
  If you're curious, I've written a FOSS record linkage library that executes everything as SQL. It supports multiple SQL backends including DuckDB and Spark for scale, and runs faster than most competitors because it's able to leverage the speed of these backends: https://github.com/moj-analytical-services/splink https://github.com/moj-analytical-services/splink
  - camgunz 4y ago
    Oh hot tip! Thank you! Love the blog btw
jammycrisp 4y ago
You might be interested in checking out Ibis (https://ibis-project.org/ https://ibis-project.org/). It provides a dataframe-like API, abstracting over many common execution engines (duckdb, postgres, bigquery, spark, ...). Ibis wrapping duckdb has pretty much replaced pandas as my tool of choice for local data analysis. All the performance of duckdb with all the ergonomics of a dataframe API. (disclaimer: I contribute to Ibis for work).
- je42 4y ago
  Interesting ! One newbie question, how does ibis differ from sqlalchemy ?
  - 89vision 4y ago
    sql alchemy is an orm, where ibis looks to be a dataframe api that is sort of a dsl over sql. It doesn't try to map relational domains to an object oriented paradigm like sql alchemy does

> working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.

wenc 4y ago

SQL is much nicer for anything non-trivial. Pandas methods get unwieldy for complex aggregations. Also Pandas methods are imperative so cannot be optimized. SQL is declarative so it can be optimized to the hilt and DuckDB is faster than Pandas in almost all cases, even on Pandas data frames themselves! (partly due to vectorization).

camgunz 4y ago

Databases are just much, much faster than Pandas, and that's before you start factoring the extraction and loading of data. I treat Pandas as a last resort when I can't do something in SQL, generally this is something like integrating with external services or running recordlinkage.

RobinL 4y ago
If you're curious, I've written a FOSS record linkage library that executes everything as SQL. It supports multiple SQL backends including DuckDB and Spark for scale, and runs faster than most competitors because it's able to leverage the speed of these backends: https://github.com/moj-analytical-services/splink https://github.com/moj-analytical-services/splink
- camgunz 4y ago
  Oh hot tip! Thank you! Love the blog btw

jammycrisp 4y ago

You might be interested in checking out Ibis (https://ibis-project.org/ https://ibis-project.org/). It provides a dataframe-like API, abstracting over many common execution engines (duckdb, postgres, bigquery, spark, ...). Ibis wrapping duckdb has pretty much replaced pandas as my tool of choice for local data analysis. All the performance of duckdb with all the ergonomics of a dataframe API. (disclaimer: I contribute to Ibis for work).

je42 4y ago
Interesting ! One newbie question, how does ibis differ from sqlalchemy ?
- 89vision 4y ago
  sql alchemy is an orm, where ibis looks to be a dataframe api that is sort of a dsl over sql. It doesn't try to map relational domains to an object oriented paradigm like sql alchemy does