Pandas take bfill limit from another column-CodePudding

amount  bfilllimit
28  6
40  0
30  0
nan 0
nan 0
nan 0
nan 0
30  3
50  0
nan 0
nan 0
nan 0
30  2
nan 0

My dataframe is like above. We can do a bfill and specify limit. Can the limit be from another column entry (in above case bfilllimit column entry ?)

Expected output:

amount  bfilllimit
28  6
40  0
30  0
nan 0
30  0
30  0
30  0
30  3
50  0
nan 0
30  0
30  0
30  2
nan 0

CodePudding user response：

Example

data = [[28.0, 6.0], [40.0, 0.0], [30.0, 0.0], [None, 0.0], [None, 0.0], [None, 0.0], [None, 0.0], 
        [30.0, 3.0], [50.0, 0.0], [None, 0.0], [None, 0.0], [None, 0.0], [30.0, 2.0], [None, 0.0]]

df = pd.DataFrame(data, columns=['amount', 'bfilllimit'])

df

  amount    bfilllimit
0   28.0    6
1   40.0    0
2   30.0    0
3   NaN     0
4   NaN     0
5   NaN     0
6   NaN     0
7   30.0    3
8   50.0    0
9   NaN     0
10  NaN     0
11  NaN     0
12  30.0    2
13  NaN     0

full code & output

grouper = df.iloc[::-1, 0].notna().cumsum()
limit = df.groupby(grouper)['bfilllimit'].transform(max)
cond1 = df[::-1].groupby(grouper).cumcount().le(limit)
df.assign(amount=df['amount'].bfill().where(cond1))

output:

    amount  bfilllimit
0   28.0    6
1   40.0    0
2   30.0    0
3   NaN     0
4   30.0    0
5   30.0    0
6   30.0    0
7   30.0    3
8   50.0    0
9   NaN     0
10  30.0    0
11  30.0    0
12  30.0    2
13  NaN     0

Process

If you are curious about intermediate process, print following code and check it.

df.assign(grouper=grouper, limit=limit, distance=df[::-1].groupby(grouper).cumcount(), cond1=cond1)

    amount  bfilllimit  grouper limit   distance    cond1
0   28.0    6           6       6       0           True
1   40.0    0           5       0       0           True
2   30.0    0           4       0       0           True
3   NaN     0           3       3       4           False
4   NaN     0           3       3       3           True
5   NaN     0           3       3       2           True
6   NaN     0           3       3       1           True
7   30.0    3           3       3       0           True
8   50.0    0           2       0       0           True
9   NaN     0           1       2       3           False
10  NaN     0           1       2       2           True
11  NaN     0           1       2       1           True
12  30.0    2           1       2       0           True
13  NaN     0           0       0       0           True

grouper : bfill group
limit : bfill limit of bfill of group
distance : distance from value of bfill

If distance is greater than limit, cond1 is False, otherwise cond1 is True.

when cond1 is True, bfill NaN