CODEWITHSUNDEEP
pythondatepandasdataframe
Ekaterina
Ekaterina

Reputation: 335

Calculating the time interval, depending on the month Data Frame pandas

I have a date frame of the following kind

id,Date 
1,2015-01-23
2,2015-02-20
3,2016-03-16

For each month I want to calculate how many days will be between the date and the last day of the corresponding month

id,Date,LastDay,Interval 
1,2015-01-23,2015-01-31,8
2,2015-02-20,2015-02-28,8
3,2016-03-16,2016-03-31,15

Upvotes: 0

Views: 1080

Answers (1)

EdChum
EdChum

Reputation: 394099

So long as the dtype of Date is already datetime then the following should work:

In[109]:
from pandas.tseries.offsets import *
df['LastDay'] = df['Date'] + MonthEnd()
df['Interval'] = (df['LastDay'] - df['Date']).dt.days
df

Out[109]: 
   id       Date    LastDay  Interval
0   1 2015-01-23 2015-01-31         8
1   2 2015-02-20 2015-02-28         8
2   3 2016-03-16 2016-03-31        15

If needed convert the column using to_datetime:

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

So this calculates the last day by adding an offset , in this case the month end, to the existing date.

We then subtract the LastDay from the Date, this will return a timedelta, this has a member to get just the number of days dt.days

EDIT

To handle the situation where the date is the already the month end, you can subtract a day and then add the month end offset:

In[117]:
from pandas.tseries.offsets import *
df['LastDay'] = (df['Date'] + DateOffset(days=-1)) + MonthEnd()
df['Interval'] = (df['LastDay'] - df['Date']).dt.days
df

Out[117]: 
   id       Date    LastDay  Interval
0   1 2015-01-23 2015-01-31         8
1   2 2015-02-20 2015-02-28         8
2   3 2016-03-16 2016-03-31        15
3   4 2015-01-31 2015-01-31         0
4   5 2015-01-01 2015-01-31        30

Upvotes: 3

Related Questions