atoti.function.value module¶
- 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[Iterable[Level]]) –The levels that must be expressed for this measure to possibly be non-null.
When
None, the measure will also beNoneif 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