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 (Measure) – The measure to take at other coordinates.

coordinates (Mapping[Level, Any]) –

A {level_to shift_on: value_to_shift_to} mapping. Values can either be:

A literal matching an existing member of the key level:

# Return the value of Quantity for France on each member of the Country level.
atoti.at(m["Quantity"], {l["Country"]: "France"})

Another level whose current member the key level will be shifted to:

# Return the value of Quantity for the current member
# of the Target Country and Target City levels.
atoti.at(m["Quantity"], {
    l["Country"]: l["Target Country"],
    l["City"]: l["Target City"],
})

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 (Union[SessionConfiguration, Path, str, None]) –
The session configuration regrouping all the aspects of the session that might change depending on where it is deployed. It can be passed either as:
- A Python object created with atoti.config.create_config().
- A path to a YAML file, enabling the config to be changed without modifying the project’s code. Environment variables can be referenced (even recursively) in this file:
```
>>> yaml_config = '''
... url_pattern: ${{ env.SOME_ENVIRONMENT_VARIABLE }}
... '''
```

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[Measure, MeasureConvertible, date, datetime]) – The first date measure or object.
to_date (Union[Measure, 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

>>> df = pd.DataFrame(
...     columns=["From", "To"],
...     data=[
...         ("2020-01-01", "2020-01-02"),
...         ("2020-02-01", "2020-02-21"),
...         ("2020-03-20", None),
...         ("2020-05-15", "2020-04-15"),
...     ],
... )
>>> store = session.read_pandas(
...     df,
...     store_name="date_diff example",
...     types={
...         "From": tt.type.LOCAL_DATE,
...         "To": tt.type.local_date("yyyy-MM-dd", nullable=True),
...     },
... )
>>> cube = session.create_cube(store)
>>> 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: Measure

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

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

Parameters

measure (Measure) – 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’, ‘following’, ‘interpolate’]) –

Determine the value to use when there is no member at the shifted date:

exact: None.
previous: Value at the previous existing date.
following: Value at the following existing date.

interpolate: Linear interpolation of the values at the previous and following existing dates:

Example:
m2 = atoti.date_shift("m1", on=h["date"], offset="1M", method="interpolate")
date

m1

m2

explanation

2000-01-05

15

10.79

linear interpolation of 2000-02-03’s 10 and 2000-03-03’s 21 for 2000-02-05

2000-02-03

10

21

exact match at 2000-03-03: no need to interpolate

2000-03-03

21

9.73

linear interpolation of 2000-03-03’s 21 and 2000-04-05’s 9 for 2000-04-03

2000-04-05

9

∅

no record after 2000-04-05: cannot interpolate

Return type

Measure

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.

Different types of conditions are supported:

Levels compared to literals of the same type:

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

A conjunction of conditions using the & operator:

(l["source"] == l["destination"]) & (l["city"] == "Paris")

Parameters

measure (Union[date, datetime, int, float, str, List[int], List[float], Measure, MeasureConvertible]) – The measure to filter.
condition (Union[LevelCondition, MultiCondition, LevelIsInCondition, HierarchyIsInCondition]) – The condition to evaluate.

Return type

Measure

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, on, *, apply_filters=False, total_value=None, degrees=None)¶

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

Parameters

measure (Union[Measure, str]) – The measure to take the parent value of.
on (Union[Hierarchy, Collection[Hierarchy]]) – Hierarchy or hierarchies to drill up to take the parent value.
apply_filters (bool) – Whether to apply the query filters when computing the value at the parent member.
total_value (Union[date, datetime, int, float, str, List[int], List[float], Measure, MeasureConvertible, None]) – The value to take when the drill up went above the top level of the hierarchy.
degrees (Optional[Mapping[Hierarchy, int]]) – The number of levels to go up to take the value on each given hierarchy. If a hierarchy is not specified, a degree of 1 is used.

Example

