atoti.function.where module#

atoti.where(condition: ColumnCondition, true_value: _OperationLike, false_value: Optional[_OperationLike] = None, /) → TernaryOperation#

atoti.where(condition: Union[Condition, Measure], true_value: MeasureLike, false_value: Optional[MeasureLike] = None, /) → MeasureDescription

atoti.where(condition_to_value: Mapping[Union[Condition, Measure], MeasureLike], /, *, default: Optional[MeasureLike] = 'None') → MeasureDescription

Return a conditional measure.

This function is like an if-then-else statement:

Where the condition is True, the new measure will be equal to true_value.
Where the condition is False, the new measure will be equal to false_value.

If false_value is not None, true_value and false_value must either be both numerical, both boolean or both objects.

If one of the values compared in the condition is None, the condition will be considered False.

Different types of conditions are supported:

Measures compared to anything measure-like:
```
m["Test"] == 20
```
Levels compared to levels, (if the level is not expressed, it is considered None):
```
l["source"] == l["destination"]
```

Levels compared to constants of the same type:

l["city"] == "Paris"
l["date"] > datetime.date(2020,1,1)
l["age"] <= 18

A conjunction or disjunction of conditions using the & operator or | operator:

(m["Test"] == 20) & (l["city"] == "Paris")
(l["Country"] == "USA") | (l["Currency"] == "USD")

Example

>>> df = pd.DataFrame(
...     columns=["Id", "City", "Value"],
...     data=[
...         (0, "Paris", 1.0),
...         (1, "Paris", 2.0),
...         (2, "London", 3.0),
...         (3, "London", 4.0),
...         (4, "Paris", 5.0),
...     ],
... )
>>> table = session.read_pandas(df, keys=["Id"], table_name="filter example")
>>> cube = session.create_cube(table)
>>> l, m = cube.levels, cube.measures
>>> m["Paris value"] = tt.where(l["City"] == "Paris", m["Value.SUM"], 0)
>>> cube.query(m["Paris value"], levels=[l["City"]])
       Paris value
City
London         .00
Paris         8.00

When a mapping of condition to value is passed, the resulting value is the one of the first condition evaluating to True:

>>> m["Value.RECAP"] = tt.where(
...     {
...         m["Value.SUM"] < 3: "less than 3",
...         m["Value.SUM"] <= 3: "less than or equal to 3",
...         m["Value.SUM"]
...         == 3: "equal to 3",  # never used because of the broader condition before
...     },
...     default="more than 3",
... )
>>> cube.query(m["Value.SUM"], m["Value.RECAP"], levels=[l["Id"]])
   Value.SUM              Value.RECAP
Id
0       1.00              less than 3
1       2.00              less than 3
2       3.00  less than or equal to 3
3       4.00              more than 3
4       5.00              more than 3