First, what are we talking about? If you're new to your python pandas journey, python is a programming language and pandas is a library, used to enhance the functionality of python. But what's the hype about using python and pandas, and how can it replace excel in my day to day life? Here's the thing, excel is irreplaceable. Too many people use it for it ever to be considered obsolete. The idea behind using python and pandas isn't replacement, but greater functionality, ease of learning, and opening more doors for your career as your progress.
Let's face it, VBA is king whenever it comes to automating excel work. The native experience provided, all in an excel program, is tough to beat. But what happens after you learn VBA? You can do the work everyone else can in excel in an automated manner in likely less time. Awesome right? But if you're here you likely want more than this for yourself.
When deciding VBA and python, if you ever want to do more than just work in excel, learn python instead! Python is able to accomplish nearly all tasks you're trying to do in VBA. It also unlocks so many doors once you learn it well. Learning excel is valuable, learning a programming language is on another level of value. Picking an activity such as excel, where you can already apply what python you learn, makes it so those headaches of learning are worth so much more in the end.
A huge advantage of picking python over VBA, is the ease of learning. Python is designed to be easily understood whenever reading code. Things like whitespace and PEP guidelines keep python code easily understandable. Once you get past the initial shock of looking at a script for the first time, you'll find learning python methods become quicker and quicker.
The built in functionality of excel is hard to rival in every facet. However python and pandas can do almost everything excel can. In fact, the open source nature, meaning others from all across the world contribute, of pandas means that if someone needs a feature which is unavailable, they can make it themselves and add it for you.
Let's face it, technical people are being asked to know more and more. VBA is limited in scope. Learning VBA means you can be the excel person in the office. Learning python means you can do anything.