CODEWITHSUNDEEP
pythonpandasdataframedatedatetime
qazwsx123
qazwsx123

Reputation: 247

counting months between two days in dataframe

I have a dataframe with multiple columns, one of which is a date column. I'm interested in creating a new column which contains the number of months between the date column and a preset date. For example one of the dates in the 'start date' column is '2019-06-30 00:00:00' i would want to be able to calculate the number of months between that date and the end of 2021 so 2021-12-31 and place the answer into a new column and do this for the entire date column in the dataframe. I haven't been able to work out how i could go about this but i would like it in the end to look like this if the predetermined end date was 2021-12-31:

df = 

  |start date  months
 0|2019-06-30     30
 1|2019-08-12     28
 2|2020-01-24     23

Upvotes: 1

Views: 900

Answers (2)

NYC Coder
NYC Coder

Reputation: 7604

You can do this using np.timedelta64:

end_date = pd.to_datetime('2021-12-31')
df['start date'] = pd.to_datetime(df['start date'])
df['month'] = ((end_date - df['start date'])/np.timedelta64(1, 'M')).astype(int)
print(df)

  start date  month
0 2019-06-30     30
1 2019-08-12     28
2 2020-01-24     23

Upvotes: 2

Valdi_Bo
Valdi_Bo

Reputation: 31011

Assume that start date column is of datetime type (not string) and the reference date is defined as follows:

refDate = pd.to_datetime('2021-12-31')

or any other date of your choice.

Then you can compute the number of months as:

df['months'] = (refDate.to_period('M') - df['start date']\
    .dt.to_period('M')).apply(lambda x: x.n)

Upvotes: 2

Related Questions