I've been using SQL since 1996, so I may be biased. I've used MySQL and SQLite 3 extensively, but have also used Microsoft SQL Server and Oracle.

The vast majority of the operations I've seen done with Pandas can be done more easily with SQL. This includes filtering a dataset, selecting specific columns for display, applying a function to a values, and so on.

SQL has the advantage of having an optimizer and data persistence. SQL also has error messages that are clear and understandable. Pandas has a somewhat cryptic API, in which sometimes it's appropriate to use a single [ stuff ], other times you need [[ stuff ]], and sometimes you need a .loc. Part of the complexity of Pandas arises from the fact that there is so much overloading going on.

So I'm trying to understand why Pandas is so popular.

up vote 29 down vote accepted

You can probably have many technical discussions around this, but I'm considering the user perspective below.

One simple reason why you may see a lot more questions around Pandas data manipulation as opposed to SQL is that to use SQL, by definition, means using a database, and a lot of use-cases these days quite simply require bits of data for 'one-and-done' tasks (from .csv, web api, etc.). In these cases loading, storing, manipulating and extracting from a database is not viable.

However, considering cases where the use-case may justify using either Pandas or SQL, you're certainly not wrong. If you want to do many, repetitive data manipulation tasks and persist the outputs, I'd always recommend trying to go via SQL first. From what I've seen the reason why many users, even in these cases, don't go via SQL is two-fold.

Firstly, the major advantage pandas has over SQL is that it's part of the wider Python universe, which means in one fell swoop I can load, clean, manipulate, and visualize my data (I can even execute SQL through Pandas...). The other is, quite simply, that all too many users don't know the extent of SQL's capabilities. Every beginner learns the 'extraction syntax' of SQL (SELECT, FROM, WHERE, etc.) as a means to get your data from a DB to the next place. Some may pick up some of the more advance grouping and iteration syntax. But after that there tends to be a pretty significant gulf in knowledge, until you get to the experts (DBA, Data Engineers, etc.).

tl;dr: It's often down to the use-case, convenience, or a gap in knowledge around the extent of SQL's capabilities.

  • 2
    I think SQL largely being set based plays a large part, when a lot of people from other technical areas are used to handling data line by line. Also consider that data is mostly just data to pandas but different SQL engines support different built in functions which can get wildly annoying fast if you have to chop and change during your workday – Dave Jul 12 at 14:26
  • 3
    I wouldn't say it's not viable. If you can get the data into a pandas data frame, you can probably shove it into a PostgreSQL DB. But for one and done, it's probably more effort and time than you would save. – jpmc26 Jul 13 at 7:37
  • 2
    I agree that some ETL approaches appear to be programmer-centric decisions. That is, they prefer to manipulate the data then present this "perfect" payload to the database. However, as you indicate, if it can be done via several SQL queries, then the extra programmatic layer is unnecessary. Exactly what I faced recently. As the OP and your answer indicates, it could be that "old-school" or DBA-centric people look at it and say, why not do it in SQL (even just several simple queries!). That said, I've found pandas to be very powerful for extremely diverse data sets. – SaltySub Jul 13 at 8:45
  • 1
    @SaltySub Just a point on shifting things out of the programmatic layer into SQL: It's a fair point and can be perfectly valid, but going as far as burying application logic in SQL procedures can bring its own special flavour of headache. – Electric Head Jul 13 at 18:23
  • @ElectricHead I agree that there needs to be a right balance. If a series of SQL queries can perform the tasks adequately, it can definitely be easier and more efficient. Conversely, as you indicate, if one has to place a huge amount of logic into SQL procedures, etc. then pandas should be strongly considered. Particularly as above if you are using different database flavours - SQL syntax differences can get very hairy then. – SaltySub Jul 15 at 3:26

As much as there is overlap in the application of these two things, this is comparing apples to oranges.

pandas is a data analysis toolkit implemented in Python, a general purpose programming language. SQL is a domain-specific language for querying relational data (usually in an relational database management system which SQLite, MySQL, Oracle, SQL Server, PostgreSQL etc. are examples).

SQL implies

  • working with data in an RDBMS* which may or may not be appropriate for the workload, even if it's just a small SQLite database,
  • database domain knowledge (as an end user, developer and/or administrator; the suggestion that "SQL is faster" I often see is a massive over-simplification), and
  • overcoming the not-insignificant learning curve in using SQL effectively, particularly in specialist applications such as data analysis (as opposed to creating simple reports of simple data).

* It's worth underlining the fact that SQL is so domain-specific it's becoming much less relevant to working with increasingly common alternatives to relational databases such as NoSQL databases. This represents a fundamental shift in how data is stored and structured, and there is really no universally common way of accessing it like the development of SQL standardisation aimed to achieve.

