WHERE in pandas

Posted by Kevin

Basic filtering of rows in SQL's WHERE clause, translated to Python's pandas. The filtering methods used here apply 'boolean masking', evaluating if a row returns True or False for the applied filter. True rows are returned to the user.

Basic filtering of rows in a pandas DataFrame using one filter

In SQL:

SELECT
  *
FROM
  table
WHERE
  column_1 == 'Example';

In pandas:

table[table['column_1'] == "Example"]

Alternatively, apply .query(): on your DataFrame

table.query("column_1 == 'Example'")

Basic filtering of rows in a pandas DataFrame using multiple filters

In SQL:

SELECT
  *
FROM
  table
WHERE
  column_1 == 'Example' AND column_2 > 10;

In pandas:

table[table['column_1'] == "Example"
     & table['column_2'] > 10]

Or, using .query():

table.query("column_1 == 'Example' and column_2 > 10")

Alternatively, when combining filters using OR:

In SQL:

SELECT
  *
FROM
  table
WHERE
  column_1 == 'Example 1' OR column_1 == 'Example 2';

In pandas:

table[table['column_1'] == "Example 1"
     | table['column_1'] == "Example 2"]

Using .query():

table.query("column_1 == 'Example 1' or column_1 == 'Example 2'")

The last example could also be done with SQL's IN, see the IN recipe of the SQL2pandas Cookbook!

References