Constructors
Functions that construct a table.
table
Creates a table by accepting block of column names and flat block of values
columns
data
table { "a" } { 1 2 } |type?
; returns tabletable { 'a } { 1 2 } |type?
; returns tabletable\columns
Creats a table by accepting a block of columns
columns - names of the columns
data - block or list of columns (each column is a block or list)
table\columns { "a" } { { 1 2 3 } } |type?
; returns tabletable\columns { "a" "b" } { { 1 2 3 } { 4 5 6 } } |length?
; returns 3table\ ows
Creates a table by accepting a block or list of rows
columns - names of the columns
data - block or list of rows (each row is a block or list)
table\rows { 'a 'b } { { 1 2 } { 3 4 } }
; returns table { "a" "b" } [ 1 2 3 4 ]table\rows { 'a 'b } list [ list [ 1 2 ] list [ 3 4 ] ] |type?
; returns tableto-table
Creates a table by accepting block or list of dicts
data
to-table list [ dict { "a" 1 } dict { "a" 2 } ] |type?
; returns tableto-table list [ dict { "a" 1 "b" "Jim" } dict { "a" 2 "b" "Bob" } ] |header? |sort
; returns list { "a" "b" }to-table list [ dict { "a" 1 "b" "Jim" } dict { "a" 2 "b" "Bob" } ] |column? "b" |first
; returns "Jim"Filtering
Functions that construct a table.
where-equal
Returns table of rows where specific colum is equal to given value.
sheet
column
value
Tags #filter #tables
table { 'a } { 1 2 3 2 } |where-equal "a" 2 |length?
; returns 2where-not-equal
Returns table of rows where specific colum is not equal to given value.
sheet
column
value
Tags #filter #tables
table { 'a } { 1 2 3 2 } |where-not-equal "a" 2 |length?
; returns 2where-void
Returns table of rows where specific colum is equal to given value.
sheet
column
Tags #filter #tables
table { 'a } { 1 _ 3 _ } |where-void "a" |length?
; returns 2where-match
Returns table of rows where a specific colum matches a regex.
sheet
column
regexp
Tags #filter #tables
table { 'a } { "1" "2" "a3" "2b" } |where-match 'a regexp "^[0-9]$" |length?
; returns 2where-contains
Returns table of rows where specific colum contains a given string value.
sheet
column
substring
Tags #filter #tables
table { 'a } { "1" "2" "a3" "2b" } |where-contains 'a "2" |length?
; returns 2where-not-contains
Returns table of rows where specific colum contains a given string value.
sheet
column
substring
Tags #filter #tables
table { 'a } { "1" "2" "a3" "2b" } |where-not-contains 'a "3" |length?
; returns 3where-greater
Returns table of rows where specific colum is greater than given value.
sheet
column
value
Tags #filter #table
table { 'a } { 1 2 3 2 } |where-greater 'a 1 |length?
; returns 3where-lesser
Returns table of rows where specific colum is lesser than given value.
sheet
column
value
Tags #filter #table
table { 'a } { 1 2 3 2 } |where-lesser 'a 3 |length?
; returns 3where-between
Returns table of rows where specific colum is between given values.
sheet
column
lower-limit
upper-limit
Tags #filter #table
table { 'a } { 1 2 3 2 } |where-between 'a 1 3 |length?
; returns 2where-between\inclusive
Returns table of rows where specific colum is between given values.
sheet
column
lower-limit
upper-limit
Tags #filter #table
table { 'a } { 1 2 3 2 5 } |where-between\inclusive 'a 2 3 |length?
; returns 3where-in
Returns table of rows where specific colum value if found in block of values.
sheet
column
values-filtered-for
Tags #filter #table
table { "name" "age" } { "Enno" 30 "Enya" 25 "Bob" 19 } |where-in 'name { "Enno" "Enya" "Roger" } |column? "age"
; returns { 30 25 }Row level functions
Functions that construct a table.
add-row
Returns a table with new-row added to it
sheet
new-row
table { "a" "b" } { 6 60 7 70 } |add-row { 8 80 } |-> 2 |-> "b"
; returns 80get-rows
Get rows as a native. This value can be used in `add-rows` and `add-rows!`
sheet
table { "a" "b" } { 6 60 7 70 } |get-rows |type?
; returns nativeadd-rows
Add one or more rows to a table
sheet - the sheet that is getting rows added to it
rows - a block containing one or more rows worth of values, or a TableRow Native value
ref table { "a" "b" } { 6 60 7 70 } :sheet sheet .deref |add-rows [ 3 30 ] |length?
; returns 3ref table { "a" "b" } { 1 80 2 90 } :sheet sheet .deref |add-rows { 3 30 } |length?
; returns 3add-rows!
Add one or more rows to a table ref
sheet - the reference to the sheet that is getting rows added to it
rows - a block containing one or more rows worth of values, or a TableRow Native value
Tags #spreasheet #mutation
ref table { "a" "b" } { 1 10 2 20 } :sheet sheet .add-rows! [ 3 30 ] sheet .deref .length?
; returns 3update-row!
Update the row at the given index.
sheet-ref - A ref to a table
idx - the index of the row to update, 1-based
updater - One of either a function, a dict, or a Table Row
Tags #table #mutation
spr1: ref table { "a" "b" } { 1 10 2 20 } spr1 .update-row! 1 dict [ "a" 111 ] spr1 .deref .A1
; returns 111spr1: ref table { "a" "b" } { 1 10 2 20 } incrA: fn { row } { row ++ [ "a" ( "a" <- row ) + 9 ] } update-row! spr1 1 ?incrA spr1 |deref |A1
; returns 10remove-row!
Remove a row from a table by index
sheet-ref
row-idx - Index of row to remove, 1-based
Tags #table #mutation
spr1: ref table { "a" "b" } { 1 10 2 20 } spr1 .remove-row! 1 spr1 .deref .A1
; returns 2Column level functions
Functions that construct a table.
columns?
Returns table with just given columns.
table { "name" "age" "job_title" } { "Bob" 25 "Janitor" "Alice" 29 } |columns? { 'name 'age } |header?
; returns list { "name" "age" }header?
Gets the column names (header) as block.
table { "age" "name" } { 123 "Jim" 29 "Anne" } |header?
; returns list { "age" "name" }column?
Gets all values of a column as a block.
table { "name" "age" "job_title" } { "Bob" 25 "Janitor" "Alice" 29 "Teacher" } |column? 'name
; returns { "Bob" "Alice" }drop-column
Remove a column from a table. Returns new table
table { "name" "age" "job_title" } { "Bob" 25 "Janitor" "Alice" 29 "Librarian" } |drop-column "name" |header?
; returns list { "age" "job_title" }rename-column!
Remove a column from a table. Returns new table
tab: ref table { "name" "age" } { "Bob" 25 "Alice" 29 "Charlie" 19 } tab .rename-column! "name" "first_name" , tab .header?
; returns list { "first_name" "age" }add-column
Adds a new column to table. Changes in-place and returns the new table.
table { "name" "age" } { "Bob" 25 "Alice" 29 "Charlie" 19 } |add-column 'job { } { "Cook" } |column? "job"
; returns { "Cook" "Cook" "Cook" }Miscelaneous
order-by!
Sorts row by given column, changes table in place.
tab: table { "name" "age" } { "Bob" 25 "Alice" 29 "Charlie" 19 } tab .order-by! 'age 'asc |column? "age"
; returns { 19 25 29 }order-by
Sorts row by given column, changes table in place.
tab: table { "name" "age" } { "Bob" 25 "Alice" 29 "Charlie" 19 } |order-by 'age 'desc |column? "age"
; returns { 29 25 19 }add-indexes!
Creates an index for all values in the provided columns. Changes in-place and returns the new table.
table { "name" "age" } { "Bob" 25 "Alice" 29 "Charlie" 19 } |add-indexes! { name } |indexes?
; returns { "name" }indexes?
Returns the columns that are indexed in a table.
table { "name" "age" } { "Bob" 25 "Alice" 29 "Charlie" 19 } |add-indexes! { name age } |indexes?
; returns { "name" "age" }autotype
Takes a table and tries to determine and change the types of columns.
table { "age" } { 123 29 19 } |autotype 1 |types?
; returns { integer }left-join
Left joins two tables on the given columns.
names: table { "id" "name" } { 1 "Paul" 2 "Chani" 3 "Vladimir" } , houses: table { "id" "house" } { 1 "Atreides" 3 "Harkonnen" } , names .inner-join houses 'id 'id |header?
; returns list { "id" "name" "id_2" "house" }names: table { "id" "name" } { 1 "Paul" 2 "Chani" 3 "Vladimir" } , houses: table { "id" "house" } { 1 "Atreides" 3 "Harkonnen" } , names .left-join houses 'id 'id |column? "name"
; returns { "Paul" "Chani" "Vladimir" }inner-join
Inner joins two tables on the given columns.
names: table { "id" "name" } { 1 "Paul" 2 "Chani" 3 "Vladimir" } , houses: table { "id" "house" } { 1 "Atreides" 3 "Harkonnen" } , names .inner-join houses 'id 'id |header?
; returns list { "id" "name" "id_2" "house" }names: table { "id" "name" } { 1 "Paul" 2 "Chani" 3 "Vladimir" } , houses: table { "id" "house" } { 1 "Atreides" 3 "Harkonnen" } , names .inner-join houses 'id 'id |column? "name"
; returns { "Paul" "Vladimir" }group-by
Groups a table by the given column(s) and (optional) aggregations.
table { "name" "val" } { "a" 1 "b" 6 "a" 5 "b" 10 "a" 7 } |group-by 'name { 'name count 'val sum } |column? "val_sum" |sort
; returns { 13 16 }table { "name" "val" } { "a" 1 "b" 6 "a" 5 "b" 10 "a" 7 } |group-by 'name { 'name count 'val min } |column? "val_min" |sort
; returns { 1 6 }table { "name" "val" } { "a" 1 "b" 6 "a" 5 "b" 10 "a" 12 } |group-by 'name { 'name count 'val avg } |column? "val_avg" |sort
; returns { 6 8 }A1
Accepts a Table and returns the first row first column cell.
table { 'a } { 123 234 345 } |A1
; returns 123B1
Accepts a Table and returns the first row second column cell.
table { 'a 'b } { 123 234 345 456 } |B1
; returns 234A2
Accepts a Table and returns the first row first column cell.
table { 'a 'b } { 123 234 345 456 } |A2
; returns 345B2
Accepts a Table and returns the first row second column cell.
table { 'a 'b } { 123 234 345 456 } |B2
; returns 456Loading and saving
Functions that construct a table.
load\csv
Loads a .csv file to a table datatype.
file-uri - location of csv file to load
Tags #table #loading #csv
cc os f:: mktmp ++ "/test.csv" spr1:: table { "a" "b" "c" } { 1 1.1 "a" 2 2.2 "b" 3 3.3 "c" } spr1 .save\csv f spr2:: load\csv f |autotype 1 spr1 = spr2
; returns trueload\ sv
Loads a .csv file to a table datatype.
file-uri - location of csv file to load
cc os f:: mktmp ++ "/test.tsv" spr1:: table { "a" "b" "c" } { 1 1.1 "a" 2 2.2 "b" 3 3.3 "c" } spr1 .save\tsv f spr2:: load\tsv f |autotype 1 spr1 = spr2
; returns truesave\csv
Saves a table to a .csv file.
sheet - the sheet to save
file-url - where to save the sheet as a .csv file
Tags #table #saving #csv
cc os f:: mktmp ++ "/test.csv" spr1:: table { "a" "b" "c" } { 1 1.1 "a" 2 2.2 "b" 3 3.3 "c" } spr1 .save\csv f spr2:: load\csv f |autotype 1 spr1 = spr2
; returns truesave\ sv
Saves a table to a .csv file.
sheet - the table to save
file-url - where to save the sheet as a .csv file
Tags #table #saving #csv
cc os f:: mktmp ++ "/test.csv" spr1:: table { "a" "b" "c" } { 1 1.1 "a" 2 2.2 "b" 3 3.3 "c" } spr1 .save\tsv f spr2:: load\tsv f |autotype 1 spr1 = spr2
; returns trueload\xlsx
Loads the first sheet in an .xlsx file to a Table.
file-uri - location of xlsx file to load
Tags #table #loading #xlsx
cc os f:: mktmp ++ "/test.xlsx" spr1:: table { "a" "b" "c" } { 1 1.1 "a" 2 2.2 "b" 3 3.3 "c" } spr1 .save\xlsx f spr2:: load\xlsx f |autotype 1 spr1 = spr2
; returns truesave\xlsx
Saves a Table to a .xlsx file.
table - the table to save
file-url - where to save the table as a .xlsx file
Tags #table #saving #xlsx
cc os f:: mktmp ++ "/test.xlsx" spr1:: table { "a" "b" "c" } { 1 1.1 "a" 2 2.2 "b" 3 3.3 "c" } spr1 .save\xlsx f spr2:: load\xlsx f |autotype 1 spr1 = spr2
; returns truepersistent-table
Creates a persistent table using BadgerDB for storage
columns - block of column names
db-path - path to BadgerDB database
table-name - name of the table
close-persistent-table!
Closes the BadgerDB connection for a persistent table
persistent-table - the persistent table to close