Filter Expressions

A filter expression is an expression that narrows the rows of a dataset by a condition. MIDAS reads filter expressions with the same syntax and the same evaluation rules in the following places: the filter inputs of the Data Table tab and the Filtered Data tab, the filter of a Graph Builder layer, and datasets.filter(), datasets.selectRows(), and datasets.excludeRows() of the Agent API.

The examples on this page use the columns of the Auto MPG dataset. See Sample Datasets for the meaning of the columns.

Structure of an Expression

A filter expression combines conditions such as model_year >= 80 with AND, OR, NOT, and parentheses. Besides a column, a condition can test a stat function. Operators such as AND and LIKE can be written in uppercase or lowercase.

Write a column name in double quotes, as in "model year". A column name that starts with an ASCII letter and consists only of ASCII letters, digits, and underscores can also be written without quotes. However, a column name that is the same as a reserved word of the filter expression, such as and, like, or null, must be enclosed in double quotes.

Write values in the following forms, depending on the data type. Enclose string and enum values in single quotes, as in 'usa'. To include a single quote in a value, write two single quotes, as in 'O''Brien'. Write int64 and float64 values without quotes, as in 80, -1.5, and 1e3. Write boolean values as true or false without quotes. Write date and datetime values in the form '2024-01-31' or '2024/01/31'. To add a time, write the form '2024-01-31T09:00:00'. Add a time zone at the end, as in '2024-01-31T09:00:00+09:00'.

Conditions

The following conditions are available.

ConditionExampleMeaning
= != > < >= <=model_year >= 80Comparison of values
LIKE / ILIKE / NOT LIKE / NOT ILIKEname LIKE 'ford%'Pattern matching
IS NULL / IS NOT NULLhorsepower IS NULLTest for missing values
IN / NOT INorigin IN ('europe', 'japan')Match against any of the candidates
BETWEEN / NOT BETWEENmodel_year BETWEEN 70 AND 75Range test that includes both ends
NOT (condition)NOT (origin = 'usa' AND model_year = 70)Negation of a condition

In a LIKE pattern, % matches any string of zero or more characters, and _ matches any single character. To match % or _ as the character itself, put a backslash before it. ILIKE matches without distinguishing uppercase from lowercase. LIKE and ILIKE can be used only on string and enum columns. Write the pattern as a string, as in 'ford%'.

Comparison Rules

MIDAS converts a value written in the expression to the data type of the column before comparing it with the column values. For example, model_year = '70' on the int64 column model_year is a comparison with the number 70 and gives the same result as model_year = 70. To compare a string column as numbers, convert its data type first in the Convert Column Types tab.

MIDAS compares date and datetime values in UTC. A time written without a time zone is treated as a UTC time. When a value of a date column is compared with a value that has a time, the date value is treated as 00:00 UTC of that day.

The order of values depends on the data type. An enum column compares its values in the order of the enum definition. For example, in a column whose enum definition lists Low, Medium, and High in that order, level > 'Medium' matches the rows with High. MIDAS compares string values in the order of their UTF-16 code units, so among ASCII letters, uppercase letters come before lowercase letters. For boolean values, false is smaller than true.

Missing Values

A missing value is not equal to any value, has no order relative to other values, and matches no pattern. Therefore a row whose value in the compared column is missing does not match any of the conditions =, >, <, >=, <=, LIKE, ILIKE, IN, and BETWEEN.

A negated condition matches every row that does not match the condition before negation1. !=, NOT IN, NOT BETWEEN, NOT LIKE, NOT ILIKE, and NOT (condition) all match rows with a missing value, because the condition before negation does not match those rows. For example, horsepower != 100 also matches the rows where horsepower is missing. To remove rows with a missing value from a negated condition, add AND horsepower IS NOT NULL.

The only conditions that test whether a value is missing are IS NULL and IS NOT NULL. NULL cannot be written as a value.

Stat Functions

