Question: If you come from the future, hopefully this PR has already been merged. If you don't come from the future, hopefully this answer solves your problem. I want to solve my problem only with polars (which I am no expert, but I can follow what is going on), before just copy-pasting the DuckDB integration suggested above and compare the results in my real data. I have a list of events (name and timestamp), and a list of time windows. I want to count how many of each event occur in each time window. I feel like I am close to getting something that works correctly, but I have been stuck for a couple of hours now: import polars as pl events = { "name": ["a", "b", "a", "b", "a", "c", "b", "a", "b", "a", "b", "a", "b", "a", "b", "a", "b", "a", "b"], "time": [0.0, 1.0, 1.5, 2.0, 2.25, 2.26, 2.45, 2.5, 3.0, 3.4, 3.5, 3.6, 3.65, 3.7, 3.8, 4.0, 4.5, 5.0, 6.0], } windows = { "start_time": [1.0, 2.0, 3.0, 4.0], "stop_time": [3.5, 2.5, 3.7, 5.0], } events_df = pl.DataFrame(events).sort("time").with_row_index() windows_df = ( pl.DataFrame(windows) .sort("start_time") .join_asof(events_df, left_on="start_time", right_on="time", strategy="forward") .drop("name", "time") .rename({"index": "first_index"}) .sort("stop_time") .join_asof(events_df, left_on="stop_time", right_on="time", strategy="backward") .drop("name", "time") .rename({"index": "last_index"}) ) print(windows_df) """ shape: (4, 4) ┌────────────┬───────────┬─────────────┬────────────┐ │ start_time ┆ stop_time ┆ first_index ┆ last_index │ │ --- ┆ --- ┆ --- ┆ --- │ │ f64 ┆ f64 ┆ u32 ┆ u32 │ ╞════════════╪═══════════╪═════════════╪════════════╡ │ 2.0 ┆ 2.5 ┆ 3 ┆ 7 │ │ 1.0 ┆ 3.5 ┆ 1 ┆ 10 │ │ 3.0 ┆ 3.7 ┆ 8 ┆ 13 │ │ 4.0 ┆ 5.0 ┆ 15 ┆ 17 │ └────────────┴───────────┴─────────────┴────────────┘ """  So far, for each time window, I can get the index of the first and last events that I care about. Now I "just" need to count how many of these are of each type. Can I get some help on how to do this? The output I am looking for should look like: shape: (4, 5) ┌────────────┬───────────┬─────┬─────┬─────┐ │ start_time ┆ stop_time ┆ a ┆ b ┆ c │ │ --- ┆ --- ┆ --- ┆ --- ┆ --- │ │ f64 ┆ f64 ┆ i64 ┆ i64 ┆ i64 │ ╞════════════╪═══════════╪═════╪═════╪═════╡ │ 1.0 ┆ 3.5 ┆ 4 ┆ 5 ┆ 1 │ │ 2.0 ┆ 2.5 ┆ 2 ┆ 2 ┆ 1 │ │ 3.0 ┆ 3.7 ┆ 3 ┆ 3 ┆ 0 │ │ 4.0 ┆ 5.0 ┆ 2 ┆ 1 ┆ 0 │ └────────────┴───────────┴─────┴─────┴─────┘  I feel like using something like int_ranges(), gather(), and explode() can get me a dataframe with each time window and all it's corresponding events. Finally, something like group_by(), count(), and pivot() can get me to the dataframe I want. But I have been struggling with this for a while.

