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
  • 265,629
  • 48
  • 427
  • 571
John
  • 37,208
  • 27
  • 78
  • 105
  • 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
  • 93,141
  • 30
  • 140
  • 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