移动平均的窗口函数

Question

我正试图在 pandas 中复制 SQL 的窗口函数。

SELECT avg(totalprice) OVER (
    PARTITION BY custkey
    ORDER BY orderdate
    RANGE BETWEEN interval '1' month PRECEDING AND CURRENT ROW)
FROM orders

我有这个数据框：

from io  import StringIO
import pandas as pd

myst="""cust_1,2020-10-10,100
cust_2,2020-10-10,15
cust_1,2020-10-15,200
cust_1,2020-10-16,240
cust_2,2020-12-20,25
cust_1,2020-12-25,140
cust_2,2021-01-01,5

"""
u_cols=['customer_id', 'date', 'price']

myf = StringIO(myst)
import pandas as pd
df = pd.read_csv(StringIO(myst), sep=',', names = u_cols)
df=df.sort_values(list(df.columns))

在计算仅限于最近1个月的移动平均值之后，它将如下所示...

from io  import StringIO
import pandas as pd

myst="""cust_1,2020-10-10,100,100
cust_2,2020-10-10,15,15
cust_1,2020-10-15,200,150
cust_1,2020-10-16,240,180
cust_2,2020-12-20,25,25
cust_1,2020-12-25,140,140
cust_2,2021-01-01,5,15

"""
u_cols=['customer_id', 'date', 'price', 'my_average']

myf = StringIO(myst)
import pandas as pd
my_df = pd.read_csv(StringIO(myst), sep=',', names = u_cols)
my_df=my_df.sort_values(list(my_df.columns))

如图所示：

https://trino.io/assets/blog/window-features/running-average-range.svg

我尝试编写了以下函数...

import numpy as np
def mylogic(myro):
    mylist = list()
    mydate = myro['date'][0]
    for i in range(len(myro)):            
        if myro['date'][i] > mydate:
            mylist.append(myro['price'][i])
            mydate = myro['date'][i]
    return np.mean(mylist)

但是它返回了一个关键错误（key_error）。

- shantanuo

---

你可能需要更新你分享的数据；它与分享的图片不同，会导致不同的结果。 - sammywemmy

另外，请查看这是否足够：df['total_price'] = df.groupby(['customer_id', pd.Grouper(key='date', freq='1M')]).expanding(1).price.mean().array - sammywemmy

不，结果不匹配。 - shantanuo

正如我之前提到的，所分享的数据与图像不匹配。而且如果您在 SQL 中运行代码，输出结果也会有所不同。 - sammywemmy

mozway的答案是正确的。在你的情况下，cust_2 2021-01-01的5条记录没有改变。（应该为15） - shantanuo

Answer 1

您可以在最近30天内使用滚动功能。

df['date'] = pd.to_datetime(df['date'])    

df['my_average'] = (df.groupby('customer_id')
                      .apply(lambda d: d.rolling('30D', on='date')['price'].mean())
                      .reset_index(level=0, drop=True)
                      .astype(int)
                   )

输出：

  customer_id       date  price  my_average
0      cust_1 2020-10-10    100         100
2      cust_1 2020-10-15    200         150
3      cust_1 2020-10-16    240         180
5      cust_1 2020-12-25    140         140
1      cust_2 2020-10-10     15          15
4      cust_2 2020-12-20     25          25
6      cust_2 2021-01-01      5          15

- mozway

---

不对。只有日期差小于30天时才需要计算平均值。 - shantanuo

抱歉，我漏掉了这个点。现在应该可以正常工作了。 - mozway