Indexing Activity Windows with Start and End Times in Pandas Using IntervalIndex for Point-in-Time Queries

Learn how to index activity windows in Pandas DataFrames using IntervalIndex. Build intervals from start/end timestamps and query active users at a given time.

Indexing activity windows in a Pandas DataFrame gets tricky when your data has both start and end timestamps per row. Standard time-based slicing expects a DatetimeIndex tied to a single timestamp column, while here each record represents a span. The goal is to index these spans so that you can quickly ask: who was active at a specific moment in time?

Problem setup

The dataset holds users with activity start and end datetimes. A naive approach might be to try a label slice with a string range, but that mechanism applies to a DatetimeIndex, not to pairs of datetime columns. This means it won’t select rows whose spans overlap a period by itself.

import pandas as pd
records = {
    'user': ['John Doe', 'Jane Doe'],
    'start': [pd.Timestamp('2025-03-21 11:30:35'), pd.Timestamp('2023-12-31 01:02:03')],
    'end': [pd.Timestamp('2025-03-21 13:05:26'), pd.Timestamp('2024-01-02 03:04:05')],
}
tbl = pd.DataFrame(records)
# This kind of label slice applies to a DatetimeIndex, not two datetime columns holding a span.
_ = tbl['2024-01-01:2024-01-31']

What’s going on

Each row is a time interval, not a single timestamp. Pandas Period represents a fixed granularity like a day or minute, but not arbitrary start-to-end windows per row. MultiIndex is great for hierarchical labels, yet it isn’t a structure for continuous time ranges. To model a span per record directly in the index, you need an index that understands intervals.

Solution: use IntervalIndex for time spans

Pandas provides IntervalIndex, a native way to index rows by continuous intervals. Build it from your start and end columns, set it as the index, and then use contains to check whether a given point in time falls inside any interval.

import pandas as pd
records = {
    'user': ['John Doe', 'Jane Doe'],
    'start': [pd.Timestamp('2025-03-21 11:30:35'), pd.Timestamp('2023-12-31 01:02:03')],
    'end': [pd.Timestamp('2025-03-21 13:05:26'), pd.Timestamp('2024-01-02 03:04:05')],
}
tbl = pd.DataFrame(records)
span_idx = pd.IntervalIndex.from_arrays(tbl['start'], tbl['end'], closed='both')
tbl.set_index(span_idx, inplace=True)
tbl.drop(columns=['start', 'end'], inplace=True)
probe_ts = pd.Timestamp("2024-01-01 12:00:00")
online_subset = tbl[tbl.index.contains(probe_ts)]
print(online_subset)

This returns the row for Jane Doe, because her activity window covers the probed timestamp. The key is that IntervalIndex models each row as a closed interval, and index.contains(timestamp) selects the records whose intervals include that moment.

Why this matters

When each row denotes an activity window, a point-in-time question like “who was active at 2024-01-01 12:00:00?” maps cleanly to an interval containment check. Instead of bending DatetimeIndex, Period, or MultiIndex into shapes they weren’t made for, IntervalIndex gives you direct, readable logic and maintains alignment with Pandas indexing semantics.

Takeaways

Use IntervalIndex when your rows represent time spans. Keep the interval closure explicit with the closed parameter. For point-in-time lookups, rely on index.contains with a Timestamp to fetch active users precisely when you need them.