Forward fill missing values by group after condition is met in pandas

user_9462829 0
Viewed 1175

I'm having a bit of trouble with this. My dataframe looks like this:

id    amount    dummy
1      130        0
1      120        0
1      110        1
1      nan       nan 
1      nan       nan   
2      nan        0
2      50         0
2      20         1
2      nan       nan 
2      nan       nan

So, what I need to do is, after the dummy gets value = 1, I need to fill the amount variable with zeroes for each id, like this: 

id    amount    dummy
1      130        0
1      120        0
1      110        1
1       0        nan 
1       0        nan   
2      nan        0
2      50         0
2      20         1
2       0        nan 
2       0        nan

I'm guessing I'll need some combination of groupby('id'), fillna(method='ffill'), maybe a .loc or a shift() , but everything I tried has had some problem or is very slow. Any suggestions?

pandas nan group-by python
4 Answers

The way I will use 

s = df.groupby('id')['dummy'].ffill().eq(1)
df.loc[s&df.dummy.isna(),'amount']=0
user_79645270

You can do this much easier:

data[data['dummy'].isna()]['amount'] = 0

This will select all the rows where dummy is nan and fill the amount column with 0.

user_119789730

IIUC, ffill() and mask the still-nan:

s = df.groupby('id')['amount'].ffill().notnull()
df.loc[df['amount'].isna() & s, 'amount'] = 0

Output:

   id  amount  dummy
0   1   130.0    0.0
1   1   120.0    0.0
2   1   110.0    1.0
3   1     0.0    NaN
4   1     0.0    NaN
5   2     NaN    0.0
6   2    50.0    0.0
7   2    20.0    1.0
8   2     0.0    NaN
9   2     0.0    NaN
user_42384080

Could you please try following.

df.loc[df['dummy'].isnull(),'amount']=0
df

Output will be as follows.

    id  amount  dummy
0   1   130.0   0.0
1   1   120.0   0.0
2   1   110.0   1.0
3   1   0.0     NaN
4   1   0.0     NaN
5   2   NaN     0.0
6   2   50.0    0.0
7   2   20.0    1.0
8   2   0.0     NaN
9   2   0.0     NaN
user_58665800
Related