Filtering pandas in Python using 'where'

Question

I have a new question concerning filtering pandas in Python.

My original data frame, DF, looks like this

date        currency      1Y    1Y1M    1Y2M    1Y3M    1Y4M
2013-09-25  EUR            0       0       0       0       0
2013-09-26  EUR            0       0       0       0       0
2013-09-27  EUR            0       0       0       0       0
2013-09-28  EUR            0       0       0       0       0
2013-09-29  EUR            0       0       0       0       0
2013-09-25  USD            0       0       0       0       0
2013-09-26  USD            0       -2      -4      -8      -10  
2013-09-27  USD            0       -1      -1      -6      -1   
2013-09-28  USD            0       -3      -6      -2      -6
2013-09-29  USD            0       -5      -1      -6      -7

What I would like to do, is to somehow filter this data on 'USD', and change signs to all available data matching the criteria. I want the original data to be changed (so not a copy), i.e. the resulting dataframe (i.e. DF) would be,

date        currency      1Y    1Y1M    1Y2M    1Y3M    1Y4M
2013-09-25  EUR            0       0       0       0       0
2013-09-26  EUR            0       0       0       0       0
2013-09-27  EUR            0       0       0       0       0
2013-09-28  EUR            0       0       0       0       0
2013-09-29  EUR            0       0       0       0       0
2013-09-25  USD            0       0       0       0       0
2013-09-26  USD            0       2       4       8       10   
2013-09-27  USD            0       1       1       6       1    
2013-09-28  USD            0       3       6       2       6
2013-09-29  USD            0       5       1       6       7

I have tried using the function 'where' as so (with printing to see effect)

mask = DF['currency'].str.contains('USD')
print DF.ix[mask,'1Y1M']
DF.where(DF[~mask], -1 * DF,inplace=True)
print data_BBG.ix[mask,'1Y1M']

But the print-results show no effect.

Any insights would be great!

unutbu · Accepted Answer · 2014-02-17 10:21:18Z

3

df.loc[df['currency']=='USD', '1Y':'1Y4M'] *= -1

yields

         date currency  1Y  1Y1M  1Y2M  1Y3M  1Y4M
0  2013-09-25      EUR   0     0     0     0     0
1  2013-09-26      EUR   0     0     0     0     0
2  2013-09-27      EUR   0     0     0     0     0
3  2013-09-28      EUR   0     0     0     0     0
4  2013-09-29      EUR   0     0     0     0     0
5  2013-09-25      USD   0     0     0     0     0
6  2013-09-26      USD   0     2     4     8    10
7  2013-09-27      USD   0     1     1     6     1
8  2013-09-28      USD   0     3     6     2     6
9  2013-09-29      USD   0     5     1     6     7

Although DF['currency'].str.contains('USD') may be faster than df['currency']=='USD', I would suggest using the latter to guarantee that you only select rows where the currency exactly equals USD. The former might include rows whose currency happens to include the letters USD. I'm not sure if that is possible, but why risk it.

edited Feb 17, 2014 at 10:21

answered Feb 17, 2014 at 9:59

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

5 Comments

gussilago Over a year ago

Appreciate the quick answer @unutbu. However, I seem to get conversion error when trying to apply the .loc. ValueError: ('could not convert string to float: ', u'occurred at index 1Y') Moreover, as this is just an example, do you think there is a way to apply the "*= -1" to all columns, as '1Y':'1Y4M'?

unutbu Over a year ago

Oh yes, '1Y':'1Y4m' also works. Can you tell me more about the "conversion error", perhaps supply a runnable example which reproduces the error? Please post the full error message.

gussilago Over a year ago

Sorry for being obscure. File "C:\anaconda\lib\site-packages\pandas\core\frame.py", line 4556, in applymap return self.apply(infer) File "C:\anaconda\lib\site-packages\pandas\core\frame.py", line 4416, in apply return self._apply_standard(f, axis) File "C:\anaconda\lib\site-packages\pandas\core\frame.py", line 4491, in _apply_standard raise e ValueError: ('could not convert string to float: ', u'occurred at index 1Y') >>>

unutbu Over a year ago

Please post DF.dtypes.

gussilago Over a year ago

Hi @unutbu, I realized some of my data points are formatted as "165,67" instead of "165.67", which is most likely causing the problem. I'll try to work this issue out, and get back a.s.a.p

Collectives™ on Stack Overflow

Filtering pandas in Python using 'where'

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related