Offset pandas datetime series by months-CodePudding

I've a pandas df with dates and index like the following

df = pd.DataFrame({'dates': ['2021-09-30','2021-12-31','2021-12-31'],
            'idx':[1,2,3]})

I'd like to move the dates bu n months using the idx column.

I converted the column dates using the datetime format:

df['dates'] = pd.to_datetime(df['dates'])

And then I tried to use some function like MonthEnd or DateOffset from pandas.tseries.offsets but they only work with an integer (and not a list). Do you have any suggestions?

The final result should be the following:

df = pd.DataFrame({'newdates': ['2021-10-31','2022-02-28','2022-03-31']})

Thank you

CodePudding user response：

Using @Psidom method just adjusting the month end date:

from pandas.tseries.offsets import DateOffset

df['new_dates']=(df.dates DateOffset(days=1)   df.idx.map(lambda m: DateOffset(months=m)))-DateOffset(days=1)



    dates   idx     new_dates
0   2021-09-30  1   2021-10-31
1   2021-12-31  2   2022-02-28
2   2021-12-31  3   2022-03-31

CodePudding user response：

from dateutil.relativedelta import relativedelta

df['new_dates'] = df.apply(lambda x: x['dates']   relativedelta(month=x['idx']), axis=1)

CodePudding user response：

With DateOffset / MonthEnd, you can try this:

from pandas.tseries.offsets import DateOffset, MonthEnd
df.dates   df.idx.map(lambda m: DateOffset(months=m))   MonthEnd(0)

0   2021-10-31
1   2022-02-28
2   2022-03-31
dtype: datetime64[ns]