多条件群+排序+和在熊猫数据行中的应用

Question

我有一个dataframe，它有以下列：

会计学，通信日期，开放日期

对于每个开户账户，我都要回顾一下在账户开户后30天内发生的所有信件，然后按以下方式对这些信函进行评分：

Forty-twenty-forty: Attribute 40% (0.4 points) of the attribution to the first touch,
40% to the last touch, and divide the remaining 20% between all touches in between

因此，我知道申请和分组的功能，但这超出了我的工资等级。我必须按帐户分组，根据2列对彼此比较的条件，我必须这样做才能得到通信的总数，我想它们也必须排序，因为为通信分配点数的下面一步取决于它们发生的顺序。

我想高效地完成这个任务，因为我有很多行，我知道applying ()可以快速运行，但是当我想要做的行级操作变得有点复杂时，我很难应用它。

我很感激任何帮助，因为我不擅长熊猫。

根据请求编辑

Acct, ContactDate, OpenDate, Points (what I need to calculate)
123, 1/1/2018, 1/1/2021, 0 (because correspondance not within 30 days of open)
123, 12/10/2020, 1/1/2021, 0.4 (first touch gets 0.4)
123, 12/11/2020, 1/1/2021, 0.2 (other 'touches' get 0.2/(num of touches-2) 'points')
123, 12/12/2020, 1/1/2021, 0.4 (last touch gets 0.4)
456, 1/1/2018, 1/1/2021, 0 (again, because correspondance not within 30 days of open)
456, 12/10/2020, 1/1/2021, 0.4 (first touch gets 0.4)
456, 12/11/2020, 1/1/2021, 0.1 (other 'touches' get 0.2/(num of touches-2) 'points')
456, 12/11/2020, 1/1/2021, 0.1 (other 'touches' get 0.2/(num of touches-2) 'points')
456, 12/12/2020, 1/1/2021, 0.4 (last touch gets 0.4)

Answer 1

这将返回一个减少的数据，因为它不包括超过30天的时间框架，然后将原始df合并到其中，获取一个df中的所有数据。这假设您的日期排序是正确的，否则，在应用下面的函数之前，您可能必须先这样做。

df['Points'] = 0 #add column to dataframe before analysis

#df.columns
#Index(['Acct', 'ContactDate', 'OpenDate', 'Points'], dtype='object')

def points(x):
    newx = x.loc[(x['OpenDate'] - x['ContactDate']) <= timedelta(days=30)] # reduce for wide > 30 days
    # print(newx.Acct)
    if newx.Acct.count() > 2: # check more than two dates exist
        newx['Points'].iloc[0] = .4 # first row
        newx['Points'].iloc[-1] = .4 # last row
        newx['Points'].iloc[1:-1] = .2 / newx['Points'].iloc[1:-1].count() # middle rows / by count of those rows
        return newx
    elif newx.Acct.count() == 2: # placeholder for later
        #edge case logic here for two occurences
        return newx
    elif newx.Acct.count() == 1: # placeholder for later
        #edge case logic here one onccurence
        return newx

# groupby Acct then clean up the indices so it can be merged back into original df
dft = df.groupby('Acct', as_index=False).apply(points).reset_index().set_index('level_1').drop('level_0', axis=1)

# merge on index
df_points = df[['Acct', 'ContactDate', 'OpenDate']].merge(dft['Points'], how='left', left_index=True, right_index=True).fillna(0)

输出：

   Acct ContactDate   OpenDate  Points
0   123  2018-01-01 2021-01-01     0.0
1   123  2020-12-10 2021-01-01     0.4
2   123  2020-12-11 2021-01-01     0.2
3   123  2020-12-12 2021-01-01     0.4
4   456  2018-01-01 2021-01-01     0.0
5   456  2020-12-10 2021-01-01     0.4
6   456  2020-12-11 2021-01-01     0.1
7   456  2020-12-11 2021-01-01     0.1
8   456  2020-12-12 2021-01-01     0.4