Array measures¶
Atoti is optimized to handle array data
Loading arrays from CSV¶
Atoti can load array from CSV files. The separator for array elements must be provided to the read_csv method, and the CSV columns must use another separator. All the arrays should have the same length.
[1]:
import atoti as tt
session = tt.create_session()
[2]:
store = session.read_csv(
"data/arrays.csv", keys=["TradeId"], store_name="Store With Arrays", array_sep=";"
)
store.head()
[2]:
| Continent | Country | City | PnL | PnlArray | IntArray | |
|---|---|---|---|---|---|---|
| TradeId | ||||||
| 0 | Europe | France | Paris | 3.469516 | doubleVector[10]{-0.46511920664962714, ...} | intVector[10]{1, ...} |
| 1 | Europe | France | Paris | 92.851919 | doubleVector[10]{10.587927935842618, ...} | intVector[10]{11, ...} |
| 2 | Europe | France | Lyon | 425.866214 | doubleVector[10]{68.43655816283167, ...} | intVector[10]{19, ...} |
| 3 | Europe | France | Bordeaux | 454.127060 | doubleVector[10]{-149.61978026139195, ...} | intVector[10]{14, ...} |
| 4 | Europe | UK | London | 187.015613 | doubleVector[10]{11.449089651224922, ...} | intVector[10]{13, ...} |
[3]:
cube = session.create_cube(store, "Cube")
Arrays default aggregations¶
Atoti provides default aggregation functions on arrays: SUM and AVG. They are applied element by element on the array.
[4]:
lvl = cube.levels
m = cube.measures
cube.query(m["PnlArray.SUM"], levels=lvl["Continent"])
[4]:
| PnlArray.SUM | |
|---|---|
| Continent | |
| Asia | [-14.696414629727794, -35.38205147348333, -62.... |
| Europe | [6.711474315042935, -1.4346671713308226, -72.3... |
[5]:
cube.query(m["PnlArray.AVG"], levels=lvl["Continent"])
[5]:
| PnlArray.AVG | |
|---|---|
| Continent | |
| Asia | [-2.939282925945559, -7.076410294696666, -12.5... |
| Europe | [1.1185790525071557, -0.23911119522180374, -12... |
Additional array functions¶
Sum, Average, Min or Max of all the array elements¶
[6]:
m["sum vect"] = tt.array.sum(m["PnlArray.SUM"])
cube.query(m["sum vect"], levels=lvl["Continent"])
[6]:
| sum vect | |
|---|---|
| Continent | |
| Asia | 357.870873 |
| Europe | 49.101546 |
[7]:
m["avg vect"] = tt.array.avg(m["PnlArray.SUM"])
cube.query(m["avg vect"], levels=lvl["Continent"])
[7]:
| avg vect | |
|---|---|
| Continent | |
| Asia | 35.787087 |
| Europe | 4.910155 |
[8]:
m["min vect"] = tt.array.min(m["PnlArray.SUM"])
cube.query(m["min vect"], levels=lvl["Continent"])
[8]:
| min vect | |
|---|---|
| Continent | |
| Asia | -222.821477 |
| Europe | -86.079550 |
[9]:
m["max vect"] = tt.array.max(m["PnlArray.SUM"])
cube.query(m["max vect"], levels=lvl["Continent"])
[9]:
| max vect | |
|---|---|
| Continent | |
| Asia | 232.607605 |
| Europe | 195.824737 |
Length¶
[10]:
m["length"] = tt.array.len(m["PnlArray.SUM"])
cube.query(m["length"])
[10]:
| length | |
|---|---|
| 0 | 10 |
Variance and Standard Deviation¶
[11]:
m["variance"] = tt.array.variance(m["PnlArray.SUM"])
m["standard deviation"] = tt.array.std(m["PnlArray.SUM"])
cube.query(m["variance"], m["standard deviation"], levels=lvl["Continent"])
[11]:
| variance | standard deviation | |
|---|---|---|
| Continent | ||
| Asia | 16149.335147 | 127.080034 |
| Europe | 6952.275020 | 83.380304 |
Sort¶
[12]:
m["sort"] = tt.array.sort(m["PnlArray.SUM"])
cube.query(m["sort"], levels=lvl["Continent"])
[12]:
| sort | |
|---|---|
| Continent | |
| Asia | [-222.82147745688468, -62.89214785725346, -35.... |
| Europe | [-86.07955036608166, -72.30864853533133, -71.1... |
Percentile¶
[13]:
m["95 percentile"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "simple")
m["95 exc percentile"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "exc")
m["95 inc percentile"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "inc")
m["95 centered percentile"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "centered")
cube.query(
m["95 percentile"],
m["95 exc percentile"],
m["95 inc percentile"],
m["95 centered percentile"],
levels=[lvl["Continent"], lvl["Country"]],
)
[13]:
| 95 percentile | 95 exc percentile | 95 inc percentile | 95 centered percentile | ||
|---|---|---|---|---|---|
| Continent | Country | ||||
| Asia | China | 195.220094 | 256.014079 | 201.299492 | 256.014079 |
| India | 22.352927 | 25.270438 | 22.644678 | 25.270438 | |
| Europe | France | 128.173379 | 163.731662 | 131.729208 | 163.731662 |
| UK | 112.243790 | 146.715692 | 115.690980 | 146.715692 |
[14]:
m["95 linear"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "inc", "linear")
m["95 lower"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "inc", "lower")
m["95 higher"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "inc", "higher")
m["95 nearest"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "inc", "nearest")
m["95 midpoint"] = tt.array.percentile(m["PnlArray.SUM"], 0.95, "inc", "midpoint")
cube.query(
m["95 linear"], m["95 lower"], m["95 higher"], m["95 nearest"], m["95 midpoint"]
)
[14]:
| 95 linear | 95 lower | 95 higher | 95 nearest | 95 midpoint | |
|---|---|---|---|---|---|
| 0 | 299.288892 | 290.080802 | 306.822784 | 306.822784 | 298.451793 |
n greatest / n lowest¶
Returns an array with the n greatest/lowest values of a another array.
[15]:
m["Top 3"] = tt.array.n_greatest(m["PnlArray.SUM"], 3)
cube.query(m["Top 3"])
[15]:
| Top 3 | |
|---|---|
| 0 | [290.0808017776343, 306.8227843309065, 195.349... |
[16]:
m["Bottom 2"] = tt.array.n_lowest(m["PnlArray.SUM"], 2)
cube.query(m["Bottom 2"])
[16]:
| Bottom 2 | |
|---|---|
| 0 | [-135.20079639258478, -308.90102782296634] |
nth greatest value / nth lowest value¶
Returns nth greatest or lowest value of a vector
[17]:
m["Third largest value"] = tt.array.nth_greatest(m["PnlArray.SUM"], 3)
cube.query(m["Third largest value"])
[17]:
| Third largest value | |
|---|---|
| 0 | 195.349055 |
[18]:
m["Second smallest value"] = tt.array.nth_lowest(m["PnlArray.SUM"], 2)
cube.query(m["Second smallest value"])
[18]:
| Second smallest value | |
|---|---|
| 0 | -135.200796 |
Scale¶
[19]:
m["scale x10"] = m["PnlArray.SUM"] * 10.0
cube.query(m["scale x10"])
[19]:
| scale x10 | |
|---|---|
| 0 | [-79.8494031468486, -368.16718644814154, -1352... |
Element at index¶
Extract the element at a given index
[20]:
m["first element"] = m["PnlArray.SUM"][0]
cube.query(m["first element"], m["PnlArray.SUM"])
[20]:
| first element | PnlArray.SUM | |
|---|---|---|
| 0 | -7.98494 | [-7.984940314684859, -36.81671864481415, -135.... |
With the create_parameter_hierarchy function it is possible to create a hierarchy corresponding to the indices of the array. This hierarchy can then be used to “slice” this array and create a measure which depends on the selected index.
[21]:
cube.create_parameter_hierarchy("index", list(range(0, 10)))
m["PnL at index"] = m["PnlArray.SUM"][lvl["index"]]
cube.query(m["PnL at index"], levels=lvl["index"])
[21]:
| PnL at index | |
|---|---|
| index | |
| 0 | -7.984940 |
| 1 | -36.816719 |
| 2 | -135.200796 |
| 3 | 30.371913 |
| 4 | 195.349055 |
| 5 | 306.822784 |
| 6 | 96.072594 |
| 7 | -308.901028 |
| 8 | 290.080802 |
| 9 | -22.821246 |
You can also build non-integer hierarchies and map each member to its index in the hierarchy using the index_measure argument:
[22]:
from datetime import date, timedelta
cube.create_parameter_hierarchy(
"Vector Dates",
[date(2020, 1, 1) + timedelta(days=x) for x in range(0, 10)],
index_measure="Date Index",
)
m["PnL at date"] = m["PnlArray.SUM"][m["Date Index"]]
cube.query(m["Date Index"], m["PnL at date"], levels=lvl["Vector Dates"])
[22]:
| Date Index | PnL at date | |
|---|---|---|
| Vector Dates | ||
| 2020-01-01 | 0 | -7.984940 |
| 2020-01-02 | 1 | -36.816719 |
| 2020-01-03 | 2 | -135.200796 |
| 2020-01-04 | 3 | 30.371913 |
| 2020-01-05 | 4 | 195.349055 |
| 2020-01-06 | 5 | 306.822784 |
| 2020-01-07 | 6 | 96.072594 |
| 2020-01-08 | 7 | -308.901028 |
| 2020-01-09 | 8 | 290.080802 |
| 2020-01-10 | 9 | -22.821246 |
In cases the indices also need to be of arbitrary order or range, it is also possible to manually provide them as a list.
[23]:
cube.create_parameter_hierarchy(
"Custom Vector Dates",
[date(2020, 1, 1) + timedelta(days=x) for x in range(0, 10)],
indices=[9, 8, 7, 6, 5, 0, 1, 2, 3, 4],
index_measure="Custom Date Index",
)
m["PnL at custom date"] = m["PnlArray.SUM"][m["Custom Date Index"]]
cube.query(
m["Custom Date Index"], m["PnL at custom date"], levels=lvl["Custom Vector Dates"]
)
[23]:
| Custom Date Index | PnL at custom date | |
|---|---|---|
| Custom Vector Dates | ||
| 2020-01-01 | 9 | -22.821246 |
| 2020-01-02 | 8 | 290.080802 |
| 2020-01-03 | 7 | -308.901028 |
| 2020-01-04 | 6 | 96.072594 |
| 2020-01-05 | 5 | 306.822784 |
| 2020-01-06 | 0 | -7.984940 |
| 2020-01-07 | 1 | -36.816719 |
| 2020-01-08 | 2 | -135.200796 |
| 2020-01-09 | 3 | 30.371913 |
| 2020-01-10 | 4 | 195.349055 |
Sub-arrays¶
Extract a slice of the array
[24]:
m["first 2 elements"] = m["PnlArray.SUM"][0:2]
cube.query(m["first 2 elements"], m["PnlArray.SUM"])
[24]:
| first 2 elements | PnlArray.SUM | |
|---|---|---|
| 0 | [-7.984940314684859, -36.81671864481415] | [-7.984940314684859, -36.81671864481415, -135.... |