Skip to main content
Version: Python

Table operations cheat sheet

Create tables

Empty tables

from deephaven import empty_table
result = empty_table(5)

# Empty tables are often followed with a formula
result1 = result.update(formulas=["X = 5"])

New tables

Columns are created using the following methods:

from deephaven import new_table
from deephaven.column import string_col, int_col

result = new_table([
int_col("IntegerColumn", [1, 2, 3]),
string_col("Strings", ["These", "are", "Strings"])
])

Time tables

The following code makes a time_table that updates every second.

from deephaven import time_table
result = time_table("00:00:01")

Filter

tip

You should filter your data before performing other operations to optimize performance. Less data generally means better, faster queries.

where

tip

For SQL developers: In Deephaven, filter your data before joining using where operations. Deephaven is optimized for filtering rather than matching.

from deephaven import new_table
from deephaven.column import string_col, int_col, double_col
from deephaven.constants import NULL_INT

source = new_table([
string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
int_col("Number", [NULL_INT, 2, 1, NULL_INT, 4, 5, 3]),
string_col("Color", ["red", "blue", "orange", "purple", "yellow", "pink", "blue"]),
int_col("Code", [12, 14, 11, NULL_INT, 16, 14, NULL_INT]),
])


resultSingleFilter = source.where(filters=["Color = `blue`"])
resultOR = source.where_one_of(filters=["Color = `blue`", "Number > 2"]) # OR operation - result will have _either_ criteria
resultAND = source.where(filters=["Color = `blue`", "Number > 2"]) # AND operation - result will have _both_ criteria

To filter results based on a filterTable:

filterTable = new_table([
string_col("Colors", ["blue", "red", "purple", "white"]),
int_col("Codes", [10, 12, 14, 16])
])

# returns a new table containing rows from the source table
whereInColors = source.where_in(filter_table=filterTable, cols=["Color = Colors"])
whereInColorsAndCodes = source.where_in(filter_table=filterTable, cols=["Color = Colors", "Code = Codes"]) # AND operation - result will have both criteria
whereNotInColors = source.where_not_in(filter_table=filterTable, cols=["Color = Colors"])

head and tail

Used to reduce the number of rows:

tail = source.tail(5) # returns last 5 rows
tail = source.tail(2) # returns last 2 rows
tailPct = source.tail_pct(0.25) # returns last 25% of rows
headPct = source.head_pct(0.75) # returns first 75% of rows
head = source.head(2) # returns first 2 rows

Join data

See our guide Choose the right join for more details.

tip

For SQL developers: in Deephaven, joins are normally used to enrich a data set, not filter. Use where to filter your data instead of using a join.

Joins for close matches (time)

aj (As-Of Join)

aj

As-of joins aj find "the exact match" of the key or "the record just before". For timestamp aj-keys, this means "that time or the record just before".

leftTable = rightTable.aj(columnsToMatch, columnsToAdd)

from deephaven import new_table
from deephaven.column import string_col, int_col, double_col, datetime_col
from deephaven.time import to_datetime

trades = new_table([
string_col("Ticker", ["AAPL", "AAPL", "AAPL", "IBM", "IBM"]),
datetime_col("TradeTime", [to_datetime("2021-04-05T09:10:00 NY"), to_datetime("2021-04-05T09:31:00 NY"), to_datetime("2021-04-05T16:00:00 NY"), to_datetime("2021-04-05T16:00:00 NY"), to_datetime("2021-04-05T16:30:00 NY")]),
double_col("Price", [2.5, 3.7, 3.0, 100.50, 110]),
int_col("Size", [52, 14, 73, 11, 6])
])

quotes = new_table([
string_col("Ticker", ["AAPL", "AAPL", "IBM", "IBM", "IBM"]),
datetime_col("QuoteTime", [to_datetime("2021-04-05T09:11:00 NY"), to_datetime("2021-04-05T09:30:00 NY"), to_datetime("2021-04-05T16:00:00 NY"), to_datetime("2021-04-05T16:30:00 NY"), to_datetime("2021-04-05T17:00:00 NY")]),
double_col("Bid", [2.5, 3.4, 97, 102, 108]),
int_col("BidSize", [10, 20, 5, 13, 23]),
double_col("Ask", [2.5, 3.4, 105, 110, 111]),
int_col("AskSize", [83, 33, 47, 15, 5]),
])
result = trades.aj(table=quotes, on=["Ticker", "TradeTime = QuoteTime"])

raj (Reverse As-Of Join)

raj

Reverse As-of joins raj find "the exact match" of the key or "the record just after". For timestamp reverse aj-keys, this means "that time or the record just after".

result = leftTable.raj(rightTabke, columnsToMatch, columnsToAdd)