Python on the other hand (pandas is fairly "pythonic" so it holds true here) is flexible and accessible to people from various backgrounds. It can be used as a "scripting language", as a functional language and a fully featured OOP language. Visualisation capabilities and data source interoperability are built into pandas, but you're free to incorporate whatever Python can do into your workflow (which is most things); the scientific Python ecosystem has ballooned and includes great tools such as Jupyter Notebook and essential scipy libraries such as matplotlib and numpy (which pandas builds on). Significant elements of pandas' data analysis is R-inspired and you won't generally find statisticians umming and ahhing about whether they use R (or possibly increasingly pandas!) over putting everything in a database and writing their analyses in SQL.

I'm not saying pandas is better than SQL or vice versa, but SQL is a very domain-specific tool whereas pandas is part of a giant, flexible and accessible ecosystem. I work with geospatial data systems, of which relational databases are a huge part, and SQL is a powerful and essential tool. However, pandas is an equally if not more essential part of my day-to-day toolkit and SQL is often relegated to fetching data -- perhaps with some pre-processing -- so I can do things with it in pandas.

  • 1
    This is the only true answer, it should be the chosen one. SQL and Pandas are two different things, I do not understand what comparison people are trying to make. – gented Jul 13 at 6:51
  • I suspect it's an end-user perspective of writing something code-like to fetch and massage some data from somewhere and spit out some numbers. I'm not entirely surprised; I've had first hand experience of how data analysts presented with an old but otherwise unremarkable Oracle database haven't even the first idea of what it is and how to connect to it let alone get data out. I believe it betrays a fundamental lack of understanding of the technology - I've actually added a bit to hopefully emphasise how quickly misunderstanding the scope of SQL falls down. – Electric Head Jul 13 at 18:00
  • I'd challenge your bit about being irrelevant to NoSQL situations. Consider for example the strides PostgreSQL has made with its JSON storage. – jpmc26 Jul 14 at 20:31
  • I tried to choose my words carefully; PostgreSQL is still an RDBMS despite doing a lot of things well (as SQL Server is despite supporting graphs). But, I've relaxed the wording a touch because it's still a good point: there is some crossover and, importantly, SQL APIs do exist for some NoSQL systems. It is crossover though, SQL is not a universal language and not all data is structured relationally. – Electric Head Jul 15 at 8:25
  • I think you can do everything in SQL which is possible in pandas. SQL is not flexible but is so much optimised. – Media Jul 18 at 0:55

First, pandas is not that much popular. I use both pandas and SQL. First I try to understand the task- if it can be done in SQL, I prefer SQL because it is more efficient than pandas. Try working on a large data (10,000,000 x 50). Try to do some groupby operation in both SQL and pandas. You will understand.

I use pandas where it comes handy- like splitting a column values into an array and doing some stuff on it (like choosing only some values out of that array). Now this kind of task is relatively hard to code in SQL, but pandas will ease your task.

  • Is this inefficiency specific to pandas? I've done quite a bit of in-memory data manipulation in C# and found it pretty easy and efficient, provided it fit memory and was one-shot (i.e. no need to incrementally update indexes as the data changes). – CodesInChaos Jul 13 at 11:59
  • pandas is meant to be convenient over fast, but that's not to say it can't be fast if you use it right. In the end, executing a SQL query on data in a database isn't magic - it requires resources like anything, it's just that (if you do it right!) you're hopefully making use of resources on carefully configured, beefy database servers. Getting your pipeline right in pandas or similar (e.g. streaming data rather than loading it all into memory) is going to determine how successful some efforts are. – Electric Head Jul 13 at 18:05
  • @CodesInChaos There is this answer of pandas vs SQl - qr.ae/TUIpzE. There it is described the advantages and disadvantages of using pandas. – Ankit Seth Jul 14 at 2:38

I'm one of those people who would use (in my case) R's dplyr (the language, not necessarily the tool) in every case if I could even though I know my SQL.

The major benefit I see in Pandas/dplyr/data.table pipelines is that the operations are atomic and can be read top to bottom.

In SQL you need to parse the whole script, jumping around (what's being sumamrized, what's being joined and how - left? inner? right?, are there any filters applied?) to fully grasp what is happening.

In Pandas et al each step of the pipeline is self contained, it does something with the input data and returns output data, this sequential process makes it easier to reason about what's happening since there is a clearly defined state for each operation rather than just on a query level.

And yes you can do WITH statements and such but it requires much more code and is not as clear what object is being used compared to piping.

I thought I would add that I do a lot of time-series based data analysis, and the pandas resample and reindex methods are invaluable for doing this. Yes, you can do similar things in SQL (I tend to create a DateDimension table for helping with date-related queries), but I just find the pandas methods much easier to use.

Also, as others have said, the rest of my modelling is in Python, and I often have web calls or CSV files.

The only thing not covered in these answers that I'd like to mention is that it also depends on how you're using SQL. Take arcpy for example. For some reason none of the arcpy.da functions have an execute many feature. This is really strange because pretty much every other python sql library does. The Where statement in the arcpy.da functions is also limited to around 120 characters. This essentially means that if you have any relatively high number of things you're trying to do with your database your only real choice is to call your chosen arcpy.da function multiple times, changing the where statement each time you do. There are a few tricks you can use to make this process go faster - you can iterate over chunks of your dataset for example - but literally every single of these tricks is much much slower than just using one arcpy.da.searchcursor to load your entire table into a pandas data frame, and then manipulating it using pandas, numpy, and, if your data is really this massive, dask. I need to emphasize here that pandas isn't just a little faster in this case. It's disgustingly faster. It's so much faster that I was literally laughing at myself for not doing it sooner. Using pandas dropped one scripts execution time down from well over an hour - I forget if this was the jump from 3.5 hours or from 1.5 hours - to literally 12 minutes.

