在数据帧中跨多个组应用iterrows逻辑

Question

我在整个数据集中应用某些逻辑时遇到问题。我能够将逻辑应用于一个小的“组”，但不能应用于所有的组（注意，这些组是由primaryFilter和secondaryFilter组成的。您是否介意为我指出正确的方向来进行此操作

整个数据

import pandas as pd
import numpy as np

myInput = {
    'primaryFilter': [100,100,100,100,100,100,100,100,100,100,200,200,200,200,200,200,200,200,200,200],
    'secondaryFilter': [1,1,1,1,2,2,2,3,3,3,1,1,2,2,2,2,3,3,3,3],
    'constantValuePerGroup': [15,15,15,15,20,20,20,17,17,17,10,10,30,30,30,30,22,22,22,22], 
    'someValue':[3,1,4,7,9,9,2,7,3,7,6,4,7,10,10,3,4,6,7,5]
          }
df_input = pd.DataFrame(data=myInput)
df_input

试验数据（第一组）

df_test = df_input[df_input.primaryFilter.isin([100])]
df_test = df_test[df_test.secondaryFilter == 1.0]

df_test['newColumn'] = np.nan

for index,row in df_test.iterrows():

    if index==0:
        print("start")
        df_test.loc[0, 'newColumn'] = 0

    elif index==df_test.shape[0]-1:
        df_test.loc[index, 'newColumn'] = df_test.loc[index-1, 'newColumn'] + df_test.loc[index-1, 'someValue']
        print("end")

    else:
        print("inter")
        df_test.loc[index, 'newColumn'] = df_test.loc[index-1, 'newColumn'] + df_test.loc[index-1, 'someValue']

df_test["delta"] = df_test["constantValuePerGroup"] - df_test['newColumn']
df_test.head()

这是测试的输出

我现在想将上述逻辑应用于其余的组100,2和100,3和200,1等等

Answer 1

无需使用iterrows在这里，您可以group在primaryFilter和secondaryFilter列上的数据帧，然后对于每个唯一组，将someValue列和shift列中的值的累积和向下1位置得到newColumn。最后从constantValuePerGroup中减去newColumn得到delta

df_input['newColumn'] = df_input.groupby(['primaryFilter', 'secondaryFilter'])['someValue'].apply(lambda s: s.cumsum().shift(fill_value=0))
df_input['delta'] = df_input['constantValuePerGroup'] - df_input['newColumn']

---

>>> df_input

    primaryFilter  secondaryFilter  constantValuePerGroup  someValue  newColumn  delta
0             100                1                     15          3          0     15
1             100                1                     15          1          3     12
2             100                1                     15          4          4     11
3             100                1                     15          7          8      7
4             100                2                     20          9          0     20
5             100                2                     20          9          9     11
6             100                2                     20          2         18      2
7             100                3                     17          7          0     17
8             100                3                     17          3          7     10
9             100                3                     17          7         10      7
10            200                1                     10          6          0     10
11            200                1                     10          4          6      4
12            200                2                     30          7          0     30
13            200                2                     30         10          7     23
14            200                2                     30         10         17     13
15            200                2                     30          3         27      3
16            200                3                     22          4          0     22
17            200                3                     22          6          4     18
18            200                3                     22          7         10     12
19            200                3                     22          5         17      5