Skip to content

Resampling by Time - BareFrame.resampleBy

BareFrame.resampleBy groups time-series rows into fixed-duration buckets and returns a GroupedFrame ready for aggregation.

JavaScript
const weekly = df.resampleBy("timestamp", "1w").sum(["sales"]);

Note: The time column in the result is replaced with numeric bucket boundary values (milliseconds since epoch), not the original Date values.

Without gaslamp

Grouping time-series data by a fixed interval requires manual bucket calculation:

JavaScript
// Sheet columns: timestamp(0), sales(1)
const values = sheet.getDataRange().getValues();
const rows = values.slice(1);  // remaining rows are data

const WEEK_MS = 7 * 24 * 60 * 60 * 1000;

const buckets = {};
for (const row of rows) {
  const ms = row[0] instanceof Date ? row[0].getTime() : row[0];
  const bucket = Math.floor(ms / WEEK_MS) * WEEK_MS;  // floor to week boundary
  if (!buckets[bucket]) buckets[bucket] = 0;
  buckets[bucket] += typeof row[1] === "number" ? row[1] : 0;
}
// buckets is now { <ms>: <sum>, ... } — still needs conversion to write back to a sheet

Changing the interval, adding columns, or switching aggregation methods all require touching multiple parts of this code.

With gaslamp

resampleBy handles the bucket calculation, and the result is a GroupedFrame with the same aggregation methods as groupBy:

JavaScript
const df = gaslamp.BareFrame.fromSheet(sheet);
const weekly = df.resampleBy("timestamp", "1w").sum(["sales"]);

Frequency Format

The freq argument controls the bucket size. The format is a positive integer followed by a unit character:

Format Unit Example
"Ns" seconds "30s", "60s"
"Nm" minutes "15m", "30m"
"Nh" hours "1h", "2h"
"Nd" days "1d", "7d"
"Nw" weeks "1w", "2w"

Note: Month and year intervals are not supported. Use "4w" as an approximation for monthly grouping.

Rounding Options

By default, each timestamp is rounded down to the bucket boundary ("floor"). Use the round option to change this:

JavaScript
// Floor (default): round down to bucket start
df.resampleBy("timestamp", "1h").count();

// Ceil: round up to next bucket boundary
df.resampleBy("timestamp", "15m", { round: "ceil" }).count();

// Round: round to nearest bucket boundary
df.resampleBy("timestamp", "30s", { round: "round" }).count();

Result Timestamp Column

The time column in the result contains numeric bucket boundary values (milliseconds since epoch), not the original Date values.

This is because JavaScript has no built-in timezone-aware date formatting method. Returning milliseconds lets you choose how to format the value depending on your environment.

In GAS, use Utilities.formatDate to convert to a human-readable string with the correct timezone:

JavaScript
const result = df.resampleBy("timestamp", "1h").sum(["sales"]);

const formatted = result.withColumn("timestamp", (row) =>
  Utilities.formatDate(
    new Date(row.get("timestamp")),
    Session.getScriptTimeZone(),
    "yyyy-MM-dd HH:mm",
  )
);

Aggregation Methods

resampleBy returns a GroupedFrame, so all the same aggregation methods as groupBy are available:

JavaScript
const df = gaslamp.BareFrame.fromSheet(sheet);

df.resampleBy("timestamp", "1d").count();
df.resampleBy("timestamp", "1d").sum(["sales"]);
df.resampleBy("timestamp", "1d").mean(["price"]);
df.resampleBy("timestamp", "1d").min(["price"]);
df.resampleBy("timestamp", "1d").max(["price"]);
df.resampleBy("timestamp", "1d").first();
df.resampleBy("timestamp", "1d").last();

Error Handling

resampleBy throws if the column does not exist or the frequency format is invalid:

JavaScript
df.resampleBy("missing", "1h").count();
// Error: BareFrame.resampleBy: column "missing" does not exist

df.resampleBy("timestamp", "1month").count();
// Error: resampleDate: invalid frequency format "1month". Expected format: "Ns", "Nm", "Nh", "Nd", or "Nw"

Practical Workflow

Read time-series data, aggregate by week, and write the result to another sheet:

JavaScript
function workflow_resampleBy() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheet  = book.getSheetByName("Access Log");
  const outputSheet = book.getSheetByName("Weekly Summary")
    ?? book.insertSheet("Weekly Summary");

  const df = gaslamp.BareFrame.fromSheet(inputSheet);

  // Sum page views per week
  const weekly = df.resampleBy("timestamp", "1w").sum(["pageviews"]);

  weekly.toSheet(outputSheet);
  console.log(`Exported ${weekly.length} weekly buckets.`);
}

Next Steps