如何按Creative分组对数值变量执行Adstock累积变换？

我需要基于Creative字段分组，对grps列执行周度Adstock变换，要求每个分组的第一周保持原值，后续周基于同组前一周的结果计算，且不同Creative之间的计算完全独立。

现有Adstock变换函数如下：

import numpy as np
import pandas as pd

def adstock(grps, rate):
    adstock = np.zeros(len(grps))
    adstock[0] = grps[0]
    for i in range(1, len(grps)):
        adstock[i] = grps[i] + rate * adstock[i - 1]
    return adstock

示例数据：

df = pd.DataFrame(
    {
        "Creative": [
            "Phone",
            "Phone",
            "Phone",
            "Yoga",
            "Yoga",
            "Yoga",
            "Yoga",
            "Grass",
            "Grass",
            "Grass",
            "Grass",
        ],
        "airing_week": [
            "2015-12-28",
            "2016-01-04",
            "2016-01-11",
            "2018-01-01",
            "2018-01-08",
            "2018-01-15",
            "2018-01-22",
            "2022-02-28",
            "2022-03-07",
            "2022-03-14",
            "2022-03-21",
        ],
        "week_num": [1, 2, 3, 1, 2, 3, 4, 1, 2, 3, 4],
        "grps": [
            38.5,
            67.13,
            50.15,
            43.11,
            28.61,
            9.04,
            4.02,
            28.83,
            28.81,
            36.91,
            37.79,
        ],
    }
)

如果直接对整列调用函数：

df['adstock_grps']=adstock(df['grps'], .5)

会得到错误结果，因为计算跨分组传递了：

Creative airing_week  week_num   grps  adstock_grps
0      Phone  2015-12-28         1  38.50      38.500000
1      Phone  2016-01-04         2  67.13      86.380000
2      Phone  2016-01-11         3  50.15      93.340000
3       Yoga  2018-01-01         1  43.11      89.780000
4       Yoga  2018-01-08         2  28.61      73.500000
5       Yoga  2018-01-15         3   9.04      45.790000
6       Yoga  2018-01-22         4   4.02      26.915000
7      Grass  2022-02-28         1  28.83      42.287500
8      Grass  2022-03-07         2  28.81      49.953750
9      Grass  2022-03-14         3  36.91      61.886875
10     Grass  2022-03-21         4  37.79      68.733437

解决方案 #

使用pandas的groupby+apply，让每个Creative分组单独执行Adstock计算：

df['adstock_grps'] = df.groupby('Creative')['grps'].apply(lambda x: adstock(x.values, 0.5))

执行后得到正确结果：

Creative airing_week  week_num   grps  adstock_grps
0      Phone  2015-12-28         1  38.50      38.500000
1      Phone  2016-01-04         2  67.13      86.380000
2      Phone  2016-01-11         3  50.15      93.340000
3       Yoga  2018-01-01         1  43.11      43.110000
4       Yoga  2018-01-08         2  28.61      50.165000
5       Yoga  2018-01-15         3   9.04      34.122500
6       Yoga  2018-01-22         4   4.02      21.081250
7      Grass  2022-02-28         1  28.83      28.830000
8      Grass  2022-03-07         2  28.81      43.225000
9      Grass  2022-03-14         3  36.91      58.522500
10     Grass  2022-03-21         4  37.79      67.051250

原理说明：通过groupby('Creative')将数据按创意分组，每个分组内的grps列单独传入adstock函数计算，确保不同分组之间的计算完全独立，每个分组的第一周保持自身grps值，后续周基于同组前一周的Adstock结果迭代计算。

内容的提问来源于stack exchange，提问作者GioC