count_where
agg.count_where returns an aggregator that computes the number of values that pass a set of filters within an aggregation group.
Syntax
count_where(col: str, filters: Union[str, Filter, Sequence[str], Sequence[Filter]]) -> Aggregation
Parameters
| Parameter | Type | Description |
|---|---|---|
| col | str | The name of the column that will contain the count of values that pass the filters. |
| filters | Union[str, Filter, Sequence[str], Sequence[Filter]] | Formulas for filtering as a list of Strings. Filters can be:
|
Providing multiple filter strings in the filters parameter will result in an AND operation being applied to the
filters. For example,
"Number % 3 == 0", "Number % 5 == 0" will return the count of values where Number is evenly divisible by
both 3 and 5. You can also write this as a single conditional filter, "Number % 3 == 0 && Number % 5 == 0", and
receive the same result.
You can use the || operator to OR multiple filters. For example, Y == `M` || Y == `N` matches when Y equals
M or N.
Returns
An aggregator that computes the number of values within an aggregation group that pass the provided filters.
Examples
The following table is used for all the examples:
from deephaven import new_table
from deephaven.column import string_col, int_col
source = new_table(
[
string_col(
"X",
[
"A",
"B",
"A",
"C",
"B",
"A",
"B",
"B",
"A",
"A",
"B",
"A",
"C",
"B",
"A",
"B",
"B",
"C",
],
),
string_col(
"Y",
[
"M",
"N",
"M",
"N",
"N",
"M",
"O",
"P",
"N",
"M",
"N",
"M",
"N",
"N",
"M",
"O",
"P",
"N",
],
),
int_col(
"Number",
[
55,
76,
55,
130,
230,
50,
76,
137,
214,
55,
76,
55,
130,
230,
50,
76,
137,
214,
],
),
]
)
- source
In this example, agg.count_where returns the number of values of Number that are >= 20 and < 99 as grouped by X.
from deephaven import agg
result = source.agg_by(
aggs=[agg.count_where(col="count_num", filters=["Number >= 20", "Number < 99"])],
by=["X"],
)
- result
In this example, agg.count_where returns the number of values of Y that are M or N, as grouped by X.
from deephaven import agg
result = source.agg_by(
aggs=agg.count_where(col="count_y", filters='Y == "M" || Y == "N"'), by=["X"]
)
- result
In this example, agg.count_where returns the number of rows where Y equals 'M' and Number > 50 over the entire table (no grouping).
from deephaven import agg
result = source.agg_by(
aggs=agg.count_where(col="count", filters=['Y == "M"', "Number > 50"])
)
- result
In this example, agg.count_where returns the number of rows where Number is between 50 and 100 (inclusive), as grouped by X and Y.
from deephaven import agg
result = source.agg_by(
aggs=agg.count_where(col="count_num", filters=["Number >= 50", "Number <= 100"]),
by=["X", "Y"],
)
- result