Skip to content

Pivoting Data - BareFrame.pivot

BareFrame.pivot transforms long-format data (many rows, few columns) into wide-format data (few rows, many columns). Use it when you need to turn one column's values into new column headers and fill the cells with another column's values.

JavaScript
const wide = df.pivot({
  index: "date",
  columns: "metric",
  values: "value",
});

Without gaslamp

Pivoting data manually requires nested loops and careful tracking of indices. You must find unique values, match rows, and build a new structure:

JavaScript
const values = sheet.getDataRange().getValues();
const headers = values[0];  // ["date", "metric", "value"]
const rows = values.slice(1);

// Find unique dates and metrics
const uniqueDates = [...new Set(rows.map(r => r[0]))];
const uniqueMetrics = [...new Set(rows.map(r => r[1]))];

// Build the pivoted structure
const result = [["date", ...uniqueMetrics]];

for (const date of uniqueDates) {
  const row = [date];
  for (const metric of uniqueMetrics) {
    const match = rows.find(r => r[0] === date && r[1] === metric);
    row.push(match ? match[2] : null);
  }
  result.push(row);
}

// result:
// ["date",    "sales", "profit"]
// ["2024-01",  100,     20]
// ["2024-02",  110,     25]

If columns are added or the layout changes, the hardcoded indices break immediately.

With gaslamp

pivot uses column names instead of indices. The operation remains correct even if the source data changes structure:

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

const wide = df.pivot({
  index: "date",
  columns: "metric",
  values: "value",
});

console.log(wide.display());
// | date    | sales | profit |
// | ------- | ----- | ------ |
// | 2024-01 | 100   | 20     |
// | 2024-02 | 110   | 25     |

Understanding pivot

The three option parameters define how the pivot works:

Parameter Role Example
index Column whose unique values become row labels "date" → rows labeled 2024-01, 2024-02, etc.
columns Column whose unique values become new column headers "metric" → columns sales, profit, etc.
values Column whose values fill the cells "value" → cell contents are sales/profit numbers

Common Patterns

Sales by Date and Product

Pivot sales data to see each product as a column:

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

const byProduct = df.pivot({
  index: "date",
  columns: "product",
  values: "sales",
});

// date    | Laptop | Phone | Desk
// ------- | ------ | ----- | -----
// 2024-01 | 2500   | 800   | 150
// 2024-02 | 2700   | 900   | 200

Metrics by Date

Pivot metrics to compare multiple measures side by side:

JavaScript
const byMetric = df.pivot({
  index: "date",
  columns: "metric",
  values: "value",
});

// date    | cpu_usage | memory_usage | disk_usage
// ------- | --------- | ------------ | ----------
// 2024-01 | 45        | 78           | 62
// 2024-02 | 52        | 81           | 65

Use Cases Beyond Tables

Heatmaps

pivot creates the data foundation for heatmaps — transforming time-series or categorical data into a matrix where each cell represents an aggregate value. Once the data is pivoted, you can apply conditional formatting or color scales to visualize patterns.

For example, a heatmap showing website traffic (rows = page, columns = hour, values = visits) makes it easy to spot peak traffic times:

JavaScript
const wide = df.pivot({
  index: "page",
  columns: "hour",
  values: "visits",
});
// Pivoted data can then be rendered as a heatmap with conditional formatting

See the Cookbook for heatmap visualization examples.

Aggregation Before Pivot

By default, pivot uses the first matching value when a (index, columns) pair occurs multiple times in the source data. If your data has duplicate combinations, you must aggregate before pivoting.

Aggregating with groupBy

The most common approach is to use groupBy with an aggregation function:

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

// Source: multiple rows per (product, month) combination
// We want the sum of sales per (product, month)
const aggregated = df
  .groupBy(["product", "month"])
  .sum(["sales"]);

// Now each (product, month) pair appears exactly once
const wide = aggregated.pivot({
  index: "product",
  columns: "month",
  values: "sales",
});

// | product | Jan | Feb | Mar |
// | Laptop  | 500 | 600 | 550 |  (sum of all Jan Laptop sales, etc.)
// | Phone   | 300 | 350 | 400 |

Available aggregation functions: sum, mean, min, max, count

If aggregation is not needed

If each (index, columns) pair appears at most once, no aggregation is required:

JavaScript
// Each (date, metric) pair appears exactly once
const wide = df.pivot({
  index: "date",
  columns: "metric",
  values: "value",
});

Handling Missing Intersections

When a combination of index and columns has no matching row in the source data, the cell is filled with null:

JavaScript
// Source data (incomplete):
// product | month | sales
// Laptop  | Jan   | 100
// Laptop  | Mar   | 110
// Phone   | Jan   | 80
// (Phone / Mar is missing)

const wide = df.pivot({
  index: "product",
  columns: "month",
  values: "sales",
});

// product | Jan | Feb  | Mar
// ------- | --- | ---- | ---
// Laptop  | 100 | null | 110
// Phone   | 80  | null | null

Error Handling

pivot throws an error immediately if any of the three columns does not exist:

JavaScript
df.pivot({
  index: "missing_col",
  columns: "month",
  values: "sales",
});
// Error: BareFrame.pivot: column "missing_col" does not exist

This makes typos and schema mismatches visible right away.

Practical Workflow

Read long-format time-series data from a sheet, pivot it to wide format, and write to a summary sheet:

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

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

  // Transform: long format (date, metric, value) → wide format
  const wide = df.pivot({
    index: "date",
    columns: "metric",
    values: "value",
  });

  wide.toSheet(outputSheet);
  console.log(`Pivoted ${df.length} rows → ${wide.length} rows, ${wide.headers.length} columns.`);
}

Next Steps