我有一个包含以下前三列的数据集.
包括购物篮ID(唯一标识符),销售金额(美元)和交易日期.我想为数据集的每一行计算以下列,我想在Python中使用它.
以前出售同一个篮子(如果有的话);目前购物篮的销售数量;当前购物篮的平均日期(如果有);当前购物篮的最大日期(如果有)
Basket Sale Date PrevSale SaleCount MeanToDate MaxToDate
88 $15 3/01/2012 1
88 $30 11/02/2012 $15 2 $23 $30
88 $16 16/08/2012 $30 3 $20 $30
123 $90 18/06/2012 1
477 $77 19/08/2012 1
477 $57 11/12/2012 $77 2 $67 $77
566 $90 6/07/2012 1
我是Python的新手,我很难找到任何可以用花哨方式做的事情.我已经按照BasketID和Date对数据进行了分类(如上所述),因此我可以通过向每个单一篮子向前移动一次来批量进行先前的销售.不知道如何以有效的方式获得MeanToDate和MaxToDate除了循环…任何想法？
最佳答案
这应该做的伎俩：
from pandas import concat
from pandas.stats.moments import expanding_mean,expanding_count
def handler(grouped):
se = grouped.set_index('Date')['Sale'].sort_index()
# se is the (ordered) time series of sales restricted to a single basket
# we can now create a dataframe by combining different metrics
# pandas has a function for each of the ones you are interested in!
return concat(
{
'MeanToDate': expanding_mean(se),# cumulative mean
'MaxToDate': se.cummax(),# cumulative max
'SaleCount': expanding_count(se),# cumulative count
'Sale': se,# simple copy
'PrevSale': se.shift(1) # prevIoUs sale
},axis=1
)
# we then apply this handler to all the groups and pandas combines them
# back into a single dataframe indexed by (Basket,Date)
# we simply need to reset the index to get the shape you mention in your question
new_df = df.groupby('Basket').apply(handler).reset_index()
您可以阅读有关分组/聚合here的更多信息.