Changing certain values in multiple columns of a pandas DataFrame at once

Question

Suppose I have the following DataFrame:

In [1]: df
Out[1]:
  apple banana cherry
0     0      3   good
1     1      4    bad
2     2      5   good

This works as expected:

In [2]: df['apple'][df.cherry == 'bad'] = np.nan
In [3]: df
Out[3]:
  apple banana cherry
0     0      3   good
1   NaN      4    bad
2     2      5   good

But this doesn't:

In [2]: df[['apple', 'banana']][df.cherry == 'bad'] = np.nan
In [3]: df
Out[3]:
  apple banana cherry
0     0      3   good
1     1      4    bad
2     2      5   good

Why? How can I achieve the conversion of both the 'apple' and 'banana' values without having to write out two lines, as in

In [2]: df['apple'][df.cherry == 'bad'] = np.nan
In [3]: df['banana'][df.cherry == 'bad'] = np.nan

Andy Hayden · Accepted Answer · 2013-11-08 20:14:12Z

39

You should use loc and do this without chaining:

In [11]: df.loc[df.cherry == 'bad', ['apple', 'banana']] = np.nan

In [12]: df
Out[12]: 
   apple  banana cherry
0      0       3   good
1    NaN     NaN    bad
2      2       5   good

See the docs on returning a view vs a copy, if you chain the assignment is made to the copy (and thrown away) but if you do it in one loc then pandas cleverly realises you want to assign to the original.

answered Nov 8, 2013 at 20:14

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

4 Comments

dermen Over a year ago

Is it easy to extend this to the case where np.nan is instead another data frame with the shape of df[['apple', 'banana']][df.cherry == 'bad'] ?

Andy Hayden Over a year ago

@dermen it should "just work", though you may have issues aligning (on the index).... you may have to assign other_df.values. Perhaps worth asking as another question if that fails?

dermen Over a year ago

I was having troubles doing this, which lead me to this posting.. and as it turns out, assigning other_df.values is the way to go.

scrollout Over a year ago

Is there a way to do this, but assign NaN to apple and 123 to banana ?

roman · Accepted Answer · 2013-11-08 20:14:38Z

5

It's because df[['apple', 'banana']][df.cherry == 'bad'] = np.nan assigning to the copy of DataFrame. Try this:

df.ix[df.cherry == 'bad', ['apple', 'banana']] = np.nan

answered Nov 8, 2013 at 20:14

roman

118k30 gold badges205 silver badges209 bronze badges

1 Comment

WY Hsu Over a year ago

Don't use api ix again, use loc or iloc instead, see this What is meant by ".ix is deprecated" in Python Pandas?

NL23codes · Accepted Answer · 2021-08-17 00:40:24Z

1

While this question is broad, the answers seem very specific and not very versatile. This is just to clarify...

df = pandas.DataFrame({'Test1' :[1,2,3,4,5], 'Test2': [3,4,5,6,7], 'Test3': [5,6,7,8,9]})

   Test1 Test2 Test3
0  1     3     5
1  2     4     6
2  3     5     7
3  4     6     8
4  5     7     9

# When the index or row you want to edit is known
df.loc[3, ['Test1', 'Test2', 'Test3'] = [10, 12, 14]

# When you don't know the index but can find it by looking in a column for a specific value

df.loc[df[df['Test1'] == 4].index[0], ['Test1', 'Test2', 'Test3']] = [10, 12, 14]

   Test1 Test2 Test3
0  1     3     5
1  2     4     6
2  3     5     7
3  10    12    14
4  5     7     9

Both methods allow you to change the values of multiple columns in one line of code.

edited Aug 17, 2021 at 0:40

answered Aug 16, 2021 at 22:03

NL23codes

1,2291 gold badge15 silver badges32 bronze badges

Collectives™ on Stack Overflow

Changing certain values in multiple columns of a pandas DataFrame at once

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related