I'm relatively new to Pandas after years of working with Apache Spark. I've found it to be relatively intuitive. From what I understand, Pandas has gotten much less memory greedy over time as PyArrow has been integrated. They just released a new type to make strings much more efficient for example: https://youtu.be/_zoPmQ6J1aE

It's probably good for someone who hasn't really done programming before, but for me I can certainly agree with it being unintuitive. Like putting filters within square brackets. It felt so unnatural to put something that was evaluated across all rows into what should just take an index or key.

And there's gotchas like join() not actually working like you'd expect a join to coming from SQL. IIRC a SQL-like join is done via merge().

And then I still don't understand when I need to differentiate between indexes and columns. I don't even know why they need to be differentiated at all. Would it really have been that hard for them to design indexes to always project out to a column so you can treat them as such?

But, what can replace it in the python ecosystem?

Apart from using xarray whenever feasible, but that's just one small part (and always in conjunction with pandas).