4 ms·
Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL stat
by qsort 6y ago
Pandas is great when you're working on the data manually because it lets you interleave python code and "queries", but it's strictly worse than a plain SQL statement if you're writing, say, a REST service that needs to output some data.
SQL executed by the database is orders of magnitude more efficient, way more expressive, and doesn't require you to memorize pandas' absurd API.
And I say this as someone who is by no means a SQL wizard, and fully acknowledging all of SQL's blemishes.
- tomnipotent 6y ago> a REST service This is a post about data analysis, and everyone wants to point out that pandas isn't good at real-time service requests. > SQL executed by the database is orders of magnitude more efficient Compared to pandas? No, it's not. Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database.
- qsort 6y ago> Compared to pandas? No, it's not. I'm not saying you shouldn't use pandas, it depends on the size of the data. I'm working right now on a project where a SELECT * of the entire fact table would be a couple hundred gigabytes. The flow is SQL -> pandas -> manipulation, and as always in pipelines like those, the most work you can do at the earliest stage, the better.
- disgruntledphd2 6y agoYeah, speaking as a data person, the SQL argument is correct. Python/R are much, much, much slower for this kind of work. OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging. Personally, I'm a big fan of using SQL/Airflow/whatever to generate whatever data I'll need all the time at a high level of granularity (user/action etc), and then just run a (very quick) SQL query to get whatever you need into your analytics environment. Gives you the best of both worlds, IME.
- CRConrad 6y ago> OTOH, SQL is super limiting for a lot of data analysis tasks and you'll inevitably need the data in weird forms that require lots of munging. OTGH, to some (I suspect often rather large) extent, that's because most people (I suspect including many "data scientists") are pretty bad at doing their data munging in SQL.
- disgruntledphd2 6y agoPerhaps (although this tends to be a core skill for any experienced DS - I 100% would not hire anyone without basic SQL, as it's just setting them up for failure). SQL is the only tool I've used everywhere I worked. But, SQL is bad at lots of stuff. For example, if you need to compare id1 and id2 (with some kind of locality sensitive hash or something). This requires a full join, which is prohibitive in any large data environment. And honestly, writing 50 case when statements when I could write a function in R or Python is not my idea of a good time. I love SQL, and do a lot with it, but there are definitely cases where it's the wrong tool. As an example, retention analyses are really annoying to do in SQL, but pretty easy in R/Python (to be fair, the article actually provides a solution here, but it's not standard).
- crazygringo 6y ago> Once I have all the data I need locally, it's MUCH faster to use pandas locally then to re-issue queries to a remote database. Both of you are right. Sure, if you need to grab a huge chunk of the entire database and then do tons of processing on every row that SQL simply cannot do, then you're right. But when most people think SQL and database, they're thinking of grabbing a tiny fraction of rows, sped up by many orders of magnitude because it utilizes indexes, and doing all calculations/aggregations/joins server-side. Where it absolutely is going to be orders of magnitude more efficient. Traditional database client API's often aren't going to be particularly performant in your case, because they're usually designed to read and store the entire result of a query in-memory before you can access it. If you're lucky you can enable streaming of results that bypasses this. Other times you'll be far better off accessing the data via some kind of command-line table export tool and streaming its output directly into your program.
- 3pt14159 6y agoI agree, they're both right. I've been doing data science since around 2008 and it's a balance between local needs and repeated analysis and an often more efficient one off query. Sure the SQL optimizer is going to read off the index for a count(*), but it doesn't really help if I need all the rows locally for data mining anyway. The counts need to line up! So I'll take the snapshot of the data locally for the one off analysis and call it a day. If I need this type of report to be run nightly, it will be off of the data warehouse infrastructure not the production DB server. Shrug. These things take type and experience to fully internalize and appreciate.
- tomnipotent 6y ago> But when most people think SQL and database We're not talking about most people, but data analysts. If we're just doing simple sum/count/average aggregated by a column or two with some basic predicates, SQL and an RDBMS are your best friend. Always better to keep compute + storage as close together as possible. > Traditional database client API's I'm not sure what point you're trying to make with this. Most data analysts are not working against streaming data, but performing one-off analyses for business counterparts that can be as simple as "X,Y,Z by A,B,C" reports to "which of three marketing treatments for our snail mail catalogue was most effective at converting customers".
- waltherg 6y agoThis is a great point and way of looking at it: pandas and SQL live at opposite ends of the maturation level of data wrangling pipelines.