如何使用Python/Pandas从日期字段按月，日分组 [英] How can I Group By Month, Day from a Date field using Python/Pandas

问题描述

我有一个数据帧df，如下所示:

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

| date      | Revenue | Cost |
|-----------|---------|------|
| 6/1/2017  | 100     | 20   |
| 5/21/2017 | 200     | 40   |
| 5/21/2017 | 300     | 60   |
| 6/20/2017 | 400     | 80   |
| 6/1/2017  | 500     | 100  |

我需要将以上数据按月分组，然后按天分组，以得到如下输出:

I need to group the above data by Month and then by Day to get output as:

| Month | Day | SUM(Revenue) | SUM(Cost) |
|-------|-----|--------------|-----------|
| May   | 21  | 500          | 100       |
| June  | 1   | 600          | 120       |
| June  | 20  | 400          | 80        |

我尝试了这段代码，但是没有用:

I tried this code but it did not work:

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

我只想使用Pandas或Numpy，而没有其他库

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

推荐答案

让我们将set_index和sum与参数level一起使用:

Let's use set_index and sum with argument level:

df['date'] = pd.to_datetime(df['date'])
df['Month'] = df['date'].dt.strftime('%b')
df['Day'] = df['date'].dt.day   
df.set_index(['Month','Day']).sum(level=[0,1]).reset_index()

输出:

  Month  Day  Revenue  Cost
0   Jun    1      600   120
1   Jun   20      400    80
2   May   21      500   100

这篇关于如何使用Python/Pandas从日期字段按月，日分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！