Logical operators for boolean indexing in Pandas

Question

I m working with boolean index in Pandas  The question is why the statement   a  a  some column    some number   amp   a  some other column    some other number     works fine whereas  a  a  some column    some number  and  a  some other column    some other number     exits with error   Example   a pd DataFrame   x   1 1   y   10 20     In  a  a  x    1  amp  a  y    10   Out     x   y      0  1  10  In  a  a  x    1  and  a  y    10   Out  ValueError  The truth value of an array with more than one element is ambiguous      Use a any   or a all

User · Answer

Logical operators for boolean indexing in Pandas

It's important to realize that you cannot use any of the Python logical operators (and, or or not) on pandas.Series or pandas.DataFrames (similarly you cannot use them on numpy.arrays with more than one element). The reason why you cannot use those is because they implicitly call bool on their operands which throws an Exception because these data structures decided that the boolean of an array is ambiguous:

>>> import numpy as np
>>> import pandas as pd
>>> arr = np.array([1,2,3])
>>> s = pd.Series([1,2,3])
>>> df = pd.DataFrame([1,2,3])
>>> bool(arr)
ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()
>>> bool(s)
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
>>> bool(df)
ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I did cover this more extensively in my answer to the "Truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()" Q+A.

NumPys logical functions

However NumPy provides element-wise operating equivalents to these operators as functions that can be used on numpy.array, pandas.Series, pandas.DataFrame, or any other (conforming) numpy.array subclass:

and has np.logical_and
or has np.logical_or
not has np.logical_not
numpy.logical_xor which has no Python equivalent but is a logical "exclusive or" operation

So, essentially, one should use (assuming df1 and df2 are pandas DataFrames):

np.logical_and(df1, df2)
np.logical_or(df1, df2)
np.logical_not(df1)
np.logical_xor(df1, df2)

Bitwise functions and bitwise operators for booleans

However in case you have boolean NumPy array, pandas Series, or pandas DataFrames you could also use the element-wise bitwise functions (for booleans they are - or at least should be - indistinguishable from the logical functions):

bitwise and: np.bitwise_and or the & operator
bitwise or: np.bitwise_or or the | operator
bitwise not: np.invert (or the alias np.bitwise_not) or the ~ operator
bitwise xor: np.bitwise_xor or the ^ operator

Typically the operators are used. However when combined with comparison operators one has to remember to wrap the comparison in parenthesis because the bitwise operators have a higher precedence than the comparison operators:

(df1 < 10) | (df2 > 10)  # instead of the wrong df1 < 10 | df2 > 10

This may be irritating because the Python logical operators have a lower precendence than the comparison operators so you normally write a < 10 and b > 10 (where a and b are for example simple integers) and don't need the parenthesis.

Differences between logical and bitwise operations (on non-booleans)

It is really important to stress that bit and logical operations are only equivalent for boolean NumPy arrays (and boolean Series & DataFrames). If these don't contain booleans then the operations will give different results. I'll include examples using NumPy arrays but the results will be similar for the pandas data structures:

>>> import numpy as np
>>> a1 = np.array([0, 0, 1, 1])
>>> a2 = np.array([0, 1, 0, 1])

>>> np.logical_and(a1, a2)
array([False, False, False,  True])
>>> np.bitwise_and(a1, a2)
array([0, 0, 0, 1], dtype=int32)

And since NumPy (and similarly pandas) does different things for boolean (Boolean or “mask” index arrays) and integer (Index arrays) indices the results of indexing will be also be different:

>>> a3 = np.array([1, 2, 3, 4])

>>> a3[np.logical_and(a1, a2)]
array([4])
>>> a3[np.bitwise_and(a1, a2)]
array([1, 1, 1, 2])

Summary table

Logical operator | NumPy logical function | NumPy bitwise function | Bitwise operator
-------------------------------------------------------------------------------------
       and       |  np.logical_and        | np.bitwise_and         |        &
-------------------------------------------------------------------------------------
       or        |  np.logical_or         | np.bitwise_or          |        |
-------------------------------------------------------------------------------------
                 |  np.logical_xor        | np.bitwise_xor         |        ^
-------------------------------------------------------------------------------------
       not       |  np.logical_not        | np.invert              |        ~

Where the logical operator does not work for NumPy arrays, pandas Series, and pandas DataFrames. The others work on these data structures (and plain Python objects) and work element-wise. However be careful with the bitwise invert on plain Python bools because the bool will be interpreted as integers in this context (for example ~False returns -1 and ~True returns -2).

User · Answer

