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"], "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