I guess I'm wondering what the point of pandas is here. And maybe in general? We hired a data engineer awhile ago and he kept complaining that the "python kernel" was crashing and he needed bigger instances to run his pandas scripts. I took a look and realized he was trying to pull the entire database of a nearly a billion rows into python to do some trivial joins, groupings, and filtering. I'm thinking, what are you doing man.
I think this is pretty common in the pandas world right
The places where pandas blows SQL out of the water are in exploratory data analysis and visual output.
The exploratory data analysis part is mostly a product of composability and verbosity. Composability is hard to define succinctly, but an easy example of it would just be the ability to easily define a filter as a variable and then reference that same filter in later code. Python provides a better interface for defining higher-order operations than pgPL/SQL, hands down. As for verbosity, I get data requests at work that require a lot of chained transformations - where I’d have to write 5-10 CTEs and a bunch of GROUP BYs and window functions that would total ~300 lines of SQL. Pandas equivalent would be <50 lines, and also easier to iterate on due to the composability factor. Other “killer-feature” examples of pandas terseness: https://pandas.pydata.org/pandas-docs/stable/reference/api/p...,
https://pandas.pydata.org/pandas-docs/stable/reference/api/p....
Feature parity varies by db, but pandas is generally much more expressive and capable than SQL for string munging and time-series stuff.
This is before getting to Jupyter, which blows every SQL interface I’ve ever seen out of the water. One-liner plotting with Pandas is a complete game changer for investigating any unfamiliar dataset; the first class plotting integration is really hard to beat.
All that said, can’t comment on your coworker’s situation without knowing the context. A billion time series datapoints might be fine, but if it’s a wide table using 100s of gigs, probably not smart. I wouldn’t use pandas in a large ETL pipeline without a very good reason, nor would I ever use it for serious joining. If he is just straight up trying trying to functionally replace SQL with pandas code, he’s definitely not doing it right.
(Am data engineer, have been working professionally w/ SQL+Pandas on a near-daily basis for over 3 years.)
Yikes, please tell me they were hired as a junior?
Obviously in your case some trivial SQL would have sufficed, which I feel like should have been caught in an interview but could be excused for a junior eng.
Also Pandas is not an amazing choice for data engineering tasks in general IMO, type conversion wonkiness is one reason I have questions about people who decide to use Pandas for legitimate data engineering tasks (as opposed to exploratory work or analysis).
The issue is a lot of the tools in the ecosystem are positioning themselves as “do everything” and the supporting marketing/services is all about increasing numbers of workarounds to enable this.
Using pandas but your data doesn’t fit in memory anymore? Here just use this fragile series of packages to distribute it across processors. Don’t reevaluate whether Pandas/Python is still the best choice, just layer more things in.
Run into issues with that approach? Here lift and shift your whole thing into Spark! Don’t ever optimise or actually engineer your process, just layer more things in!!!
Yeah me too, But there is a point where you have to stop. I also at some point switched from Excel/Origin to Pandas because of scaling, and now I'm making my first steps in to learning SQL. A point comes where you have to grab and learn a different tool. But it's a difficult to thing to do when you have to get stuff done!
I think one approach that can help you is to start writing your pandas logic using only apply/merge/groupby etc. I.e. no iterators like df.iterrows() and similar.
Once you have done that, the logic will be easily translatable to SQL, and your brain is prepared for the SQL/relational-algebra way of thinking.
Sounds like an inexperienced data engineer. One of the first things you learn is to read tabular data into a DataFrame, like pd.read_csv. So, there’s a natural progression to pd.read_sql [0], which default behavior loads SELECT * FROM t without any filters into a DataFrame. But, it also accepts a raw sql query or SQLAlchemy selectable, which should be used to join/filter data before loading into python.
> what the point of pandas is here. And maybe in general?
In general, if you are comparing to an SQL database, the most important is complex anaylsis/manipulation. Of course SQL is great for database level analysis - sorting/filtering/etc. But SQL queries for domain-specific analysis can become very opaque quickly and debugging of complex queries is a mess. Pandas is great for more complex manipulation as it keeps available the wider python ecosystem, allows for simpler debugging of complex processes through a REPL loop, links easily to visualisation, is pretty fast for some things through leveraging things like numpy, in-memory avoids multiple database calls, etc.
Obviously pandas isn't a great choice for persistent large datasets (maybe around the 0.1-1TB mark?) though, simply because it is memory stored.
The use cases definitely overlap significantly, but run into problems (typically ridiculous SQL queries on one side, giant data sets on the other) and you really need to stop and think about the tooling.
Pretty common and I think one of the main reasons why is that Pandas API has a very low entry barrier to get productive. It is very easy to do something without really knowing anything about computer science.
I like to think of Pandas as a replacement of Excel. I think where Pandas users go off the tracks is when you start getting to 100M rows and start having memory/kernel issues. For many of us, it is obvious that the next station is a database with the transformations necessary. But not many people ever get to this scale. It doesn't help that Pandas has a read_sql() function.
It's very true that pandas is in memory and so not good with large datasets... it is good for playing around with data and plotting / visualising. Scikitlearn, statsmodels are very powerful for this.
SQL is sometimes under appreciated as superior for grouping sorting and filtering... although with Bigquery and KSQL, SQL is definitely finding it's groove again.
Since the original post criticizes the behavior of Panda runtime where it loads everything into memory before processing, I think replacing the underlying Panda runtime with Spark runtime is very much a valid solution.
You can keep using Panda code for exploratory work, then with a little effort move it to production with Spark runtime.
“In the moment when I truly understand my enemy, understand him well enough to defeat him, then in that very moment I also love him. I think it’s impossible to really understand somebody, what they want, what they believe, and not love them the way they love themselves. And then, in that very moment when I love them.... I destroy them.”
Yes and no. I am not a data scientist but there are quite a few python scripts where we use pandas. Initially, I thought it was really nice and convenient but the more I have used it the more I think it's bloated, a pain to use, and dumping the data into sqlite would be easier for a lot of my tasks.
I think this is pretty common in the pandas world right