atoti.Cube.create_date_hierarchy()#

Cube.create_date_hierarchy(name, *, column, levels={'Year': 'y', 'Month': 'M', 'Day': 'd'})#

Create a multilevel date hierarchy based on a date column.

The new levels are created by matching a date pattern. Here is a non-exhaustive list of patterns that can be used:

Pattern	Description	Type	Examples
y	Year	Integer	`2001, 2005, 2020`
yyyy	4-digits year	String	`"2001", "2005", "2020"`
M	Month of the year (1 based)	Integer	`1, 5, 12`
MM	2-digits month	String	`"01", "05", "12"`
d	Day of the month	Integer	`1, 15, 30`
dd	2-digits day of the month	String	`"01", "15", "30"`
w	Week number	Integer	`1, 12, 51`
Q	Quarter	Integer	`1, 2, 3, 4`
QQQ	Quarter prefixed with Q	String	`"Q1", "Q2", "Q3", "Q4"`
H	Hour of day (0-23)	Integer	`0, 12, 23`
HH	2-digits hour of day	String	`"00", "12", "23"`
m	Minute of hour	Integer	`0, 30, 59`
mm	2-digits minute of hour	String	`"00", "30", "59"`
s	Second of minute	Integer	`0, 5, 55`
ss	2-digits second of minute	String	`"00", "05", "55"`

Parameters:

name (str) – The name of the hierarchy to create.
column (Column) – A table column containing a date or a datetime.
levels (Mapping[str, str]) – The mapping from the names of the levels to the patterns from which they will be created.

Example

>>> from datetime import date
>>> df = pd.DataFrame(
...     columns=["Date", "Quantity"],
...     data=[
...         (date(2020, 1, 10), 150.0),
...         (date(2020, 1, 20), 240.0),
...         (date(2019, 3, 17), 270.0),
...         (date(2019, 12, 12), 200.0),
...     ],
... )
>>> table = session.read_pandas(
...     df, keys=["Date"], table_name="create_date_hierarchy example"
... )
>>> cube = session.create_cube(table)
>>> l, m = cube.levels, cube.measures
>>> cube.create_date_hierarchy("Date parts", column=table["Date"])
>>> cube.query(
...     m["Quantity.SUM"],
...     include_totals=True,
...     levels=[l["Year"], l["Month"], l["Day"]],
... )
                Quantity.SUM
Year  Month Day
Total                 860.00
2019                  470.00
      3               270.00
            17        270.00
      12              200.00
            12        200.00
2020                  390.00
      1               390.00
            10        150.00
            20        240.00

The full date can also be added back as the last level of the hierarchy:

>>> h = cube.hierarchies
>>> h["Date parts"] = {**h["Date parts"].levels, "Date": table["Date"]}
>>> cube.query(
...     m["Quantity.SUM"],
...     include_totals=True,
...     levels=[l["Date parts", "Date"]],
... )
                           Quantity.SUM
Year  Month Day Date
Total                            860.00
2019                             470.00
      3                          270.00
            17                   270.00
                2019-03-17       270.00
      12                         200.00
            12                   200.00
                2019-12-12       200.00
2020                             390.00
      1                          390.00
            10                   150.00
                2020-01-10       150.00
            20                   240.00
                2020-01-20       240.00

Data inserted into the table after the hierarchy creation will be automatically hierarchized:

>>> table += (date(2021, 8, 30), 180.0)
>>> cube.query(
...     m["Quantity.SUM"],
...     include_totals=True,
...     levels=[l["Date parts", "Date"]],
...     filter=l["Year"] == "2021",
... )
                           Quantity.SUM
Year  Month Day Date
Total                            180.00
2021                             180.00
      8                          180.00
            30                   180.00
                2021-08-30       180.00