[FIXED] back fill data on the condition that there is a 1 located <=2 rows previously to your current location

Issue

df = pd.DataFrame(
{ 
    "group":          [0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 1,1, 1, 1, 1], 
    "value":          [0, 1, 1, 0, 0, 1, 0, 1, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0,0, 0, 1, 1], 
    "desiredResult":  [0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0,0, 0, 1, 1], 
 })

My goal here is to bfill() based on 2 conditions:

• bfill() only if "group" has a 1
• in "value", bfill() only if there is a 1, two rows or less from the current location

ex.

• values[2:5] ==> we want to backfill the 1's in desiredResult[2:5], since there is a 1 located two rows away from the current location aka values[5]

• values[5:7] ==> we want to backfill the 1's in desiredResult[5:7], since there is a 1 located 1 row away from the current location aka values[7]

• values[11:14] ==> we want to backfill the 1's in desiredResult[11:14], since there is a 1 located two rows away from the current location aka values[14]

• values[17:20] ==> we want to backfill the 1's in desiredResult[17:20], since there is a 1 located two rows away from the current location aka values[20]

• values [24:25] === nothing will occur here because the 1 is located about 3 rows away from values[24]

Solution

You can form groups ending in 1 in value, check the group size with groupby.transform and use this to replace the 0s by 1:

N = 2
g = df.loc[::-1, 'value'].eq(1).cumsum()
m1 = df.groupby(g).transform('size').le(N+1)
m2 = df['group'].eq(1) & df['value'].eq(0)

df.loc[m1&m2, 'value'] = 1

NB. if you want a new column replace df.loc[m1&m2, 'value'] = 1 by df['out'] = df['value'].mask(m1&m2, 1).

Output:

    group  value  desiredResult
0       0      0              0
1       1      1              1
2       1      1              1
3       1      1              1
4       1      1              1
5       1      1              1
6       1      1              1
7       1      1              1
8       0      0              0
9       0      0              0
10      0      0              0
11      1      1              1
12      1      1              1
13      1      1              1
14      1      1              1
15      0      0              0
16      0      0              0
17      1      1              1
18      1      1              1
19      1      1              1
20      1      1              1
21      1      0              0
22      1      0              0
23      1      0              0
24      1      1              1
25      1      1              1

Intermediates and output as new column:

    group  value  desiredResult   g     m1     m2  out
0       0      0              0  10   True  False    0
1       1      1              1  10   True  False    1
2       1      1              1   9   True  False    1
3       1      0              1   8   True   True    1
4       1      0              1   8   True   True    1
5       1      1              1   8   True  False    1
6       1      0              1   7   True   True    1
7       1      1              1   7   True  False    1
8       0      0              0   6  False  False    0
9       0      0              0   6  False  False    0
10      0      0              0   6  False  False    0
11      1      1              1   6  False  False    1
12      1      0              1   5   True   True    1
13      1      0              1   5   True   True    1
14      1      1              1   5   True  False    1
15      0      0              0   4   True  False    0
16      0      0              0   4   True  False    0
17      1      1              1   4   True  False    1
18      1      0              1   3   True   True    1
19      1      0              1   3   True   True    1
20      1      1              1   3   True  False    1
21      1      0              0   2  False   True    0
22      1      0              0   2  False   True    0
23      1      0              0   2  False   True    0
24      1      1              1   2  False  False    1
25      1      1              1   1   True  False    1

Answered By - mozway