Pandas rolling apply skip certain values

Question

I have a dataframe and I want to calculate the mean column up til the value points I have for True valid cases.

ids              valid           value      mean (target output)
 1               False            0.1         0
 1               True             0.2        0.2
 1               True             0.4        0.3
 2               True             0.1        0.1
 2               False            0.5        0.1
 2               True             0.3        0.2
 3               True             0.1        0.1
 3               True             0.1        0.1
 3               False            0.5        0.1
 3               False            0.9        0.1

How do I exclude the False cases from the mean calculation but still carries on the previous mean. I tried this but it doesn't skip the values from the False cases. I also tried df[~df.valid] before groupby but index doesn't match the original df.

df['mean'] = df.groupby('ids').value.rolling(len(df), min_periods=1).apply(lambda x: np.mean(x)).values

score 2 · Accepted Answer · answered Nov 10 '18 at 00:52

You can do this by writing a customised rolling mean with groupby.apply

df['mean'] = (
    df
    .groupby('ids')
    .apply(
        lambda df_: (df_['valid'] * df_['value']).cumsum() / (df_['valid']).cumsum()
    )
    .fillna(0)  # No valid rows seen -> 0
    .values     # get rid of the index
)
print(df)

   ids  valid  value  mean (target output)  mean
0    1  False    0.1                   0.0   0.0
1    1   True    0.2                   0.2   0.2
2    1   True    0.4                   0.3   0.3
3    2   True    0.1                   0.1   0.1
4    2  False    0.5                   0.1   0.1
5    2   True    0.3                   0.2   0.2
6    3   True    0.1                   0.1   0.1
7    3   True    0.1                   0.1   0.1
8    3  False    0.5                   0.1   0.1
9    3  False    0.9                   0.1   0.1

Since a rolling mean is just the sum divided by the number of observations, we can create rolling versions of both with the cumsum while suppressing invalid rows by setting both observation number and value to zero.

Pandas rolling apply skip certain values

1 Answers1