assign value to dataframe in the first line meeting a condition

Viewed 578

I have a pandas dataframe and I would like to add a column and set certain values based on certain conditions.

Initialization

See my following example:

import numpy as np
import pandas as pd

df=pd.DataFrame(np.reshape(np.arange(20),(5,4)).tolist(),columns=list('ABCD'))
df['E']=''
df.loc[0,'E']=1
df

Until here, everything is as wished. Output:

    A   B   C   D  E
0   0   1   2   3  1
1   4   5   6   7   
2   8   9  10  11   
3  12  13  14  15   
4  16  17  18  19   

Problem

But now, I would like to set the value of the 'E' column in the first row for which the 'B' value is larger than 8 to 3 (the same as df.loc[2,'E']=1 but using that condition. I don't know which line it is a priori).

Solution attempts

I have tried different things:

df.loc[df['B']>8,'E'].iloc[0]=3
df

This doesn't change any value:

    A   B   C   D  E
0   0   1   2   3  1
1   4   5   6   7   
2   8   9  10  11   
3  12  13  14  15   
4  16  17  18  19   

The same holds for

df.loc[df['B']>8]['E'].iloc[0]=3

Other things I've tried result in an error. df.loc[df['B']>8,'E']=3works well but I only want to assign the value in the first line meeting the condition, not in all lines.

So how do I achieve this?

1 Answers

Use .idxmax(). Since True is the max of True/False it will return the index of the first True value for a boolean index.

df.loc[(df['B']>8).idxmax(),'E']=3
Related