pandas : update value if condition in 3 columns are met

Question

I have a dataframe df like this:

      A      B       C            D
1   blue    red    square        NaN
2  orange  yellow  circle        NaN
3  black   grey    circle        NaN

and I want to update column D when it meets 3 conditions. Ex:

df.ix[ np.logical_and(df.A=='blue', df.B=='red', df.C=='square'), ['D'] ] = 'succeed'

It works for the first two conditions, but it doesn't work for the third, thus:

df.ix[ np.logical_and(df.A=='blue', df.B=='red', df.C=='triangle'), ['D'] ] = 'succeed'

has exactly the same result:

      A      B       C            D
1   blue    red    square        succeed
2  orange  yellow  circle        NaN
3  black   grey    circle        NaN

Use the solution in this answer if the other solutions are slow. — cottontail
– cottontail, Commented Nov 16, 2022 at 20:06

Ena · Accepted Answer · 2019-09-04 10:27:25Z

81

Using:

df[ (df.A=='blue') & (df.B=='red') & (df.C=='square') ]['D'] = 'succeed'

gives the warning:

/usr/local/lib/python2.7/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

A better way of achieving this seems to be:

df.loc[(df['A'] == 'blue') & (df['B'] == 'red') & (df['C'] == 'square'),'D'] = 'M5'

edited Sep 4, 2019 at 10:27

Ena

3,65140 silver badges34 bronze badges

answered Aug 12, 2017 at 14:38

Praveen

2,1871 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim · Accepted Answer · 2014-01-21 16:02:29Z

28

You could try this instead:

df[ (df.A=='blue') & (df.B=='red') & (df.C=='square') ]['D'] = 'succeed'

answered Jan 21, 2014 at 16:02

Tim

2,2423 gold badges27 silver badges40 bronze badges

Comments

Aryan Firouzian · Accepted Answer · 2017-11-01 10:41:16Z

6

You could try:

df['D'] = np.where((df.A=='blue') & (df.B=='red') & (df.C=='square'), 'succeed')

This answer might provide a detailed answer to the your question: Update row values where certain condition is met in pandas

edited Nov 1, 2017 at 10:41

Aryan Firouzian

2,0346 gold badges32 silver badges46 bronze badges

answered Nov 1, 2017 at 10:04

theSanjeev

1492 silver badges10 bronze badges

Comments

Alex Schwab · Accepted Answer · 2019-06-13 07:27:49Z

4

This format might have been implied in the new answers, but the following bit actually worked for me.

df['D'].loc[(df['A'] == 'blue') & (df['B'] == 'red') & (df['C'] == 'square')] = 'succeed'

answered Jun 13, 2019 at 7:27

Alex Schwab

838 bronze badges

Comments

waitingkuo · Accepted Answer · 2014-01-21 16:56:38Z

3

The third parameter of logical_and is to assign the array used to store the result.

Currently, the method @TimRich provided might be the best. In pandas 0.13 (in development), there's a new experimental query method. Try it!

answered Jan 21, 2014 at 16:56

waitingkuo

94.5k28 gold badges119 silver badges122 bronze badges

Comments

cottontail · Accepted Answer · 2022-09-12 09:26:36Z

The existing solutions are very slow for large dataframes (100k+ rows); an alternative is to try numexpr evaluation with eval() method to build a boolean mask and use this mask to replace values using mask() method.

df['D'] = df['D'].mask(df.eval("A=='blue' and B=='red' and C=='square'"), 'succeed')

As the length of the dataframe increases eval() becomes much faster than the other alternatives. For example, for a frame with 1mil rows, it is 2.2 times faster than loc method outlined in Praveen, Tim and Alex Schwab's answers.

Yet another method is to use numpy.where() method to select values according to a condition.

df['D'] = np.where((df.A=='blue') & (df.B=='red') & (df.C=='square'), 'succeed', pd.NA)

This is similar to theSanjeev's answer; the only difference is to set the "else"-value that is missing in their answer. Speaking of missing values, pd.NA is also faster than np.nan or float('nan') as well.

Collectives™ on Stack Overflow

pandas : update value if condition in 3 columns are met

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related