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.
