pandas `value_counts` on a rolling time window

Question

I have a pandas dataframe containing string values and a datetime index, like so:

from datetime import datetime as dt
import pandas as pd

df = pd.DataFrame(['a', 'b', 'b', 'c', 'b', 'b', 'b'], 
                  [dt(2019, 1, 1), dt(2019, 1, 2), 
                   dt(2019, 1, 3), dt(2019, 1, 4), 
                   dt(2019, 1, 5), dt(2019, 1, 6), 
                   dt(2019, 1, 7)])

If I wanted to compute the number of instances that each value occurs over all times, I can simply call:

>>> print(df[0].value_counts())
b    5
c    1
a    1
Name: 0, dtype: int64

I'd like to create a rolling window and measure the number of instances of each string on a moving window of say, 2 days. Is there a way to combine rolling with value_counts, or similar?

Can you show us what is your expected output / – BENY Jun 05 '19 at 19:06 — BENY, Jun 05 '19 at 19:06

score 11 · Accepted Answer · answered Jun 05 '19 at 19:23

I guess what you are looking for is:

pd.get_dummies(df[0]).rolling('2D').sum()

Output:

            a   b   c
2019-01-01  1.0 0.0 0.0
2019-01-02  1.0 1.0 0.0
2019-01-03  0.0 2.0 0.0
2019-01-04  0.0 1.0 1.0
2019-01-05  0.0 1.0 1.0
2019-01-06  0.0 2.0 0.0
2019-01-07  0.0 2.0 0.0

pandas `value_counts` on a rolling time window

1 Answers1

Linked