Analysis mathematical detail

This page provides more detailed documentation on the various mathematical functions available in Analysis. Each section describes a different functional context and links out to the documentation that powers the functionality in Benchling. 

Aggregate and Window Functions

The following is a list of the functions available for use in Analysis when you use either "Add window function" or "Aggregate table" transformations. Because Analysis tables are powered by duckdb, we link directly to the DuckDB description of each function. 

Aggregate functions operate on multiple rows of data and return a single result, which makes them useful for summarizing large sets of data.

1. average: Returns the arithmetic mean of a set of numeric values
2. count: Counts the number of non-NULL entries
3. count nulls: Counts the number of NULL values in a dataset
4. max: Returns the largest value in a dataset
5. median: Finds the median value in a set of numbers
6. min: Returns the smallest value in a dataset
7. mode: Returns the most frequent value in a dataset
8. product: Returns the product of all values in a dataset (multiplies them together)
9. standard deviation: Measures the amount of variation or dispersion in a set of values
10. standard error: Measures the accuracy with which a sample distribution represents a population
11. sum: Returns the sum of all non-NULL values
12. variance: Measures how far a set of numbers are spread out from their average value
13. geometric mean: Calculates the geometric mean, which is useful when dealing with percentages or rates of change

Window functions perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not group the result into a single row.

1. first: Returns the first value in the window of rows
2. lag: Returns the value of a preceding row at a given offset
3. last: Returns the last value in the window of rows
4. lead: Returns the value of the following row at a given offset
5. cume dist: Calculates the cumulative distribution of a value within its partition
6. dense rank: Assigns ranks to rows without gaps in ranking values
7. percent rank: Returns the relative rank of a row as a percentage of the total
8. rank: Assigns a rank to each row within a partition, with gaps if there are ties
9. row number: Assigns a unique sequential integer to rows within a partition

Computed Column Functions

These are the functions available in the formulas for computed columns.

1. countblanks: Counts the number of blank or NULL values in a dataset
2. geomean: Computes the geometric mean in specific notebook environments

Date & Time Functions

Date functions help to manipulate and analyze date and time values.

1. datebucket: Aggregates date or timestamp values into defined time intervals or "buckets"
2. dateconvert: Converts a date to another format or data type
3. datedif: Returns the difference between two dates in a specified unit (e.g., days, months)
4. datetrunc: Truncates a date or time to a specified precision (e.g., month, day)

String Functions

1. concatenate: Concatenates (joins) multiple text values into one
2. left: Extract a certain number of characters starting from the beginning of a string
3. len: Get length of a string
4. mid: Extract a certain number of characters starting from a chosen position in a string
5. right: Extract a certain number of characters starting from the end of a string
6. split: Splits a string into an array based on a specified delimiter
7. substitute: Replace one string with another
8. textjoin: combine strings with a delimiter

Search Functions

1. extract: Extracts an element from a list
2. vlookup: Locates the first row with a value in a specified column that matches a supplied value, then returns a value from a different column in the same located row. Note that VLOOKUP does not currently support using a column as the value to search for. You can only specify a string.

Regression Models

Regressions are available as part of Analysis.

Note: Regressions, Interpolations, and Analysis Templates require an additional purchase. For access to these features, please contact your Benchling account manager. 

Linear and Quadratic regressions are powered bythe Statsmodel library. Visit the Statsmodels documentation.

The 4-Parameter Logistic (4PL) regression is commonly used in bioassay analysis. It fits a sigmoidal curve to the data, making it ideal for modeling biological systems with a limited response range. This algorithm fits the 4-Parameter Logistic curve to data by minimizing the residual sum of squares between observed and predicted values. It is typically used for nonlinear curve fitting and is implemented in Analysis using the lmfit package in Python. For more information, visit the lmfit documentation.