atoti package¶

Subpackages¶

Module contents¶

atoti.at(measure, coordinates)¶

Return a measure equal to the passed measure at some other coordinates of the cube.

Parameters

measure (MeasureDescription) – The measure to take at other coordinates.

coordinates (Mapping[Level, Any]) –

A {level_to_shift_on: value_to_shift_to} mapping.

>>> df = pd.DataFrame(
...     columns=[
...         "Country",
...         "City",
...         "Target Country",
...         "Target City",
...         "Quantity",
...     ],
...     data=[
...         ("Germany", "Berlin", "UK", "London", 15),
...         ("UK", "London", "Germany", "Berlin", 24),
...         ("USA", "New York", "UK", "London", 10),
...         ("USA", "New York", "France", "Paris", 3),
...         ("USA", "Seattle", "Germany", "Berlin", 3),
...     ],
... )
>>> table = session.read_pandas(
...     df, table_name="At", hierarchized_columns=[]
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Geography"] = [table["Country"], table["City"]]
>>> h["Target Geography"] = [
...     table["Target Country"],
...     table["Target City"],
... ]
>>> # Using a constant matching an existing member of the key level
>>> m["USA quantity"] = tt.at(
...     m["Quantity.SUM"], {l["Country"]: "USA"}
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["USA quantity"],
...     levels=[l["Country"]],
... )
        Quantity.SUM USA quantity
Country
Germany           15           16
UK                24           16
USA               16           16
>>> # Using another level whose current member the key level will be shifted to
>>> m["Target quantity"] = tt.at(
...     m["Quantity.SUM"],
...     {
...         l["Country"]: l["Target Country"],
...         l["City"]: l["Target City"],
...     },
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Target quantity"],
...     levels=[l["City"], l["Target City"]],
... )
                                            Quantity.SUM Target quantity
Country City     Target Country Target City
Germany Berlin   UK             London                15              24
UK      London   Germany        Berlin                24              15
USA     New York France         Paris                  3
                 UK             London                10              24
        Seattle  Germany        Berlin                 3              15

If this other level is not expressed, the shifting will not be done.

atoti.copy_tutorial(path)¶: Copy the tutorial files to the given path.

atoti.create_session(self, name='Unnamed', *, config=None, **kwargs)¶

Create a session.

Parameters

name (str) – The name of the session.
config (Optional[Mapping[str, Any]]) – The configuration of the session. If not None, it must be a plain Python object. For a complete list of all the configuration keys available, see atoti.config.session_config.SessionConfig.

Example

session = tt.create_session(
    config={
        "port": 9090,
        "user_content_storage": "./content",
        "java_options": ["-Xms1g", "-verbose:gc", "-XX:+UsuG1GC"],
        "logging": {"file_path": "./atoti/server.log"},
    },
)

Return type: Session

atoti.date_diff(from_date, to_date, *, unit='days')¶

Return a measure equal to the difference between two dates.

If one of the date is N/A then None is returned.

Parameters

from_date (Union[MeasureDescription, MeasureConvertible, date, datetime]) – The first date measure or object.
to_date (Union[MeasureDescription, MeasureConvertible, date, datetime]) – The second date measure or object.
unit (Literal[‘seconds’, ‘minutes’, ‘hours’, ‘days’, ‘weeks’, ‘months’, ‘years’]) – The difference unit. Seconds, minutes and hours are only allowed if the dates contain time information.

Example

>>> from datetime import date
>>> df = pd.DataFrame(
...     columns=["From", "To"],
...     data=[
...         (date(2020, 1, 1), date(2020, 1, 2)),
...         (date(2020, 2, 1), date(2020, 2, 21)),
...         (date(2020, 3, 20), None),
...         (date(2020, 5, 15), date(2020, 4, 15)),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="date_diff example",
... )
>>> cube = session.create_cube(table)
>>> l, m = cube.levels, cube.measures
>>> m["Diff"] = tt.date_diff(l["From"], l["To"])
>>> cube.query(m["Diff"], m["contributors.COUNT"], levels=[l["From"], l["To"]])
                      Diff contributors.COUNT
From       To
2020-01-01 2020-01-02    1                  1
2020-02-01 2020-02-21   20                  1
2020-03-20 N/A                              1
2020-05-15 2020-04-15  -30                  1

Return type: MeasureDescription

atoti.date_shift(measure, on, *, offset, method='exact')¶

Return a measure equal to the passed measure shifted to another date.

Parameters

measure (MeasureDescription) – The measure to shift.
on (Hierarchy) – The hierarchy to shift on. Only hierarchies with a single level of type date (or datetime) are supported. If one of the member of the hierarchy is N/A their shifted value will always be None.
offset (str) – The offset of the form xxDxxWxxMxxQxxY to shift by. Only the D, W, M, Q, and Y offset aliases are supported. Offset aliases have the same meaning as Pandas’.
method (Literal[‘exact’, ‘previous’, ‘next’, ‘interpolate’]) –
Determine the value to use when there is no member at the shifted date:
- exact: None.
- previous: Value at the previous existing date.
- next: Value at the next existing date.
- interpolate: Linear interpolation of the values at the previous and next existing dates:

Example

>>> from datetime import date
>>> df = pd.DataFrame(
...     columns=["Date", "Price"],
...     data=[
...         (date(2020, 1, 5), 15.0),
...         (date(2020, 2, 3), 10.0),
...         (date(2020, 3, 3), 21.0),
...         (date(2020, 4, 5), 9.0),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="date_shift example",
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> m["Exact"] = tt.date_shift(
...     m["Price.SUM"], on=h["Date"], offset="1M", method="exact"
... )
>>> m["Previous"] = tt.date_shift(
...     m["Price.SUM"], on=h["Date"], offset="1M", method="previous"
... )
>>> m["Next"] = tt.date_shift(
...     m["Price.SUM"], on=h["Date"], offset="1M", method="next"
... )
>>> m["Interpolate"] = tt.date_shift(
...     m["Price.SUM"], on=h["Date"], offset="1M", method="interpolate"
... )
>>> cube.query(
...     m["Price.SUM"],
...     m["Exact"],
...     m["Previous"],
...     m["Next"],
...     m["Interpolate"],
...     levels=[l["Date"]],
... )
           Price.SUM  Exact Previous   Next Interpolate
Date
2020-01-05     15.00           10.00  21.00       10.76
2020-02-03     10.00  21.00    21.00  21.00       21.00
2020-03-03     21.00           21.00   9.00        9.73
2020-04-05      9.00            9.00

Explanations for Interpolate’s values:

10.76: linear interpolation of 2020-02-03’s 10 and 2020-03-03’s 21 at 2020-02-05.
21.00: no interpolation required since there is an exact match at 2000-03-03.
9.73: linear interpolation of 2020-03-03’s 21 and 2020-04-05’s 9 for 2020-04-03.
∅: no interpolation possible because there are no records after 2020-04-05.

Return type: MeasureDescription

atoti.filter(measure, condition)¶

Return a filtered measure.

The new measure is equal to the passed one where the condition is True and to None elsewhere.

Example

>>> from datetime import date
>>> data = pd.DataFrame(
...     {
...         "Date": [date(2021, 1, 13), date(2021, 7, 5), date(2021, 7, 6)],
...         "City": ["Paris", "Paris", "London"],
...         "Age": [18, 25, 8],
...         "Quantity": [200, 500, 100],
...     }
... )
>>> table = session.read_pandas(
...     data,
...     table_name="City date table",
...     hierarchized_columns=["Date", "City", "Age"],
... )
>>> table.head()
        Date    City  Age  Quantity
0 2021-01-13   Paris   18       200
1 2021-07-05   Paris   25       500
2 2021-07-06  London    8       100
>>> cube = session.create_cube(table)
>>> l, m = cube.levels, cube.measures
>>> # Levels compared to literals of the same type:
>>> m["London Quantity.SUM"] = tt.filter(
...     m["Quantity.SUM"], l["City"] == "London"
... )
>>> m["Quantity.SUM before July"] = tt.filter(
...     m["Quantity.SUM"], l["Date"] < date(2021, 7, 1)
... )
>>> m["Quantity.SUM for age under 18"] = tt.filter(
...     m["Quantity.SUM"], l["Age"] <= 18
... )
>>> # A conjunction of conditions using the ``&`` operator:
>>> m["July Quantity.SUM in Paris"] = tt.filter(
...     m["Quantity.SUM"],
...     (
...         (l["City"] == "Paris")
...         & ((l["Date"]) >= date(2021, 7, 1))
...         & (l["Date"] <= date(2021, 7, 31))
...     ),
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["London Quantity.SUM"],
...     m["Quantity.SUM before July"],
...     m["Quantity.SUM for age under 18"],
...     m["July Quantity.SUM in Paris"],
... )
  Quantity.SUM London Quantity.SUM Quantity.SUM before July Quantity.SUM for age under 18 July Quantity.SUM in Paris
0          800                 100                      200                           300                        500
>>> cube.query(
...     m["Quantity.SUM"],
...     m["London Quantity.SUM"],
...     m["Quantity.SUM before July"],
...     m["Quantity.SUM for age under 18"],
...     m["July Quantity.SUM in Paris"],
...     levels=[l["Date"], l["Age"], l["City"]],
... )
                      Quantity.SUM London Quantity.SUM Quantity.SUM before July Quantity.SUM for age under 18 July Quantity.SUM in Paris
Date       Age City
2021-01-13 18  Paris           200                                          200                           200
2021-07-05 25  Paris           500                                                                                                   500
2021-07-06 8   London          100                 100                                                    100

Parameters

measure – The measure to filter.
condition – The condition to evaluate.

atoti.open_query_session(self, url, name=None, *, auth=None)¶

Open an existing session to query it.

This can be used to connect to:

Other sessions with another atoti process.

ActivePivot cubes built with a classic Java project, if version >= 5.7.0.

Parameters

url (str) – The server base URL. {url}/versions/rest is expected to exist.
name (Optional[str]) – The name to give to the session. Defaults to the passed url argument.
auth (Optional[Callable[[str], Optional[Mapping[str, str]]]]) –
The authentication to use. It is a function taking the request URL and returning a dictionary of HTTP headers to include in the request.

Example:
```
auth=lambda url: {"Authorization": f"Bearer {token}"}
```
There are some built-in helpers: atoti.query.create_basic_authentication() and atoti.query.create_token_authentication().

Return type

QuerySession

atoti.parent_value(measure, *, degrees, apply_filters=False, total_value=None)¶

Return a measure equal to the passed measure at the parent member on the given hierarchies.

Parameters

measure (Union[MeasureDescription, str]) – The measure to take the parent value of.
degrees (Mapping[Hierarchy, int]) – The number of levels to go up to take the value on each given hierarchy.
apply_filters (bool) – Whether to apply the query filters on hierarchies specified in the degrees mapping when computing the value at the parent member.
total_value (Union[date, datetime, int, float, str, List[int], List[float], MeasureDescription, MeasureConvertible, None]) – The value to take when the drill up went above the top level of the hierarchy.

Example

>>> df = pd.DataFrame(
...     columns=["Year", "Month", "Day", "Shop", "Quantity", "Other"],
...     data=[
...         (2019, 7, 1, "Shop1", 15, 245),
...         (2019, 7, 2, "Shop1", 20, 505),
...         (2019, 6, 1, "Shop2", 25, 115),
...         (2019, 6, 2, "Shop2", 15, 135),
...         (2018, 7, 1, "Shop1", 5, 55),
...         (2018, 7, 2, "Shop2", 10, 145),
...         (2018, 6, 1, "Shop1", 15, 145),
...         (2018, 6, 2, "Shop2", 5, 155),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="Parent Value",
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Date"] = [table["Year"], table["Month"], table["Day"]]
>>> m["Degree 1"] = tt.parent_value(m["Quantity.SUM"], degrees={h["Date"]: 1})
>>> m["Degree 2"] = tt.parent_value(m["Quantity.SUM"], degrees={h["Date"]: 2})
>>> m["Degree 2 with Quantity total"] = tt.parent_value(
...     m["Quantity.SUM"],
...     degrees={h["Date"]: 2},
...     total_value=m["Quantity.SUM"],
... )
>>> m["Degree 2 with Other total"] = tt.parent_value(
...     m["Quantity.SUM"],
...     degrees={h["Date"]: 2},
...     total_value=m["Other.SUM"],
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Other.SUM"],
...     m["Degree 1"],
...     m["Degree 2"],
...     m["Degree 2 with Quantity total"],
...     m["Degree 2 with Other total"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Other.SUM Degree 1 Degree 2 Degree 2 with Quantity total Degree 2 with Other total
Year  Month Day
Total                    110     1,500                                            110                     1,500
2018                      35       500      110                                   110                     1,500
      6                   20       300       35      110                          110                       110
            1             15       145       20       35                           35                        35
            2              5       155       20       35                           35                        35
      7                   15       200       35      110                          110                       110
            1              5        55       15       35                           35                        35
            2             10       145       15       35                           35                        35
2019                      75     1,000      110                                   110                     1,500
      6                   40       250       75      110                          110                       110
            1             25       115       40       75                           75                        75
            2             15       135       40       75                           75                        75
      7                   35       750       75      110                          110                       110
            1             15       245       35       75                           75                        75
            2             20       505       35       75                           75                        75
>>> h["Date"].slicing = True
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Other.SUM"],
...     m["Degree 1"],
...     m["Degree 2"],
...     m["Degree 2 with Quantity total"],
...     m["Degree 2 with Other total"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
               Quantity.SUM Other.SUM Degree 1 Degree 2 Degree 2 with Quantity total Degree 2 with Other total
Year Month Day
2018 6     1             15       145       20       35                           35                        35
           2              5       155       20       35                           35                        35
     7     1              5        55       15       35                           35                        35
           2             10       145       15       35                           35                        35
2019 6     1             25       115       40       75                           75                        75
           2             15       135       40       75                           75                        75
     7     1             15       245       35       75                           75                        75
           2             20       505       35       75                           75                        75
>>> h["Date"].slicing = False
>>> m["Degree 1 with applied filter"] = tt.parent_value(
...     m["Quantity.SUM"], degrees={h["Date"]: 1}, apply_filters=True
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Degree 1"],
...     m["Degree 1 with applied filter"],
...     levels=[l["Day"]],
...     include_totals=True,
...     condition=l["Year"] == "2018",
... )
                Quantity.SUM Degree 1 Degree 1 with applied filter
Year  Month Day
Total                     35
2018                      35      110                           35
      6                   20       35                           35
            1             15       20                           20
            2              5       20                           20
      7                   15       35                           35
            1              5       15                           15
            2             10       15                           15
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Degree 1"],
...     m["Degree 1 with applied filter"],
...     levels=[l["Day"]],
...     include_totals=True,
...     condition=l["Shop"] == "Shop1",
... )
                Quantity.SUM Degree 1 Degree 1 with applied filter
Year  Month Day
Total                     55
2018                      20       55                           55
      6                   15       20                           20
            1             15       15                           15
      7                    5       20                           20
            1              5        5                            5
2019                      35       55                           55
      7                   35       35                           35
            1             15       35                           35
            2             20       35                           35

See also

total() to take the value at the top level member on each given hierarchy.

Return type: MeasureDescription

atoti.rank(measure, hierarchy, *, ascending=True, apply_filters=True)¶

Return a measure equal to the rank of a hierarchy’s members according to a reference measure.

Members with equal values are further ranked using the level comparator.

Parameters

measure (MeasureDescription) – The measure on which the ranking is done.
hierarchy (Hierarchy) – The hierarchy containing the members to rank.
ascending (bool) – When set to False, the 1st place goes to the member with greatest value.
apply_filters (bool) – When True, query filters will be applied before ranking members. When False, query filters will be applied after the ranking, resulting in “holes” in the ranks.

Example

>>> df = pd.DataFrame(
...     columns=["Year", "Month", "Day", "Quantity"],
...     data=[
...         (2000, 1, 1, 15),
...         (2000, 1, 2, 10),
...         (2000, 2, 1, 30),
...         (2000, 2, 2, 20),
...         (2000, 2, 5, 30),
...         (2000, 4, 4, 5),
...         (2000, 4, 5, 10),
...         (2020, 12, 6, 15),
...         (2020, 12, 7, 15),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="Rank",
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Date"] = [table["Year"], table["Month"], table["Day"]]
>>> m["Rank"] = tt.rank(m["Quantity.SUM"], h["Date"])
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Rank"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Rank
Year  Month Day
Total                    150    1
2000                     120    2
      1                   25    2
            1             15    2
            2             10    1
      2                   80    3
            1             30    2
            2             20    1
            5             30    3
      4                   15    1
            4              5    1
            5             10    2
2020                      30    1
      12                  30    1
            6             15    1
            7             15    2
>>> m["Rank with filters not applied"] = tt.rank(
...     m["Quantity.SUM"], h["Date"], apply_filters=False
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Rank"],
...     m["Rank with filters not applied"],
...     levels=[l["Month"]],
...     include_totals=True,
...     condition=l["Year"] == "2000",
... )
            Quantity.SUM Rank Rank with filters not applied
Year  Month
Total                120    1                             1
2000                 120    1                             2
      1               25    2                             2
      2               80    3                             3
      4               15    1                             1

2000-01-01 and 2000-01-05 have the same Quantity.SUM value so l[“Day”]’s comparator is used to rank them.

Return type: MeasureDescription

atoti.shift(measure, on, *, offset=1)¶

Return a measure equal to the passed measure shifted to another member.

Parameters

measure (MeasureDescription) – The measure to shift.
on (Level) – The level to shift on.
offset (int) – The amount of members to shift by.

Return type

MeasureDescription

atoti.total(measure, *hierarchies)¶

Return a measure equal to the passed measure at the top level member on each given hierarchy.

It ignores the filters on this hierarchy.

If the hierarchy is not slicing, total is equal to the value for all the members. If the hierarchy is slicing, total is equal to the value on the first level.

Parameters

measure (MeasureDescription) – The measure to take the total of.
hierarchies (Hierarchy) – The hierarchies on which to find the top-level member.

Example

>>> df = pd.DataFrame(
...     columns=["Year", "Month", "Day", "Price"],
...     data=[
...         (2019, 7, 1, 15.0),
...         (2019, 7, 2, 20.0),
...         (2019, 6, 1, 25.0),
...         (2019, 6, 2, 15.0),
...         (2018, 7, 1, 5.0),
...         (2018, 7, 2, 10.0),
...         (2018, 6, 1, 15.0),
...         (2018, 6, 2, 5.0),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="Total",
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Date"] = [table["Year"], table["Month"], table["Day"]]
>>> m["Total(Price)"] = tt.total(m["Price.SUM"], h["Date"])
>>> cube.query(
...     m["Price.SUM"],
...     m["Total(Price)"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Price.SUM Total(Price)
Year  Month Day
Total              110.00       110.00
2018                35.00       110.00
      6             20.00       110.00
            1       15.00       110.00
            2        5.00       110.00
      7             15.00       110.00
            1        5.00       110.00
            2       10.00       110.00
2019                75.00       110.00
      6             40.00       110.00
            1       25.00       110.00
            2       15.00       110.00
      7             35.00       110.00
            1       15.00       110.00
            2       20.00       110.00
>>> h["Date"].slicing = True
>>> cube.query(
...     m["Price.SUM"],
...     m["Total(Price)"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
               Price.SUM Total(Price)
Year Month Day
2018 6     1       15.00        35.00
           2        5.00        35.00
     7     1        5.00        35.00
           2       10.00        35.00
2019 6     1       25.00        75.00
           2       15.00        75.00
     7     1       15.00        75.00
           2       20.00        75.00

Return type: MeasureDescription

atoti.value(column, *, levels=None)¶

Return a measure equal to the value of the given table column.

Parameters

column (Column) – The table column to get the value from.
levels (Optional[Collection[Level]]) –
The levels that must be expressed for this measure to possibly be non-null.

When None, the measure will also be None if the levels corresponding to the keys of column’s table are not expressed.

Passing an empty collection propagate the value on all levels when possible.

Example

>>> sales_df = pd.DataFrame(
...     columns=["Month", "City", "Product"],
...     data=[
...         ("January", "Manchester", "Ice cream"),
...         ("January", "London", "Ice cream"),
...         ("January", "London", "Burger"),
...         ("March", "New York", "Ice cream"),
...         ("March", "New York", "Burger"),
...     ],
... )
>>> products_df = pd.DataFrame(
...     columns=["Name", "Month", "Purchase price"],
...     data=[
...         ("Ice cream", "January", 10.0),
...         ("Ice cream", "February", 10.0),
...         ("Ice cream", "March", 10.0),
...         ("Burger", "January", 10.0),
...         ("Burger", "February", 10.0),
...         ("Burger", "March", 8.0),
...     ],
... )
>>> sales_table = session.read_pandas(
...     sales_df, keys=["Month", "City", "Product"], table_name="Sales"
... )
>>> products_table = session.read_pandas(
...     products_df, keys=["Name", "Month"], table_name="Products"
... )
>>> sales_table.join(
...     products_table, mapping={"Month": "Month", "Product": "Name"}
... )
>>> cube = session.create_cube(sales_table)
>>> l, m = cube.levels, cube.measures
>>> m["Purchase price"] = tt.value(products_table["Purchase price"])

By default, the values do not propagate:

>>> cube.query(
...     m["Purchase price"],
...     m["contributors.COUNT"],
...     include_totals=True,
...     levels=[l["Month"], l["City"], l["Product"]],
... )
                             Purchase price contributors.COUNT
Month   City       Product
Total                                                        5
January                                                      3
        London                                               2
                   Burger             10.00                  1
                   Ice cream          10.00                  1
        Manchester                                           1
                   Ice cream          10.00                  1
March                                                        2
        New York                                             2
                   Burger              8.00                  1
                   Ice cream          10.00                  1

To propagate the values to the City level, the measure can instead be defined as follows:

>>> m["Purchase price"] = tt.value(
...     products_table["Purchase price"], levels=[l["City"]]
... )

With this definition, if all products of a city share the same purchase price, then the city inherits that price:

>>> cube.query(
...     m["Purchase price"],
...     m["contributors.COUNT"],
...     include_totals=True,
...     levels=[l["Month"], l["City"], l["Product"]],
... )
                             Purchase price contributors.COUNT
Month   City       Product
Total                                                        5
January                                                      3
        London                        10.00                  2
                   Burger             10.00                  1
                   Ice cream          10.00                  1
        Manchester                    10.00                  1
                   Ice cream          10.00                  1
March                                                        2
        New York                                             2
                   Burger              8.00                  1
                   Ice cream          10.00                  1

Since the measure has not been defined to propagate on Product, changing the order of the levels prevents any propagation:

>>> cube.query(
...     m["Purchase price"],
...     m["contributors.COUNT"],
...     include_totals=True,
...     levels=[l["Month"], l["Product"], l["City"]],
... )
                             Purchase price contributors.COUNT
Month   Product   City
Total                                                        5
January                                                      3
        Burger                                               1
                  London              10.00                  1
        Ice cream                                            2
                  London              10.00                  1
                  Manchester          10.00                  1
March                                                        2
        Burger                                               1
                  New York             8.00                  1
        Ice cream                                            1
                  New York            10.00                  1

Using levels=[], the value propagates to Month too:

>>> m["Purchase price"] = tt.value(products_table["Purchase price"], levels=[])
>>> cube.query(
...     m["Purchase price"],
...     m["contributors.COUNT"],
...     include_totals=True,
...     levels=[l["Month"], l["City"], l["Product"]],
... )
                             Purchase price contributors.COUNT
Month   City       Product
Total                                                        5
January                               10.00                  3
        London                        10.00                  2
                   Burger             10.00                  1
                   Ice cream          10.00                  1
        Manchester                    10.00                  1
                   Ice cream          10.00                  1
March                                                        2
        New York                                             2
                   Burger              8.00                  1
                   Ice cream          10.00                  1

When filtering out the members with a different Product Price, it even propagates all the way to the grand total:

>>> cube.query(
...     m["Purchase price"],
...     m["contributors.COUNT"],
...     condition=l["Month"] == "January",
...     include_totals=True,
...     levels=[l["Month"], l["City"], l["Product"]],
... )
                             Purchase price contributors.COUNT
Month   City       Product
Total                                 10.00                  3
January                               10.00                  3
        London                        10.00                  2
                   Burger             10.00                  1
                   Ice cream          10.00                  1
        Manchester                    10.00                  1
                   Ice cream          10.00                  1

Return type: MeasureDescription

atoti.where(condition, true_value, false_value=None)¶

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 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 literals 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")

Parameters

condition (Union[BooleanMeasure, ConditionOperation, LevelCondition, MultiCondition, HierarchyIsInCondition, LevelIsInCondition, Measure]) – The condition to evaluate.
true_value (Union[date, datetime, int, float, str, List[int], List[float], MeasureDescription, MeasureConvertible, Column, Operation]) – The measure to propagate where the condition is True.
false_value (Union[date, datetime, int, float, str, List[int], List[float], MeasureDescription, MeasureConvertible, Column, Operation, None]) – The measure to propagate where the condition is False.

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

Return type: Union[MeasureDescription, TernaryOperation]

atoti package¶

Subpackages¶

Submodules¶

Module contents¶