如何使用 Python/Pandas 从日期字段按月分组

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

提问by Symphony

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

我有一个数据框 df 如下：

| 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

我只想使用 Pandas 或 Numpy 而没有额外的库

回答by shivsn

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

回答by qbzenker

Try a groupby using a pandas Grouper:

使用 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

回答by yongtw123

For DataFrame with many rows, using strftimetakes up more time. If the date column already has dtype of datetime64[ns](can use pd.to_datetime()to convert, or specify parse_datesduring csv import, etc.), one can directly access datetime property for groupbylabels (Method 3). The speedup is substantial.

对于多行的DataFrame，使用strftime会占用更多时间。如果日期列已经有dtype datetime64[ns]（可用于pd.to_datetime()转换，或parse_dates在csv导入时指定等），则可以直接访问groupby标签的日期时间属性（方法3）。加速是可观的。

import numpy as np
import pandas as pd

T = pd.date_range(pd.Timestamp(0), pd.Timestamp.now()).to_frame(index=False)
T = pd.concat([T for i in range(1,10)])
T['revenue'] = pd.Series(np.random.randint(1000, size=T.shape[0]))
T.columns.values[0] = 'date'

print(T.shape) #(159336, 2)
print(T.dtypes) #date: datetime64[ns], revenue: int32

Method 1: strftime

方法一：strftime

%timeit -n 10 -r 7 T.groupby(T['date'].dt.strftime('%B'))['revenue'].sum()

1.47 s ± 10.1 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

每个循环 1.47 s ± 10.1 ms（平均值 ± 标准偏差，7 次运行，每次 10 次循环）

Method 2: Grouper

方法二：石斑鱼

%timeit -n 10 -r 7 T.groupby(pd.Grouper(key='date', freq='1M')).sum()
#NOTE Manually map months as integer {01..12} to strings

56.9 ms ± 2.88 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

每个循环 56.9 ms ± 2.88 ms（7 次运行的平均值 ± 标准偏差，每次 10 次循环）

Method 3: datetime properties

方法 3：日期时间属性

%timeit -n 10 -r 7 T.groupby(T['date'].dt.month)['revenue'].sum()
#NOTE Manually map months as integer {01..12} to strings

34 ms ± 3.34 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

每个循环 34 ms ± 3.34 ms（7 次运行的平均值 ± 标准偏差，每次 10 次循环）

回答by Jeywanth Kannan

This will work better.

这样效果会更好。

Try this:

尝试这个：

#explicitly convert to date
df['Date'] = pd.to_datetime(df['Date'])
# set your date column as index 
df.set_index('Date',inplace=True) 

# For monthly use 'M', If needed for other freq you can change.
df[revenue].resample('M').sum()

This code gives same result as @shivsn answer on first post.

此代码在第一篇文章中给出与@shivsn 答案相同的结果。

But thing is we can do lot more operations in this mentioned code. Recommended to use this:

但问题是我们可以在上面提到的代码中做更多的操作。推荐使用这个：

>>> df['Date'] = pd.to_datetime(df['Date'])
>>> df.set_index('Date',inplace=True)
>>> df['withdrawal'].resample('M').sum().sort_values()
Date
2019-10-31     28710.00
2019-04-30     31437.00
2019-07-31     39728.00
2019-11-30     40121.00
2019-05-31     46495.00
2020-02-29     57751.10
2019-12-31     72469.13
2020-01-31     76115.78
2019-06-30     76947.00
2019-09-30     79847.04
2020-03-31     97920.18
2019-08-31    205279.45
Name: withdrawal, dtype: float64

where @shivsn code's does same.

@shivsn 代码的功能相同。

>>> df.groupby(df['Date'].dt.strftime('%B'))['withdrawal'].sum().sort_values()
Date
October       28710.00
April         31437.00
July          39728.00
November      40121.00
May           46495.00
February      57751.10
December      72469.13
January       76115.78
June          76947.00
September     79847.04
March         97920.18
August       205279.45
Name: withdrawal, dtype: float64

回答by Shubham gupta

Try this:

尝试这个：

1. Chaged the date column into datetime formate.

   ---> df['Date'] = pd.to_datetime(df['Date'])

2. Insert new row in data frame which have month like->[May, 'June']

   ---> df['months'] = df['date'].apply(lambda x:x.strftime('%B'))

   ---> here x is date which take from date column in data frame.

3. Now aggregate aggregate data on month column and sum the revenue.

   --->response_data_frame = df.groupby('months')['Revenue'].sum()

   ---->print(response_data_frame)

1. 将日期列更改为日期时间格式。

   ---> df['Date'] = pd.to_datetime(df['Date'])

2. 在具有月份的数据框中插入新行 ->[May, 'June']

   ---> df['months'] = df['date'].apply(lambda x:x.strftime('%B'))

   ---> 这里 x 是取自数据框中日期列的日期。

3. 现在汇总月份列的汇总数据并汇总收入。

   --->response_data_frame = df.groupby('months')['Revenue'].sum()

   ---->print(response_data_frame)

output -:

输出 -：

| month | Revenue |

|-------|---------|

| May   | 500     |

| June  | 1000    |