How can I group by month from a Date field using Python / Pandas
I have a Data-frame df that looks like this:
| 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 specified data by month to get the output as:
| date | SUM(Revenue) |
|------|--------------|
| May | 500 |
| June | 1000 |
I tried this code but it didn't work:
df.groupby(month('date')).agg({'Revenue': 'sum'})
I only want to use Pandas or Numpy and no additional libraries
+3
Symphony
source
to share
2 answers
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
+4
shivsn
source
to share
Try a group with 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
+6
qbzenker
source
to share