CumCountWhere
CumCountWhere performs a cumulative count of values that pass a set of filters in an updateBy table operation.
Syntax
CumCountWhere(resultColumn, filters...)
Parameters
| Parameter | Type | Description |
|---|---|---|
| resultColumn | 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. Any filter is permitted, as long as it is not refreshing and does not use row position/key variables or arrays. |
Providing multiple filter strings in the filters parameter results in an AND operation being applied to the filters. For example, "Number % 3 == 0", "Number % 5 == 0" returns 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 UpdateByOperation to be used in an updateBy table operation.
Examples
The following example performs an update_by on the source table using the cum_count_where operation
and counting rows where Y is >= 20 and < 99. No grouping columns are given, so the cumulative count is calculated
for all rows in the table.
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i % 3", "Y = randomInt(0, 100)")
result = source.updateBy(CumCountWhere("count", "Y >= 20", "Y < 99"))
- result
- source
The following example builds off the previous by specifying Letter as the grouping column. Thus, the cumulative count
includes rows where Y is >= 20 and < 99 and is calculated for each unique letter.
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i % 3", "Y = randomInt(0, 100)")
result = source.updateBy(CumCountWhere("count", "Y >= 20 && Y < 99"), "Letter")
- result
- source
In the next example, cum_count_where returns the number of rows where Letter equals 'A' and Y > 50 over the
entire table (without grouping).
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i % 3", "Y = randomInt(0, 100)")
result = source.updateBy(CumCountWhere("count", "Letter == `A`", "Y > 50"))
- result
- source
This example returns the number of rows where Y is between 50 and 100 (inclusive), as grouped by Letter and X.
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i % 3", "Y = randomInt(0, 100)")
result = source.updateBy(CumCountWhere("count", "Y >= 50", "Y <= 100"), "Letter", "X")
- result
- source