Pandas pivot_table on date

Question

I have a pandas DataFrame with a date column. It is not an index.

I want to make a pivot_table on the dataframe using counting aggregate per month for each location.

The data look like this:

['INDEX']                 DATE LOCATION  COUNT
0          2009-01-02 00:00:00      AAH      1
1          2009-01-03 00:00:00      ABH      1
2          2009-01-03 00:00:00      AAH      1
3          2009-01-03 00:00:00      ABH      1
4          2009-01-04 00:00:00      ACH      1

I used:

pivot_table(cdiff, values='COUNT', rows=['DATE','LOCATION'], aggfunc=np.sum)

to pivot the values. I need a way to convert cdiff.DATE to a month rather than a date. I hope to end up with something like: The data look like this:

  
  MONTH LOCATION  COUNT
January      AAH      2
January      ABH      2
January      ACH      1

I tried all manner of strftime methods on cdiff.DATE with no success. It wants to apply the to strings, not series object.

Answer 1

I would suggest:

months = cdiff.DATE.map(lambda x: x.month)
pivot_table(cdiff, values='COUNT', rows=[months, 'LOCATION'],
            aggfunc=np.sum)

To get a month name, pass a different function or use the built-in calendar.month_name. To get the data in the format you want, you should call reset_index on the result, or you could also do:

cdiff.groupby([months, 'LOCATION'], as_index=False).sum()