A stat function returns, for each row, a value that a stat of Graph Builder computes. A stat gathers rows into aggregated results. On this page, one aggregated result that a stat makes, such as one bin of a histogram or the mean of one group, is called an element. A stat function returns to each row the value of the element that the row belongs to. For example, summary(mpg, fun: mean, by: [origin]) returns to each row the mean of mpg over the rows that have the same origin value as that row.

A function aggregates over all rows of the dataset that the filter applies to. Other conditions joined with AND in the same expression do not narrow the rows it aggregates; only where does.

A function can be written wherever a column can be written. It can be written on either side of a comparison, and it can be the subject of IS NULL, IN, and BETWEEN.

Function Syntax

Write a function in the form name(column, ..., by: [column, ...], key: value, ..., where: condition). The positional arguments are the columns that the function takes as input. by is a list of columns that divides the rows into groups, and the function forms a group for each combination of the values of the by columns. The function treats a missing value in a by column as one value and forms a group for it. where is a condition on the rows that the function uses in its computation, written in the same syntax as a filter expression. Write column names by the same rules as a column tested by a condition. Function names are case-insensitive and are read as a function only when ( follows, so a column named count can be written as in count > 3.

Write the value of a named argument as a number, a string in single quotes, true or false, or a list of column names in square brackets. An identifier without quotes is read as a string, so fun: mean means the same as fun: 'mean'.

Most named arguments have the same meaning and range as the stat parameters of the same name. The exceptions are the seven arguments output, within, timeZone, fun, p, k, and closedEnd, and two cases that concern date and datetime columns. output is the named argument that selects which value the function returns. interval of timebin delimits the intervals by the local time of timeZone, not by the viewer's local time. When bin takes a date or datetime column, the unit of binwidth, boundary, and center is the time in milliseconds since 1970-01-01T00:00:00Z.

Values Returned by a Function

A function returns a missing value for a row whose value it cannot compute. A row that does not satisfy where and a row whose value in a positional column is missing belong to no element, so they get a missing value. The rows of a group for which the stat cannot compute a value also get a missing value. summary with fun: sd returns a missing value for the rows of a group that has only one row. density returns a missing value for the rows of a group whose automatic bandwidth is 0 when bandwidth is omitted. A group of one row, a group whose values are all the same, and a group whose middle 50% of values are the same are such groups. roc returns a missing value for a row whose label is outside the range from 0 to 1 and for the rows of a group with only one class of label. survival returns a missing value for every row when even one row has a negative time or an event value other than 0 or 1. A row whose function value is missing does not match a comparison, by the same rule as a row whose column value is missing.

The value of a function has the type datetime for center, start, and end of timebin, boolean for outlier of boxplot, and float64 otherwise. MIDAS compares the value of a function by the same rules as a column of the same type. When bin takes a date or datetime column, its center, start, and end are float64 values in the same millisecond unit as the arguments.

List of Functions

The following functions are available. The output column of the table lists the values that can be selected, and the first value is the default.

FunctionPositional argumentsoutputNamed argumentsReturned value
countCounted columnvalueNoneNumber of rows whose values in the counted column and the by columns are the same as those of the row
summaryNumeric columnvaluefun (required), p, kAggregate fun of the group
binNumeric, date, or datetime columncenter, value, start, endbins, binwidth, boundary, center, yScale, closedEndCenter, height, lower end, or upper end of the bin that contains the row
timebinDate or datetime columncenter, value, start, endinterval, yScale, timeZone (required for a datetime column)Center, height, start, or end of the time interval that contains the row
boxplotNumeric columnvalue, outliercoefMedian of the group for a row inside the whiskers, or the value of the row for a row beyond the whiskers; outlier is whether the row is beyond the whiskers
densityNumeric columnvaluebandwidth, yScaleKernel density estimate of the group at the value of the row
smoothx column (numeric, date, or datetime), y column (numeric)value, lower, uppermethod, span, level, intervalValue of the smoothing curve at the x of the row, or the lower or upper end of its interval
survivalTime column, event column (both numeric)value, lower, upperconfLevelKaplan-Meier estimate at the time of the row, or the lower or upper end of its pointwise confidence interval
rocScore column, label column (both numeric)tpr, fprNoneTrue positive rate or false positive rate of the ROC point whose threshold is the score of the row
ecdfNumeric columnvalueNoneEmpirical cumulative distribution function of the group at the value of the row
qqNumeric columntheoreticalNoneStandard normal quantile at the plotting position of the row within its group
cumsumInner functionvaluenormalize, withinCumulative sum of the values of the inner function in the order of the elements
sortInner functionrank, valueby (one column), order, limitRank of the category of the by column after sorting the categories by the sum of the values of the inner function (rank), or the value of the inner function for the elements kept by limit (value)
transformInner functionvaluemultiply, addValue of the inner function × multiply + add
stackInner functiontop, bottomwithinUpper or lower end of the segment of the element that contains the row, after stacking the values of the inner function
fillInner functiontop, bottomwithinUpper or lower end of the segment after the same stacking as stack, with each stack scaled to a total of 1

fun of summary takes one of the values in the following table. In the table, SD is the sample standard deviation with denominator n−1, and SE is the standard error SD / √n.

funAggregate
meanMean
sumSum
medianMedian
minMinimum
maxMaximum
sdSD
varSample variance with denominator n−1
lower_se, upper_seMean − SE, mean + SE
lower_sd, upper_sdMean − SD, mean + SD
quantilep-quantile, computed by the same linear interpolation as the quantiles of the Statistics tab
mean_plus_k_sd, mean_minus_k_sdMean + k × SD, mean − k × SD

p is the probability for quantile, a value from 0 to 1; it defaults to 0.5. k is the SD multiplier for mean_plus_k_sd and mean_minus_k_sd; it defaults to 1.

The bin edges of bin and timebin are determined by the range of all rows the function uses in its computation, regardless of by. by divides the row count of each bin by group. bin and timebin put into each bin the rows whose values are at or above its lower end and below its upper end. When binwidth of bin is omitted, the number of bins is fixed to bins, and the last bin also takes the rows whose value is at or above its upper end. The rows above the upper end of the last bin that appear when boundary or center shifts the edges also go into the last bin. When binwidth is written, bin adds bins until the row of the maximum is in one, so when the maximum lies on a bin edge, bin makes a bin that starts at that edge.

closedEnd of bin determines the bin for the rows whose value is at or above closedEnd. Write closedEnd together with binwidth. bin puts the rows whose value is at or above closedEnd into the same bin as a row whose value is closedEnd − binwidth / 2. When closedEnd is a bin edge, these rows go into the bin that ends at closedEnd. When you enclose a histogram whose binwidth is omitted in Select mode, MIDAS records the upper end of its last bin as closedEnd. For a Histogram of a column whose values are all integers and for a layer whose Bin stat applies Integer Bins, MIDAS records the integer binwidth and does not record closedEnd.

For a date column, timebin delimits the intervals by calendar dates. For a datetime column, timebin delimits the intervals by the local time of the IANA time zone written in timeZone (such as 'Asia/Tokyo'), so timeZone is required for a datetime column.

For a datetime column, timebin also delimits intervals at a moment when the local time goes back, such as the end of daylight saving time. In a time zone whose local time goes back from 1:00 to 0:00 (such as Atlantic/Azores), the 1day interval of that day splits in two at the moment the time goes back.

boxplot treats the rows below the first quartile − coef × IQR and the rows above the third quartile + coef × IQR as the rows beyond the whiskers. IQR is the interquartile range, the third quartile − the first quartile. The quartiles are computed by the same linear interpolation as the quantiles of the Statistics tab. The default of coef is 1.5.

density returns the density curve of the group, computed at 100 equally spaced points over the range of the group and linearly interpolated at the value of the row. When bandwidth is omitted, Silverman's rule of thumb determines the bandwidth. When the range of the group is wide relative to the bandwidth, the spacing of the 100 points is wider than the bandwidth, and the difference between the interpolated value and the estimate at the value of the row grows.

smooth returns the smoothing curve of the group, linearly interpolated at the x of the row. lower and upper are the ends of the interval selected by interval, which defaults to the prediction interval when omitted.

The interval of survival is a pointwise confidence interval at each time, built on the log scale from the variance given by Greenwood's formula. It is not a simultaneous band that covers the whole curve.

roc returns the true positive rate and the false positive rate when the rows whose score is at or above the score of the row are classified as positive. When the labels are proportions from 0 to 1, each row counts as positive with the weight of its label value and as negative with the weight 1 − the label value.

qq assigns different plotting positions to rows with the same value, in the order of the data.

Functions That Transform Elements

cumsum, sort, transform, stack, and fill take one inner function as the positional argument and transform the values of the elements that the inner function makes. The transformed value is the output value of the inner function. Bin positions and outlier are not amounts, so write output: value on an inner bin or timebin, and outlier of boxplot cannot be an inner function.

With sort as the inner function, the outer function transforms the values of the inner function of sort in the sorted order, not the ranks.

For within and for by of sort, write columns chosen from the columns that divide the elements of the inner function. The columns that divide the elements are the counted column and the by columns for count, and the by columns for the other functions. Among these five functions, only sort takes a by of its own, which names the one column whose categories are ranked.

cumsum accumulates the values of the inner function in the order of the elements, within each combination of the values of the within columns. The order of the elements is the output order of the inner function: the order in which the groups first appear in the data for count, summary, and boxplot, the order of the bins for bin and timebin, and the sorted order for sort. To accumulate in the order of the values, write sort as the inner function. When within is omitted, it accumulates over all elements. Elements whose value is missing are left out of the sum. With normalize: true, cumsum returns the cumulative sums as percentages with the total as 100. For a combination whose total is 0, it returns the cumulative sums as they are.

sort ranks the categories of the by column from 1 in the order of the sum of the values of the inner function. When order is omitted, it ranks the categories from the largest sum. It gives categories with the same sum different ranks in the order in which they first appear in the data. It returns a missing value for the rows of a category none of whose elements has a computable value, and for the rows of the categories dropped by limit.

stack and fill stack, within each combination of the values of the within columns, the elements of each value of the columns that divide the elements and are not in within, one on top of another. When the inner function bins, they stack within each bin. When within is omitted, it defaults to the counted column for count and to the bin for bin and timebin. fill returns a missing value for the rows of a combination whose total is 0.

Examples of Conditions with Functions

The following table shows examples of conditions with functions. The last example assumes a dataset with a datetime column ts.

ExpressionMatching rows
summary(mpg, fun: mean, by: [origin, cylinders], where: model_year >= 80) BETWEEN 25 AND 35Rows whose origin and cylinders combination has a mean of mpg, computed over the rows with model_year 80 or later only, from 25 to 35 inclusive. Rows with model_year before 80 do not match
mpg > summary(mpg, fun: mean, by: [origin])Rows whose mpg is greater than the mean of mpg in the origin group of the row
boxplot(horsepower, by: [cylinders], output: outlier) = trueRows beyond the whiskers of the box plot of their cylinders group
sort(count(origin), by: [origin]) <= 2Rows of the two origin values with the most rows
timebin(ts, interval: '1month', timeZone: 'Asia/Tokyo', output: start) = '2024-04-01T00:00:00+09:00'Rows whose datetime column ts falls in April 2024 in the local time of Tokyo

See also

Footnotes

  1. The WHERE clause in the SQL Query Editor tab does not follow this rule. WHERE horsepower != 100 does not match the rows where horsepower is missing. ↩