---
title: Filter Expressions
description: Describes the syntax of the filter expressions that narrow rows in the Data Table tab, the Filtered Data tab, Graph Builder layers, and the Agent API, including how to write column names and values, the kinds of conditions, the rules for comparison and missing values, and stat functions.
priority: 0.6
audience: フィルタ式を書く読者。Data Table タブ、Filtered Data タブ、Graph Builder の layer、Agent API のどれかでフィルタ式を書く場面にいて、構文と評価の規則を調べたい人。統計の知識は仮定しない
criteria: フィルタ式の構文と評価の規則（列名、値、条件、型の変換、比較の順序、欠損値、stat の関数）はこのページを正とし、フィルタ式を使う各ページからはここへリンクする。フィルタ入力欄の操作と、列の型が後から変わったときにタブが式を保持する挙動は data-table と row-selection に、stat のパラメータの意味は custom-graph-reference に委ねる。例は Auto MPG の列で書く
---

# Filter Expressions {#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](data-table#filter-feature) and the [Filtered Data tab](row-selection#additional-filter), the filter of a Graph Builder layer, and [`datasets.filter()`](agent-api#datasetsfilterdatasetid-input), [`datasets.selectRows()`](agent-api#datasetsselectrows), and [`datasets.excludeRows()`](agent-api#datasetsexcluderows) of the Agent API.

The examples on this page use the columns of the [Auto MPG](sample:mpg) dataset. See [Sample Datasets](sample-datasets#auto-mpg) for the meaning of the columns.

## Structure of an Expression {#structure}

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](#stat-functions). 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 {#conditions}

The following conditions are available.

| Condition | Example | Meaning |
|-----------|---------|---------|
| `=` `!=` `>` `<` `>=` `<=` | `model_year >= 80` | Comparison of values |
| `LIKE` / `ILIKE` / `NOT LIKE` / `NOT ILIKE` | `name LIKE 'ford%'` | Pattern matching |
| `IS NULL` / `IS NOT NULL` | `horsepower IS NULL` | Test for missing values |
| `IN` / `NOT IN` | `origin IN ('europe', 'japan')` | Match against any of the candidates |
| `BETWEEN` / `NOT BETWEEN` | `model_year BETWEEN 70 AND 75` | Range 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 {#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](column-type-conversion).

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](enum-definitions#graph-axis-ordering). 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 {#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 negation[^sql-null]. `!=`, `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`.

[^sql-null]: The `WHERE` clause in the [SQL Query Editor tab](sql-editor) does not follow this rule. `WHERE horsepower != 100` does not match the rows where `horsepower` is missing.

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 {#stat-functions}

A stat function returns, for each row, a value that a [stat](custom-graph-reference#statistics) 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 {#stat-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](custom-graph-reference#stat-params) 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 {#stat-function-values}

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 {#stat-function-list}

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.

| Function | Positional arguments | output | Named arguments | Returned value |
|----------|----------------------|--------|-----------------|----------------|
| `count` | Counted column | `value` | None | Number of rows whose values in the counted column and the `by` columns are the same as those of the row |
| `summary` | Numeric column | `value` | `fun` (required), `p`, `k` | Aggregate `fun` of the group |
| `bin` | Numeric, date, or datetime column | `center`, `value`, `start`, `end` | `bins`, `binwidth`, `boundary`, `center`, `yScale`, `closedEnd` | Center, height, lower end, or upper end of the bin that contains the row |
| `timebin` | Date or datetime column | `center`, `value`, `start`, `end` | `interval`, `yScale`, `timeZone` (required for a datetime column) | Center, height, start, or end of the time interval that contains the row |
| `boxplot` | Numeric column | `value`, `outlier` | `coef` | Median 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 |
| `density` | Numeric column | `value` | `bandwidth`, `yScale` | Kernel density estimate of the group at the value of the row |
| `smooth` | x column (numeric, date, or datetime), y column (numeric) | `value`, `lower`, `upper` | `method`, `span`, `level`, `interval` | Value of the smoothing curve at the x of the row, or the lower or upper end of its interval |
| `survival` | Time column, event column (both numeric) | `value`, `lower`, `upper` | `confLevel` | Kaplan-Meier estimate at the time of the row, or the lower or upper end of its pointwise confidence interval |
| `roc` | Score column, label column (both numeric) | `tpr`, `fpr` | None | True positive rate or false positive rate of the ROC point whose threshold is the score of the row |
| `ecdf` | Numeric column | `value` | None | Empirical cumulative distribution function of the group at the value of the row |
| `qq` | Numeric column | `theoretical` | None | Standard normal quantile at the plotting position of the row within its group |
| `cumsum` | Inner function | `value` | `normalize`, `within` | Cumulative sum of the values of the inner function in the order of the elements |
| `sort` | Inner function | `rank`, `value` | `by` (one column), `order`, `limit` | Rank 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`) |
| `transform` | Inner function | `value` | `multiply`, `add` | Value of the inner function × `multiply` + `add` |
| `stack` | Inner function | `top`, `bottom` | `within` | Upper or lower end of the segment of the element that contains the row, after stacking the values of the inner function |
| `fill` | Inner function | `top`, `bottom` | `within` | Upper 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.

| `fun` | Aggregate |
|-------|-----------|
| `mean` | Mean |
| `sum` | Sum |
| `median` | Median |
| `min` | Minimum |
| `max` | Maximum |
| `sd` | SD |
| `var` | Sample variance with denominator n−1 |
| `lower_se`, `upper_se` | Mean − SE, mean + SE |
| `lower_sd`, `upper_sd` | Mean − SD, mean + SD |
| `quantile` | p-quantile, computed by the same linear interpolation as the [quantiles of the Statistics tab](basic-statistics#quantiles) |
| `mean_plus_k_sd`, `mean_minus_k_sd` | Mean + 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](row-selection#selection-condition), 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](basic-statistics#quantiles). 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](glossary#kde) 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](concepts-survival#kaplan-meier-estimator). 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 {#element-functions}

`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 {#stat-function-examples}

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

| Expression | Matching rows |
|------------|---------------|
| `summary(mpg, fun: mean, by: [origin, cylinders], where: model_year >= 80) BETWEEN 25 AND 35` | Rows 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) = true` | Rows beyond the whiskers of the box plot of their `cylinders` group |
| `sort(count(origin), by: [origin]) <= 2` | Rows 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 {#see-also}

- **[The Data Table Tab](data-table#filter-feature)** -- The filter input and saving the filtered rows
- **[Row Selection and the Filtered Data Tab](row-selection#additional-filter)** -- Additional filtering in the Filtered Data tab
- **[Custom Graph Reference](custom-graph-reference#statistics)** -- Stat types and their parameters
- **[Agent API](agent-api#datasetsfilterdatasetid-input)** -- Filtering with `datasets.filter()`
