How can I Group By Month from a Date field using Python/Pandas

Question

I have a Data-frame df which is as follows:

| date      | Revenue | |-----------|---------| | 6/2/2017  | 100     | | 5/23/2017 | 200     | | 5/20/2017 | 300     | | 6/22/2017 | 400     | | 6/21/2017 | 500     | 

I need to group the above data by month to get output as:

| date | SUM(Revenue) | |------|--------------| | May  | 500          | | June | 1000         | 

I tried this code but it did not work:

df.groupby(month('date')).agg({'Revenue': 'sum'}) 

I want to only use Pandas or Numpy and no additional libraries

Answer 1

try this:

In [6]: df['date'] = pd.to_datetime(df['date'])  In [7]: df Out[7]:          date  Revenue 0 2017-06-02      100 1 2017-05-23      200 2 2017-05-20      300 3 2017-06-22      400 4 2017-06-21      500    In [59]: df.groupby(df['date'].dt.strftime('%B'))['Revenue'].sum().sort_values() Out[59]:  date May      500 June    1000 

Answer 2

Try a groupby using a pandas Grouper:

df = pd.DataFrame({'date':['6/2/2017','5/23/2017','5/20/2017','6/22/2017','6/21/2017'],'Revenue':[100,200,300,400,500]}) df.date = pd.to_datetime(df.date) dg = df.groupby(pd.Grouper(key='date', freq='1M')).sum() # groupby each 1 month dg.index = dg.index.strftime('%B')       Revenue  May    500 June    1000