One thing to note is that while I could have done this with sql it would have taken me a lot longer to learn. I would have either had to learn operations specifically for sql in Access - that's where the data for this script ended up - - sql in Access wasn't as robust as I needed it to be when I was actually looking into doing this -, or I would have had to write all my data to a sqlite3 database, manipulate it there, and then put it in Access. While this might have given me similar performance results, it would have made my script harder to modify in the future.

So yeah, sometimes Pandas and is just strictly better than using the sql options you have at your disposal. Everything I would have needed to do in sql was done with a function in pandas. You can also use sql syntax with pandas if you want to. There's little reason not to use pandas and sql in tandem.

One more thing I want to mention about Pandas and numpy is that both of these libraries are by nature set based approaches. You can loop through dataframes and series build with these libraries, but it's really hard to modify data in these structures like that so you'll end up writing more efficient code - set based - with both of these libraries purely because it's so much easier to do. Being "guided" if not rail-roaded into using set based approaches is not something I've experienced with SQL.

One more massive thing I forgot to mention with Pandas. Money. Pandas is a tool that a lot of Data Science jobs want you to know how to use. Pretty much every Data Science job I've looked at has paid more than database management type jobs. The only exception to this that I've noticed is in Data Engineering, but I've seen far less of those job postings. Pandas looks like it makes you more money at a glance.

  • 4
    Perhaps sad that when it comes to modern jobs it's about having the right buzzwords in your resume as opposed to the approaches you take to solving a problem (assuming you can learn said buzzword relatively fast). It's like the buzzword is more important than the problem solving. When the problem solving for X should involve learning and using technology A, B, C, not the reverse. I wonder if most development teams now smash out things because of buzzword-ism and trendiness, then think about problem solving as a secondary, or "old-school" thing because you didn't know/use said buzzword. – SaltySub Jul 13 at 8:49
  • 1
    @ElectricHead in my experience if you're writing your own function involving sql in python, it's easier to just misuse your cursor and write bad queries than it is using pandas/numpy. Gotta remember that not all sql modules/libraries are made the same. In my case, with arcpy.da.SearchCursors and the like, there's really not a good way to do something to a bunch of records efficiently because of weird limitations. If I use pandas/numpy there becomes one good way to do things, and that's what I want when using python. – Steve Jul 13 at 18:23
  • 1
    Ahhh, OK. You mean a homespun SQL pipeline via a python dbapi implementation vs using numpy/pandas? In which case, yeah gotcha, no argument from me there; care required! It read to me as vs plain SQL which you obviously need to understand set operations with, but will find that out quite quickly when running silly queries from a database client. – Electric Head Jul 13 at 18:33
  • 1
    @Steve Yes, won't stop people trying to dynamically modify stuff in loops in pandas or similar though :) I think understanding SQL helps working in pandas effectively (it's not like they hide the similarity in some concepts though). – Electric Head Jul 13 at 18:35
  • 1
    @Steve Indeed pandas is powerful too... I guess one of my frustrations is developers and management both, including myself, not spending adequate time evaluating solutions and chasing trends (where money is involved to promote self/company). But even in lean prototyping/ mvp one would have to lay appropriate groundwork for scaling. SQL, noSQL and Pandas... all have their purposes for the appropriate tasks and projects at different stages. For the past year plus, noSQL for a lean prototype/ mvp certainly helped me in more ways than one. SQL would have been overkill for that. – SaltySub Jul 15 at 3:32

Things Pandas can do, that SQL can't do

  1. df.describe()
  2. Plotting, e.g. df['population'].plot(kind='hist')
  3. Use a dataframe directly for training machine learning algorithms

Things Pandas can do, I wasn't aware that SQL can do as well

  1. Export to csv: df.to_csv('foobar.sv'). This is important when you want to show something to a business owner who wants to work with Excel. And there is df.to_excel as well. But in SQL, you can do SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table; (thank you, vy32!)
  • Nice. Although most of these seem like functions that could be implemented in SQL. (SQL does have directly CSV export.) – vy32 Jul 18 at 19:51
  • Could you please send me a query which exports to CSV? (I only know tools which do this for some SQL-based databases, but I've never seen a query ... so I doubt that this is part of the SQL specification) – Martin Thoma Jul 18 at 20:19
  • 1
    SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table; See dev.mysql.com/doc/refman/8.0/en/select-into.html – vy32 Jul 19 at 14:17
  • Thank you so much, vy! I think I'll adjust my answer when I'm home :-) – Martin Thoma Jul 19 at 16:52
  • Sure thing. Remember, the file ends up on the SQL server, not the client. – vy32 Jul 20 at 18:21

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.