Pandas pivot_table per day

Question

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

I want to make a pivot_table in a dataframe, using the monthly population count for each location.

The data is as follows:

  ['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 rotate values. I need a way to convert cdiff.DATE to a month, not a date. I hope this will result in something like: The data looks like this:

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

I tried all kinds of strftime methods on cdiff.DATE without success. He wants to apply to strings, not to a 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 the name of the month, execute another function or use the built-in calendar.month_name . To get the data in the desired format, you must call reset_index as a result, or you can also do:

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