Tim.Lucas Tim.Lucas - 1 month ago 12
Python Question

Setting values based on number comparison with mixed type columns - pandas

This should be a really easy one, but I am close to banging my head against the wall because I can figure out how to this. Any help would be greatly appreciated.

I have a pandas DataFrame with a mixed type column (it's actually Decimal, float and string, but let's just assume it's float and string). I am trying to set the value of all the floats which are smaller than zero to 0. What I have tried so far:

In [1]: import pandas as pd
In [2]: ex = [-1, 0, 'rabbit', 'carrots', 10, 24, 'dogs']
In [3]: df = pd.DataFrame(data = ex, columns=['Test'], index = range(len(ex)))
In [4]: Mask = df.loc[:,'Test'].apply(type) != str
In [5]: df.loc[Mask,:] < 0
Out[5]: Test
0 True
1 False
4 False
5 False


Ok great so that works, but now I need to pass this into an
.ix
to actually set the -1 value, to zero, and here is where things get difficult (which is also completely understandable, since the index is not of the same size as the original index)

In [6]: df.ix[df.loc[Mask,'Test'] < 0,'Test'] = 0
Out[6]: [...] IndexingError: Unalignable boolean Series key provided


This is off course completely understandable, but I do not know how to fix this.

I tried
.index.tolist()
to create a second mask to get me the index values on which I should act, but this just returns all of the indexes of the
df.loc[Mask,'Test'] < 0
comparison, regardless of whether they are True or False.

Any help would be greatly appreciated.

Regards,

Tim

Answer
mask = df['Test'].map(lambda x: isinstance(x, (int, float)) and x < 0)
df.ix[mask, 'Test'] = 0