I really, really dislike all the dtype wrangling and how those choices resonate throughout the API. I understand that a lot of work has been done to make that API "work", but in practice it feels like that effort would have been better avoided by changing expectations and interfaces.

Now, to be clear, that's a hard problem. Heterogenous named bags of homogenous columns with a variety of data types, storage patterns, and ideas about missingness isn't an easy domain... but instead of just trying to make everything work through hammering 6+ semi-coherent interfaces (indices, databases, mutability, immutability/chaining, numpy, dataframes) together, I'd be willing to pay a lot more in verbosity and explicitness for something simple.

    pd.Series(str, [np.nan, "a"]) => ["nan", "a"] # or even an exception!
    pd.Series(nullable(str), [np.nan, "a"]) => [nan, "a"]

Indexing is vastly over-designed. GroupBy is a very common API and is poorly documented and just weird in no small part due to attempts at dtype inference. Foundational useful concepts like categories feel bolted on. There's join, merge, pivot, pivot_table.

I'd chalk this all up to just being "hard", but at the same time I can go pick up R's dplyr library and get a very nice existence proof of how a nice interface could work. Not to say dplyr has it all figured out, but it's a night-and-day improvement to Pandas.

Pandas is great. It makes doing data science in Python so vastly much less of a chore than working with straight Numpy. It steals some great ideas and tries out a few interesting ones of its own... but it is far from a joy to work with.