CODEWITHSUNDEEP
pythonpandas
ProcolHarum
ProcolHarum

Reputation: 741

how to fill a column based on a condition

I have the following df:

df = pd.DataFrame({"Value":[1,4,2,1,2,1,2,2],'type':['S','C','L','C','C','S','L','S'],'exit_value':[8,'','','','',2,'','1']})

    Value   type    exit_value
0   1          S    8
1   4          C    
2   2          L    
3   1          C    
4   2          C    
5   1          S    2
6   2          L    
7   2          S    1

I wish to have the following results:

    Value   type    exit_value
0   1          S    8
1   4          C    8
2   2          L    8
3   1          C    
4   2          C    
5   1          S    2
6   2          L    2
7   2          S    1

The logic: in each type S I have an exit_value and that value should be fill all the way to the first L type.

What have I tried? I can get the indexes of each type S and the indexes of each type L and iterate from S to L and provide the value I had in index S but I sense that this solution is overdoing.

Upvotes: 1

Views: 117

Answers (1)

Henry Yik
Henry Yik

Reputation: 22503

IIUC first select the rows with S and L, groupby and transform on first, assign to column and finally compare with where:

s = df[df["type"].isin(["S", "L"])]

df["exit_value"] = s.groupby(s["type"].eq("S").cumsum())["exit_value"].transform("first")

print (df.ffill().where(df.ffill()==df.bfill()))

   Value type exit_value
0      1    S          8
1      4    C          8
2      2    L          8
3      1    C        NaN
4      2    C        NaN
5      1    S          2
6      2    L          2
7      2    S          1

Upvotes: 4

Related Questions