How can I count the rows between a date index and a date one month in the future in pandas vectorized to add them as a column?

user_10441659 0
Viewed 45

I have a dataframe (df) with a date index. And I want to achieve the following:

1. Take Dates column and add one month -> e.g. nxt_dt = df.index + np.timedelta64(month=1) and lets call df.index curr_dt

2. Find the nearest entry in Dates that is >= nxt_dt.

3 Count the rows between curr_dt and nxt_dt and put them into a column in df.

The result is supposed to look like this:

            px_volume  listed_sh  ...  iv_mid_6m      '30d'
Dates                             ...                               
2005-01-03     228805        NaN  ...   0.202625       21
2005-01-04     189983        NaN  ...   0.203465       22           
2005-01-05     224310        NaN  ...   0.202455       23
2005-01-06     221988        NaN  ...   0.202385       20
2005-01-07     322691        NaN  ...   0.201065       21

Needless to mention that there are only dates/rows in the df for which there are observations.

I can think of some different ways to get this done in loops, but since the data I work with is quite big, I would really like to avoid to loop through rows to fill them.

Is there a way in pandas to get this done vectorized?

pandas timedelta python
1 Answers

If you are OK to reindex this should do the job:

import numpy as np
import pandas as pd

df = pd.DataFrame({'date': ['2020-01-01', '2020-01-08', '2020-01-24', '2020-01-29', '2020-02-09', '2020-03-04']})

df['date'] = pd.to_datetime(df['date'])
df['value'] = 1

df = df.set_index('date')
df = df.reindex(pd.date_range('2020-01-01','2020-03-04')).fillna(0)

df = df.sort_index(ascending=False)
df['30d'] = df['value'].rolling(30).sum() - 1

df.sort_index().query("value == 1")

gives:

    value   30d
2020-01-01  1.0 3.0
2020-01-08  1.0 2.0
2020-01-24  1.0 2.0
2020-01-29  1.0 1.0
2020-02-09  1.0 NaN
2020-03-04  1.0 NaN
user_27839770
Related