3 ms·

I converted to DuckDB and Polars. It’s worth it for the speed improvement. However there are subtle differences between Pandas and Polars behaviors so regressi

by wenc 2y ago

I converted to DuckDB and Polars. It’s worth it for the speed improvement. However there are subtle differences between Pandas and Polars behaviors so regression testing is your friend. It’s not 1:1 mapping.

willseth 2y ago
Which things did you decide to move to duckdb?
- wenc 2y ago
  A lot of aggregation, pivot and join logic, like: df = duckdb.query("from tbl1 join tbl2 using (id) where id is not null ").pl() The .pl() is a Arrow-based conversation from DuckDB to Polars. It's in-memory and I believe zero-copy, so it happens almost instantaneously. I go back and forth between DuckDB and Polars functions in the same scope because it's so cheap to convert between the two.
  - __mharrison__ 2y ago
    I'm genuinely curious, if you are already using (and assuming that you like) dataframe APIs, why you would use SQL?
    - wenc 2y ago
      It's the opposite; I prefer DuckDB and generally work with DuckDB's friendly SQL interface. SQL is declarative and is (for me) more intuitive than method-chaining -- especially for complex analytic operations that happen in one go. (software people might beg to differ about the intuitive bit because they are more used to an imperative style, and to my surprise, even the best software engineers struggle with SQL because it requires one to think in set and relation operations rather than function calls, which many software folks are not used to) I actually don't use the Polars dataframe APIs much except for some operations which are easier to do in dataframe form, like applying a Python function as UDF, or transposing (not pivoting) a dataframe. Also Polars is good for materializing the query into a dataframe rapidly, which can then be passed into methods/functions. It's also a lot easier to unit test dataframes than SQL tables. There's a lot more tooling for that.
kzrdude 2y ago
There's been so many subtle changes in pandas to pandas upgrades (especially groupby is somehow always hit), so regression tests are always needed...

by wenc 2y ago

willseth 2y ago

Which things did you decide to move to duckdb?

wenc 2y ago
A lot of aggregation, pivot and join logic, like: df = duckdb.query("from tbl1 join tbl2 using (id) where id is not null ").pl() The .pl() is a Arrow-based conversation from DuckDB to Polars. It's in-memory and I believe zero-copy, so it happens almost instantaneously. I go back and forth between DuckDB and Polars functions in the same scope because it's so cheap to convert between the two.
- __mharrison__ 2y ago
  I'm genuinely curious, if you are already using (and assuming that you like) dataframe APIs, why you would use SQL?
  - wenc 2y ago
    It's the opposite; I prefer DuckDB and generally work with DuckDB's friendly SQL interface. SQL is declarative and is (for me) more intuitive than method-chaining -- especially for complex analytic operations that happen in one go. (software people might beg to differ about the intuitive bit because they are more used to an imperative style, and to my surprise, even the best software engineers struggle with SQL because it requires one to think in set and relation operations rather than function calls, which many software folks are not used to) I actually don't use the Polars dataframe APIs much except for some operations which are easier to do in dataframe form, like applying a Python function as UDF, or transposing (not pivoting) a dataframe. Also Polars is good for materializing the query into a dataframe rapidly, which can then be passed into methods/functions. It's also a lot easier to unit test dataframes than SQL tables. There's a lot more tooling for that.

kzrdude 2y ago

There's been so many subtle changes in pandas to pandas upgrades (especially groupby is somehow always hit), so regression tests are always needed...