Selecting with complex criteria from pandas DataFrame

Question

For example I have simple DF   import pandas as pd from random import randint  df   pd DataFrame   A    randint 1  9  for x in xrange 10                        B    randint 1  9  10 for x in xrange 10                        C    randint 1  9  100 for x in xrange 10       Can I select values from  A  for which corresponding values for  B  will be greater than 50  and for  C  - not equal 900  using methods and idioms of Pandas

User · Answer

You can use pandas it has some built in functions for comparison. So if you want to select values of "A" that are met by the conditions of "B" and "C" (assuming you want back a DataFrame pandas object)

df[['A']][df.B.gt(50) & df.C.ne(900)]

df[['A']] will give you back column A in DataFrame format.

pandas 'gt' function will return the positions of column B that are greater than 50 and 'ne' will return the positions not equal to 900.

User · Answer

Another solution is to use the query method   import pandas as pd  from random import randint df   pd DataFrame   A    randint 1  9  for x in xrange 10                        B    randint 1  9    10 for x in xrange 10                        C    randint 1  9    100 for x in xrange 10     print df     A   B    C 0  7  20  300 1  7  80  700 2  4  90  100 3  4  30  900 4  7  80  200 5  7  60  800 6  3  80  900 7  9  40  100 8  6  40  100 9  3  10  600  print df query  B  gt  50 and C    900       A   B    C 1  7  80  700 2  4  90  100 4  7  80  200 5  7  60  800   Now if you want to change the returned values in column A you can save their index   my query index   df query  B  gt  50  amp  C    900   index       and use  iloc to change them i e   df iloc my query index  0    5000  print df        A   B    C 0     7  20  300 1  5000  80  700 2  5000  90  100 3     4  30  900 4  5000  80  200 5  5000  60  800 6     3  80  900 7     9  40  100 8     6  40  100 9     3  10  600

User · Answer

Sure   Setup    gt  gt  gt  import pandas as pd  gt  gt  gt  from random import randint  gt  gt  gt  df   pd DataFrame   A    randint 1  9  for x in range 10                        B    randint 1  9  10 for x in range 10                        C    randint 1  9  100 for x in range 10      gt  gt  gt  df    A   B    C 0  9  40  300 1  9  70  700 2  5  70  900 3  8  80  900 4  7  50  200 5  9  30  900 6  2  80  700 7  2  80  400 8  5  80  300 9  7  70  800   We can apply column operations and get boolean Series objects    gt  gt  gt  df  B    gt  50 0    False 1     True 2     True 3     True 4    False 5    False 6     True 7     True 8     True 9     True Name  B  gt  gt  gt   df  B    gt  50   amp   df  C      900  0    False 1    False 2     True 3     True 4    False 5    False 6    False 7    False 8    False 9    False    Update  to switch to new-style  loc    And then we can use these to index into the object   For read access  you can chain indices    gt  gt  gt  df  A    df  B    gt  50   amp   df  C      900   2    5 3    8 Name  A  dtype  int64   but you can get yourself into trouble because of the difference between a view and a copy doing this for write access   You can use  loc instead    gt  gt  gt  df loc  df  B    gt  50   amp   df  C      900    A   2    5 3    8 Name  A  dtype  int64  gt  gt  gt  df loc  df  B    gt  50   amp   df  C      900    A   values array  5  8   dtype int64   gt  gt  gt  df loc  df  B    gt  50   amp   df  C      900    A      1000  gt  gt  gt  df       A   B    C 0     9  40  300 1     9  70  700 2  5000  70  900 3  8000  80  900 4     7  50  200 5     9  30  900 6     2  80  700 7     2  80  400 8     5  80  300 9     7  70  800   Note that I accidentally typed    900 and not    900  or   df  C      900   but I m too lazy to fix it  Exercise for the reader

User · Answer

And remember to use parenthesis   Keep in mind that  amp  operator takes a precedence over operators such as  gt  or  lt  etc  That is why   4  lt  5  amp  6  gt  4   evaluates to False  Therefore if you re using pd loc  you need to put brackets around your logical statements  otherwise you get an error  That s why do   df loc  df  A    gt  10   amp   df  B    lt  15     instead of   df loc df  A    gt  10  amp  df  B    lt  15    which would result in      TypeError  cannot compare a dtyped  float64  array with a scalar of type  bool

[python] Selecting with complex criteria from pandas.DataFrame

Examples related to python

Examples related to pandas