>>> df = pd.DataFrame(
...     columns=["Year", "Month", "Day", "Quantity", "Other"],
...     data=[
...         (2019, 7, 1, 15.0, 245.0),
...         (2019, 7, 2, 20.0, 505.0),
...         (2019, 6, 1, 25.0, 115.0),
...         (2019, 6, 2, 15.0, 135.0),
...         (2018, 7, 1, 5.0, 55.0),
...         (2018, 7, 2, 10.0, 145.0),
...         (2018, 6, 1, 15.0, 145.0),
...         (2018, 6, 2, 5.0, 155.0),
...     ],
... )
>>> store = session.read_pandas(
...     df,
...     store_name="Parent Value",
...     hierarchized_columns=["Year", "Month", "Day"],
... )
>>> cube = session.create_cube(store)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> del h["Year"]
>>> del h["Month"]
>>> del h["Day"]
>>> h["Date"] = [store["Year"], store["Month"], store["Day"]]
>>> m["Degree 1"] = tt.parent_value(m["Quantity.SUM"], h["Date"])
>>> m["Degree 2"] = tt.parent_value(
...     m["Quantity.SUM"], h["Date"], degrees={h["Date"]: 2}
... )
>>> m["Degree 2 with Quantity total"] = tt.parent_value(
...     m["Quantity.SUM"],
...     h["Date"],
...     degrees={h["Date"]: 2},
...     total_value=m["Quantity.SUM"],
... )
>>> m["Degree 2 with Other total"] = tt.parent_value(
...     m["Quantity.SUM"],
...     h["Date"],
...     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.00  1,500.00                                         110.00                  1,500.00
2018                   35.00    500.00   110.00                                110.00                  1,500.00
      6                20.00    300.00    35.00   110.00                       110.00                    110.00
            1          15.00    145.00    20.00    35.00                        35.00                     35.00
            2           5.00    155.00    20.00    35.00                        35.00                     35.00
      7                15.00    200.00    35.00   110.00                       110.00                    110.00
            1           5.00     55.00    15.00    35.00                        35.00                     35.00
            2          10.00    145.00    15.00    35.00                        35.00                     35.00
2019                   75.00  1,000.00   110.00                                110.00                  1,500.00
      6                40.00    250.00    75.00   110.00                       110.00                    110.00
            1          25.00    115.00    40.00    75.00                        75.00                     75.00
            2          15.00    135.00    40.00    75.00                        75.00                     75.00
      7                35.00    750.00    75.00   110.00                       110.00                    110.00
            1          15.00    245.00    35.00    75.00                        75.00                     75.00
            2          20.00    505.00    35.00    75.00                        75.00                     75.00
>>> 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.00    145.00    20.00    35.00                        35.00                     35.00
           2           5.00    155.00    20.00    35.00                        35.00                     35.00
     7     1           5.00     55.00    15.00    35.00                        35.00                     35.00
           2          10.00    145.00    15.00    35.00                        35.00                     35.00
2019 6     1          25.00    115.00    40.00    75.00                        75.00                     75.00
           2          15.00    135.00    40.00    75.00                        75.00                     75.00
     7     1          15.00    245.00    35.00    75.00                        75.00                     75.00
           2          20.00    505.00    35.00    75.00                        75.00                     75.00

See also

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

Return type: Measure

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 (Measure) – 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.0),
...         (2000, 1, 2, 10.0),
...         (2000, 2, 1, 30.0),
...         (2000, 2, 2, 20.0),
...         (2000, 2, 5, 30.0),
...         (2000, 4, 4, 5.0),
...         (2000, 4, 5, 10.0),
...         (2020, 12, 6, 15),
...         (2020, 12, 7, 15),
...     ],
... )
>>> store = session.read_pandas(
...     df,
...     store_name="Rank",
...     hierarchized_columns=[],
... )
>>> cube = session.create_cube(store)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Date"] = [store["Year"], store["Month"], store["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.00    1
2000                  120.00    2
      1                25.00    2
            1          15.00    2
            2          10.00    1
      2                80.00    3
            1          30.00    2
            2          20.00    1
            5          30.00    3
      4                15.00    1
            4           5.00    1
            5          10.00    2
2020                   30.00    1
      12               30.00    1
            6          15.00    1
            7          15.00    2

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: Measure

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

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

Parameters

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

Return type

Measure

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 (Measure) – 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),
...     ],
... )
>>> store = session.read_pandas(
...     df,
...     store_name="Total",
...     hierarchized_columns=["Year", "Month", "Day"],
... )
>>> cube = session.create_cube(store)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> del h["Year"]
>>> del h["Month"]
>>> del h["Day"]
>>> h["Date"] = [store["Year"], store["Month"], store["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: Measure

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

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

Parameters

column (Column) – The store 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 store 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_store = session.read_pandas(
...     sales_df, keys=["Month", "City", "Product"], store_name="Sales"
... )
>>> products_store = session.read_pandas(
...     products_df, keys=["Name", "Month"], store_name="Products"
... )
>>> sales_store.join(
...     products_store, mapping={"Month": "Month", "Product": "Name"}
... )
>>> cube = session.create_cube(sales_store)
>>> l, m = cube.levels, cube.measures
>>> m["Purchase price"] = tt.value(products_store["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_store["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_store["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: Measure

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, NamedMeasure]) – The condition to evaluate.
true_value (Union[date, datetime, int, float, str, List[int], List[float], Measure, MeasureConvertible, Column, Operation]) – The measure to propagate where the condition is True.
false_value (Union[date, datetime, int, float, str, List[int], List[float], Measure, 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),
...     ],
... )
>>> store = session.read_pandas(df, keys=["Id"], store_name="filter example")
>>> cube = session.create_cube(store)
>>> 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[Measure, TernaryOperation]

atoti package¶

Subpackages¶

Submodules¶

Module contents¶