CODEWITHSUNDEEP
pythondatetime
user11642562
user11642562

Reputation:

How to calculate months left to a date with datetime in pandas column?

I want to replace all my the days in my Date column to 01 (the first day of the month) and also calculate the months remaining until a certain date for each of the value in my dataframe.

My dataframe looks like this:

Date
2019-02-10
2017-03-02
2018-02-03

and my date is 2019-10-31. I want to know how many months between these two dates and produce a column with the number of months.

I have tried replace for the days but it hasn't worked.

For the months calculation, I add one month to original date (required to the calculation), generate the closing date (closing) and simple subtract them.

dfA.loc[dfA.day > 1, 'DepreDate'] = dfA.date + pd.DateOffset(months=1)

closing = date(today.year, 10, 31)

time_to = abs(dfA.DepreDate - closing)

But I get the error:

unsupported operand type(s) for -: 'DatetimeIndex' and 'datetime.date'

EDIT (SOLVED): Ankur Sinha solved it by adding: 

dfA['First_Date_Month'] = dfA['date'] + pd.offsets.MonthBegin(1)
dfA['Time_Left_In_Months'] = dfA['Closing'].dt.month - dfA['First_Date_Month'].dt.month

and to add 12 months according to the year:

dfA.loc[dfA["First_Date_Month"].dt.year == 2018,'Time_Left_In_Months'] = dfA["Time_Left_In_Months"] + 12

Upvotes: 1

Views: 1145

Answers (1)

Ankur Sinha
Ankur Sinha

Reputation: 6679

If I understand correctly, you have this:

        Date     Target
0 2019-02-10 2019-10-31
1 2017-03-02 2019-10-31
2 2018-02-03 2019-10-31

You want to add one month from the first date of the original date:

df['First_Date_Month'] = df['Date'] + pd.offsets.MonthBegin(1)

And now find months between the two dates:

df['Time_Left_In_Months'] = ((df['Target'] - df['First_Date_Month'])/np.timedelta64(1, 'M')).astype(int)

Output:

        Date     Target First_Date_Month  Time_Left_In_Months
0 2019-02-10 2019-10-31       2019-03-01                    8
1 2017-03-02 2019-10-31       2017-04-01                   30
2 2018-02-03 2019-10-31       2018-03-01                   20

Upvotes: 1

Related Questions