Pandas can be pretty fast, but DuckDB and Polars are both faster than Pandas. DuckDB supports vectorized and parallelized operations on Pandas dataframes, while Polars is written in Rust.

I feel though the killer is that inner loop where dataframe operations are being performed across a large number of iterations, and there's significant overhead there.

For-loops are usually not the most performant solution in Python.

I could be wrong, I feel that there's a SQL way to answer that question, in which case DuckDB might be able to exploit vectorization, parallelization, indexing and query optimization across the dataset in one fell swoop.