Answer: update join_where() was released in version 1.7.0: ( windows_df .join_where( events_df, pl.col.time >= pl.col.start_time, pl.col.time <= pl.col.stop_time, ) .sort("name", "start_time") .pivot(on="name", index=["start_time","stop_time"], aggregate_function="len", values="time") .fill_null(0) ) ┌────────────┬───────────┬─────┬─────┬─────┐ │ start_time ┆ stop_time ┆ a ┆ b ┆ c │ │ --- ┆ --- ┆ --- ┆ --- ┆ --- │ │ f64 ┆ f64 ┆ u32 ┆ u32 ┆ u32 │ ╞════════════╪═══════════╪═════╪═════╪═════╡ │ 1.0 ┆ 3.5 ┆ 4 ┆ 5 ┆ 1 │ │ 2.0 ┆ 2.5 ┆ 2 ┆ 2 ┆ 1 │ │ 3.0 ┆ 3.7 ┆ 3 ┆ 3 ┆ 0 │ │ 4.0 ┆ 5.0 ┆ 2 ┆ 1 ┆ 0 │ └────────────┴───────────┴─────┴─────┴─────┘  previous I was looking at your double join_asof and thought that maybe you could also use another approach, which would not require an explode(). The thing is, you don't really need the data from events_df, only counts. So if we do join_asof for every possible value in name then we can calculate counts by simple arithmetic. First, let's prepare our DataFrames. events_df = pl.DataFrame(events) windows_df = ( pl.DataFrame(windows) .join( events_df.select(pl.col.name.unique()), how="cross" ) ┌────────────┬───────────┬──────┐ │ start_time ┆ stop_time ┆ name │ │ --- ┆ --- ┆ --- │ │ f64 ┆ f64 ┆ str │ ╞════════════╪═══════════╪══════╡ │ 1.0 ┆ 3.5 ┆ c │ │ 1.0 ┆ 3.5 ┆ a │ │ 1.0 ┆ 3.5 ┆ b │ │ 2.0 ┆ 2.5 ┆ c │ │ 2.0 ┆ 2.5 ┆ a │ │ … ┆ … ┆ … │ │ 3.0 ┆ 3.7 ┆ a │ │ 3.0 ┆ 3.7 ┆ b │ │ 4.0 ┆ 5.0 ┆ c │ │ 4.0 ┆ 5.0 ┆ a │ │ 4.0 ┆ 5.0 ┆ b │ └────────────┴───────────┴──────┘  events_df= ( events_df .with_columns(index = pl.int_range(pl.len()).over("name")) ) ┌──────┬──────┬───────┐ │ name ┆ time ┆ index │ │ --- ┆ --- ┆ --- │ │ str ┆ f64 ┆ i64 │ ╞══════╪══════╪═══════╡ │ a ┆ 0.0 ┆ 0 │ │ b ┆ 1.0 ┆ 0 │ │ a ┆ 1.5 ┆ 1 │ │ b ┆ 2.0 ┆ 1 │ │ a ┆ 2.25 ┆ 2 │ │ … ┆ … ┆ … │ │ b ┆ 3.8 ┆ 6 │ │ a ┆ 4.0 ┆ 7 │ │ b ┆ 4.5 ┆ 7 │ │ a ┆ 5.0 ┆ 8 │ │ b ┆ 6.0 ┆ 8 │ └──────┴──────┴───────┘  Now we can do the same join you did, but add by parameter, so we do it within name column: result_df = ( windows_df .sort("name", "start_time") .join_asof(events_df, left_on="start_time", right_on="time", strategy="forward", by="name") .drop("time") .rename({"index": "first_index"}) .sort("name", "stop_time") .join_asof(events_df, left_on="stop_time", right_on="time", strategy="backward", by="name") .drop("time") .rename({"index": "last_index"}) ) ┌────────────┬───────────┬──────┬─────────────┬────────────┐ │ start_time ┆ stop_time ┆ name ┆ first_index ┆ last_index │ │ --- ┆ --- ┆ --- ┆ --- ┆ --- │ │ f64 ┆ f64 ┆ str ┆ i64 ┆ i64 │ ╞════════════╪═══════════╪══════╪═════════════╪════════════╡ │ 2.0 ┆ 2.5 ┆ a ┆ 2 ┆ 3 │ │ 1.0 ┆ 3.5 ┆ a ┆ 1 ┆ 4 │ │ 3.0 ┆ 3.7 ┆ a ┆ 4 ┆ 6 │ │ 4.0 ┆ 5.0 ┆ a ┆ 7 ┆ 8 │ │ 2.0 ┆ 2.5 ┆ b ┆ 1 ┆ 2 │ │ … ┆ … ┆ … ┆ … ┆ … │ │ 4.0 ┆ 5.0 ┆ b ┆ 7 ┆ 7 │ │ 2.0 ┆ 2.5 ┆ c ┆ 0 ┆ 0 │ │ 1.0 ┆ 3.5 ┆ c ┆ 0 ┆ 0 │ │ 3.0 ┆ 3.7 ┆ c ┆ null ┆ 0 │ │ 4.0 ┆ 5.0 ┆ c ┆ null ┆ 0 │ └────────────┴───────────┴──────┴─────────────┴────────────┘  And now you can calculate the result by simple last_index - first_index + 1: ( result_df .with_columns(index = pl.col.last_index - pl.col.first_index + 1) .pivot(on="name", index=["start_time","stop_time"], values="index") .fill_null(0) .sort("start_time", "stop_time") ) ┌────────────┬───────────┬─────┬─────┬─────┐ │ start_time ┆ stop_time ┆ a ┆ b ┆ c │ │ --- ┆ --- ┆ --- ┆ --- ┆ --- │ │ f64 ┆ f64 ┆ i64 ┆ i64 ┆ i64 │ ╞════════════╪═══════════╪═════╪═════╪═════╡ │ 1.0 ┆ 3.5 ┆ 4 ┆ 5 ┆ 1 │ │ 2.0 ┆ 2.5 ┆ 2 ┆ 2 ┆ 1 │ │ 3.0 ┆ 3.7 ┆ 3 ┆ 3 ┆ 0 │ │ 4.0 ┆ 5.0 ┆ 2 ┆ 1 ┆ 0 │ └────────────┴───────────┴─────┴─────┴─────┘