Tabular Asa🔗ℹ

A fast, efficient, immutable, dataframes implementation.

1 Sources🔗ℹ

The source code can be found at https://github.com/massung/tabular-asa.

2 Quick Example🔗ℹ

This is a brief example of loading a table from a CSV, filtering, grouping, aggregating, and plotting the data. Note: This example uses ~> from the threading module for clarity, but Tabular Asa does not require it.

(define books (call-with-input-file "books.csv" table-read/csv))

(let ([df (~> books

(table-drop-na '(Publisher))

(table-cut '(Genre Title))

(table-groupby '(Genre))

(group-count))])

(parameterize ([plot-x-tick-label-angle 30]

[plot-x-tick-label-anchor 'top-right])

(plot (discrete-histogram (for/list ([x (table-column df 'Genre)]

[y (table-column df 'Title)])

(list x y)))

#:x-label "Genre"

#:y-label "Number of Titles Published")))

3 Introduction🔗ℹ

Tabular Asa is intended to fulfill the following goals:

• Be as efficient as possible sharing memory.

• Be as lazy as possible; use sequences and streams.

• Be usable for everyday tabular data tasks.

• Be extremely simple to understand and extend.

• Be flexible by exposing low-level functionality.

• Be a learning example for students who want to learn how to implement databases.

Tabular Asa does this by starting with a couple very simple concepts and building on them. In order, those concepts are:

• Columns.

• Tables.

• Indexing.

• Grouping.

• Aggregating.

4 Row vs. Column Major🔗ℹ

When thinking about tabular data, it’s very common to think of each row (or record) as a thing to be grouped together. However, this is extremely inefficient for most operations; it requires extracting data from a larger collection into a smaller collection for many operations. It is also an inefficient use of cache. For this reason Tabular Asa is column-major.

A simple example of this difference in implementation would be cutting or inserting columns (a SELECT operation in SQL) to a table. Consider the following table of data:

When rows are stored as a sequence or hash, removing or adding a column requires duplicating every single row of data and copying it into a new sequence or hash, essentially doubling the memory usage and increasing the time it takes to perform the operation. However, if the table is stored as 3 columns, then creating a new table with a column added only adds the memory cost of the new column. Selecting a subset of columns is even easier.

Additionally, every table contains a vector which is the index of which rows it contains from the original column data. This allows for tables that are filters to simply reference the existing column data, but with a new index. In the above example table, the index would be the vector #(0 1 2). If a new table was generated by filtering the original, keeping only the girls, then the new table would contain all the same column data (referencing the exact same columns in memory), but the index would be #(2).

5 Row vs. Record🔗ℹ

For the purposes of this documentation and function names, a "row" is defined as a list? and a "record" is defined as hash?.

Likewise, the symbol k is used in place of a column name (a symbol?) and the symbol ks is used for a list of column names.

6 Reading Tables🔗ℹ

It is important to note that - when reading tables - columns that don’t already exist will be generated on-demand. The column names will be equivelant to calling (gensym "col") to ensure they are unique symbols.

• A list of values

• An associative list of (col value) pairs

• A hash-eq? mapping columns to values

If the columns parameter is supplied, that will be used as the initial set of column names supplied to the table-builder%. This can be especially useful when supplying hashes to guarantee the column order.

(table-read/columns '((0 1 2) #("a" "b" "c") "def") '(col1 col2 col3))

If jsexpr is a JSON object (hash-eq?), then it is assumed to be a hash of columns, where each column contains the values for it.

If jsexpr is a JSON array (list?), then it is assumes to be a list of hash-eq? records, where the key/value pairs are the column names and values for each row of the table.

The header argument - if #t - indicates that the first non-comment row of the CSV should be treated as the list of column names. If #f then the column names will be generated as needed.

The drop-index arugment - if #t - assumes that the first column of the CSV is the row index (i.e., an auto-incrementing integer) and shouldn’t be kept. If there is a row index column, and it is not dropped, it’s important to note that it will be treated just like any other column and is NOT used as the table’s index.

The na-values argument is a list of strings that - when parsed as the value for a given cell - are replaced with the na value to indicate N/A (not available). The values in this list are case-insensitive.

7 Building Tables🔗ℹ

Tables can also be built at constructed using an instance of table-builder% or with the for/table macro.

The init-forms are the optional, initial forms used to create a table-builder% instance.

Each iteration of body should return either a list? or hash-eq?, which will automatically be sent to the table-builder% using add-row or add-record. It’s also possible to mix and match (i.e., return a list for one iteration and a hash for another).

When the for-clause terminates, the table is built and returned.

Example:

(for/table ([initial-size 3]

[columns '(hero universe)])

([i 3])

(case i

((0) '("Superman" "DC"))

((1) '("Wolverine" "Marvel"))

((2) '("Batman" "DC"))))

8 Tables🔗ℹ

All tables are also sequences and can be iterated using for, where each iteration returns the next index and row (list). For example:

(define df (table #(0 1 2)

'((hero . #("Superman" "Batman" "Wonder Woman"))

(gender . #(m m f)))))

(for ([(i row) df])

(displayln row))

However, if you may supply your own function, or even replace it with display-table or print-table if you always want to see a preview of the table on the REPL.

If the data sequence contains fewer elements than there are rows in the table, then the extra rows will be filled with #f for the new column. Likewise, if data contains more values than there are rows, the extra values will be dropped.

It’s important to note that the vector for the column generated will be as large as necessary for it to be indexed properly by the table! For example, let’s say you begin with a table that has 1M rows and filter it, which returns a new table with a single row. If the index of that single row is 999999 (i.e., the last row), then adding a new column will create a vector with 1M entries, all but one of which will contain #f.

(table-with-columns-renamed df #hasheq((person . name)))

For a more concrete example of this, imagine a brand new table with a single column of 3 value: #(a b c); it has an index of #(0 1 2). Now, reverse the table; the index is now #(2 1 0). Calling (table-irow df 2) will return '(c), because that’s the value at index 2. However, calling (table-row df 2) will return '(a), because that’s the value of the third row; the table has been reversed.

This can be seen in action easily with the following code:

(let ([df (table-reverse (table-with-column empty-table '(a b c)))])

(for ([(i row) df] [n (in-naturals)])

(displayln (format " for ~a = ~v" i row))

(displayln (format "irow ~a = ~v" i (table-irow df i)))

(displayln (format " row ~a = ~v" n (table-row df n)))))

The for loop follows the index, so index 2 should be output first, which is reference row 0. The last index output is 0, which is the last reference row (2).

See the comment for table-irow for more details.

Example:

(define df (table-read/columns '(("Jeff" "Aaron" "Rachel")

(48 14 24))

'(name age)))

(sequence->list (table-map df (λ (row) (string-join (map ~a row)))))

Example:

(define df (table-read/columns '(("Jeff" "Aaron" "Rachel")

(48 14 24))

'(name age)))

(sequence->list (table-apply df (λ (name age) (format "~a ~a" name age))))

(table-with-column df (table-apply df proc (list k)) #:as k)

If ignore-na is #t (the default), then all #f values are returned as #f instead of being updated.

Rows are joined using the columns of df specified by on with the columns of other specified by with. If with is not provided, then the columns joined are expected to be the same for both tables. The columns joined must be equal?.

Example joining two tables using the same column name:

(table-join/inner df other '(id))

Example joining two tables with different column names:

(table-join/inner df other '(name gender) #:with '(surname sex))

If the on and with parameters are of different lengths, no error will be triggered and an empty table will be returned as all comparisons will fail.

9 Printing Tables🔗ℹ

10 Writing Tables🔗ℹ

If orient is 'records (the default) then every row of the table is written as an array of JSON objects. If lines is #t (and orient is 'records) then instead of an array, then each row is written as a record on each line.

If orient is 'columns then the table is written as a single JSON object, where each key/value pairing is the column name and an array of values.

The na determines what Racket value in written out as a JSON null.

11 Columns🔗ℹ

12 Groups🔗ℹ

13 Indexes🔗ℹ

If the index is not sorted then the order of the sequence returned is undefined.