result = trades.raj(table=quotes,  on=["Ticker", "TradeTime = QuoteTime"], joins=["Bid", "Offer = Ask"])

Joins with exact match

nj (Natural Join)

natural_join

  • Returns all the rows of the left table, along with up to one matching row from the right table.
  • If there is no match in the right table for a given row, nulls will appear for that row in the columns from the right table.
  • If there are multiple matches in the right table for a given row, the query will fail.

leftTable.natural_join(rightTable, columnsToMatch, columnsToAdd)

note

The right table of the join needs to have only one match based on the key(s).

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

left = new_table([
string_col("LastName", ["Rafferty", "Jones", "Steiner", "Robins", "Smith", "Rogers", "DelaCruz"]),
int_col("DeptID", [31, 33, 33, 34, 34, 36, NULL_INT]),
string_col("Telephone", ["(303) 555-0162", "(303) 555-0149", "(303) 555-0184", "(303) 555-0125", "", "", "(303) 555-0160"])
])

right = new_table([
int_col("DeptID", [31, 33, 34, 35]),
string_col("DeptName", ["Sales", "Engineering", "Clerical", "Marketing"]),
string_col("Telephone", ["(303) 555-0136", "(303) 555-0162", "(303) 555-0175", "(303) 555-0171"])
])
result = left.natural_join(table=right, on=["DeptID"], joins=["DeptName", "DeptTelephone = Telephone"])

join

Similar to SQL inner join, join returns all rows that match between the left and right tables, potentially with duplicates.

  • Returns only matching rows.
  • Multiple matches will have duplicate values, which can result in a long table.

exactJoin

exactJoin

  • Returns all rows of leftTable.
  • If there are no matching keys result will fail.
  • Multiple matches will fail.

Merge tables

Create a new table made of all of table 1, followed by all of table 2, etc. All tables must have the same column names (schema) when merged.

from deephaven import merge, new_table
from deephaven.column import int_col, string_col

source1 = new_table([string_col("Letter", ["A", "B", "D"]), int_col("Number", [1, 2, 3])])
source2 = new_table([string_col("Letter", ["C", "D", "E"]), int_col("Number", [14, 15, 16])])
source3 = new_table([string_col("Letter", ["E", "F", "A"]), int_col("Number", [22, 25, 27])])

tableArray = [source1, source2, source3]

result = merge(tableArray)

View table metadata

Useful to make sure schema matches before merging. Shows the column names, data types, partitions, and groups for the table.

seeMetadata = source.meta_table

Sort

Single direction sorting:

Sort on multiple column or directions:

Reverse the order of rows in a table:

from deephaven import new_table, SortDirection
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

source = new_table([
string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
int_col("Number", [NULL_INT, 2, 1, NULL_INT, 4, 5, 3]),
string_col("Color", ["red", "blue", "orange", "purple", "yellow", "pink", "blue"]),
int_col("Code", [12, 14, 11, NULL_INT, 16, 14, NULL_INT]),
])
sort_columns = [
SortDirection.ASCENDING,
SortDirection.DESCENDING
]
sorted_table = source.sort(order_by=['Letter','Number'], order=sort_columns)

Select and create new columns

Option 1:  Choose and add new columns - calculate and write to memory

Use select and update when data is expensive to calculate or accessed frequently. Results are saved in RAM for faster access, but takes more memory.

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

source = new_table([
string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
int_col("Number", [NULL_INT, 2, 1, NULL_INT, 4, 5, 3]),
string_col("Color", ["red", "blue", "orange", "purple", "yellow", "pink", "blue"]),
int_col("Code", [12, 14, 11, NULL_INT, 16, 14, NULL_INT]),
])


selectColumns = source.select(formulas=["Letter", "Number"])
# constrain to only those 2 columns, write to memory

selectAddCol = source.select(formulas=["Letter", "Number", "New = Number - 5"])
# constrain and add a new calculated column

selectAndUpdateCol = source.select(formulas=["Letter", "Number"]).update(formulas=["New = Number - 5"])
# add a new calculated column - logically equivalent to previous example

Option 2: Choose and add new columns - reference a formula and calculate on the fly

Use view and update_view when formula is quick to calculate or only a portion of the data is used at a time. Minimizes RAM used.

viewColumns = source.view(formulas=["Letter", "Number"])
# similar to select(), but uses on-demand formula


viewAddCol = source.update_view(formulas=["Letter", "Number", "New = Number - 5"])

# view set and add a column with an on-demand formula

viewAndUpdateViewCol = source.view(formulas=["Letter", "Number"]).update_view(formulas=["New = Number - 5"])
# logically equivalent to previous example

Option 3:  Add new columns - reference a formula and calculate on the fly

Use lazy_update when there are a small number of unique values. On-demand formula results are stored in cache and re-used.