When you say   a  x    1  and  a  y    10    You are implicitly asking Python to convert  a  x    1  and  a  y    10  to boolean values    NumPy arrays  of length greater than 1  and Pandas objects such as Series do not have a boolean value -- in other words  they raise   ValueError  The truth value of an array is ambiguous  Use a empty  a any   or a all      when used as a boolean value  That s because its unclear when it should be True or False  Some users might assume they are True if they have non-zero length  like a Python list  Others might desire for it to be True only if all its elements are True  Others might want it to be True if any of its elements are True    Because there are so many conflicting expectations  the designers of NumPy and Pandas refuse to guess  and instead raise a ValueError   Instead  you must be explicit  by calling the empty    all   or any   method to indicate which behavior you desire   In this case  however  it looks like you do not want boolean evaluation  you want element-wise logical-and  That is what the  amp  binary operator performs    a  x    1   amp   a  y    10    returns a boolean array      By the way  as alexpmil notes   the parentheses are mandatory since  amp  has a higher operator precedence than     Without the parentheses  a  x    1  amp  a  y    10 would be evaluated as a  x       1  amp  a  y       10 which would in turn be equivalent to the chained comparison  a  x       1  amp  a  y     and   1  amp  a  y       10   That is an expression of the form Series and Series  The use of and with two Series would again trigger the same ValueError as above  That s why the parentheses are mandatory

User · Answer

