如何有条件地在熊猫数据上求和

Question

我正在寻找一种有效的方法(不需要循环)来向dataframe添加一个列，其中包含一个在同一数据帧的列上的和，该列被行中的一些值过滤。示例：

Dataframe：

ClientID   Date           Orders
123        2020-03-01     23
123        2020-03-05     10
123        2020-03-10     7
456        2020-02-22     3
456        2020-02-25     15
456        2020-02-28     5
...

我想添加一个colum "orders_last_week“，其中包含该特定客户在指定日期前7天内的订单总数。Excel等效的内容如下：

SUMIFS([orders],[ClientID],ClientID,[Date]>=Date-7,[Date]<Date)

所以这就是结果：

ClientID   Date           Orders  Orders_Last_Week
123        2020-03-01     23      0
123        2020-03-05     10      23
123        2020-03-10     7       10
456        2020-02-22     3       0
456        2020-02-25     15      3
456        2020-02-28     5       18
...

我可以用循环来解决这个问题，但是由于我的数据文件包含超过20m的记录，这不是一个可行的解决方案。有人能帮帮我吗？非常感谢！

Answer 1

我假设您的dataframe名为df。我还将假设，对于给定的ClientID，日期不会重复，并且按升序排列(如果不是这样的话，按组和进行分组并对结果进行排序)。

对于给定的ClientID和日期，我的解决方案的要点是。

1. 使用groupby.transform将此问题分解为ClientID。
2. 使用rolling检查下7行的日期是否在1周的时间范围内。
3. 在这7行中，timespan中的日期被标记为True (=1)。未标记为False的日期(=0)。
4. 在这7行中，将Orders列乘以日期的真/假标签。
5. 结果之和。

实际上，我们使用8行，因为，例如，SuMoTuWeThFrSaSu有8天。

使这一点变得困难的是，滚动一次聚合一个列，因此显然不允许您在聚合时使用多个列。如果是这样的话，您可以使用date列创建一个筛选器，并使用它对订单进行求和。

但是有一个漏洞:如果你愿意通过索引走私多列，你可以使用多列！

我使用一些辅助函数。注a被理解为熊猫系列，8行，值为"Orders"，索引中有“日期”。

很想知道真实数据上的性能是怎样的。

import pandas as pd

data =  {
    'ClientID': {0: 123, 1: 123, 2: 123, 3: 456, 4: 456, 5: 456},
    'Date': {0: '2020-03-01', 1: '2020-03-05', 2: '2020-03-10',
             3: '2020-02-22', 4: '2020-02-25', 5: '2020-02-28'},
 'Orders': {0: 23, 1: 10, 2: 7, 3: 3, 4: 15, 5: 5}
}

df = pd.DataFrame(data)

# Make sure the dates are datetimes
df['Date'] = pd.to_datetime(df['Date'])

# Put into index so we can smuggle them through "rolling"
df = df.set_index(['ClientID', 'Date'])


def date(a):
    # get the "Date" index-column from the dataframe 
    return a.index.get_level_values('Date')

def previous_week(a):
    # get a column of 0s and 1s identifying the previous week, 
    # (compared to the date in the last row in a).
    return (date(a) >= date(a)[-1] - pd.DateOffset(days=7)) * (date(a) < date(a)[-1]) 

def previous_week_order_total(a):
    #compute the order total for the previous week
    return sum(previous_week(a) * a)

def total_last_week(group):
    # for a "ClientID" compute all the "previous week order totals"
    return group.rolling(8, min_periods=1).apply(previous_week_order_total, raw=False)

# Ok, actually compute this
df['Orders_Last_Week'] = df.groupby(['ClientID']).transform(total_last_week)

# Reset the index back so you can have the ClientID and Date columns back
df = df.reset_index()

以上代码所依赖的事实是，过去一周最多包含7行数据，即一周中的7天(尽管在您的示例中，它实际上小于7)。

如果你的时间窗口不是一个星期，你就需要用你的时间戳中最好的部分来替换所有对一周长度的引用。

例如，如果您的日期时间戳间隔不超过1秒，并且您感兴趣的时间窗口为1分钟(例如"Orders_last_minute")，请将pd.DateOffset(days=7)替换为pd.DateOffset(seconds=60)，group.rolling(8,...替换为group.rolling(61,....)。

显然，这段代码有点悲观:对于每一行，它总是查看61行(在本例中)。不幸的是，rolling没有提供合适的可变窗口大小函数。我怀疑，在某些情况下，利用数据按日期排序的python循环可能比这个部分矢量化的解决方案运行得更快。