主页 / coding / 问题 / 79027616
Accepted
Ishigami
Asked: 2024-09-26 21:41:17 +0800 CST

Pandas groupby 变换平均值，日期早于当前行，适用于巨大的数据框

  • 772

我有一个 Pandas 数据框，看起来像

df = pd.DataFrame([['John', '1/1/2017','10'],
                   ['John', '2/2/2017','15'],
                   ['John', '2/2/2017','20'],
                   ['John', '3/3/2017','30'],
                   ['Sue', '1/1/2017','10'],
                   ['Sue', '2/2/2017','15'],
                   ['Sue', '3/2/2017','20'],
                   ['Sue', '3/3/2017','7'],
                   ['Sue', '4/4/2017','20']],
                   columns=['Customer', 'Deposit_Date','DPD'])

我想创建一个名为 的新行PreviousMean。此列是该客户今年迄今为止的 DPD 平均值。即包括所有 DPD，但不包括与当前存款日期匹配的行。如果不存在以前的记录，则为空或 0。

因此期望的结果如下

  Customer Deposit_Date  DPD  PreviousMean
0     John   2017-01-01   10           NaN
1     John   2017-02-02   15          10.0
2     John   2017-02-02   20          10.0
3     John   2017-03-03   30          15.0
4      Sue   2017-01-01   10           NaN
5      Sue   2017-02-02   15          10.0
6      Sue   2017-03-02   20          12.5
7      Sue   2017-03-03    7          15.0
8      Sue   2017-04-04   20          13.0

在网站和互联网上进行一些研究后，找到了一个解决方案：

df['PreviousMean'] = df.apply(
    lambda x: df[(df.Customer == x.Customer) & (df.Deposit_Date < x.Deposit_Date)].DPD.mean(), 
axis=1)

并且运行良好。但是，我的实际数据帧要大得多（约 100 万行），上面的代码运行非常慢。有没有更好的方法？谢谢

python

3 个回答

  1. 尝试使用expanding！这里是示例：

    df['Deposit_Date']= pd.to_datetime(df['Deposit_Date'])
df =df.sort_values(by=['Customer','Deposit_Date'])
df['DPD']= pd.to_numeric(df['DPD'])
df['PreviousMean'] = (
    df.groupby('Customer')['DPD']
    .expanding() #!!!
    .apply(lambda x: x[:-1].mean() if len(x) > 1 else float('nan'))
    .reset_index(level=0, drop=True)
)
    • 1
  2. Best Answer

    您可以在输出日期时使用自定义的groupby.applywithexpanding.mean和 a ：maskduplicatedffill

    df['Deposit_Date'] = pd.to_datetime(df['Deposit_Date'])
df['PreviousMean'] = (df.groupby('Customer')
                        .apply(lambda s: s['DPD'].expanding().mean().shift()
                                                 .mask(lambda x: s['Deposit_Date'].duplicated())
                                                 .ffill(),
                               include_groups=False)
                        .droplevel(0)
                     )

    注意：这是假设日期已排序。

    输出：

      Customer Deposit_Date DPD  PreviousMean
0     John   2017-01-01  10           NaN
1     John   2017-02-02  15          10.0
2     John   2017-02-02  20          10.0
3     John   2017-03-03  30          15.0
4      Sue   2017-01-01  10           NaN
5      Sue   2017-02-02  15          10.0
6      Sue   2017-03-02  20          12.5
7      Sue   2017-03-03   7          15.0
8      Sue   2017-04-04  20          13.0

    中间体：

      Customer Deposit_Date DPD  expanding.mean  shift  duplicated  mask  PreviousMean
0     John   2017-01-01  10           10.00    NaN       False   NaN           NaN
1     John   2017-02-02  15           12.50   10.0       False  10.0          10.0
2     John   2017-02-02  20           15.00   12.5        True   NaN          10.0
3     John   2017-03-03  30           18.75   15.0       False  15.0          15.0
4      Sue   2017-01-01  10           10.00    NaN       False   NaN           NaN
5      Sue   2017-02-02  15           12.50   10.0       False  10.0          10.0
6      Sue   2017-03-02  20           15.00   12.5       False  12.5          12.5
7      Sue   2017-03-03   7           13.00   15.0       False  15.0          15.0
8      Sue   2017-04-04  20           14.40   13.0       False  13.0          13.0
    • 1

  3. 下面是更加优化后的版本代码，减少了组内排序等冗余操作，简化了扩展计算：

    import pandas as pd
import numpy as np

# Create the DataFrame
df = pd.DataFrame([['John', '1/1/2017','10'],
                   ['John', '2/2/2017','15'],
                   ['John', '2/2/2017','20'],
                   ['John', '3/3/2017','30'],
                   ['Sue', '1/1/2017','10'],
                   ['Sue', '2/2/2017','15'],
                   ['Sue', '3/2/2017','20'],
                   ['Sue', '3/3/2017','7'],
                   ['Sue', '4/4/2017','20']],
                   columns=['Customer', 'Deposit_Date','DPD'])

# Convert 'Deposit_Date' to datetime and 'DPD' to numeric
df['Deposit_Date'] = pd.to_datetime(df['Deposit_Date'])
df['DPD'] = pd.to_numeric(df['DPD'])

# Sort by 'Customer' and 'Deposit_Date' once, outside the groupby
df = df.sort_values(['Customer', 'Deposit_Date']).reset_index(drop=True)

# Group the DataFrame by 'Customer'
df['PreviousMean'] = df.groupby('Customer')['DPD'].expanding().apply(
    lambda x: np.nan if len(x) == 1 else x[:-1].mean()
).reset_index(level=0, drop=True)

print(df)
    • 0

相关问题