Honestly "a great CSV reader" is the only thing it has going from my experience. Other then that, it is not intuitive, a kitchen sink of features, memory greedy.
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?
Apart from using xarray whenever feasible, but that's just one small part (and always in conjunction with pandas).