Applying Pandas iterrows logic across many groups in a dataframe

user_7570259 0
Viewed 115

I am having trouble applying some logic across my entire dataset. I am able to apply the logic on a small "group" but not on all of the groups (note, the groups are made by primaryFilter and secondaryFilter. Do you all mind pointing me in the right direction to go about this?

Entire Data

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

enter image description here

Test Data (First Group)

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()

Here is the output of the test

enter image description here

I now would like to apply the above logic to the remaining groups 100,2 and 100,3 and 200,1 and so forth..

pandas-groupby pandas dataframe lambda python
1 Answers

No need to use iterrows here, you can group the dataframe on primaryFilter and secondaryFilter columns then for each unique group take the cumulative sum of values in column someValue and shift the resulting cummulative sum by 1 position downwards to obtain newColumn. Finally subtract newColumn from constantValuePerGroup to get the 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
user_128331660
Related