CODEWITHSUNDEEP
pandaspython-3.6
Shanoo
Shanoo

Reputation: 1255

Adding months to a date variable in pandas dataframe

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

Upvotes: 1

Views: 988

Answers (2)

Andy L.
Andy L.

Reputation: 25239

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]

Upvotes: 0

jsmart
jsmart

Reputation: 3001

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

Upvotes: 2

Related Questions