8

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.

piRSquared
  • 285,575
  • 57
  • 475
  • 624
John
  • 41,131
  • 31
  • 82
  • 106
  • I've provided several detailed examples and alternative approaches in this [**Q&A**](https://stackoverflow.com/q/47152691/2336654) that you or others might find helpful. – piRSquared Nov 11 '17 at 22:24

1 Answers1

12

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()

Wes McKinney
  • 101,437
  • 32
  • 142
  • 108
  • 2
    Thanks @Wes, `cdiff['month'] = cdiff.DATE.apply(lambda x: datetime.datetime.strftime(x,'%B'))` did the trick to convert to months. – John Apr 03 '12 at 09:33