Date Utils

Utility functions for time-related operations in retail analysis.

filter_and_label_by_periods(transactions, period_ranges, period_col='period_name')

Filters transactions to specified time periods and adds period labels.

This function filters transactions based on specified time periods and adds a new column indicating the period name. It is useful for analyzing transactions within specific date ranges and comparing KPIs between them.

Example

transactions = ibis.table("transactions") period_ranges = { "Q1": ("2023-01-01", "2023-03-31"), "Q2": ("2023-04-01", "2023-06-30"), } filtered_transactions = filter_and_label_by_periods(transactions, period_ranges)

filtered_transactions will only contain transactions from the date ranges specified in Q1 and Q2 and a new

column 'period_name' will be in the table defining the period for each transaction.

Parameters:

Name	Type	Description	Default
transactions	Table	An ibis table with a transaction_date column.	required
period_ranges	dict[str, tuple[datetime, datetime] | tuple[str, str]]	Dict where keys are period names and values are(start_date, end_date) tuples.	required
period_col	str	Name of the column to create for period labels. Defaults to "period_name".	'period_name'

Returns:

Type	Description
Table	An ibis table with filtered transactions and added period label column.

Raises:

Type	Description
ValueError	If any value in period_ranges is not a tuple of length 2.
ValueError	If first date > second date for any period.
ValueError	If periods overlap with each other.

Source code in openretailscience/utils/date.py

def filter_and_label_by_periods(
    transactions: ibis.Table,
    period_ranges: dict[str, tuple[datetime, datetime] | tuple[str, str]],
    period_col: str = "period_name",
) -> ibis.Table:
    """Filters transactions to specified time periods and adds period labels.

    This function filters transactions based on specified time periods and adds a new column indicating the period name.
    It is useful for analyzing transactions within specific date ranges and comparing KPIs between them.

    Example:
        transactions = ibis.table("transactions")
        period_ranges = {
            "Q1": ("2023-01-01", "2023-03-31"),
            "Q2": ("2023-04-01", "2023-06-30"),
        }
        filtered_transactions = filter_and_label_by_periods(transactions, period_ranges)
        # filtered_transactions will only contain transactions from the date ranges specified in Q1 and Q2 and a new
        # column 'period_name' will be in the table defining the period for each transaction.

    Args:
        transactions (ibis.Table): An ibis table with a transaction_date column.
        period_ranges (dict[str, tuple[datetime, datetime] | tuple[str, str]]): Dict where keys are period names and
            values are(start_date, end_date) tuples.
        period_col (str, optional): Name of the column to create for period labels. Defaults to "period_name".

    Returns:
        An ibis table with filtered transactions and added period label column.

    Raises:
        ValueError: If any value in period_ranges is not a tuple of length 2.
        ValueError: If first date > second date for any period.
        ValueError: If periods overlap with each other.
    """
    # Validate periods first
    _validate_and_normalize_periods(period_ranges)

    branches = []
    date_column = transactions[get_option("column.transaction_date")]

    date_col_dtype = date_column.type()

    for period_name, date_range in period_ranges.items():
        start_date, end_date = date_range

        period_condition = date_column.between(
            ibis.literal(start_date, type=date_col_dtype),
            ibis.literal(end_date, type=date_col_dtype),
        )
        branches.append((period_condition, ibis.literal(period_name)))

    conditions = ibis.or_(*[condition[0] for condition in branches])
    return transactions.filter(conditions).mutate(**{period_col: ibis.cases(*branches)})

find_overlapping_periods(start_date, end_date, return_str=True)

Find overlapping time periods within the given date range, split by year.

This function generates overlapping periods between a given start date and end date. The first period will start from the given start date, and each subsequent period will start on the same month and day for the following years, ending each period on the same month and day of the end date but in the subsequent year, except for the last period, which ends at the provided end date.

Note

This function does not adjust for leap years. If the start or end date is February 29, it may cause an issue in non-leap years.

Parameters:

