Common Lisp Package: DATA-TABLE

README:

Data Table

A Common Lisp library providing a data-table data-structure that has rows of data and column names and types

  • reading/writing values by row/column or index
  • combining data-tables as overlapping square regions
  • pulling sub tables from a data table
  • comparing data from two data-tables
  • conversion from data-tables of strings into data-tables of lisp-values, automatically guessing types if necessary
  • converting from / two other common-lisp data-structures (lists of alists/plists)

Goals

  • easier interaction with databases by having a common data-structure that can be queried and inserted
  • generating complex reports that contain multiple sections on a single spreadsheet of data
  • make getting/setting values anywhere in the results easier

Authors

;; Copyright (c) 2011 Russ Tyndall , Acceleration.net http://www.acceleration.net ;; All rights reserved. ;; ;; Redistribution and use in source and binary forms, with or without ;; modification, are permitted provided that the following conditions are ;; met: ;; ;; - Redistributions of source code must retain the above copyright ;; notice, this list of conditions and the following disclaimer. ;; ;; - Redistributions in binary form must reproduce the above copyright ;; notice, this list of conditions and the following disclaimer in the ;; documentation and/or other materials provided with the distribution. ;; ;; THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS ;; "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT ;; LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR ;; A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT ;; OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, ;; SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT ;; LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, ;; DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY ;; THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT ;; (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE ;; OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

FUNCTION

Public

ADD-COLUMN (DT COLUMN-NAME &OPTIONAL DEFAULT (COLUMN-TYPE 'STRING) (INDEX 0))

Adds a new column to the data table passed in

ALISTS-TO-DATA-TABLE (LIST-OF-ALISTS &KEY (TEST #'EQUALP) (KEYS (ALIST-KEYS (FIRST LIST-OF-ALISTS))))

given a list of alists, (all with the same keys) convert them to a data-table

COERCE-DATA-TABLE-OF-STRINGS-TO-TYPES (DT)

Figure out what the data-table-types should be then convert all the data in the table to those types

COERCE-DATA-TABLE-VALUES-FOR-OUTPUT (DT &KEY OUTPUT-TYPE)

Coerce for output all the values in all the rows and return the new rows

COERCE-DATA-TABLE-VALUES-FOR-OUTPUT! (DT &KEY OUTPUT-TYPE)

Coerce for output all the values in all the rows and save the result to the rows slot

SELECT-COLUMNS (TABLE COLUMN-NAMES)

returns a new data table with only the columns requested, by name

Undocumented

DATA-TABLE-TO-ALISTS (DT)

DATA-TABLE-TO-PLISTS (DT)

PLISTS-TO-DATA-TABLE (LIST-OF-PLISTS &KEY (KEYS (PLIST-KEYS (FIRST LIST-OF-PLISTS))))

Private

%ADD-COLUMN-HEADING/TYPE (DT NAME TYPE INDEX)

this function tries to handle their not being any current column types or names or incomplete specification but will leave us with the same (+ 1 number-of-columns) as we started with

%INSERT-VALUE-IN-LIST (ROW INDEX VALUE)

build a new data row by splicing a value into the existing row

ENSURE-COLUMN-DATA-TYPES (DT)

Given missing data types or data-types only of strings, figure out what the data-types for the table should be set the slot on the data-table

MAYBE-APPLY (FN &REST ARGS)

Call a function, when it exists used to avoid calling clsql code if it does not exits

SAMPLE-ROWS (ROWS &KEY (SAMPLE-SIZE *GUESSING-TYPES-SAMPLE-SIZE*))

get a subset of the rows using reservior sampling

SIMPLIFY-TYPES (VAL &AUX (COMPLEX-TYPE (TYPE-OF VAL)))

try to get simple type definitions from complex ones

TRANSPOSE-LISTS (LIST-OF-LISTS)

Transpose a matrix represented as a list of lists. Example: (transpose '((a b c) (d e f))) => ((a d) (b e) (c f)).

TRIM-AND-NULLIFY (S)

trims the whitespace from a string returning nil if trimming produces an empty string or the string 'nil'

Undocumented

ALIST-KEYS (AL)

ALIST-VALUES (AL &OPTIONAL KEYS (TEST #'EQUALP))

GUESS-TYPES-FOR-DATA-TABLE (DATA-TABLE)

PLIST-KEYS (PL)

PLIST-VALUES (PL &OPTIONAL KEYS)

TRIM-WHITESPACE (S)

MACRO

Private

WHEN-LET (BINDINGS &BODY FORMS)

Creates new variable bindings, and conditionally executes FORMS. BINDINGS must be either single binding of the form: (variable initial-form) or a list of bindings of the form: ((variable-1 initial-form-1) (variable-2 initial-form-2) ... (variable-n initial-form-n)) All initial-forms are executed sequentially in the specified order. Then all the variables are bound to the corresponding values. If all variables were bound to true values, then FORMS are executed as an implicit PROGN.

GENERIC-FUNCTION

Public

Undocumented

COERCE-VALUE-FOR-OUTPUT (DT COLUMN-NAME VAL OUTPUT-TYPE)

DATA-TABLE-DATA-COMPARE (DT1 DT2 &KEY (TEST #'EQUALP) (KEY #'IDENTITY))

DATA-TABLE-VALUE (DT &KEY COL-NAME ROW-IDX COL-IDX)

SETFDATA-TABLE-VALUE (NEW DT &KEY COL-NAME ROW-IDX COL-IDX)

FILL-IN-MISSING-CELLS (DT &OPTIONAL MISSING-VALUE)

MAKE-SUB-TABLE (PARENT &KEY (LCI 0) (UCI (NUMBER-OF-COLUMNS PARENT)) (LRI 0) (URI (NUMBER-OF-ROWS PARENT)))

NUMBER-OF-COLUMNS (DT)

NUMBER-OF-ROWS (DT)

OVERLAY-REGION (NEW DT &KEY (ROW-IDX 0) (COL-IDX 0))

SYMBOLIZE-COLUMN-NAMES (DT)

SYMBOLIZE-COLUMN-NAMES! (DT)

Private

Undocumented

COLUMN-TYPE (CONDITION)

DATA-TABLE-COERCE (D TYPE)

ORIGINAL-ERROR (CONDITION)

RELAXED-PARSE-FLOAT (STR &KEY (TYPE 'DOUBLE-FLOAT))

VALUE (CONDITION)

SLOT-ACCESSOR

Public

Undocumented

COLUMN-NAMES (OBJECT)

SETFCOLUMN-NAMES (NEW-VALUE OBJECT)

COLUMN-TYPES (OBJECT)

SETFCOLUMN-TYPES (NEW-VALUE OBJECT)

ROWS (OBJECT)

SETFROWS (NEW-VALUE OBJECT)

VARIABLE

Public

*GUESSING-TYPES-SAMPLE-SIZE*

how many rows to look at when trying to guess the types for each column of a data table

Private

*LIST-DELIMITER*

What we will splice lists together with in coerce-value-for-output

+LARGEST-NUMBER+

this is the largest number that will be considered a number for data-type purposes.

Undocumented

+COMMON-WHITE-SPACE-TRIMBAG+

CLASS

Public

DATA-TABLE

A class representing a table of data

CONDITION

Public

Undocumented

BAD-TYPE-GUESS