lazyUpdateEx = source.lazy_update(formulas=["Letter", "Number", "New = Number - 5"])

Manipulate columns

uniqueValues = source.select_distinct(formulas=["Letter"]) # show unique set
# works on all data types - be careful with doubles, longs

renameStuff = source.rename_columns(cols=["NewLetter = Letter", "NewNumber = Number"])
dropColumn = source.drop_columns(cols=["Number"]) # drop one or many

putColsAtStart = source.move_columns_up(cols=["Number"]) # make Number the first column(s)
putColsWherever = source.move_columns(idx=1, cols=["Number"]) # make Number the second column

Group

See How to group and ungroup data for more details.

groupToArrays1 = source.group_by(by=["Letter"]) # one row per key; all other columns are arrays
multipleKeys = source.group_by(by=["Letter", "Number"]) # one row for each key-combination

Ungroup

Expands out each row so that each value in any array inside that row becomes itself a new row.

aggByKey = source.group_by(by=["Letter"])
# one row per Letter; other fields are arrays from source
ungroupThatOutput = aggByKey.ungroup() # no arguments usually
# each array value becomes its own row
# in this case turns grouped table back into source

Aggregate

# IMPORTANT: Any columns not in the parentheses of the whateverBy("Col1", "Col2") statement
# need to be an appropriate type for that aggregation method
# i.e., sums need to have all non-key columns be numbers.
firstByKey = source.first_by(by=["Number"])
firstByTwoKeys = source.first_by(by=["Number", "Letter"]) # all below work with multi
countOfEntireTable = source.count_by(col="Letter") # single argument returns total count
countOfGroup = source.count_by(col="Number", by=["Letter"])
firstOfGroup = source.first_by(by=["Letter"])
lastOfGroup = source.last_by(by=["Letter"])
sumOfGroup = source.view(formulas=["Letter", "Number"]).sum_by(by=["Letter"])
# non-key field must be numerical
avgOfGroup = source.view(formulas=["Letter", "Number"]).avg_by(by=["Letter"])
stdOfGroup = source.view(formulas=["Letter", "Number"]).std_by(by=["Letter"])
#See our guides for more details:
varOfGroup = source.view(formulas=["Letter", "Number"]).var_by(by=["Letter"])
medianOfGroup = source.view(formulas=["Letter", "Number"]).median_by(by=["Letter"])
minOfGroup = source.view(formulas=["Letter", "Number"]).min_by(by=["Letter"])
maxOfGroup = source.view(formulas=["Letter", "Number"]).max_by(by=["Letter"])

Other useful methods

note

Copy and paste these working examples into the console.

Reduce ticking frequency

Uses snapshot to reduce the ticking frequency.

from deephaven import time_table
import random

source = time_table("00:00:00.5").update(formulas=["X = (int) random.randint(0, 100)", "Y = sqrt(X)"])
trigger = time_table("00:00:05").rename_columns(cols=["TriggerTimestamp = Timestamp"])

result = trigger.snapshot(source_table=source)

Capture the history of ticking tables

Uses snapshot_history to capture the history of ticking tables.

from deephaven import time_table
import random

source = time_table("00:00:00.01").update(formulas=["X = i%2 == 0 ? `A` : `B`", "Y = (int) random.randint(0, 100)", "Z = sqrt(Y)"]).last_by(by=["X"])

trigger = time_table("00:00:01").rename_columns(cols=["TriggerTimestamp = Timestamp"])
result = trigger.snapshot(source)

Use DynamicTableWriter and NumPy

See our guide How to write data to an in-memory, real-time table.

from deephaven import DynamicTableWriter
import deephaven.dtypes as dht
from deephaven.plot import Figure

import numpy as np, threading, time

table_writer = DynamicTableWriter({
"X": dht.double, "SawToothWave": dht.double, "SquareWave": dht.double, "TriangleWave": dht.double
})

waveforms = table_writer.table

def create_waveforms():
for i in range(200):
start = time.time()
x = 0.1 * i
y_sawtooth = (x % 1 - 0.5) * 2
y_square = 1. if x % 2 < 1 else -1.
y_triangle = (x % 1 - 0.5) * 2 if x % 2 >= 1 else -(x % 1 - 0.5) * 2
table_writer.write_row(x, y_sawtooth, y_square, y_triangle)
end = time.time()
time.sleep(0.2 - (end - start))

thread = threading.Thread(target=create_waveforms)
thread.start()

figure = Figure()
new_fig = figure.\
plot_xy(series_name="Sawtooth Wave", t=waveforms, x="X", y="SawToothWave").\
plot_xy(series_name="Square Wave", t=waveforms, x="X", y="SquareWave").\
plot_xy(series_name="Triangle Wave", t=waveforms, x="X", y="TriangleWave")
new_plot = new_fig.show()