Which operator is used to fetch data when any one of the multiple conditions is true?

This article describes how to select rows of pandas.DataFrame by multiple conditions.

• Basic method for selecting rows of pandas.DataFrame
• Select rows with multiple conditions
• The operator precedence

Two points to note are:

1. Use &、|、~ (not and, or, not)
2. Enclose each conditional expression in parentheses when using comparison operators

Error when using and, or, not:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Error when no parentheses:

TypeError: cannot compare a dtyped [object] array with a scalar of type [bool]

In the sample code, the following CSV file is read and used.

• sample_pandas_normal.csv

import pandas as pd

df = pd.read_csv('data/src/sample_pandas_normal.csv')
print(df)
#       name  age state  point
# 0    Alice   24    NY     64
# 1      Bob   42    CA     92
# 2  Charlie   18    CA     70
# 3     Dave   68    TX     70
# 4    Ellen   24    CA     88
# 5    Frank   30    NY     57

The sample code uses pandas.DataFrame, but the same applies to pandas.Series.

Basic method for selecting rows of pandas.DataFrame

Using a list, array, or pandas.Series of boolean bool, you can select rows that are True.

mask = [True, False, True, False, True, False]
df_mask = df[mask]
print(df_mask)
#       name  age state  point
# 0    Alice   24    NY     64
# 2  Charlie   18    CA     70
# 4    Ellen   24    CA     88

Select rows with multiple conditions

You can get pandas.Series of bool which is an AND of two conditions using &.

Note that == and ~ are used here as the second condition for the sake of explanation, but you can use != as well.

print(df['age'] < 35)
# 0     True
# 1    False
# 2     True
# 3    False
# 4     True
# 5     True
# Name: age, dtype: bool

print(~(df['state'] == 'NY'))
# 0    False
# 1     True
# 2     True
# 3     True
# 4     True
# 5    False
# Name: state, dtype: bool

print((df['age'] < 35) & ~(df['state'] == 'NY'))
# 0    False
# 1    False
# 2     True
# 3    False
# 4     True
# 5    False
# dtype: bool

Use this to select only True rows.

df_and = df[(df['age'] < 35) & ~(df['state'] == 'NY')]
print(df_and)
#       name  age state  point
# 2  Charlie   18    CA     70
# 4    Ellen   24    CA     88

Use | for OR.

print((df['age'] < 20) | (df['point'] > 90))
# 0    False
# 1     True
# 2     True
# 3    False
# 4    False
# 5    False
# dtype: bool

df_or = df[(df['age'] < 20) | (df['point'] > 90)]
print(df_or)
#       name  age state  point
# 1      Bob   42    CA     92
# 2  Charlie   18    CA     70

See the following article for why you must use &, |, ~ instead of and, or, not and why parentheses are necessary.

• NumPy, pandas: How to fix ValueError: The truth value ... is ambiguous

The operator precedence

The order of operator precedence in Python is ~ > & > |.

• 6. Expressions - — Python 3.10.4 documentation

If there are three or more conditions, the results may vary depending on the order.

df_multi_1 = df[(df['age'] < 35) | ~(df['state'] == 'NY') & (df['point'] < 75)]
print(df_multi_1)
#       name  age state  point
# 0    Alice   24    NY     64
# 2  Charlie   18    CA     70
# 3     Dave   68    TX     70
# 4    Ellen   24    CA     88
# 5    Frank   30    NY     57

df_multi_2 = df[(df['age'] < 35) & (df['point'] < 75) | ~(df['state'] == 'NY')]
print(df_multi_2)
#       name  age state  point
# 0    Alice   24    NY     64
# 1      Bob   42    CA     92
# 2  Charlie   18    CA     70
# 3     Dave   68    TX     70
# 4    Ellen   24    CA     88
# 5    Frank   30    NY     57

It is safer to enclose them in parentheses.

df_multi_3 = df[((df['age'] < 35) | ~(df['state'] == 'NY')) & (df['point'] < 75)]
print(df_multi_3)
#       name  age state  point
# 0    Alice   24    NY     64
# 2  Charlie   18    CA     70
# 3     Dave   68    TX     70
# 5    Frank   30    NY     57

Which operator is used to fetch data?

Which operator is used to display all records if all conditions are true?

Which operator is used to specify two conditions?

Which SQL operator is commonly used in place of multiple OR conditions?