4

M is a col in dataframe df which indicates the number of month.

M
1
0
15

I am trying to find the number of days between 2015-01-01 and 2015-01-01 + df.M. The following col is what i want to get.

daynum
31
0
456

I know how to do it by using loop and list:

int((datetime.strptime("2015-01-01", "%Y-%m-%d") + relativedelta(months=df.M[i]) 
                    - datetime.strptime("2015-01-01", "%Y-%m-%d")).days)

Is there any build-in function in pandas that can solve this problem easily?

Meng
  • 1,148
  • 5
  • 15
  • 23
  • Sorry are you trying to add months or days to `2015-01-01`? – EdChum Feb 05 '16 at 20:58
  • For instance is this what you want: `In [41]: pd.to_datetime('2015-01-01') + pd.TimedeltaIndex(df['M'], unit='M') Out[41]: DatetimeIndex(['2015-01-31 10:29:06', '2015-01-01 00:00:00', '2016-04-01 13:16:30'], dtype='datetime64[ns]', freq=None)`? – EdChum Feb 05 '16 at 20:59
  • Thx! TimedeltaIndex is what i am looking for! – Meng Feb 05 '16 at 21:04
  • See my answer, Timedelta will *not* give the exact correct answer – joris Feb 05 '16 at 21:07

1 Answers1

3

You can use the same approach as in the question, but using the automatic vectorized operations instead of looping.
First convert the series of integers to relativedelta's:

In [76]: M = pd.Series([1, 0, 15])

In [77]: M2 = M.apply(lambda x: dateutil.relativedelta.relativedelta(months=x))

In [78]: M2
Out[78]:
0              relativedelta(months=+1)
1                       relativedelta()
2    relativedelta(years=+1, months=+3)
dtype: object

Then you can do the same calculation:

In [80]: (pd.Timestamp('2015-01-01') + M2) - pd.Timestamp('2015-01-01')
Out[80]:
0    31 days
1     0 days
2   456 days
dtype: timedelta64[ns]

If you want to have it as integer values instead of the timedelta as above, you can get this with .dt.days:

In [81]: days = (pd.Timestamp('2015-01-01') + M2) - pd.Timestamp('2015-01-01')

In [82]: days.dt.days
Out[82]:
0     31
1      0
2    456
dtype: int64

Reason to not use Timedelta

In this case, you cannot work with a timedelta, as this does not exactly shift the date with a certain amount of months, but it appears to give you a certain kind of mean month length:

In [83]: pd.to_timedelta(1, unit='M')
Out[83]: Timedelta('30 days 10:29:06')

In [84]: (pd.Timestamp('2015-01-01') + pd.to_timedelta(M, unit='M')) - pd.Timestamp('2015-01-01')
Out[84]:
0    30 days 10:29:06
1     0 days 00:00:00
2   456 days 13:16:30
dtype: timedelta64[ns]

So this will give slightly different answers. For example in this case, it gives you 30 days instead of 31 for the first element.

The pandas equivalent to relativedelta would be to use a DateOffset. In this case eg pd.DateOffset(months=1)

joris
  • 133,120
  • 36
  • 247
  • 202