Numbers Of Day In Month

I have a data frame with a date time index, and I would like to multiply some columns with the number of days in that month. TUFNWGTP TELFS t070101 t070102 t

Solution 1:

There is now a Series.dt.days_in_month attribute for datetime series. Here is an example based on Jeff's answer.

In [3]:df=pd.DataFrame({'date':pd.date_range('20120101',periods=15,freq='M')})In [4]:df['year']=df['date'].dt.yearIn [5]:df['month']=df['date'].dt.monthIn [6]:df['days_in_month']=df['date'].dt.days_in_monthIn [7]:dfOut[7]:dateyearmonthdays_in_month02012-01-31  2012      13112012-02-29  2012      22922012-03-31  2012      33132012-04-30  2012      43042012-05-31  2012      53152012-06-30  2012      63062012-07-31  2012      73172012-08-31  2012      83182012-09-30  2012      93092012-10-31  2012     1031102012-11-30  2012     1130112012-12-31  2012     1231122013-01-31  2013      131132013-02-28  2013      228142013-03-31  2013      331

Solution 2:

pd.tslib.monthrange is an unadvertised / undocumented function that handles the days_in_month calculation (adjusting for leap years). This could/should prob be added as a property to Timestamp/DatetimeIndex.

In [34]:df=DataFrame({'date':pd.date_range('20120101',periods=15,freq='M')})In [35]:df['year']=df['date'].dt.yearIn [36]:df['month']=df['date'].dt.monthIn [37]:df['days_in_month']=df.apply(lambdax:pd.tslib.monthrange(x['year'],x['month'])[1],axis=1)In [38]:dfOut[38]:dateyearmonthdays_in_month02012-01-31  2012      13112012-02-29  2012      22922012-03-31  2012      33132012-04-30  2012      43042012-05-31  2012      53152012-06-30  2012      63062012-07-31  2012      73172012-08-31  2012      83182012-09-30  2012      93092012-10-31  2012     1031102012-11-30  2012     1130112012-12-31  2012     1231122013-01-31  2013      131132013-02-28  2013      228142013-03-31  2013      331

Solution 3:

Here is a little clunky hand-made method to get the number of days in a month

import datetime

def days_in_month(dt):
  next_month = datetime.datetime(
      dt.year + dt.month / 12, dt.month % 12 + 1, 1)
  start_month = datetime.datetime(dt.year, dt.month, 1)
  td = next_month - start_month
  return td.days

For example:

>>>days_in_month(datetime.datetime.strptime('2013-12-12', '%Y-%m-%d'))
31
>>>days_in_month(datetime.datetime.strptime('2013-02-12', '%Y-%m-%d'))
28
>>>days_in_month(datetime.datetime.strptime('2012-02-12', '%Y-%m-%d'))
29
>>>days_in_month(datetime.datetime.strptime('2012-01-12', '%Y-%m-%d'))
31
>>>days_in_month(datetime.datetime.strptime('2013-11-12', '%Y-%m-%d'))
30

I let you figure out how to read your table and do the multiplication yourself :)

Solution 4:

import pandas as pd
from pandas.tseries.offsetsimportMonthEnd

df['dim'] = (pd.to_datetime(df.index) + MonthEnd(0)).dt.day

You can omit pd.to_datetime(), if your index is already DatetimeIndex.