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

Question

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

You seem to be using pandas. Please mention this in your question. — Sven Marnach, Jun 16 '17 at 09:13
There are several easy to find answers on how to find the last day of a month on StackOverflow, e.g. https://stackoverflow.com/questions/42950/get-last-day-of-the-month-in-python. Once you have that, computing the number of days is just taking the difference of the two dates. — Sven Marnach, Jun 16 '17 at 09:14
@s_vishnu this hava i tried def last_day_of_month(any_day): next_month = any_day.replace(day=28) + datetime.timedelta(days=4) return next_month - datetime.timedelta(days=next_month.day) — Ekaterina, Jun 16 '17 at 09:15

EdChum · Accepted Answer · 2017-06-16T10:01:57.947

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

I sonst have mistake wenn - 2015-01-31, 2015-02-28, 28 @EdChum — Ekaterina, Jun 16 '17 at 09:41
See updated answer, if you subtract a day first and then add the month end it will correctly handle when the date is beginning or end of the month — EdChum, Jun 16 '17 at 10:02

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

1 Answers1