WHERE column LIKE in pandas

Posted by Kevin

Filtering rows in SQL conditional on a string starting or ending a certain way, or containing a substring, translated to Python's pandas

Filtering using SQL's LIKE in pandas

In SQL:

--Filtering on the end of a value:
SELECT
  *
FROM
  table
WHERE column_1 LIKE '%xample';
--Filtering on the start of a value:
SELECT
  *
FROM
  table
WHERE column_1 LIKE 'Exampl%';
--Filtering on a substring in a value:
SELECT
  *
FROM
  table
WHERE column_1 LIKE '%xampl%';

In pandas:

#Filtering on the end of a value:
table[table['column_1']
    .str
    .endswith('xample')]
#Filtering on the start of a value:
table[table['column_1']
    .str
    .startswith('Exampl')]
#Filtering on a substring in a value:
table[table['column_1']
    .str
    .contains('xampl')]

Filtering using SQL's NOT LIKE in pandas

In SQL: 
--Filtering on the end of a value:
SELECT
  *
FROM
  table
WHERE column_1 NOT LIKE '%xample';

In pandas, using ~:

#Filtering on the end of a value:
table[~table['column_1']
    .str
    .endswith('xample')]

References