Resample/aggregate intervals in pandas

user_1864294 0
Viewed 218

For a given data frame containing the active time interval for each item, I would like to compute the number of active items in total over time (likely resampled).

For example, for the data frame

df = pd.DataFrame({
    'item': ['a', 'b', 'c', 'd'],
    'active': [
        pd.Interval(pd.Timestamp('2021-04-01 00:00:00'), pd.Timestamp('2021-04-05 00:00:05')),
        pd.Interval(pd.Timestamp('2021-04-01 00:30:00'), pd.Timestamp('2021-04-01 01:30:00')),
        pd.Interval(pd.Timestamp('2021-04-01 01:00:00'), pd.Timestamp('2021-04-02 02:00:00')),
        pd.Interval(pd.Timestamp('2021-04-01 01:00:00'), pd.Timestamp('2021-04-01 01:00:05'))]})

on 2021-04-01 00:45:00, there are two active items (a and b), on 2021-04-03 01:00:00 only one (a).

How can I do that?

pandas intervals dataframe
2 Answers

I think not yet implemented, so use:

s = pd.concat([pd.Series(r.item,pd.date_range(r.active.left,r.active.right, freq='15Min')) 
                 for r in df.itertuples()])
print (s)
2021-04-01 00:00:00    a
2021-04-01 00:15:00    a
2021-04-01 00:30:00    a
2021-04-01 00:45:00    a
2021-04-01 01:00:00    a
                      ..
2021-04-02 01:15:00    c
2021-04-02 01:30:00    c
2021-04-02 01:45:00    c
2021-04-02 02:00:00    c
2021-04-01 01:00:00    d
Length: 492, dtype: object

And then:

s = s.groupby(level=0).size()
print (s)
2021-04-01 00:00:00    1
2021-04-01 00:15:00    1
2021-04-01 00:30:00    2
2021-04-01 00:45:00    2
2021-04-01 01:00:00    4
                      ..
2021-04-04 23:00:00    1
2021-04-04 23:15:00    1
2021-04-04 23:30:00    1
2021-04-04 23:45:00    1
2021-04-05 00:00:00    1
Length: 385, dtype: int64
user_29010020

From pandas.Interval(), you can use in operator to check if a pd.Timestamp() is in pd.Interval(). So you can use apply() on each row to check if the date to check is in active column. Then use boolean indexing to retrieve the desired rows.

date_to_check = '2021-04-01 00:45:00'
df.loc[df.apply(lambda row: pd.Timestamp(date_to_check) in row['active'] , axis=1)]

'''
  item                                      active
0    a           (2021-04-01, 2021-04-05 00:00:05]
1    b  (2021-04-01 00:30:00, 2021-04-01 01:30:00]
'''
user_103151630
Related