Skip to main content

Table operations cheat sheet

Create tables

Empty tables

from deephaven.TableTools import emptyTable
result = emptyTable(5)

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

New tables

Columns are created using the following methods:

from deephaven.TableTools import newTable, intCol, stringCol

result = newTable(
intCol("IntegerColumn", 1, 2, 3),
stringCol("Strings", "These", "are", "Strings")

Time tables

The following code makes a timeTable that updates every second.

from deephaven.TableTools import timeTable

result = timeTable("00:00:01")



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



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

from deephaven.TableTools import newTable, stringCol, intCol, doubleCol
from deephaven.conversion_utils import NULL_INT
from deephaven.filter import or_

source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),

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

To filter results based on a filterTable:

filterTable = newTable(
stringCol("Colors", "blue", "red", "purple", "white"),
intCol("Codes", 10, 12, 14, 16)

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

head and tail

Used to reduce the number of rows:

tail = source.tail(5) # returns last 5 rows
tailPct = source.tailPct(0.25) # returns last 25% of rows
headPct = source.headPct(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.


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)


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.TableTools import newTable, stringCol, intCol, doubleCol, dateTimeCol
from deephaven.DateTimeUtils import convertDateTime

trades = newTable(
stringCol("Ticker", "AAPL", "AAPL", "AAPL", "IBM", "IBM"),
dateTimeCol("TradeTime", convertDateTime("2021-04-05T09:10:00 NY"), convertDateTime("2021-04-05T09:31:00 NY"), convertDateTime("2021-04-05T16:00:00 NY"), convertDateTime("2021-04-05T16:00:00 NY"), convertDateTime("2021-04-05T16:30:00 NY")),
doubleCol("Price", 2.5, 3.7, 3.0, 100.50, 110),
intCol("Size", 52, 14, 73, 11, 6)

quotes = newTable(
stringCol("Ticker", "AAPL", "AAPL", "IBM", "IBM", "IBM"),
dateTimeCol("QuoteTime", convertDateTime("2021-04-05T09:11:00 NY"), convertDateTime("2021-04-05T09:30:00 NY"), convertDateTime("2021-04-05T16:00:00 NY"), convertDateTime("2021-04-05T16:30:00 NY"), convertDateTime("2021-04-05T17:00:00 NY")),
doubleCol("Bid", 2.5, 3.4, 97, 102, 108),
intCol("BidSize", 10, 20, 5, 13, 23),
doubleCol("Ask", 2.5, 3.4, 105, 110, 111),
intCol("AskSize", 83, 33, 47, 15, 5),

result = trades.aj(quotes, "Ticker, TradeTime = QuoteTime")

raj (Reverse As-Of Join)


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(quotes, "Ticker, TradeTime = QuoteTime", "Bid, Offer = Ask")

Joins with exact match

nj (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.naturalJoin(rightTable, columnsToMatch, columnsToAdd)


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

from deephaven.TableTools import newTable, stringCol, intCol
from deephaven.conversion_utils import NULL_INT

left = newTable(
stringCol("LastName", "Rafferty", "Jones", "Steiner", "Robins", "Smith", "Rogers", "DelaCruz"),
intCol("DeptID", 31, 33, 33, 34, 34, 36, NULL_INT),
stringCol("Telephone", "(303) 555-0162", "(303) 555-0149", "(303) 555-0184", "(303) 555-0125", "", "", "(303) 555-0160")

right = newTable(
intCol("DeptID", 31, 33, 34, 35),
stringCol("DeptName", "Sales", "Engineering", "Clerical", "Marketing"),
stringCol("Telephone","(303) 555-0136", "(303) 555-0162", "(303) 555-0175", "(303) 555-0171")

result = left.naturalJoin(right, "DeptID", "DeptName, DeptTelephone = Telephone")


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.



  • 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.TableTools import merge, newTable, col

source1 = newTable(col("Letter", "A", "B", "D"), col("Number", 1, 2, 3))
source2 = newTable(col("Letter", "C", "D", "E"), col("Number", 14, 15, 16))
source3 = newTable(col("Letter", "E", "F", "A"), 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.getMeta()


Single direction sorting:

Sort on multiple column or directions:

Reverse the order of rows in a table:

from deephaven.TableTools import newTable, stringCol, intCol
from deephaven.conversion_utils import NULL_INT
from deephaven import SortColumn, as_list
from deephaven.TableManipulation import ColumnName

source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),

sort_columns = as_list([

resultMultiDirection = source.sort(sort_columns)
resultMultiSort = source.sort("Letter", "Number") # Letter then Time
sortDesc = source.sortDescending("Number") # highest to lowest
sortAsc = source.sort("Number") # lowest to highest
reverseTable = source.reverse() # HEAVILY USED! Very cheap to support GUIs

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.TableTools import newTable, stringCol, intCol
from deephaven.conversion_utils import NULL_INT

source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),

selectColumns ="Letter", "Number")
# constrain to only those 2 columns, write to memory

selectAddCol ="Letter", "Number", "New = Number - 5")
# constrain and add a new calculated column

selectAndUpdateCol ="Letter", "Number").update("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 updateView when formula is quick to calculate or only a portion of the data is used at a time. Minimizes RAM used.

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

viewAddCol = source.updateView("Letter", "Number", "New = Number - 5")
# view set and add a column with an on-demand formula

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

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

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

lazyUpdateEx = source.lazyUpdate("Letter", "Number", "New = Number - 5")

Manipulate columns

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

renameStuff = source.renameColumns("NewLetter = Letter", "NewNumber = Number")
dropColumn = source.dropColumns("Number") # drop one or many

putColsAtStart = source.moveColumnsUp("Number") # make Number the first column(s)
putColsWherever = source.moveColumns(1, "Number") # make Number the second column


See How to group and ungroup data for more details.

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


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

aggByKey = source.groupBy("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


See our guides for more details:

# 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.firstBy("Number")
firstByTwoKeys = source.firstBy("Number", "Letter") # all below work with multi

countOfEntireTable = source.countBy("Letter") # single argument returns total count
countOfGroup = source.countBy("Number", "Letter")

firstOfGroup = source.firstBy("Letter")
lastOfGroup = source.lastBy("Letter")

sumOfGroup = source.view("Letter", "Number").sumBy("Letter")
# non-key field must be numerical
avgOfGroup = source.view("Letter", "Number").avgBy("Letter")
stdOfGroup = source.view("Letter", "Number").stdBy("Letter")
varOfGroup = source.view("Letter", "Number").varBy("Letter")
medianOfGroup = source.view("Letter", "Number").medianBy("Letter")
minOfGroup = source.view("Letter", "Number").minBy("Letter")
maxOfGroup = source.view("Letter", "Number").maxBy("Letter")
## Combined Aggregations
# combine aggregations in a single method (using the same key-grouping)
from deephaven import Aggregation as agg, as_list

agg_list = as_list([\
agg.AggLast("LastNumber = Number","LastLetter = Number"),\
agg.AggSum("Sum = Number", "Code"),\
agg.AggFirst("First = Number"),\
agg.AggMax("Max = Number"),\
agg.AggMin("Min = Number"),\
agg.AggAvg("AvgNumber = Number"),\
agg.AggWAvg("Number", "WtdAvgNumber = Number"),\
agg.AggVar("VarNumber = Number"),\
agg.AggStd("StdNumber = Number"),\
agg.AggMed("MedianNumber = Number"),\
agg.AggPct(0.75, "Perc75Number = Number")\

combinationAgg = source.updateView("Number = Number * Code").aggBy(agg_list, "Code", "Letter")

Other useful methods


Copy and paste these working examples into the console.

Reduce ticking frequency

Uses snapshot to reduce the ticking frequency.

from deephaven.TableTools import timeTable
import random

source = timeTable("00:00:00.5").update("X = (int) random.randint(0, 100)", "Y = sqrt(X)")

trigger = timeTable("00:00:05").renameColumns("TriggerTimestamp = Timestamp")

result = trigger.snapshot(source)

Capture the history of ticking tables

Uses snapshotHistory to capture the history of ticking tables.

from deephaven.TableTools import timeTable
import random

source = timeTable("00:00:00.01").update("X = i%2 == 0 ? `A` : `B`", "Y = (int) random.randint(0, 100)", "Z = sqrt(Y)").lastBy("X")

trigger = timeTable("00:00:01").renameColumns("TriggerTimestamp = Timestamp")

result = trigger.snapshotHistory(source)

Use DynamicTableWriter and Pandas

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

import os
os.system("pip install pycoingecko")

from pycoingecko import CoinGeckoAPI

from deephaven.DateTimeUtils import secondsToTime, millisToTime
from deephaven.TableTools import merge
from deephaven import DynamicTableWriter
import deephaven.Types as dht

from time import sleep, time
import pandas as pd
import threading

# minutes to query crypto prices
timeToWatch = 20

# secondsToSleep should be 10 or higher. If too fast, will hit request limit.
secondsToSleep = 10

getHistory = False

# if getHistory = true, the days to pull
daysHistory = 90

# coins to get data
ids=['bitcoin', 'ethereum','litecoin', 'dogecoin', 'tether', 'binancecoin', 'cardano', 'ripple', 'polkadot']

# array to store tables for current and previous data

tableWriter = DynamicTableWriter(["coin", "dateTime", "price", "marketCap", "totalVolume"], [dht.string, dht.datetime, dht.double, dht.double, dht.double])


cg = CoinGeckoAPI()

# get historical data
if getHistory:
for names in ids:
coin_data_hist = cg.get_coin_market_chart_by_id(names, vs_currency = "usd", days = daysHistory)
sub = pd.DataFrame(coin_data_hist)
tableArray.append(dataFrameToTable(sub).view("dateTime = millisToTime((long)prices_[i][0])", "coin = names", "price = prices_[i][1]", "marketCap = market_caps_[i][1]", "totalVolume = total_volumes_[i][1]").moveColumnsUp("dateTime", "coin"))

#add each coin data to the master table
result = merge(tableArray).selectDistinct("dateTime", "coin", "price", "marketCap", "totalVolume").sortDescending("dateTime")

def thread_func():

cg.get_coins_markets(vs_currency = 'usd')

for i in range(int(timeToWatch*60/secondsToSleep)):
coin_data = cg.get_price(ids, vs_currencies = 'usd', include_market_cap = True, include_24hr_vol = True, include_24hr_change = False, include_last_updated_at = True)

for id in ids:
#Add new data to the dynamic table
tableWriter.logRow( id, secondsToTime(int(coin_data[id]['last_updated_at'])), float(coin_data[id]['usd']), coin_data[id]['usd_market_cap'], coin_data[id]['usd_24h_vol'])


thread = threading.Thread(target = thread_func)