I 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.

I 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.

I 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.

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.

Without having checked, maybe something like:

  select id, max(views) from <tbl>
  where sales > avg(sales) over (partition by id) group by 1

  df |> group_by(id) |>
    filter(sales > mean(sales)) |>
    summarize(max(views))

  df |> filter(.by=id, sales>mean(sales)) |> summarize(.by=id,max(views))

  df |> summarize(.by=id, max(views[sales>mean(sales)]))

Props 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.

Here's an example implementation in MSSQL - 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.

https://en.wikipedia.org/wiki/SQL?useskin=vector#Standardiza...

According to wikipedia, windowing was standardized back in 2003.

  -- "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'".