5 ms·
Non-elementary group-by aggregations in Polars vs pandas
- akdor1154 2y agoThe difference is a sanely and presciently designed expression API, which is a bit more verbose in some common cases, but is more predictable and much more expressive in more complex situations like this. On a tangent, i wonder what this op would look like in SQL? Probably would need support for filtering in a window function, which I'm not sure is standardized?
- andy81 2y agoHere's an example implementation in MSSQL - https://data.stackexchange.com/stackoverflow/query/edit/1873356 https://data.stackexchange.com/stackoverflow/query/edit/1873... No need to filter within the window function if you use subquery or CTE, which is supported everywhere.
- deleted 2y ago[deleted]
- capitainenemo 2y agohttps://en.wikipedia.org/wiki/SQL?useskin=vector#Standardization_history https://en.wikipedia.org/wiki/SQL?useskin=vector#Standardiza... According to wikipedia, windowing was standardized back in 2003.
- hobs 2y ago-- "find the maximum value of 'views', -- where 'sales' is greater than its mean, per 'id'". select max(views), id -- "find the maximum value of 'views', from example_table as et where exists ( SELECT * FROM ( SELECT id, avg(sales) as mean_sales FROM example_table GROUP by id ) as f -- where et.sales > f.mean_sales -- where 'sales' is greater than its mean and et.id = f.id ) group by id; -- per 'id'".
- wenc 2y agoProps to Ritchie Vink for designing polars. But also props to Wes McKinney for giving us a dataframe library during a time when we had none. Java still doesn’t have a decent dataframe library so we mustn’t take these things for granted. The Pandas API is no longer the way things should be done today nor should it be in new tutorials. Pandas was the jquery of its time —- great but no longer the state of the art. But I have much gratitude for it being around when it was needed.
- tomrod 2y agoWell said.
- nerdponx 2y agoI think it's worth giving some credit to prior art here. Polars was heavily inspired by the "Spark SQL" API, and Pandas by R data frames. I disagree that Pandas is no longer state of the art: its interface is optimized for a different use case compared to Polars.
- disgruntledphd2 2y agoI think a lot of the weirdness in pandas comes from firstly the original focus on time series, and secondly the attempt to make logical indexing work like it did in R. I'm not a big fan of the pandas API but it's a super useful tool nonetheless.
- dan-robertson 2y agoWithout having checked, maybe something like: select id, max(views) from <tbl> where sales > avg(sales) over (partition by id) group by 1 In dplyr, there is an ‘old style’ method which works on an intermediate ‘grouped data frame’ and a new style which doesn’t. In the old style: df |> group_by(id) |> filter(sales > mean(sales)) |> summarize(max(views)) In the new style, either: df |> filter(.by=id, sales>mean(sales)) |> summarize(.by=id,max(views)) Or: df |> summarize(.by=id, max(views[sales>mean(sales)]))
- countrymile 2y agoI understand that is the new style, but I'm not aware of the rationale of the switch to this method. The old style seems must clearer and easier to read.
- dan-robertson 2y agoTrying to find a good reference for you. There’s this from the author of dplyr: https://news.ycombinator.com/item?id=30067406 https://news.ycombinator.com/item?id=30067406 And here in the docs: https://dplyr.tidyverse.org/reference/dplyr_by.html https://dplyr.tidyverse.org/reference/dplyr_by.html And maybe also: https://www.tidyverse.org/blog/2023/02/dplyr-1-1-0-per-operation-grouping/ https://www.tidyverse.org/blog/2023/02/dplyr-1-1-0-per-opera... I guess part of it is that there’s some ‘non-locality’ in the pipeline where the grouping could be relatively distant from the operation acting on the grouped data. Similarly, you get to worry about eg grouping data that is already grouped. I quite like the prql solution which is to have a ‘structured grouping’ where you have to delimit the pipeline that operates on grouped data, but maybe it can still lead to bad edits for complex queries.
- bcoates 2y agoIf I understand what you mean, it's supported everywhere that has windowing at all afaik. If you mean filtering the rows in the window, you can do 'sum(case when condition then value else null end) over (window)'; if you mean selecting rows based on the value of a window function you use 'qualify' where supported or a trivial subquery or CTE and 'where' (which qualify is just shorthand for)
- __mharrison__ 2y agoPandas sat alone in the Python ecosphere for a long time. Lack of competition is generally not a good thing. I'm thrilled to have Polars around to innovate on the API end (and push Pandas to be better). And I say this as someone who makes much of their living from Pandas.
- 0cf8612b2e1e 2y agoI think pandas is well aware of some of the unfortunate legacy API decisions without Polars. They are trapped by backwards compatibility. Wes’ “Things I Hate About Pandas” post covers the highlights. Most of which boils down to having not put a layer between numpy and pandas. Which is why they were stuck with the unfortunate integer null situation.
- Twirrim 2y agoWhich is all stuff they could fix, if they'd be willing to, with a major version bump. They'd need a killer feature to encourage that migration though.
- code_biologist 2y agoThe really brutal thing is all of the code using Pandas written by researchers and non-software engineers running quietly in lab environments. Difficult to reproduce environments, small or non-existent test suites, code written by grad students long gone. If the Pandas interface breaks for installs done via `pip install pandas` it will cause a lot of pain. With that acknowledged, it'll make life a lot easier on everyone if the "fix the API" Pandas 3 had a different package name. Polars and others seem like exactly that solution, even if not literally Pandas.
- __mharrison__ 2y agoYes, but the interesting thing is that pandas developers are moving towards adopting polars-like advances now. Which wasn't the case previously.
- Nihilartikel 2y agoI did non trivial work with apache spark dataframes and came to appreciate them before ever being exposed to Pandas. After spark, pandas just seemed frustrating and incomprehensible. Polars is much more like spark and I am very happy about that. DuckDb even goes so far as to include a clone of the pyspark dataframe API, so somebody there must like it too.
- banku_brougham 2y agoI had a similar experience with spark, especially in the Scala API it felt very expressive and concise once I got used to certain idioms. Also +1 on duckdb which is excellent. There are some frustrations in spark however, I remember getting stuck on Winsorizing over groups. Hilariously there are identical functions called `percentile_approx` and `approx_percentile` and it wasn't clear from the docs they were the same or at least did the same thing. Given all that, the ergonomics of Julia for general purpose data handling is really unmatched IMO. I've got a lot of clean and readable data pipeline and shaping code that I revisited a couple years later and could easily understand. And making updates with new more type-generic functions is a breeze. Very enjoyable.
- appplication 2y agoSpark docs are way too minimal for my taste, at least the API docs.
- banku_brougham 2y agoyeah i couldnt get it done in spark api had to combine spark and spark sql bc the window function i needed was (probably) not available in spark. it was inelegant i thought.
- coding123 2y agoI don't know how well the polars implementation works, but what I love about PySpark is that sometimes spark is able to push those groupings down to the database. Not always, but sometimes. However I imagine that many people love polars/pandas performance for transactional queries (from start to finish get me a result in less than a second (as long as the number of underlying rows is not greater than 20k-ish). Pyspark will never be super great for that.
- lend000 2y agoI've wanted to convert a massive Pandas codebase to Polars for a long time. Probably 90% of the compute time is Pandas operations, especially creating new columns / resizing dataframes (which I understand to involve less of a speed difference compared to the grouping operations mentioned in the post, but still substantial). Anyone had success doing this and found it to be worth the effort?
- wenc 2y agoI 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 agoWhich things did you decide to move to duckdb?
- wenc 2y agoA 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 agoI'm genuinely curious, if you are already using (and assuming that you like) dataframe APIs, why you would use SQL?
- wenc 2y agoIt'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.
- Vaslo 2y agoI’ve moved mostly to polars. I still have some frameworks that demand pandas and pandas is still a very solid dataframe, but when I need to interpolate months in millions of lines of quarterly data, polars just blows it away. Even better is using tools like Narwhals and Ibis which can convert back and forth to any frames you want.
- winwang 2y agoThe power of having an API that allows usage of the Free monad. And in less-funny-FP-speak, the power of allowing the user write a program (expressions), that the sufficiently-smart backend later compiles/interprets. Awesome! Didn't expect such a vast difference in usability at first.
- xgdgsc 2y agoI' m tired of remembering of all these library invented concepts and prefer doing brainless for loops to process data in Julia.
- Demiurge 2y agoYou’re tired, but I don’t know if I have enough brains to rewrite the API “query” from this article using brainless loops.
- __mharrison__ 2y agoYou can do brainless for loops in Python really easily. Oh yeah, toss numba or cython on top and you are back to numpy speed...
- xgdgsc 2y agoNope they are still libraries with their own feature set that need remembering.
- __mharrison__ 2y agoOk, if you consider @jit a feature set... (Admittedly, there are more features, but in my book, I demonstrate pure Python code that runs as fast as numpy and cython with this decorator added.)
- jampekka 2y agoOutside relatively straightforward operations working with groups with "expressions" gets really painful. And Python gets too slow fast. And Pandas (multi-)indexing madness is a constant source of bugs and frustration. Sad that Julia is unusable for my workflows, and will likely remain so.
- kolja005 2y agoDoes anyone have a good heuristic for when a dataframe library is a good tool choice? I work on a team that has a lot of data scientists and a few engineers (including myself) and I often see the data scientists using dataframes when simple python classes would be much more appropriate so that you have a better sense of the object you're working with. I'm been having a hard time getting this idea across to people though.
- sanderjd 2y agoFrankly, the heuristic I've developed over the past few years working on a team that sounds like yours is: The data scientists are probably right. If you're actually operating on an object, ie. the equivalent to a single row in a dataframe, then yeah, it's silly to use a dataframe library. But if you're operating on N value objects ... yeah you probably want a dataframe with N rows and a column for each field in your object. Your mileage may vary I guess, but I resisted this for quite awhile and I now think I was the one who was wrong.
- wenc 2y agoI appreciate this. Most software devs are used to working 1-dimensional collections like lists, or tree-like abstractions like dicts, or some combination of those. This is why most abstractions and complex data types are built on these. Objects are a natural progression. But in the data world, high-dimensions are modeled using dataframes (analogously, tables). This is a paradigm shift for most pure software people because manipulating tables typically require manipulating sets and relations. Joining two tables requires knowing how the join-columns relate (inner, full outer, left, right, cross). Aggregation and window functions require thinking in terms of sub-groupings and subsets. It's not super intuitive unless you have to manipulate and reshape large amounts of data every day, which data scientists and data engineers have to do, and which software engineers typically don't do very often. It's just a kind of muscle memory that gets developed. I definitely had trouble at first getting software engineers to buy into DuckDB because they were unfamiliar and uncomfortable with SQL. Fortunately some of them had a growth mindset and were willing to learn, and those folks now have now acquired a very powerful tool (DuckDB) and a new way of thinking about large-data manipulation. When data is a certain size, iterative constructs like "for" loops become impractical.
- Larrikin 2y agoIf I'm doing some data science just for fun and personal projects, is there any reason to not go with Polars? I took some data science classes in grad school, but basically haven't had any reason to touch pandas since I graduated. But, did like the ecosystem of tools, learning materials, and other libraries surrounding it when I was working with it. I recently just started a new project and am quickly going through my old notes to refamiliarize myself with pandas, but maybe I should just go and learn Polars?
- dgacmu 2y agoI adore polars for its speed and I find the interface easier than pandas. But pandas still has a richer ecosystem of stuff built around it. I try to use polars in greenfield things but occasionally get yoinked back.
- ibash 2y agoGo with polars, you can always convert a polars data frame back when needed.
- nerdponx 2y agoPandas can be easier for interactive poking around on smaller data sets. It feels more like Excel, whereas Polars feels more like SQL. It's no surprise that on a forum full of programmers Polars is widely preferred, but IMO it's worth at least trying both.
- deleted 2y ago[deleted]
- wismwasm 2y agoI’m just using Ibis: https://ibis-project.org/ https://ibis-project.org/ They provide a nice backend agnostic API. For most backends it will just compile to SQL and act as a query builder. SQL basically has solved the problem of providing a declarative data transformation syntax, why reinvent the wheel?
- combocosmo 2y agoI've always liked scatter solutions for these kind of problems: import numpy as np def scatter_mean(index, value): sums = np.zeros(max(index)+1) counts = np.zeros(max(index)+1) for i in range(len(index)): j = index[i] sums[j] += value[i] counts[j] += 1 return sums / counts def scatter_max(index, value): maxs = -np.inf * np.ones(max(index)+1) for i in range(len(index)): j = index[i] maxs[j] = max(maxs[j], value[i]) return maxs def scatter_count(index): counts = np.zeros(max(index)+1, dtype=np.int32) for i in range(len(index)): counts[index[i]] += 1 return counts id = np.array([1, 1, 1, 2, 2, 2]) - 1 sales = np.array([4, 1, 2, 7, 6, 7]) views = np.array([3, 1, 2, 8, 6, 7]) means = scatter_mean(id, sales).repeat(scatter_count(id)) print(views[sales > means].max()) Obviously you'd need good implementations of the scatter operations, not these naive python for-loops. But once you have them the solution is a pretty readable two-liner.
- deleted 2y ago[deleted]
- Def_Os 2y agoData point: I have a medium-complexity data transformation use case that I still prefer pandas for. Reason: I can speed things up fairly easily with Cython functions, and do multithreading using the Python module. With polars I would have to learn Rust for that.