pandas 计算每个日期过去 7 天的值 [英] pandas count values for last 7 days from each date
本文介绍了 pandas 计算每个日期过去 7 天的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有两个数据框.首先是这样的:
打印df1id 日期 月份 is_buy0 17 2015-01-16 2015-01 11 17 2015-01-26 2015-01 12 17 2015-01-27 2015-01 13 17 2015-02-11 2015-02 14 17 2015-03-14 2015-03 15 18 2015-01-28 2015-01 16 18 2015-02-12 2015-02 17 18 2015-02-25 2015-02 18 18 2015-03-04 2015-03 1
在第二个数据框中,有一些从第一个数据框中按月汇总的数据:
df2 = df1[df1['is_buy'] == 1].groupby(['id', 'month']).agg({'is_buy': np.sum})打印df2id月购买0 17 2015-01 31 17 2015-02 12 17 2015-03 13 18 2015-01 14 18 2015-02 25 18 2015-03 1
我正在尝试获取名为last_week_buys"的新 df2 列,其中包含从每个 df1['month'] 的第一天起的最后 7 天的聚合购买.换句话说,我想得到这个:
id 月份购买 last_week_buys0 17 2015 年 1 月 3 日1 17 2015-02 1 22 17 2015-03 1 03 18 2015-01 1 NaN4 18 2015-02 2 15 18 2015-03 1 1
有什么想法可以得到这个专栏吗?
解决方案
这可以通过一些日期操作魔法和 group-bys 来完成:
# datetimeindex 方便操作date = pd.DatetimeIndex(df1['date'])# 计算 df2:按月总计df1['月'] = date.to_period('M')df2 = df1[df1['is_buy'] == 1].groupby(['id', 'month']).sum()# 计算 df3:过去 7 天的总数从日期时间导入时间增量is_last_seven = date.to_period('M') != (date + timedelta(days=7)).to_period('M')df3 = df1[(df1['is_buy'] == 1) &is_last_seven].groupby(['id', df1.month + 1]).sum()# 加入结果结果 = df2.join(df3, rsuffix='_last_seven')
结果如下:
<预><代码>>>>打印(结果)is_buy is_buy_last_seven编号月份17 2015-01 3 NaN2015-02 1 22015 年 3 月 1 日18 2015-01 1 NaN2015-02 2 12015-03 1 1然后您可以根据需要填充 NaN
值.
There are two Dataframes. First is like this:
print df1
id date month is_buy
0 17 2015-01-16 2015-01 1
1 17 2015-01-26 2015-01 1
2 17 2015-01-27 2015-01 1
3 17 2015-02-11 2015-02 1
4 17 2015-03-14 2015-03 1
5 18 2015-01-28 2015-01 1
6 18 2015-02-12 2015-02 1
7 18 2015-02-25 2015-02 1
8 18 2015-03-04 2015-03 1
In second data frame there are some aggregated data by month from the first one:
df2 = df1[df1['is_buy'] == 1].groupby(['id', 'month']).agg({'is_buy': np.sum})
print df2
id month buys
0 17 2015-01 3
1 17 2015-02 1
2 17 2015-03 1
3 18 2015-01 1
4 18 2015-02 2
5 18 2015-03 1
I'm trying to get new df2 column named 'last_week_buys' with aggregated buys by last 7 days from first day of each df1['month']. In other words, I want to get this:
id month buys last_week_buys
0 17 2015-01 3 NaN
1 17 2015-02 1 2
2 17 2015-03 1 0
3 18 2015-01 1 NaN
4 18 2015-02 2 1
5 18 2015-03 1 1
Are there any ideas to get this column?
解决方案
This can be done with a bit of date manipulation magic and group-bys:
# datetimeindex makes convenient manipulations
date = pd.DatetimeIndex(df1['date'])
# compute df2: totals by month
df1['month'] = date.to_period('M')
df2 = df1[df1['is_buy'] == 1].groupby(['id', 'month']).sum()
# compute df3: totals by last seven days
from datetime import timedelta
is_last_seven = date.to_period('M') != (date + timedelta(days=7)).to_period('M')
df3 = df1[(df1['is_buy'] == 1) & is_last_seven].groupby(['id', df1.month + 1]).sum()
# join the results
result = df2.join(df3, rsuffix='_last_seven')
Here is the result:
>>> print(result)
is_buy is_buy_last_seven
id month
17 2015-01 3 NaN
2015-02 1 2
2015-03 1 NaN
18 2015-01 1 NaN
2015-02 2 1
2015-03 1 1
You can then fill the NaN
values as you desire.
这篇关于 pandas 计算每个日期过去 7 天的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文