Adding months to a date variable in pandas dataframe

Question

I have a pandas dataframe as below:

import pandas as pd
df = pd.DataFrame({'date1':['12/1/2019','12/1/2019','12/1/2019'], 'nb_months':[11,11,12]})
df['date1'] = pd.to_datetime(df['date1'], format='%m/%d/%Y', errors='coerce').dropna()
df

    date1       nb_months
0   2019-12-01  11
1   2019-12-01  11
2   2019-12-01  12

I want to add nb_months to date1 column. I did try below code but the output is not what I am expecting.The below code gives me 1 prior date as an output(for example for 1st row: date3 should be 2019-12-01 + 11 months = 2020-11-01 whereas am getting 2020-10-31.

df['date3'] = df.apply(lambda x: x['date1'] + pd.offsets.MonthEnd(x['nb_months']), axis=1)
df

    date1       nb_months   date3
0   2019-12-01  11          2020-10-31
1   2019-12-01  11          2020-10-31
2   2019-12-01  12          2020-11-30

My expected output:

    date1       nb_months   date3
0   2019-12-01  11          2020-11-01
1   2019-12-01  11          2020-11-01
2   2019-12-01  12          2020-12-01

it's technically correct, what you want is to add `nb_months` + 1 day — Jimmar, Aug 07 '20 at 16:31
Does this answer your question? [How can I get pandas Timestamp offset by certain amount of months?](https://stackoverflow.com/questions/31031138/how-can-i-get-pandas-timestamp-offset-by-certain-amount-of-months) — Jimmar, Aug 07 '20 at 16:35

jsmart · Accepted Answer · 2020-08-07T17:01:32.223

I think you want MonthBegin, not MonthEnd, in order to get new dates on the first of each month. Here is a slightly modified version of your example:

# set up the data frame
df = pd.DataFrame(
    {'date1': ['12/1/2019', '12/2/2019', '12/2/2019', '12/3/2019', '12/31/2019'], 
     'nb_months': [0, 1, 2, 3,  0]})

df['date1'] = pd.to_datetime(
    df['date1'], format='%m/%d/%Y', errors='coerce').dropna()

# apply the offsets
df['date3'] = df.apply(
    lambda x: x['date1'] + pd.offsets.MonthBegin(x['nb_months']), axis=1)

print(df)


       date1  nb_months      date3
0 2019-12-01          0 2019-12-01
1 2019-12-02          1 2020-01-01
2 2019-12-02          2 2020-02-01
3 2019-12-03          3 2020-03-01
4 2019-12-31          0 2020-01-01

score 0 · Answer 2 · answered Aug 07 '20 at 17:11

0

Use pd.DateOffset

df.apply(lambda x: x['date1'] + pd.DateOffset(months=x['nb_months']), axis=1)

Out[41]:
0   2020-11-01
1   2020-11-01
2   2020-12-01
dtype: datetime64[ns]

answered Aug 07 '20 at 17:11

Andy L.

24,909
4
17
29

Adding months to a date variable in pandas dataframe

2 Answers2