TLDR  Logical Operators in Pandas are  amp     and    and  parentheses       is important   Python s and  or and not logical operators are designed to work with scalars  So Pandas had to do one better and override the bitwise operators to achieve vectorized  element-wise  version of this functionality    So the following in python  exp1 and exp2 are expressions which evaluate to a boolean result       exp1 and exp2                Logical AND exp1 or exp2                 Logical OR not exp1                     Logical NOT      will translate to     exp1  amp  exp2                  Element-wise logical AND exp1   exp2                  Element-wise logical OR  exp1                        Element-wise logical NOT   for pandas   If in the process of performing logical operation you get a ValueError  then you need to use parentheses for grouping    exp1  op  exp2    For example    df  col1      x   amp   df  col2      y     And so on     Boolean Indexing  A common operation is to compute boolean masks through logical conditions to filter the data  Pandas provides three operators   amp  for logical AND    for logical OR  and   for logical NOT    Consider the following setup   np random seed 0  df   pd DataFrame np random choice 10   5  3    columns list  ABC    df     A  B  C 0  5  0  3 1  3  7  9 2  3  5  2 3  4  7  6 4  8  8  1   Logical AND  For df above  say you d like to return all rows where A  lt  5 and B   5  This is done by computing masks for each condition separately  and ANDing them    Overloaded Bitwise  amp  Operator Before continuing  please take note of this particular excerpt of the docs  which state     Another common operation is the use of boolean vectors to filter the   data  The operators are    for or   amp  for and  and   for not  These   must be grouped by using parentheses  since by default Python will   evaluate an expression such as df A  gt  2  amp  df B  lt  3 as df A  gt   2  amp    df B   lt  3  while the desired evaluation order is  df A  gt  2   amp   df B  lt    3     So  with this in mind  element wise logical AND can be implemented with the bitwise operator  amp     df  A    lt  5  0    False 1     True 2     True 3     True 4    False Name  A  dtype  bool  df  B    gt  5  0    False 1     True 2    False 3     True 4     True Name  B  dtype  bool      df  A    lt  5   amp   df  B    gt  5   0    False 1     True 2    False 3     True 4    False dtype  bool   And the subsequent filtering step is simply   df  df  A    lt  5   amp   df  B    gt  5       A  B  C 1  3  7  9 3  4  7  6   The parentheses are used to override the default precedence order of bitwise operators  which have higher precedence over the conditional operators  lt  and  gt   See the section of Operator Precedence in the python docs    If you do not use parentheses  the expression is evaluated incorrectly  For example  if you accidentally attempt something such as   df  A    lt  5  amp  df  B    gt  5   It is parsed as   df  A    lt   5  amp  df  B     gt  5   Which becomes    df  A    lt  something you dont want  gt  5   Which becomes  see the python docs on chained operator comparison     df  A    lt  something you dont want  and  something you dont want  gt  5    Which becomes      Both operands are Series    something else you dont want1 and something else you dont want2  Which throws  ValueError  The truth value of a Series is ambiguous  Use a empty  a bool    a item    a any   or a all      So  don t make that mistake 1  Avoiding Parentheses Grouping The fix is actually quite simple  Most operators have a corresponding bound method for DataFrames  If the individual masks are built up using functions instead of conditional operators  you will no longer need to group by parens to specify evaluation order   df  A   lt 5   0     True 1     True 2     True 3     True 4    False Name  A  dtype  bool  df  B   gt 5   0    False 1     True 2    False 3     True 4     True Name  B  dtype  bool     df  A   lt 5   amp  df  B   gt 5   0    False 1     True 2    False 3     True 4    False dtype  bool   See the section on Flexible Comparisons   To summarise  we have   ------------------------------           Operator      Function        ---- ------------ ------------       0     gt              gt             ---- ------------ ------------       1     gt              ge             ---- ------------ ------------       2     lt              lt             ---- ------------ ------------       3     lt              le             ---- ------------ ------------       4                  eq             ---- ------------ ------------       5                  ne             ------------------------------    Another option for avoiding parentheses is to use DataFrame query  or eval    df query  A  lt  5 and B  gt  5       A  B  C 1  3  7  9 3  4  7  6   I have extensively documented query and eval in Dynamic Expression Evaluation in pandas using pd eval     operator and  Allows you to perform this operation in a functional manner  Internally calls Series   and   which corresponds to the bitwise operator   import operator   operator and  df  A    lt  5  df  B    gt  5    Same as     df  A    lt  5    and   df  B    gt  5    0    False 1     True 2    False 3     True 4    False dtype  bool  df operator and  df  A    lt  5  df  B    gt  5       A  B  C 1  3  7  9 3  4  7  6   You won t usually need this  but it is useful to know   Generalizing  np logical and  and logical and reduce  Another alternative is using  np logical and  which also does not need parentheses grouping   np logical and df  A    lt  5  df  B    gt  5   0    False 1     True 2    False 3     True 4    False Name  A  dtype  bool  df np logical and df  A    lt  5  df  B    gt  5       A  B  C 1  3  7  9 3  4  7  6   np logical and is a ufunc  Universal Functions   and most ufuncs have a reduce method  This means it is easier to generalise with logical and if you have multiple masks to AND  For example  to AND masks m1 and m2 and m3 with  amp   you would have to do   m1  amp  m2  amp  m3   However  an easier option is   np logical and reduce  m1  m2  m3     This is powerful  because it lets you build on top of this with more complex logic  for example  dynamically generating masks in a list comprehension and adding all of them    import operator  cols     A    B   ops    np less  np greater  values    5  5   m   np logical and reduce  op df c   v  for op  c  v in zip ops  cols  values    m    array  False   True  False   True  False    df m     A  B  C 1  3  7  9 3  4  7  6   1 - I know I m harping on this point  but please bear with me  This is a very  very common beginner s mistake  and must be explained very thoroughly      Logical OR  For the df above  say you d like to return all rows where A    3 or B    7   Overloaded Bitwise        df  A      3  0    False 1     True 2     True 3    False 4    False Name  A  dtype  bool  df  B      7  0    False 1     True 2    False 3     True 4    False Name  B  dtype  bool      df  A      3     df  B      7   0    False 1     True 2     True 3     True 4    False dtype  bool  df  df  A      3     df  B      7       A  B  C 1  3  7  9 2  3  5  2 3  4  7  6   If you haven t yet  please also read the section on Logical AND above  all caveats apply here   Alternatively  this operation can be specified with   df df  A   eq 3    df  B   eq 7       A  B  C 1  3  7  9 2  3  5  2 3  4  7  6   operator or  Calls Series   or   under the hood   operator or  df  A      3  df  B      7    Same as     df  A      3    or   df  B      7   0    False 1     True 2     True 3     True 4    False dtype  bool  df operator or  df  A      3  df  B      7       A  B  C 1  3  7  9 2  3  5  2 3  4  7  6   np logical or For two conditions  use logical or   np logical or df  A      3  df  B      7   0    False 1     True 2     True 3     True 4    False Name  A  dtype  bool  df np logical or df  A      3  df  B      7       A  B  C 1  3  7  9 2  3  5  2 3  4  7  6   For multiple masks  use logical or reduce   np logical or reduce  df  A      3  df  B      7     array  False   True   True   True  False    df np logical or reduce  df  A      3  df  B      7        A  B  C 1  3  7  9 2  3  5  2 3  4  7  6     Logical NOT  Given a mask  such as   mask   pd Series  True  True  False     If you need to invert every boolean value  so that the end result is  False  False  True    then you can use any of the methods below   Bitwise        mask  0    False 1    False 2     True dtype  bool   Again  expressions need to be parenthesised     df  A      3   0     True 1    False 2    False 3     True 4     True Name  A  dtype  bool   This internally calls   mask   invert      0    False 1    False 2     True dtype  bool   But don t use it directly   operator inv Internally calls   invert   on the Series   operator inv mask   0    False 1    False 2     True dtype  bool   np logical not This is the numpy variant   np logical not mask   0    False 1    False 2     True dtype  bool     Note  np logical and can be substituted for np bitwise and  logical or with bitwise or  and logical not with invert

[python] Logical operators for boolean indexing in Pandas

NumPys logical functions

Bitwise functions and bitwise operators for booleans

Differences between logical and bitwise operations (on non-booleans)

Summary table

Examples related to python

Examples related to pandas

Examples related to dataframe

Examples related to boolean

Examples related to filtering