Case insensitive filtering multiple columns in pandas using .loc

Question

I would like to search for values ignoring differences in cases. So for example if I type in 'fred' I would still be able to filter for all values containing Fred, even if the F is capitalized.

This is what I currently have:

def find(**kwargs):
    result = data.loc[data.rename(columns={"FirstName": "first",
                             "LastName": "last", 
                             "City": "city",
                             })[list(kwargs.keys())]
                    .eq(list(kwargs.values())).all(axis=1)]
    return result

However, I realized that I cannot use .lower() at any point to forcible lower case both the strings I am passing in and the values I am filtering for

Here is a sample of my data:

FirstName    LastName   City
Fred           Bob       Austin
Billy          Bob       NYC

when I run my function I expect this:

find('fred')
Output: Fred    Bob  Austin

May be helpful: https://stackoverflow.com/questions/17071871/how-do-i-select-rows-from-a-dataframe-based-on-column-values — Alexander Santos, Dec 15 '22 at 20:31
You can turn all search columns to lower case with `.str.lowr` https://stackoverflow.com/questions/42750551/converting-strings-to-a-lower-case-in-pandas — samusa, Dec 15 '22 at 20:38
@samusa I want to return the results how they originally looked, so don't want to lower case the dataframe — Emm, Dec 15 '22 at 20:39
May guess would be: `.apply(lambda x: x.str.lower())` on the inner dataframe selection. The 'outer' data.loc would still remain with the upper and lower case strings. — samusa, Dec 15 '22 at 20:42

score 0 · Answer 1 · answered Dec 15 '22 at 20:59

import pandas as pd

data = pd.DataFrame({"FirstName": ['Fred', 'Billy'], 'LastName':['Bob','Bob'], 'City': ['A', 'D']} )


def find(**kwargs):
    result = data.loc[data.rename(columns={"FirstName": "first",
                         "LastName": "last",
                         "City": "city",
                         })[list(kwargs.keys())].apply(lambda x: x.str.lower()).eq(list(kwargs.values())).all(axis=1)]
    return result

print(find(first='fred'))

returns

  FirstName LastName City
  0      Fred      Bob    A

constantstranger · Accepted Answer · 2022-12-15T22:28:31.357

Here are two ways to do what I believe you've asked, which is:

case-insensitive filtering of df columns FirstName, LastName and City based on any combination of the arguments first, last and city respectively.

Way #1

import pandas as pd
def find(**kwargs):
    df = ( data.rename(columns={"FirstName": "first",
                             "LastName": "last", 
                             "City": "city",
                             })[list(kwargs.keys())]
        .apply(lambda x: x.str.lower(), axis=1) )
    mask = df.eq(list(val.lower() for val in kwargs.values())).all(axis=1)
    return data[mask]

data = pd.DataFrame({'FirstName':['Fred','Billy'],'LastName':['Bob','Bob'],'City':['Austin','NYC']})

Way #2

import pandas as pd
from operator import and_
from functools import reduce
def find(**kwargs):
    df = data.rename(columns={"FirstName": "first",
                             "LastName": "last", 
                             "City": "city",
                             })[list(kwargs.keys())]

    valsLower = pd.Series([val.lower() for val in kwargs.values()], index=kwargs.keys())
    mask = reduce(and_, (df[col].str.lower() == valsLower[col] for col in df.columns))
    return data[mask]

data = pd.DataFrame({'FirstName':['Fred','Billy'],'LastName':['Bob','Bob'],'City':['Austin','NYC']})

Test code:

print( '',"data",data,sep='\n' )
print( '',"first='fred'",find(first='fred'),sep='\n' )
print( '',"first='fReD'",find(first='fred'),sep='\n' )
print( '',"last='bob'",find(last='bob'),sep='\n' )
print( '',"city='austin'",find(city='austin'),sep='\n' )
print( '',"first='fred', city='austin'",find(first='fred', city='austin'),sep='\n' )
print( '',"city='austin', first='fred'",find(first='fred', city='austin'),sep='\n' )
print( '',"last='bob', city='austin'",find(last='bob', city='austin'),sep='\n' )
print( '',"first='billy', city='austin'",find(first='billy', city='austin'),sep='\n' )

Sample Output:

data
  FirstName LastName    City
0      Fred      Bob  Austin
1     Billy      Bob     NYC

first='fred'
  FirstName LastName    City
0      Fred      Bob  Austin

first='fReD'
  FirstName LastName    City
0      Fred      Bob  Austin

last='bob'
  FirstName LastName    City
0      Fred      Bob  Austin
1     Billy      Bob     NYC

city='austin'
  FirstName LastName    City
0      Fred      Bob  Austin

first='fred', city='austin'
  FirstName LastName    City
0      Fred      Bob  Austin

city='austin', first='fred'
  FirstName LastName    City
0      Fred      Bob  Austin

last='bob', city='austin'
  FirstName LastName    City
0      Fred      Bob  Austin

first='billy', city='austin'
Empty DataFrame
Columns: [FirstName, LastName, City]
Index: []

score 0 · Answer 3 · answered Dec 15 '22 at 21:56

Using match function.

import re
from functools import reduce

def find(df, **kwargs):
    # Using AND condition. Modify & to | for OR condition.
    cond = reduce(lambda prev, x: prev & df[x[0]].str.match(f'{x[1]}', flags=re.IGNORECASE), 
                  kwargs.items(),
                  True)

    return df[cond]

find(df, FirstName='fre', LastName='bob')
#   FirstName LastName   City
# 0      Fred      Bob Austin

Case insensitive filtering multiple columns in pandas using .loc

3 Answers3