The data models we have seen so far in this topic are generally used for both transaction processing and analytics purposes (see “Operational Versus Analytical Systems”). There are also a few data models that you are likely to encounter in an analytical or scientific context but that rarely feature in OLTP systems, including DataFrames and multidimensional arrays of numbers such as matrices.
The DataFrame data model is supported by the R language, the Pandas library for Python, Apache Spark, ArcticDB, Dask, and other systems. DataFrames are a popular tool for data scientists preparing data for training ML models, but they are also widely used for data exploration, statistical data analysis, data visualization, and similar purposes.
At first glance, a DataFrame is similar to a table in a relational database or a spreadsheet. A DataFrame supports relational-like operators that perform bulk operations on its contents; for example, applying a function to all the rows, filtering the rows based on a condition, grouping rows by some columns and aggregating other columns, and joining the rows in one DataFrame with another DataFrame based on a key (what a relational database calls join is typically called merge on DataFrames).
Instead of using a declarative query language such as SQL, a DataFrame is generally manipulated through a series of commands that modify its structure and content. This matches the typical workflow of data scientists, who incrementally “wrangle” the data into a form that allows them to find answers to the questions they are asking. These manipulations usually take place on the data scientist’s private copy of the dataset, often on their local machine, although the end result may be shared with other users.
DataFrame APIs also offer a wide variety of operations that go far beyond what relational databases offer, and the data model is often used in ways that are very different from typical relational data modeling. For example, a common use of DataFrames is to transform data from a relational-like representation into a matrix or multidimensional array representation, which is the form in which many ML algorithms expect their input.
A simple example of such a transformation is shown in Figure 3-9. On the left we have a relational table indicating users’ ratings of various movies (on a scale of 1 to 5), and on the right the data has been transformed into a matrix where each column is a movie and each row is a user (similarly to a pivot table in a spreadsheet). The matrix is sparse, which means there is no data for many user–movie combinations, but this is fine. This matrix may have many thousands of columns and would therefore not fit well in a relational database, but DataFrames and libraries that offer sparse arrays (such as NumPy for Python) can handle such data easily.
Diagram illustrating the transformation of a relational table of user movie ratings into a sparse matrix, with users as rows and movies as columns.
Figure 3-9. Transforming a relational database of movie ratings into a matrix
A matrix can contain only numbers, and various techniques are used to transform nonnumerical data into numbers in the matrix. For example:
-
Dates (which are omitted from the example matrix in Figure 3-9) could be scaled to be floating-point numbers within a suitable range.
-
For columns that can take only one of a small, fixed set of values (e.g., the genre of a movie in a database of movies), one-hot encoding is often used. We create a column for each possible value (“comedy,” “drama,” “horror,” etc.), and for each row representing a movie, we put a 1 in the column corresponding to the genre of that movie and a 0 in all the other columns. This representation also easily generalizes to movies that fit within several genres.
Once the data is in the form of a matrix of numbers, it is amenable to linear algebra operations, which form the basis of many ML algorithms. For example, the data in Figure 3-9 could be a part of a system for recommending movies that the user might like. DataFrames are flexible enough to allow data to be gradually evolved from a relational form into a matrix representation, while giving the data scientist control over the representation that is most suitable for achieving the goals of the data analysis or model training process.
Some databases, such as TileDB, specialize in storing large multidimensional arrays of numbers; they are called array databases and are most commonly used for scientific datasets such as geospatial measurements (raster data on a regularly spaced grid), medical imaging, or observations from astronomical telescopes. DataFrames are also used in the financial industry for representing time-series data, such as the prices of assets and trades over time. Because of their popularity with data scientists, DataFrames have been added to batch processing frameworks such as Spark and Flink as well; we will return to this topic in the related discussion.