Pandas adds utility methods like join, groupby, and fromsql. Also hierarchical indexing, and a bunch of other stuff that I don't know much about - I'm not in data-science either, but I work with some of them.
Overall, lots of useful stuff for processing tables locally once you've fetched them from SQL or similar. Also, the simple fact of having a tabular data structure without needing to define a custom Row class. Lists of dicts or tuples have a lot of overhead by comparison.
This dovetails nicely with most data science-ie tasks, like statistical analysis, engineering features, simple machine learning techniques (the tables can be easily transformed into numpy arrays), and plotting results.
You can use them any time you feel the need to do table manipulations on non-insanely-large data: aggregate, transform, combine, split, reshape, join...
What am I missing with data-frames?
I've written products that utilized them before (years ago), but it's been long enough that I don't know what I would even use them for in daily programming.