4 ms·
Both have terrible syntax that make SQL look like the most readable thing ever.
by mgaunard 23d ago
Both have terrible syntax that make SQL look like the most readable thing ever.
- fzumstein 23d agoI tend to agree. SQL may have been harder to write in the past (worse autocomplete than pandas/polars), but now that AI is writing the code, SQL is usually much easier to read. So DuckDB is another interesting alternative to pandas.
- refactor_master 23d agoThe cool thing about polars is that you can conditionally collect expressions over many layers of business logic, and then compute the result at the end. Doing this in SQL ends up in a hodgepodge of strings and trimmed ends to please the syntax. You can also pretty effortlessly write quite complex conditionals directly in polars, and bridge it easily to the surrounding python. I find that SQL is only easier to read with minimal abstraction, but as soon as the project gets bigger SQL becomes an unwieldy island of different that has served its purpose after we’re done with reading/writing the data.
- fzumstein 23d agoThis sounds interesting! Do you have a specific example by any chance or blog post/doc references?
- refactor_master 23d agoIt’s just the lazy/expression part of the API, which is really the bread and butter of polars, rather than just being “replacement syntax” for pandas. This allows you to tap into abstraction that SQL can’t keep up with: import polars as pl # 1. Base Dataset lazy_df = pl.LazyFrame( { "store_id": ["S01", "S02", "S03", "S04", "S05"], "revenue": [5000.0, 2400.0, 15000.0, 900.0, 3200.0], "margin": [0.45, 0.30, 0.60, 0.15, 0.50], "tx_count": [120, 45, 300, 20, 85], "returns": [5, 12, 45, 2, 8], } ) # 2. Define Layer Abstractions def get_kpi_layer() -> list[pl.Expr]: return [ (pl.col("returns") / pl.col("tx_count")).alias("return_rate"), (pl.col("revenue") / pl.col("tx_count")).alias("avg_order_value"), ] def get_threshold_layer(thresholds: dict[str, list[float]]) -> list[pl.Expr]: return [ (pl.col(col) > limit).alias(f"is_{col}above{int(limit)}") for col, limits in thresholds.items() for limit in limits ] def get_interaction_layer(numeric_cols: list[str]) -> list[pl.Expr]: return [ (pl.col(a) / (pl.col(b) + 1e-5)).alias(f"ratio_{a}per{b}") for i, a in enumerate(numeric_cols) for b in numeric_cols[i + 1 :] ] def get_segmentation_layer() -> list[pl.Expr]: return [ pl.when(pl.col("margin") > 0.4) .then(pl.literal("High")) .otherwise(pl.literal("Low")) .alias("margin_profile") ] # 3. Consolidate and Execute Single Graph Pass thresholds = {"revenue": [1000.0, 5000.0, 10000.0], "tx_count": [50, 100, 200]} numeric_cols = ["revenue", "margin", "tx_count", "returns"] expr_pool = [ *get_kpi_layer(), *get_threshold_layer(thresholds), *get_interaction_layer(numeric_cols), *get_segmentation_layer(), ] final_df = lazy_df.with_columns(expr_pool).collect()
- _zoltan_ 23d agoI'm sorry but this looks much better: WITH raw_data AS ( SELECT * FROM ( VALUES ('S01', 5000.0, 0.45, 120, 5), ('S02', 2400.0, 0.30, 45, 12), ('S03', 15000.0, 0.60, 300, 45), ('S04', 900.0, 0.15, 20, 2), ('S05', 3200.0, 0.50, 85, 8) ) AS t(store_id, revenue, margin, tx_count, returns)), base_data AS ( SELECT store_id, revenue, margin, CAST(tx_count AS DOUBLE) AS tx_count, CAST(returns AS DOUBLE) AS returns FROM raw_data ) SELECT store_id, revenue, margin, CAST(tx_count AS BIGINT) AS tx_count, CAST(returns AS BIGINT) AS returns, -- KPI Layer returns / tx_count AS return_rate, revenue / tx_count AS avg_order_value, -- Threshold Layer (matching original alias names) revenue > 1000.0 AS is_revenueabove1000, revenue > 5000.0 AS is_revenueabove5000, revenue > 10000.0 AS is_revenueabove10000, tx_count > 50 AS is_tx_countabove50, tx_count > 100 AS is_tx_countabove100, tx_count > 200 AS is_tx_countabove200, -- Interaction Layer (preserving exact numeric formula & aliases) revenue / (margin + 1e-5) AS ratio_revenuepermargin, revenue / (tx_count + 1e-5) AS ratio_revenuepertx_count, revenue / (returns + 1e-5) AS ratio_revenueperreturns, margin / (tx_count + 1e-5) AS ratio_marginpertx_count, margin / (returns + 1e-5) AS ratio_marginperreturns, tx_count / (returns + 1e-5) AS ratio_tx_countperreturns, -- Segmentation Layer CASE WHEN margin > 0.4 THEN 'High' ELSE 'Low' END AS margin_profile FROM base_data;
- sanderjd 23d ago... does it? I don't think it does, even in this form. And now write it such that all the conditions and transformations are injected into the string (somehow) rather than written in explicitly. Much worse.
- bobson_dugnutt5 23d agoReally? You've written out all the ratios and thresholds manually. If a user wanted to change the set of thresholds the polars way is far superior. In what way do you consider this better?
- throwaway7783 23d ago
- aquafox 23d agoComing from an R/dplyr background, I agree. Compare df.select( pl.col("x"), (pl.col("w")/pl.col("z")).alias("y") ) with df |> select(x, y = w/z)
- bobson_dugnutt5 23d agoFair point, but you can do something like `df.select("x", y=pl.col.w/pl.col.z)`
- orlp 23d agofrom polars import col as C df.select(C.x, y = C.w / C.z)
- dkga 23d agoStill, it’s a very good approximation but still an approximation to the more ergonomic and expressive tidyverse syntax
- sanderjd 23d agoOne person's "ergonomic and expressive" is another person's "wait what in the world is actually going on here".
- __mharrison__ 23d agoThis is the way. Favor keyword arguments to alias.
- jcattle 23d agoR really is/was the superior traditional data science language. Python ecosystem is slowly catching up though. ggplot vs matplotlib dplyr vs pandas And I loved that everything in RStudio was so easily inspectable. Have a huge dataframe? Just look at it right in your IDE.
- vovavili 23d agoAltair and Positron should be just as good for your Polars @ Python needs. With software like Marimo notebooks and VegaFusion, Polars/Python experience starts beating R by quite a substantial margin.
- condwanaland 23d agoCould not agree less. Ive always found SQL an unreadable mess but tools like polars and dplyr are such elegant ways to manipulate data. Pandas is a mess though.
- world2vec 23d agoThere's no way SQL is more unreadable than polars. IMO it's the other way around.
- benrutter 23d ago> There's no way SQL is more unreadable than polars. IMO it's the other way around. I think on basic queries, SQL is really nice, but when stuff gets more complex, with a bunch of CTEs, let alone functions requiring loops, it becomes pretty obtuse.
- sanderjd 23d agoI would say that it is easier to decompose polars (and all dataframe api) queries and to build them up from pieces than it is to do the same with sql. Any time I find myself writing more than five or so lines of sql, or especially building a sql string in parts with logic, I wish I had a dataframe api instead. But the reverse is also somewhat true, that simpler and explicit expressions are nicer with sql.
- bobson_dugnutt5 23d agoWhat is it about polars syntax you don't like? The fact that is very verbose? At first I wasn't a fan, but over time I've grown to really like it. That never happened to me with pandas, always felt the syntax was messy
- mihaelm 23d agoThe verbosity takes a bit to get used too, but it sure beats the anything-goes feeling - messy as you put it - of pandas.
- gpugreg 23d agoYou can query polars data frames with SQL: https://docs.pola.rs/api/python/stable/reference/expressions/api/polars.sql.html https://docs.pola.rs/api/python/stable/reference/expressions... Unfortunately, polars does not support parameterized queries, so the risk of SQL injection is extremely high.
- geysersam 23d agoI agree sql is more elegant. The problems arise when you have to add logic on top of sql. Often I end up constructing queries via string manipulation and that is not very ergonomic. Polars api is more verbose and complex than sql but at least it's not meta-programming. The duckdb python api is okay, but it is a bit limited, no ctes, no as of join, and it can be slow at bind/interpretation time when you do stuff like unioning multiple relations in a loop (I think that becomes O(N^2), but I might be wrong). Most issues can be worked around, but Polars is designed from the ground up to be used from python.
- vovavili 23d agoYou should be using dbt instead of string manipulation for serious query building.
- sanderjd 23d agoIt's never quite been clear to me what the advantage of dbt over a python program using sqlalchemy / duckdb / polars to transform data is. Can you enlighten me?
- vovavili 23d agoAt the minimum, it's just Jinja2 templates in your SQL queries - meaning, you can do pure SQL transformations with conditional logic in your templates. In addition to being able to run tests, specify custom macros, having version control and having some constrained way to organize your tables, you're turning SQL into a proper programming language with just one library.
- throwaway7783 23d agoMy issue with DBT is it is a mix of SQL, yaml, jinja2 flow controls (and metrics is whole another thing). SQL with jinja2 if/else can get really unmaintainable quickly. It's perhaps better than homegrown sql based transformers. polars is code and can be version controlled too. Dataframes in my opinion are more elegant, and with the right backends and some lineage enhancements, could serve a much wider set of use cases than what DBT does