A lot of people hate on SQL. I used to be one of them but I've come to think that for data transformations it's hard to beat. My current favorite is DuckDB, which is like a SQLite but columnar. It has great performance and it's easy to call it from python and even run SQL on pandas dataframes.