Multi-column replacement in Pandas based on row selection

Question

There are a plethora of questions on SO about how to select rows in a DataFrame and replace values in a column in those rows, but one use case is missing. To use the example DataFrame from this question,

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

And this works if one wants to change a single column based on another:

df.loc[df.cherry == 'bad', 'apple'] = df.banana * 2

Or this sets the values in two columns:

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

But this doesn't work:

df.loc[df.cherry == 'bad', ['apple', 'banana'] = [df.banana, df.apple]

, because apparently the right side is 3x2, while the left side is 1x2, hence the error message

ValueError: Must have equal len keys and value when setting with an ndarray

So I understand what the problem is, but what is the solution?

Answer 1

IIUC you can try:

df['a'] = df.apple * 3
df['b'] = df.banana * 2
print df
   apple  banana cherry  a   b
0      0       3   good  0   6
1      1       4    bad  3   8
2      2       5   good  6  10

df[['a', 'b']] = df.loc[df.cherry == 'bad', ['apple', 'banana']] 
print df
   apple  banana cherry    a    b
0      0       3   good  NaN  NaN
1      1       4    bad  1.0  4.0
2      2       5   good  NaN  NaN

Or use conditions with values:

df['a'] = df.apple * 3
df['b'] = df.banana * 2

df.loc[df.cherry == 'bad', ['apple', 'banana']] = 
df.loc[df.cherry == 'bad', ['a', 'b']].values
print df
   apple  banana cherry  a   b
0      0       3   good  0   6
1      3       8    bad  3   8
2      2       5   good  6  10

Another options with original columns:

print df[['apple','banana']].shift() * 2
   apple  banana
0    NaN     NaN
1   12.0     6.0
2    2.0     8.0

df.loc[df.cherry == 'bad', ['apple', 'banana']] = df[['apple','banana']].shift() * 2
print df
   apple  banana cherry
0    6.0     3.0   good
1   12.0     6.0    bad
2    2.0     5.0   good