Class SimpleTable

Example

// Compute the cumulative sum of 'sales' in a new 'cumulativeSales' column
// Ensure the table is sorted by a relevant column (e.g., date) before calling this method.
await table.accumulate("sales", "cumulativeSales").log();
Copy

Example

// Compute the cumulative sum of 'orders' by 'customer_id'
// Ensure the table is sorted by 'customer_id' and then by a relevant order column (e.g., order_date).
await table.accumulate("orders", "cumulativeOrders", { by: "customer_id" }).log();
Copy

Example

// Compute the cumulative sum of 'revenue' by 'region' and 'product_category'
await table.accumulate("revenue", "cumulativeRevenue", { by: ["region", "product_category"] }).log();
Copy

Example

// Add a total row for every numeric column, labelled "sum" in "region".
await table.addSummaryRows("all", "region", { stats: "sum" }).log();
Copy

Example

// Add two summary rows with default labels.
await table.addSummaryRows(["sales", "expenses"], "region", {
  stats: ["sum", "mean"],
  position: "top",
}).log();
Copy

Example

// Customize the labels written to the label column.
await table.addSummaryRows("all", "region", {
  stats: [
    { stat: "sum", label: "Total" },
    { stat: "mean", label: "Average" },
  ],
}).log();
Copy

Example

// If the minimum is 5, bins have boundaries 5 and 15, 15 and 25, etc.
await table.bins("column1", 10, "binStart", "binEnd").log();
Copy

Example

// Bins start at 0: a value of 10 has binStart 10 and binEnd 20.
await table
  .bins("column1", 10, "binStart", "binEnd", { startValue: 0 })
  .log();
Copy

Example

// Add a new column 'perc' with each 'column1' value divided by the sum of all 'column1' values
await table.columnProportions("column1", "perc").log();
Copy

Example

// Compute proportions for 'column1' by 'column2', rounded to two decimal places
await table.columnProportions("column1", "perc", { by: "column2", decimals: 2 }).log();
Copy

Example

// Compute percentages that sum to 100 before rounding
await table.columnProportions("sales", "sales_percentage", { base: 100, decimals: 1 }).log();
Copy

Example

// Compute proportions for 'sales' by 'region' and 'product_type'
await table.columnProportions("sales", "sales_proportion", { by: ["region", "product_type"] }).log();
Copy

Example

// Compute correlations between all numeric columns, overwriting the current table
await table.correlations().log();
Copy

Example

// Compute correlations between 'column1' and all other numeric columns
await table.correlations({ x: "column1" }).log();
Copy

Example

// Compute the correlation between 'column1' and 'column2'
await table.correlations({ x: "column1", y: "column2" }).log();
Copy

Example

// Compute correlations within 'categoryColumn' and store results in a new table
const correlationTable = await table.correlations({ by: "categoryColumn", outputTable: true }).log();
Copy

Example

// Compute correlations, rounded to 2 decimal places
await table.correlations({ decimals: 2 }).log();
Copy

Example

// Index each country's average home price to its January 2001 value.
await table.indexValues(
  "homePrice",
  "homePriceIndexed",
  {
    column: "date",
    equals: new Date("2001-01-01T00:00:00Z"),
  },
  {
    by: "country",
    base: 100,
    decimals: 1,
  },
).log();
Copy

Example

// Index each value against the mean of its group.
await table.indexValues("homePrice", "homePriceIndexed", { stat: "mean" }, {
  by: "country",
}).log();
Copy

Example

// Index each country's average home price to its earliest value.
// This throws if multiple rows share the earliest date in a country.
await table.indexValues("homePrice", "homePriceIndexed", {
  column: "date",
  at: "min",
}, { by: "country" }).log();
Copy

Example

// Compute all linear regressions between all numeric columns, overwriting the current table
await table.linearRegressions().log();
Copy

Example

// Compute linear regressions with 'column1' as the independent variable and all other numeric columns as dependent variables
await table.linearRegressions({ x: "column1" }).log();
Copy

Example

// Compute the linear regression of 'sales' (y) over 'advertising' (x)
await table.linearRegressions({ x: "advertising", y: "sales" }).log();
Copy

Example

// Compute linear regressions by 'region' and store results in a new table
const regressionTable = await table.linearRegressions({ by: "region", outputTable: true }).log();
Copy

Example

// Compute linear regressions, rounded to 3 decimal places
await table.linearRegressions({ decimals: 3 }).log();
Copy

Example

// Normalize the values in 'column1' and store them in a new 'normalizedColumn1' column
await table.normalize("column1", "normalizedColumn1").log();
Copy

Example

// Normalize 'value' by 'group'
await table.normalize("value", "normalizedValue", { by: "group" }).log();
Copy

Example

// Normalize 'data' values, rounded to 2 decimal places
await table.normalize("data", "normalizedData", { decimals: 2 }).log();
Copy

