Pandas - Replace values in a DataFrame Based on a Boollean DataFrame

Question

I'm using Pandas v0.20.2 and I have DataFrame, like the following:

df = pd.DataFrame(dict(a=[0,1], b=[3,4], c=[6,7]), 
              index=['spam', 'ham'])
#       a  b  c
# spam  0  3  6
# ham   1  4  7

And I have another DataFrame that is a mask:

mask = pd.DataFrame(dict(a=[True,False], b=[True,True]), 
                index=['spam', 'ham'])
#           a     b
# spam   True  True
# ham   False  True

And I want to set the values in df equal to 999 where it is True in the mask.

I thought that the following would work:

df[mask] = 999

But it doesn't. I get the error below:

ValueError                                Traceback (most recent call last)
<ipython-input-65-503f937859ab> in <module>()
----> 1 df[mask] = 999

/home/gbra/anaconda3/envs/outer_disk/lib/python2.7/site-packages/pandas/core/frame.pyc in __setitem__(self, key, value)
   2326             self._setitem_array(key, value)
   2327         elif isinstance(key, DataFrame):
-> 2328             self._setitem_frame(key, value)
   2329         else:
   2330             # set column

/home/gbra/anaconda3/envs/outer_disk/lib/python2.7/site-packages/pandas/core/frame.pyc in _setitem_frame(self, key, value)
   2364         self._check_inplace_setting(value)
   2365         self._check_setitem_copy()
-> 2366         self._where(-key, value, inplace=True)
   2367 
   2368     def _ensure_valid_index(self, value):

/home/gbra/anaconda3/envs/outer_disk/lib/python2.7/site-packages/pandas/core/generic.pyc in _where(self, cond, other, inplace, axis, level, try_cast, raise_on_error)
   5096             for dt in cond.dtypes:
   5097                 if not is_bool_dtype(dt):
-> 5098                     raise ValueError(msg.format(dtype=dt))
   5099 
   5100         cond = cond.astype(bool, copy=False)

ValueError: Boolean array expected for the condition, not float64

I would appreciate any help on this.

@Psidom I had the feeling that this worked too. But I guess it stopped after I updated Pandas, but I cannot be sure. I am refactoring old code. I've updated the question. — gabra
– gabra, Commented Jul 4, 2017 at 19:53

user2285236 · Accepted Answer · 2017-07-04 19:59:42Z

2

You can reindex the mask to have the same shape as df, and then use df.mask:

df.mask(mask.reindex(df.index, df.columns, fill_value=False), 999)
Out: 
        a    b  c
spam  999  999  6
ham     1  999  7

At that point, regular indexing should also work:

df[mask.reindex(df.index, df.columns, fill_value=False)] = 999

answered Jul 4, 2017 at 19:59

user2285236

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

Comments

Miriam Farber · Accepted Answer · 2017-07-04 19:55:38Z

1

This will do the job:

df = pd.DataFrame(dict(a=[0,1], b=[3,4], c=[6,7]), 
              index=['spam', 'ham'])
mask = pd.DataFrame(dict(a=[True,False], b=[True,True]), 
                index=['spam', 'ham'])
df.iloc[mask] = 999

Then df is

        a   b     c
spam    999 999   6
ham     1   999   7

answered Jul 4, 2017 at 19:55

Miriam Farber

19.7k15 gold badges66 silver badges78 bronze badges

4 Comments

gabra Over a year ago

Thanks, Miriam. But I get all cells equal to 999.

Miriam Farber Over a year ago

Wow that definitely strange. My pandas version is 0.20.2 and my python version is 2.7.12 and it worked for me.

user2285236 Over a year ago

Other than the depreciation warning, works for me too (0.20.1).

gabra Over a year ago

I created a new conda environment with python 2.7.13 and pandas 0.20.2 and now I get that the DataFrame does not change at all. Weird.

Adrien Matissart · Accepted Answer · 2017-07-04 20:04:54Z

0

Another solution, without updating mask

df[mask.columns] = df[mask.columns].mask(mask, 999)

answered Jul 4, 2017 at 20:04

Adrien Matissart

1,68016 silver badges19 bronze badges

Collectives™ on Stack Overflow

Pandas - Replace values in a DataFrame Based on a Boollean DataFrame

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related