Does anyone have good reference material on learning how to use pandas effectively and understanding the api as a whole?

Any time ive had to use pandas I am shocked at how convuluted and opaque the docs are, and end up just hacking away till something wirks alright.

I felt the same - have to relearn/lookup everything every time I went back to a project or wanted to do some operations that are simple to describe in SQL but I couldn't wrap my mind around e.g. using multi-indexed dataframes & aggregations properly. These days, I always jump to Polars instead of Pandas - much more intuitive and consistent API. Tons of props to Pandas for all that they did (and continue to do) in the data space, but their API did not evolve very well IMO.

I've also been wanting to play with Ibis[1] recently, but Polars has been sufficient for me.

[1] https://ibis-project.org/

There’s Pandas for Everyone by Daniel Chen.

What helped me with Pandas was a (very) short stint with array programming, specifically uiua. It gave me a good understanding of the possible operations on arrays.

Is this a setup??? :)

The guy two comments up wrote a great book on using pandas effectively. It’s called Effective Pandas