Name	Type	Description	Default
start_date	datetime | str	The starting date of the range, either as a datetime object or 'YYYY-MM-DD' string.	required
end_date	datetime | str	The ending date of the range, either as a datetime object or 'YYYY-MM-DD' string.	required
return_str	bool	If True, returns dates as ISO-formatted strings ('YYYY-MM-DD'). If False, returns datetime objects. Defaults to True.	True

Returns:

Type	Description
list[tuple[str | datetime, str | datetime]]	list[tuple[str | datetime, str | datetime]]:
list[tuple[str | datetime, str | datetime]]	A list of tuples where each tuple contains the start and end dates of an overlapping period,
list[tuple[str | datetime, str | datetime]]	either as strings (ISO format) or datetime objects. Returned datetimes preserve the
list[tuple[str | datetime, str | datetime]]	timezone-awareness of the input (naive in → naive out, aware in → aware out).
list[tuple[str | datetime, str | datetime]]	String inputs produce naive datetime outputs.

Raises:

Type	Description
TypeError	If start_date and end_date have mismatched timezone awareness (one naive or string and one timezone-aware, or vice versa).
ValueError	If the start date is after the end date.

Source code in openretailscience/utils/date.py

def find_overlapping_periods(
    start_date: datetime | str,
    end_date: datetime | str,
    return_str: bool = True,
) -> list[tuple[str | datetime, str | datetime]]:
    """Find overlapping time periods within the given date range, split by year.

    This function generates overlapping periods between a given start date and end date.
    The first period will start from the given start date, and each subsequent period will start on
    the same month and day for the following years, ending each period on the same month and day
    of the end date but in the subsequent year,
    except for the last period, which ends at the provided end date.

    Note:
        This function does not adjust for leap years. If the start or end date is February 29,
        it may cause an issue in non-leap years.

    Args:
        start_date (datetime | str): The starting date of the range, either as a
            datetime object or 'YYYY-MM-DD' string.
        end_date (datetime | str): The ending date of the range, either as a
            datetime object or 'YYYY-MM-DD' string.
        return_str (bool, optional): If True, returns dates as ISO-formatted strings ('YYYY-MM-DD').
                                     If False, returns datetime objects. Defaults to True.

    Returns:
        list[tuple[str | datetime, str | datetime]]:
        A list of tuples where each tuple contains the start and end dates of an overlapping period,
        either as strings (ISO format) or datetime objects. Returned datetimes preserve the
        timezone-awareness of the input (naive in → naive out, aware in → aware out).
        String inputs produce naive datetime outputs.

    Raises:
        TypeError: If start_date and end_date have mismatched timezone awareness
            (one naive or string and one timezone-aware, or vice versa).
        ValueError: If the start date is after the end date.
    """
    # Track whether outputs should be tz-naive to preserve backward compatibility.
    # String inputs and naive datetime inputs both produced naive outputs before.
    start_is_naive = _is_naive(start_date)
    end_is_naive = _is_naive(end_date)

    if start_is_naive != end_is_naive:
        msg = "start_date and end_date must have matching timezone awareness. Got naive and aware (or vice versa)."
        raise TypeError(msg)

    start_date = _normalize_datetime(start_date)
    end_date = _normalize_datetime(end_date)

    if start_date > end_date:
        raise ValueError("Start date must be before end date")

    if start_date.year == end_date.year:
        return []

    if start_is_naive:
        output_tz = None
        start_date = start_date.replace(tzinfo=None)
    else:
        output_tz = start_date.tzinfo

    period_starts = [
        start_date if year == start_date.year else datetime(year, start_date.month, start_date.day, tzinfo=output_tz)
        for year in range(start_date.year, end_date.year)
    ]
    period_ends = [
        datetime(year + 1, end_date.month, end_date.day, tzinfo=output_tz)
        for year in range(start_date.year, end_date.year)
    ]

    if return_str:
        return [
            (s.strftime("%Y-%m-%d"), e.strftime("%Y-%m-%d")) for s, e in zip(period_starts, period_ends, strict=True)
        ]

    return list(zip(period_starts, period_ends, strict=True))