Can't group values in a Pandas column by a month

user_4718221 0
Viewed 59

I would like to count the number of instances in the timelog, grouped by month. I have the following Pandas column:

print df['date_unconditional'][:5]

0    2018-10-15T07:00:00
1    2018-06-12T07:00:00
2    2018-08-28T07:00:00
3    2018-08-29T07:00:00
4    2018-10-29T07:00:00
Name: date_unconditional, dtype: object

Then I transformed it to datetime format

df['date_unconditional'] = pd.to_datetime(df['date_unconditional'].dt.strftime('%m/%d/%Y'))
print df['date_unconditional'][:5]


0   2018-10-15
1   2018-06-12
2   2018-08-28
3   2018-08-29
4   2018-10-29
Name: date_unconditional, dtype: datetime64[ns]

And then I tried counting them, but I keep getting a mistake

df['date_unconditional'] = pd.to_datetime(df['date_unconditional'], errors='coerce')
print df['date_unconditional'].groupby(pd.Grouper(freq='M')).count()

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'
pandas python
1 Answers

Use parameter key in Grouper:

df['date_unconditional'] = pd.to_datetime(df['date_unconditional'], errors='coerce')
print (df.groupby(pd.Grouper(freq='M',key='date_unconditional'))['date_unconditional'].count())
2018-06-30    1
2018-07-31    0
2018-08-31    2
2018-09-30    0
2018-10-31    2
Freq: M, Name: date_unconditional, dtype: int64

Or create DatetimeIndex by DataFrame.set_index and then is possible use GroupBy.size - difference with between is count excluded missing values, size not.

df['date_unconditional'] = pd.to_datetime(df['date_unconditional'], errors='coerce')
print (df.set_index('date_unconditional').groupby(pd.Grouper(freq='M')).size())
2018-06-30    1
2018-07-31    0
2018-08-31    2
2018-09-30    0
2018-10-31    2
Freq: M, dtype: int64
user_29010020
Related