Ggthjy

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.

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.

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.

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!)

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.

I'm fairly new to Pandas/Python but have 20+ years as a SQLServer DBA, architect, administrator, etc.. I love Pandas and I'm pushing myself to always try to make things work in Pandas before returning to my comfy, cozy SQL world.

Why RDBMS's are Better:
The advantage of RDBMS's are their years of experience optimizing query speed and data read operations. What's impressive is that they can do this while simultaneously balancing the need to optimize write speed and manage highly concurrent access. Sometimes these additional overheads tilt the advantage to Pandas when it comes to simple, single-user use cases. But even then, a seasoned DBA can tune a database to be highly optimized for read speed over write speed. DBA's can take advantage of things like optimizing data storage, strategic disk page sizing, page filling/padding, data controller and disk partitioning strategies, optimized I/O plans, in-memory data pinning, pre-defined execution plans, indexing, data compression, and many more. I get the impression from many Pandas developers that they don't understand the depth that's available there. What I think usually happens is that if Pandas developer never has data that's big enough to need these optimizations, they don't appreciate how much time they can save you out of the box. The RDBMS world has 30 years of experience optimizing this so if raw speed on large datasets are needed, RDBMS's can be beat.

Why Is Python/Pandas Better:
That said, speed isn't everything and in many use cases isn't the driving factor. It depends on how you're using the data, whether it's shared, and whether you care about the speed of the processing.
RDBMS's are generally more rigid in their data structures and put a burden on the developer to be more deterministic with data shapes. Pandas lets you be more loose here. Also, and this is my favorite reason, you're in a true programming language. Programming languages give you infinitely more flexibility to apply advanced logic to the data. Of course there's also the rich ecosystem of modules and 3rd party frameworks that SQL can't come close to. Being able to go from raw data all the way to web presentation or data visualization in one code base is VERY convenient. It's also much more portable. You can run Python almost anywhere including public notebooks that can extend the reach of your results to get to people more quickly. Databases don't excel at this.

My Advice?
If you find yourself graduating to bigger and bigger datasets you owe it to take the plunge and learn how RDBMS's can help. I've seen million row, multi-table join, summed aggregate queries tuned from 5 minutes down to 2 seconds. Having this understanding in your tool belt just makes you a more well rounded data scientist. You may be able to do everything in Pandas today but some day your may have an assignment where RDBMS is the best choice.

I'll attempt to answer this question based on my own experience. In contrast to the other answers, I prefer Sql for deep learning and big-data-related things. There are numerous reasons for that. As it can be seen here,

Pandas provides an intuitive, powerful, and fast data analysis experience on tabular data. However, because Pandas uses only one thread of execution and requires all data to be in memory at once, it doesn’t scale well to datasets much beyond the gigabyte scale.

Sql engines usually keep the keys or special columns in data-structures like $B ^+$ tree in order to facilitate CRUD operations. This data structure keeps the status of all data in the database. This is not pandas can do because it can't access all the data simultaneously. On the other hand, it can't do some operations even with its chunk parameter used in read_csv. As an example, you can't have direct batch operations for large datasets that your memory can't accommodate them. Any other tasks which depend on your entire dataset need extra coding. All of these can be handled in Sql without extra coding, just with a simple query. Simple Sql operations are just used without any fear about the memory.

Another difference is that CRUD operations in Sql can be applied distributed with different authorisation policies which are not possible in pandas.

It is not meant to say which is better, it all depends on your task. For large scale computation I prefer Sql and for small ones, I prefer pandas.

There are other things that are not in pandas which are really important for fast experience for data extraction that I'll refer to later. For now, just take a look at here.

Panda is more popular since python in the form of jupyter notebooks is the most populair toolbox as used by data scientist in neural network area. Python is becomming "the" langauge. Its even possible to use SQL backend but your not bound to SQL only with panda.