13
$\begingroup$

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.

| improve this question | | | | |
$\endgroup$
  • 5
    $\begingroup$ pandas is not a datastore. Turn off your computer and your dataframe will not be there. pandas is for munging in memory. Which means if it does not fit in memory it will not work. But it has a big brother called Spark so that is not a big deal. The big brother does in fact support SQL and query optimization. See also pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html $\endgroup$ – Emre Jul 2 '17 at 20:29
8
$\begingroup$

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.
| improve this answer | | | | |
$\endgroup$
2
$\begingroup$

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
| improve this answer | | | | |
$\endgroup$
2
$\begingroup$

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.

| improve this answer | | | | |
$\endgroup$
2
$\begingroup$

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

SQL (usually) persistently stores data and is a database. It is also possible to run an in-memory SQL db which may be faster than using pandas, such as SQLite.

| improve this answer | | | | |
$\endgroup$
0
$\begingroup$

SQL allows you to persist and do many different relation transactions and always have it readily available for multiple different uses. Essentially one source of truth or place to go. There is over head for sure. However, some analyses can be very complicated and require significant amount of set based operations which can turn even a small data set into a large one very quickly. I have had data processes that have over 2000 queries that process terabytes in less than 5 mins and can score billions of records for a predictive model at the end and python and numpy scored a fraction of the dataset in 10x time as a relational data store and serve it up to a presentation layer.

An additional point, if doing this in the cloud make sure you have a dynamical instance that can scale its memory. With SQL it is all about having disc and enough compute to get it done in a timely fashion.

I see many ways that they can work in synergy. Many data science jobs are what Pandas was designed to do. Some data science jobs are what RDBs were designed to do. Use both in balance.

It is all about the right tool to do the right job.

| improve this answer | | | | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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