CODEWITHSUNDEEP
pythonpandasdatetimeindex
user2997582
user2997582

Reputation: 67

How do I create a rolling monthly datetime index for pandas?

I have a DatetimeIndex in pandas and I want to convert it to a rolling DatetimeIndex using the last date in the series.

So if I create a sample datetime index:

dates=pd.DatetimeIndex(pd.date_range(dt(2017,10,1),dt(2018,02,02)))

An example

Input: DatetimeIndex with all dates in the above range:

dates
2017-10-01 
2017-10-02  
     .            
     .            
2018-02-01   
2018-02-02

Desired Output: DatetimeIndex with only the 2nd of every month (as that is the last date in the input):

dates
2017-10-02 
2017-11-02
2017-12-02
2018-01-02
2018-02-02

Attempts

I've tried

dates[::-1][::30]

and also

dates[dates.apply(lambda x: x.date().day==2)]

Unfortunately months can differ by 30 or 31 days so the first way doesn't work and while the second method works for days in range 1-30, for the 31st it skips every other month. So, for example, if I had:

dates
2017-10-01 
2017-10-02  
     .            
     .            
2018-01-31

I would want:

dates
2017-10-31 
2017-11-30          
2017-12-31   
2018-01-31

while the second method skips November as it doesn't have a 30th.

Is there any way to use RelativeDelta to do this?

Upvotes: 2

Views: 1253

Answers (1)

Vivek Kalyanarangan
Vivek Kalyanarangan

Reputation: 9081

You can use the .is_month_end functionality in Pandas. This gives an array of boolean values – True if the date is a month-end, false if otherwise. 

import pandas as pd
import datetime as dt

dates=pd.Series(pd.date_range('2017-10-1','2017-12-31'))
print(dates[dates.is_month_end])

Output

DatetimeIndex(['2017-10-31', '2017-11-30', '2017-12-31'], dtype='datetime64[ns]', freq=None)

This will help you filter things.

Upvotes: 1

Related Questions