Function Repository Resource:

TableSet

Create a lightweight, named column store of tabular data that is compatible with Query, Select and other SQL-like functions

Contributed by: Edmund B Robinson

ResourceFunction["TableSet"][names, data]

creates a data structure with columns named names and tabular data data.

Details

Tabular data is the most common data structure seen in data analysis. This form of data does not require much of the functionality needed to manage hierarchical data that is in Dataset. ResourceFunction["TableSet"] fills the gap between accessing a lightweight List with indices and a memory-hungry Dataset with named columns.

ResourceFunction["TableSet"] takes a list of strings for the column names.

ResourceFunction["TableSet"] takes any expression that satisfies MatrixQ for data.

The length of names must equal the number of columns in data.

ResourceFunction["TableSet"][…]["prop"] can take the following string properties as "prop":

"ColumnNames"

list of the column names

"Data"

list of the data

"RowCount"

number of rows

ResourceFunction["TableSet"] is compatible with the following functions:

Delete

delete a row or multiple rows

DeleteDuplicates

delete duplicate rows

Insert

add a row at a specified position

JoinAcross

join two ResourceFunction["TableSet"] objects by "Left", "Inner" and "Right" specifications

Part

return parts of the rows and columns

Query

perform a query using the column names as named slots

Select

pick out rows according to a criterion

TableForm

display a ResourceFunction["TableSet"] arranged in an array of rectangular cells

Union

return a sorted list of all the distinct elements that appear in a ResourceFunction["TableSet"] object

For JoinAcross, "Outer" join and the KeyCollisionFunction option are not implemented.

For Query, use named slots to reference columns.

Since ResourceFunction["TableSet"] is optimised for tabular data, it has a smaller memory footprint than Dataset, which is optimized for hierarchical data. When working with large tabular data, a significant decrease in memory usage can be gained by using ResourceFunction["TableSet"] as compared to Dataset.

Examples

Basic Examples (5)

Create a TableSet:

In[1]:=

Out[1]=

View the number of rows:

In[2]:=

Out[2]=

Insert a row:

In[3]:=

Out[3]=

View the updated number of rows:

In[4]:=

Out[4]=

View the TableSet in TableForm:

In[5]:=

Out[5]=

Scope (10)

TableSet has a summary form:

In[6]:=

Out[6]=

Return the column names:

In[7]:=

Out[7]=

Return the row count:

In[8]:=

Out[8]=

Return the data in the TableSet:

In[9]:=

Out[9]=

Delete a row or rows from a TableSet:

In[10]:=

Out[10]=

TableSet rows before deleting a row:

In[11]:=

Out[11]=

Delete row 3:

In[12]:=

Out[12]=

Row count after deleting row 3:

In[13]:=

Out[13]=

Delete row 7 and the last row:

In[14]:=

Out[14]=

Row count after deleting row 7 and the last row:

In[15]:=

Out[15]=

Delete duplicate rows:

In[16]:=

Out[16]=

The first two data rows are identical to the next two rows:

In[17]:=

Out[17]=

Delete duplicate rows:

In[18]:=

Out[13]=

Add a row at a specified position:

In[19]:=

Out[19]=

Insert a row at position 3:

In[20]:=

Out[18]=

Insert rows into position 3 and the last position:

In[21]:=

Out[14]=

For JoinAcross, "Outer" and KeyCollisionFunction are not implemented:

In[22]:=

table1 = ResourceFunction["TableSet"][{"A", "B", "E"}, Partition[Range@10, 3, 1]];
table2 = ResourceFunction["TableSet"][{"B", "C", "D"}, Partition[Range[6, 15], 3, 1]];
Grid[{TableForm /@ {table1, table2}}
, Spacings -> {3, Automatic}
]

Out[22]=

Evaluate "Left", "Inner" and "Right" joins:

In[23]:=

Function[{jspec},
Labeled[
TableForm@JoinAcross[table1, table2, "B", jspec],
jspec, Top
]] /@ {"Left", "Inner", "Right"} //
Grid[{#}, Alignment -> {Center, Top}, Spacings -> {3, Automatic}] &

Out[23]=

Return parts of the rows and columns:

In[24]:=

Out[24]=

Return the first two rows:

In[25]:=

Out[25]=

Return every other row:

In[26]:=

Out[26]=

Return the last column:

In[27]:=

Out[27]=

Use named slots to reference TableSet columns in Query:

In[28]:=

$queryTable = ResourceFunction["TableSet"][{"A", "B"}, Partition[RandomInteger[{1, 100}, 10^6], 2]]$

Out[28]=

Query a Histogram of the sum of the columns:

In[29]:=

Out[29]=

Add columns based on calculations on existing columns:

In[30]:=

queryTable = Query[All, <|#, "Total" -> #A + #B, "Difference" -> Differences@{#A, #B}|> &]@queryTable;
queryTable["ColumnNames"]

Out[27]=

Pick out rows according to a criterion:

In[31]:=

$selectTable = ResourceFunction["TableSet"][{"A", "B"}, Partition[RandomInteger[{1, 100}, 10^6], 2]]$

Out[31]=

Select rows with column A > 4 and B < 12:

In[32]:=

Out[32]=

Compare the row count of the original and result table:

In[33]:=

Out[33]=

In[34]:=

Out[34]=

Display the TableSet arranged in an array of rectangular cells:

In[35]:=

Out[35]=

In[36]:=

Out[36]=

Create TableSets with example data:

In[37]:=

View TableSetobjects in TableForm:

In[38]:=

Grid[{TableForm /@ {table1, table2}}
, Spacings -> {3, Automatic}
]

Out[38]=

Union gives the union of the rows maintaining the column names from the first table:

In[39]:=

Out[39]=

Properties and Relations (4)

When working with large tabular data, a significant decrease in memory usage can be gained by using TableSet as compared to Dataset. For example, with a two-column table of integer values, the ByteCount difference quickly widens with the record count as the graph tooltips and the following table demonstrate:

In[40]:=