Skip to main content

​
Cube.create_date_hierarchy(name, *, column, levels=frozendict({‘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:
PatternDescriptionTypeExamples
yYearInteger2001, 2005, 2020
yyyy4-digits yearString"2001", "2005", "2020"
MMonth of the year (1 based)Integer1, 5, 12
MM2-digits monthString"01", "05", "12"
dDay of the monthInteger1, 15, 30
dd2-digits day of the monthString"01", "15", "30"
wWeek numberInteger1, 12, 51
QQuarterInteger1, 2, 3, 4
QQQQuarter prefixed with QString"Q1", "Q2", "Q3", "Q4"
HHour of day (0-23)Integer0, 12, 23
HH2-digits hour of dayString"00", "12", "23"
mMinute of hourInteger0, 30, 59
mm2-digits minute of hourString"00", "30", "59"
sSecond of minuteInteger0, 5, 55
ss2-digits second of minuteString"00", "05", "55"
  • Parameters:
    • name (str) – The name of the hierarchy to create.
    • column (Column) – The column to create the hierarchy from. Its data_type must be LocalDate, LocalDateTime, or ZonedDateTime, and it must belong to an in-memory table, not an external one.
    • levels (Mapping [str , str ]) – The mapping from the names of the levels to the patterns from which they will be created.
  • Return type: None

​
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="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