Skip to content

Grouping Rows - BareFrame.groupBy

BareFrame.groupBy groups rows by one or more columns and returns a GroupedFrame. Call an aggregation method on the result to produce a flat BareFrame.

JavaScript
const counts = df.groupBy(["category"]).count();
const averages = df.groupBy(["category"]).mean(["price"]);
const totals = df.groupBy(["category"]).sum(["price", "quantity"]);

Without gaslamp

Grouping and aggregating rows from getValues() output requires building the group map manually:

JavaScript
// Sheet columns: category(0), price(1)
const values = sheet.getDataRange().getValues();
const headers = values[0];  // first row is the header
const rows = values.slice(1);  // remaining rows are data

// Count rows per category
const counts = {};
for (const row of rows) {
  const category = row[0];  // category is at index 0
  counts[category] = (counts[category] ?? 0) + 1;
}

Grouping by multiple columns (composite key) requires concatenating the key values manually, and tracking each aggregation separately:

JavaScript
// Sheet columns: category(0), status(1), price(2), quantity(3)
const values = sheet.getDataRange().getValues();
const rows = values.slice(1);

const groups = {};
for (const row of rows) {
  const key = `${row[0]}__${row[1]}`;  // composite key: category + status
  if (!groups[key]) {
    groups[key] = { category: row[0], status: row[1], price: 0, quantity: 0 };
  }
  groups[key].price    += typeof row[2] === "number" ? row[2] : 0;
  groups[key].quantity += typeof row[3] === "number" ? row[3] : 0;
}

// Convert back to a 2D array to write to a sheet
const result = Object.values(groups).map(g => [g.category, g.status, g.price, g.quantity]);
// ... and prepend a header row, then write to sheet

Each new column or aggregation type requires touching multiple parts of this code.

With gaslamp

groupBy handles the grouping, and a single method call performs the aggregation:

JavaScript
const df = gaslamp.BareFrame.fromSheet(sheet);
const summary = df.groupBy(["category"]).count();

Aggregation Methods

count

Returns the number of rows in each group. The result always includes the group-key columns followed by a count column.

JavaScript
df.groupBy(["category"]).count();
// category    | count
// Electronics | 3
// Furniture   | 2

sum

Returns the sum of each specified column per group. Non-numeric cells are silently excluded. If a group has no numeric values at all, the result is null.

JavaScript
df.groupBy(["category"]).sum(["price"]);
// category    | price
// Electronics | 3000
// Furniture   | 225

mean

Returns the arithmetic mean of each specified column per group. Non-numeric cells are excluded before computing the average.

JavaScript
df.groupBy(["category"]).mean(["price"]);
// category    | price
// Electronics | 1000
// Furniture   | 112.5

min / max

Returns the minimum or maximum numeric value of each specified column per group.

JavaScript
df.groupBy(["category"]).min(["price"]);
df.groupBy(["category"]).max(["price"]);

first / last

Returns the first or last row of each group. All columns from the original BareFrame are preserved.

JavaScript
df.groupBy(["category"]).first();
df.groupBy(["category"]).last();

agg

agg accepts a custom aggregation function per column, for cases where the built-in methods are not sufficient.

JavaScript
// Custom aggregation: concatenate strings
df.groupBy(["category"]).agg({
  name: vals => vals.filter(v => typeof v === "string").join(", "),
});

Grouping by Multiple Columns

Pass multiple column names to group by a composite key. gaslamp uses a collision-resistant key encoding internally, so values like the number 0 and the string "0" are always treated as distinct.

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

// Group by both category and status
const summary = df.groupBy(["category", "status"]).count();
// category    | status   | count
// Electronics | active   | 2
// Electronics | inactive | 1
// Furniture   | active   | 2

The column order in the headers array matters. ["category", "status"] and ["status", "category"] produce different groups because the key is formed in the order the columns are specified:

JavaScript
// These are NOT the same grouping
df.groupBy(["category", "status"]).count();
df.groupBy(["status", "category"]).count();

Error Handling

groupBy throws an error immediately if a column name does not exist:

JavaScript
df.groupBy(["missing"]);
// Error: BareFrame.groupBy: column "missing" does not exist

Aggregation methods also throw if a specified column does not exist:

JavaScript
df.groupBy(["category"]).sum(["missing"]);
// Error: GroupedFrame.sum: column "missing" does not exist

Practical Workflow

Read from a sheet, group and aggregate, then write the result to another sheet:

JavaScript
function workflow_groupBy() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheet  = book.getSheetByName("Sales");
  const outputSheet = book.getSheetByName("Summary")
    ?? book.insertSheet("Summary");

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

  // Total sales and average price per category
  const totals = df.groupBy(["category"]).sum(["price", "quantity"]);

  totals.toSheet(outputSheet);
  console.log(`Exported ${totals.length} groups.`);
}

Next Steps