CODEWITHSUNDEEP
pythonpandasnumpypandas-groupbyseries
gibbz00
gibbz00

Reputation: 1987

Fetch first non-zero value in previous rows in pandas

Following is what my dataframe looks like. Expected_Output is my desired column:

   Group  Signal  Ready  Value  Expected_Output
0      1       0      0      3              NaN
1      1       0      1     72              NaN
2      1       0      0      0              NaN
3      1       4      0      0             72.0
4      1       4      0      0             72.0
5      1       4      0      0             72.0
6      2       0      0      0              NaN
7      2       7      0      0              NaN
8      2       7      0      0              NaN
9      2       7      0      0              NaN

If Signal > 1, then I am trying to fetch the most recent non-zero Value in the previous rows within the Group where Ready = 1. So in row 3, Signal = 4, so I want to fetch the most recent non-zero Value of 72 from row 1 where Ready = 1.

Once I can fetch the value, I can do df.groupby(['Group','Signal']).Value.transform('first') as Signals appear repeatedly like 444 but not sure how to fetch Value.

Upvotes: 1

Views: 71

Answers (2)

BENY
BENY

Reputation: 323316

IIUC groupby + ffill with Boolean assign

df['Help']=df.Value.where(df.Ready==1).replace(0,np.nan)

df['New']=df.groupby('Group').Help.ffill()[df.Signal>1]
df
Out[1006]: 
   Group  Signal  Ready  Value  Expected_Output  Help   New
0      1       0      0      3              NaN   3.0   NaN
1      1       0      1     72              NaN  72.0   NaN
2      1       0      0      0              NaN   NaN   NaN
3      1       4      0      0             72.0   NaN  72.0
4      1       4      0      0             72.0   NaN  72.0
5      1       4      0      0             72.0   NaN  72.0
6      2       0      0      0              NaN   NaN   NaN
7      2       7      0      0              NaN   NaN   NaN
8      2       7      0      0              NaN   NaN   NaN
9      2       7      0      0              NaN   NaN   NaN

Upvotes: 2

jpp
jpp

Reputation: 164773

Create a series via GroupBy + ffill, then mask the resultant series:

s = df.assign(Value_mask=df['Value'].where(df['Ready'].eq(1)))\
      .groupby('Group')['Value_mask'].ffill()

df['Value'] = s.where(df['Signal'].gt(1))

   Group  Signal  Ready  Value
0      1       0      0    NaN
1      1       0      1    NaN
2      1       0      0    NaN
3      1       4      0   72.0
4      1       4      0   72.0
5      1       4      0   72.0
6      2       0      0    NaN
7      2       7      0    NaN
8      2       7      0    NaN
9      2       7      0    NaN

Upvotes: 2

Related Questions