Downsample data

Downsampling reduces the number of rows in a table by grouping rows by category or by time interval and aggregating each group into a single row. It makes large or high-frequency data easier to plot, store, and analyze.

Downsampling works on live data, too. When rows are added to or change in the source table, the downsampled result updates with it. For details, see Aggregations on ticking tables.

This guide shows you how to downsample categorical data, how to downsample time data by putting timestamps into time bins, and where to go for more complex downsampling.

Downsample categorical data

Let's say we have a dataset that tracks credit card purchases. Each entry in this dataset has a purchase amount and a purchase category.

This example downsamples the data with sumBy. The columns you group by are called key columns. Here, PurchaseCategory is the key column, so the result totals PurchasePrice for each category.

The result has one row for each of the three categories instead of six rows, which is the reduction downsampling aims for. Categorical data is easy to downsample because its values already fall into a small set of discrete categories.

Downsample time data

Time data is harder to downsample than categorical data. Let's say we add a date-time stamp to each purchase. We want to sum the purchases by day and category. How can we downsample this data when nearly every timestamp is unique? Grouping on the date-time column directly doesn't shrink the table. Instead, put each timestamp into a time bin, and then group on the bin.

Deephaven has two built-in query-language functions that you use in formula strings to assign each date-time value to a time bin. A bin can be any duration, such as a second, a minute, an hour, or a day.

When a timestamp falls exactly on a bin boundary, both functions return it unchanged. So lowerBin puts it in the bin that starts at that boundary, and upperBin puts it in the bin that ends there.

Sum values in each time bin

This example builds on the credit card purchases table from Downsample categorical data and sums the purchases by day and category:

  1. Add a TimeStamps column of date-time values.
  2. Add a TimeStampsBinned column that bins each timestamp by the day with lowerBin.
  3. Drop the original TimeStamps column. sumBy sums every column that isn't a key column, and it can't sum date-time values.
  4. Use sumBy with two key columns, PurchaseCategory and TimeStampsBinned.

lowerBin takes a bin size and an optional offset, both in nanoseconds or both as Duration values. The built-in HOUR constant is one hour in nanoseconds, so 24 * HOUR is one day.

Bin boundaries are counted from the Unix epoch, which is 1970-01-01T00:00:00 UTC. Without an offset, a one-day bin starts at midnight UTC. This example adds a five-hour offset, so each bin starts at midnight US Eastern Standard Time (UTC-5). The offset is fixed, so during daylight saving time the bins start at 1:00 AM Eastern. To align bins to a different time of day, change the offset. For details, see the offset parameter of lowerBin.

Each row of result is one category on one day, and each TimeStampsBinned value is midnight Eastern time. The six purchases become four rows. For example, the two Utilities purchases on January 1 sum to 80.

Keep the last row in each time bin

A common way to downsample high-frequency time-series data is to bin the timestamps and keep the last row in each bin. This example makes one-minute bins with upperBin, so each bin is labeled with the time it ends. lowerBin works too. Then lastBy keeps the last row in each bin.

The example adds the bin column with updateView, which computes the column on demand. update works too, but it stores the column in memory.

t has one row every 10 seconds for three minutes. ii is the row index, so each row is 10 * ii seconds after startTime. Because upperBin labels each row with the end of its bin, the rows from 09:30:05 to 09:30:55 all get the TimeBin 09:31:00. tDownsampled keeps only the last row of each minute, so the 18 rows of t become three.

Keep the last row for each symbol in each time bin

To downsample prices for several ticker symbols at once, add Symbol as a second key column, so lastBy keeps the last row for each symbol in each minute. As in Sum values in each time bin, pass several key columns. This example groups by Symbol and TimeBin:

tDownsampledMulti has one row for each symbol in each minute: six rows in all.

More complex downsampling

For more complex downsampling, use a multi-aggregation with aggBy, which applies several aggregations in one operation. For example, it can compute the minimum, maximum, average, weighted average, first value, and last value of each bin.