Create monthly pandas DatetimeIndex

Question

I have a dataframe with 12 values that I want to convert to DatetimeIndex type

months = df['date'] #e.g. '2016-04-01'
idx = pd.date_range(months[0], months[11], freq='M')

However, the resulting DatetimeIndex length is only 11, which should be 12 Not sure if is it because of the freq, which shifts date to the month end?

Can you add your all column `date` and desired output to your question? Are values sorted in column `date`? — jezrael, Apr 13 '16 at 07:51

score 2 · Accepted Answer · answered Apr 13 '16 at 08:14

For me work generating with periods:

print df
         date
0  2016-01-01
1  2016-02-01
2  2016-03-01
3  2016-04-02

print pd.date_range(df['date'].iloc[0], periods=12, freq='M')
DatetimeIndex(['2016-01-31', '2016-02-29', '2016-03-31', '2016-04-30',
               '2016-05-31', '2016-06-30', '2016-07-31', '2016-08-31',
               '2016-09-30', '2016-10-31', '2016-11-30', '2016-12-31'],
              dtype='datetime64[ns]', freq='M')

Why last value is missing - check in the end:

months = df['date'] 
print pd.date_range(months.iloc[0], months.iloc[3], freq='M')
DatetimeIndex(['2016-01-31', '2016-02-29', '2016-03-31'], dtype='datetime64[ns]', freq='M')

The start and end dates are strictly inclusive. So it will not generate any dates outside of those dates if specified.

Create monthly pandas DatetimeIndex

1 Answers1