Example

// Normalize 'score' values to a range from 0 to 10
await table.normalize("score", "scaledScore", { range: [0, 10] }).log();
Copy

Example

// Look for outliers in the 'age' column and store results in a new 'isOutlier' column
await table.outliersIQR("age", "isOutlier").log();
Copy

Example

// Look for outliers in 'salary' by 'gender'
await table.outliersIQR("salary", "salaryOutlier", { by: "gender" }).log();
Copy

Example

// Assigns a quantile from 1 to 10 for each row in a new 'quantiles' column, based on 'column1' values.
await table.quantiles("column1", 10, "quantiles").log();
Copy

Example

// Assign quantiles by 'column2', based on 'column1' values.
await table.quantiles("column1", 10, "quantiles", { by: "column2" }).log();
Copy

Example

// Assigns quartiles (4 quantiles) to 'sales' data, storing results in 'salesQuartile'
await table.quantiles("sales", 4, "salesQuartile").log();
Copy

Example

// Compute ranks in a new 'rank' column based on 'score' values (ascending)
await table.ranks("score", "rank").log();
Copy

Example

// Compute ranks in a new 'descRank' column based on 'score' values (descending)
await table.ranks("score", "descRank", { order: "desc" }).log();
Copy

Example

// Compute ranks by 'department', based on 'salary' values, without gaps
await table.ranks("salary", "salaryRank", { by: "department", dense: true }).log();
Copy

Example

// Compute ranks by both 'department' and 'city'
await table.ranks("sales", "salesRank", { by: ["department", "city"] }).log();
Copy

Example

// Compute a 7-day rolling average of 'sales' with 3 preceding and 3 following rows
// (total window size of 7: 3 preceding + current + 3 following)
await table.rolling("sales", "rollingAvgSales", "mean", 3, 3).log();
Copy

Example

// Compute a rolling sum of 'transactions' by 'customer_id'
await table.rolling("transactions", "rollingSumTransactions", "sum", 5, 0, { by: "customer_id" }).log();
Copy

Example

// Compute a rolling maximum of 'temperature' rounded to 1 decimal place
await table.rolling("temperature", "rollingMaxTemp", "max", 2, 2, { decimals: 1 }).log();
Copy

Example

// Compute horizontal proportions for 'Men', 'Women', and 'NonBinary' columns, rounded to 2 decimal places
await table.rowProportions(["Men", "Women", "NonBinary"], { decimals: 2 }).log();
Copy

The table will then look like this:

By default, the new columns will be named with a suffix of "Perc". You can customize this suffix using the suffix option.

Example

// Compute horizontal proportions with a custom suffix "Prop"
await table.rowProportions(["Men", "Women", "NonBinary"], { suffix: "Prop", decimals: 2 }).log();
Copy

The table will then look like this:

Example

// Compute percentages that sum to 100 on each row before rounding
await table.rowProportions(["Men", "Women", "NonBinary"], { base: 100, decimals: 1 }).log();
Copy

Example

// Add the name and value of the highest-scoring party on each row.
await table.rowRanks(["CAQ", "PLQ", "PQ"], {
  nameColumn: "winner",
  valueColumn: "winningVotes",
}).log();
Copy

Example

// Add only the second-lowest value on each row.
await table.rowRanks(["CAQ", "PLQ", "PQ"], {
  valueColumn: "secondLowestVotes",
  rank: 2,
  order: "asc",
}).log();
Copy

Example

// Measure distance from a reference height and weight.
await table
  .similarityMahalanobis(["height", "weight"], [175, 70], "distance")
  .log();
Copy

Example

// Compare feature vectors and add a dataset-relative similarity score.
await table
  .similarityMahalanobis("features", [175, 70], "distance", {
    similarityColumn: "similarity",
  })
  .log();
Copy

Example

// Compare profiles directly with a row and use the default score column.
const reference = await table.getRow("name === 'Alex'");
await table
  .similarityMahalanobis(["height", "weight"], reference, "distance", {
    similarityColumn: true,
  })
  .log();
Copy

Example

// Summarize all columns with all available statistics, overwriting the current table
const columns = await table.getColumns();
await table.summarize({ columns }).log();
Copy

Example

// Summarize all columns and store the results in a new table with a generated name
const columns = await table.getColumns();
const summaryTable = await table.summarize({ columns, outputTable: true }).log();
Copy

Example

// Summarize all columns and store the results in a new table named 'mySummary'
const columns = await table.getColumns();
const mySummaryTable = await table.summarize({ columns, outputTable: "mySummary" }).log();
Copy

Example

// Summarize a single column ('sales') with all available statistics
await table.summarize({ columns: "sales" }).log();
Copy

Example

// Summarize multiple columns ('sales' and 'profit') with all available statistics
await table.summarize({ columns: ["sales", "profit"] }).log();
Copy

Example

