Numbers of Day in Month

Question

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  t070103  t070104  
TUDIARYDATE                                                              
2003-01-03   8155462.672158      2        0        0        0        0   
2003-01-04   1735322.527819      1        0        0        0        0   
2003-01-04   3830527.482672      2       60        0        0        0   
2003-01-02   6622022.995205      4        0        0        0        0   
2003-01-09   3068387.344956      1        0        0        0        0 

Here, I would like to multiply all the columns starting with t with 31. That is, expected output is

                   TUFNWGTP  TELFS  t070101  t070102  t070103  t070104  
TUDIARYDATE                                                              
2003-01-03   8155462.672158      2        0        0        0        0   
2003-01-04   1735322.527819      1        0        0        0        0   
2003-01-04   3830527.482672      2     1680        0        0        0   
2003-01-02   6622022.995205      4        0        0        0        0   
2003-01-09   3068387.344956      1        0        0        0        0 

I know that there are some ways using calendar or similar, but given that I'm already using pandas, there must be an easier way - I assume.

There is no such datetime property, but there is an offset M - but I don't know how I would use that without massive inefficiency.

Answer 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.year

In [5]: df['month'] = df['date'].dt.month

In [6]: df['days_in_month'] = df['date'].dt.days_in_month

In [7]: df
Out[7]:
         date  year  month  days_in_month
0  2012-01-31  2012      1             31
1  2012-02-29  2012      2             29
2  2012-03-31  2012      3             31
3  2012-04-30  2012      4             30
4  2012-05-31  2012      5             31
5  2012-06-30  2012      6             30
6  2012-07-31  2012      7             31
7  2012-08-31  2012      8             31
8  2012-09-30  2012      9             30
9  2012-10-31  2012     10             31
10 2012-11-30  2012     11             30
11 2012-12-31  2012     12             31
12 2013-01-31  2013      1             31
13 2013-02-28  2013      2             28
14 2013-03-31  2013      3             31

Answer 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.year

In [36]: df['month'] = df['date'].dt.month

In [37]: df['days_in_month'] = df.apply(lambda x: pd.tslib.monthrange(x['year'],x['month'])[1], axis=1)

In [38]: df
Out[38]: 
         date  year  month  days_in_month
0  2012-01-31  2012      1             31
1  2012-02-29  2012      2             29
2  2012-03-31  2012      3             31
3  2012-04-30  2012      4             30
4  2012-05-31  2012      5             31
5  2012-06-30  2012      6             30
6  2012-07-31  2012      7             31
7  2012-08-31  2012      8             31
8  2012-09-30  2012      9             30
9  2012-10-31  2012     10             31
10 2012-11-30  2012     11             30
11 2012-12-31  2012     12             31
12 2013-01-31  2013      1             31
13 2013-02-28  2013      2             28
14 2013-03-31  2013      3             31