3.4 DataFrames, Matrices, and Arrays
Models you'll meet in analytical or scientific contexts that rarely feature in OLTP.
Models you'll meet in analytical or scientific contexts that rarely feature in OLTP.
DataFrames are supported by R, Pandas (Python), Apache Spark, ArcticDB, Dask. Popular for preparing data to train ML models, and widely used for data exploration, statistical analysis, and visualization.
Superficially like a relational table or spreadsheet. Supports relational-like bulk operators: apply a function to all rows, filter on a condition, group by columns and aggregate others, and join — which on DataFrames is typically called merge.
The key difference: instead of a declarative query language, a DataFrame is manipulated through a series of commands that modify its structure and content. This matches how data scientists work: incrementally "wrangling" data into a form that answers their question, usually on a private copy of the dataset, often on their local machine, with the end result possibly shared.
DataFrame APIs go far beyond relational databases, and the model is often used in very un-relational ways. A common use: transform data from a relational-like representation into a matrix or multidimensional array — the form many ML algorithms expect.
| Relational — long | Matrix — wide and sparse | ||
|---|---|---|---|
| user | movie | rating | m1 m2 m3 … m9999 |
| u1 | m1 | 5 | |
| u1 | m3 | 3 | |
| u2 | m2 | 4 | |
| u2 | m9999 | 2 | |
Pivoting gives thousands of columns, which does not fit a relational database well — while sparse arrays (NumPy) handle it easily.
Getting non-numeric data into a matrix (a matrix can contain only numbers):
- Dates → scaled to floating-point numbers within a suitable range
- Categorical columns with a small fixed set of values (movie genre) → one-hot encoding: create a column per possible value ("comedy", "drama", "horror"), put 1 in the column matching the row's genre and 0 in the others. This generalizes easily to movies that fit several genres (multiple 1s).
Once numeric, the data is amenable to linear algebra operations, which form the basis of many ML algorithms — e.g. a movie recommender. DataFrames are flexible enough to let data gradually evolve from a relational form into a matrix representation, while giving the data scientist control over the most suitable representation.
Array databases (e.g. TileDB) specialize in large multidimensional arrays of numbers, used for scientific datasets: geospatial raster data on a regularly spaced grid, medical imaging, astronomical telescope observations. DataFrames are also used in finance for time-series data (asset prices and trades over time). Because of their popularity, DataFrames have been added to batch frameworks like Spark and Flink (Ch 11).