// Summarize 'sales' by 'region' (single category)
await table.summarize({ columns: "sales", by: "region" }).log();
Copy

Example

// Summarize 'sales' by 'region' and 'product_type'
await table.summarize({ columns: "sales", by: ["region", "product_type"] }).log();
Copy

Example

// Summarize 'sales' by 'region' with a specific statistic (mean)
await table.summarize({ columns: "sales", by: "region", stats: "mean" }).log();
Copy

Example

// Summarize 'sales' by 'region' with specific statistics (mean and sum)
await table.summarize({ columns: "sales", by: "region", stats: ["mean", "sum"] }).log();
Copy

Example

// Summarize 'sales' by 'region' with custom named statistics
await table.summarize({ columns: "sales", by: "region", stats: { averageSales: "mean", totalSales: "sum" } }).log();
Copy

Example

// Summarize 'price' and 'cost', rounding aggregated columns to 2 decimal places
await table.summarize({ columns: ["price", "cost"], decimals: 2 }).log();
Copy

Example

// Summarize 'timestamp_column' by converting to milliseconds first
await table.summarize({ columns: "timestamp_column", datesToMs: true, stats: "mean" }).log();
Copy

Example

// Calculate the Z-score for 'age' values and store results in a new 'ageZScore' column
await table.zScore("age", "ageZScore").log();
Copy

Example

// Calculate Z-scores for 'salary' by 'department'
await table.zScore("salary", "salaryZScore", { by: "department" }).log();
Copy

Example

// Calculate Z-scores for 'score', rounded to 2 decimal places
await table.zScore("score", "scoreZScore", { decimals: 2 }).log();
Copy

Example

// Computations are re-run if the callback changes or earlier operations modify the table
const sdb = new SimpleDB();
const items = await sdb.newTable("items").cache((table) => {
  table
    .loadData("items.csv")
    .summarize({
      columns: "price",
      by: "department",
      stats: ["min", "max", "mean"],
    });
});
await items.log();

// It's important to call close() on the SimpleDB instance to clean up the cache.
// This prevents the cache from growing indefinitely.
await sdb.close();
Copy

Example

// Cache with a Time-To-Live (TTL) of 60 seconds
// The computations will be re-run if the cached data is older than 1 minute, the callback changes, or the table changes.
const sdb = new SimpleDB();
const table = await sdb.newTable().cache((table) => {
  table
    .loadData("items.csv")
    .summarize({
      columns: "price",
      by: "department",
      stats: ["min", "max", "mean"],
    });
}, { ttl: 60 });
await table.log();

await sdb.close();
Copy

Example

// Enable verbose logging for cache operations via SimpleDB instance
const sdb = new SimpleDB({ cacheVerbose: true });
const table = await sdb.newTable().cache((table) => {
  table
    .loadData("items.csv")
    .summarize({
      columns: "price",
      by: "department",
      stats: ["min", "max", "mean"],
    });
});
await table.log();

await sdb.close();
Copy

Example

// Read-only table dependencies are tracked automatically. Other captured values go in inputs.
const year = 2026;
const summary = await sdb.newTable("summary").cache(async (table) => {
  table.loadArray(
    await fires.getData({ conditions: `year = ${year}` }),
  );
}, { inputs: [year] });
await summary.log();
Copy

Example

// Add a new column 'total' as a float, calculated from 'column1' and 'column2'
await table.addColumn("total", "float", "column1 + column2").log();
Copy

Example

// Add a new geometry column 'centroid' using the centroid of an existing 'country' geometry column
await table.addColumn("centroid", "geometry('EPSG:4326')", `ST_Centroid("country")`).log();
Copy

Example

// Read a number from a JSON object into a new column using dot notation.
await table.loadArray([{ details: { count: 3 } }], {
  columnTypes: { details: "JSON" },
})
  .addColumn("count", "integer", "details.count")
  .log();
Copy

Example

// Create JSON and fixed-size vector columns with DuckDB expressions.
await table.loadArray([{ count: 3 }])
  .addColumn("details", "JSON", "json_object('count', count)")
  .addColumn("embedding", "FLOAT[3]", "[0.25, 0.5, 0.75]")
  .log();
Copy

Example

// Add numeric IDs: 0, 1, 2, ...
await table
  .addId("edgeId")
  .log();
Copy

Example

// Add string IDs: "flight-0", "flight-1", "flight-2", ...
await flights
  .addId("edgeId", { prefix: "flight-" })
  .log();
Copy

Example

// Add a new column named 'rowNumber' with the row number for each row
await table.addRowNumber("rowNumber").log();
Copy

Example

// Add a new column named 'rowNumber' with the row number for each 'category'
await table.addRowNumber("rowNumber", { by: "category" }).log();
Copy

Example

// Clean all column names in the table
// e.g., "First Name" becomes "firstName", "Product ID" becomes "productId"
await table.cleanColumnNames().log();
Copy