Trjtdtk

In Data Science, many seem to be using pandas dataframes as the datastore. What are the features of pandas that make it a superior datastore compared to regular relational databases like MySQL, which are used to store data in many other fields of programming?

While pandas does provide some useful functions for data exploration, you can't use SQL and you lose features like query optimization or access restriction.

I think the premise of your question has a problem. Pandas is not a "datastore" in the way an RDBMS is. Pandas is a Python library for manipulating data that will fit in memory. Disadvantages:

• Pandas does not persist data. It even has a (slow) function called TO_SQL that will persist your pandas data frame to an RDBMS table.


• Pandas will only handle results that fit in memory, which is easy to fill. You can either use dask to work around that, or you can work on the data in the RDBMS (which uses all sorts of tricks like temp space) to operate on data that exceeds RAM.

From the pandas (Main Page)

Python Data Analysis Library¶

pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.

While pandas can certainly access data via SQL, or from several other data storage methods, its primary purpose is to make it easier when using Python to do data analysis.

To that end pandas has various methods available that allow some relational algebra operations that can be compared to SQL.

Also Pandas provides easy access to NumPy, which

is the fundamental package for scientific computing with Python. It contains among other things:

• a powerful N-dimensional array object


• sophisticated (broadcasting) functions


• tools for integrating C/C++ and Fortran code


• useful linear algebra, Fourier transform, and random number capabilities

Pandas is an in-memory data storage tool. This allows you to do very rapid calculations over large amounts of data very quickly.

SQL persistently stores data and is a database.

In addition to the accepted answer:

Relational databases have a large number of bytes of per-row overhead (example: this question), which is used for bookkeeping, telling nulls from not nulls, ensuring standards such as ACID. Every time you read/write a column, not only the few bytes representing the value of this column will be read, but also these bookkeeping bytes will be accessed and possibly updated.

In contrast, pandas (also R data.table) is more like an in-memory column store. One column is just an array of values and you are able to use fast numpy vectorized operations / list apprehensions that only access values that you really need. Just that for tables with few primitive columns makes relational databases multiple times slower for many data science use cases.