Data Science Stack Exchange is a question and answer site for Data science professionals, Machine Learning specialists, and those interested in learning more about the field. It only takes a minute to sign up.
Sign up to join this community
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:
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
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.
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.
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.
