set value in multiple rows in pandas dataframe based on condition

 temperature precipitation
0 1.26 0.0279
1 1.64 0.0330
2 1.98 0.0381
3 2.31 0.0406
4 2.61 0.0406
5 2.89 0.0381
6 3.15 0.0356
7 3.51 0.0305
8 3.78 0.0305
9 3.78 0.0305

In the dataframe above, I want to create a new column C where the value is 1 for 4 rows after precipitation is less than 0.04 iff precipitation in those 4 rows is less than 0.04. I tried using pd.where but that only sets the value for the present row.

Expected output:

1 Answer

IIUC, the following;

Create column 'C' and fill with nan's:

df['C'] = np.nan

count consecutive occurrences of 'precipitation' < 0.04 in column 'C_:

def rolling_count(val): if val < 0.04: rolling_count.count +=1 else: rolling_count.count = 0 return rolling_count.count
rolling_count.count = 0
df['C_'] = df['precipitation'].apply(rolling_count)

fill column 'C' with '1', where the first '4' is found and backward fill the other 3:

df.loc[df[df['C_'] == 4].head(1).index.item(), 'C'] = 1
df['C'] = df['C'].fillna(method = 'bfill', limit = 3)
df['C'] = df['C'].fillna(0)
df['C'] = df['C'].astype(int)
df temperature precipitation C C_
0 1.26 0.0279 0 1
1 1.64 0.0330 0 2
2 1.98 0.0381 0 3
3 2.31 0.0406 0 0
4 2.61 0.0406 0 0
5 2.89 0.0381 1 1
6 3.15 0.0356 1 2
7 3.51 0.0305 1 3
8 3.78 0.0305 1 4
9 3.78 0.0305 0 5

Note; this result differs from what your example shows, but IIUC you need to find 4 consecutive rows below 0.04 and fill 'C'. Problem is that you have a '0.0406' value filled with '1' in 'C' which is not below 0.04.