how to define a function that filters a data frame using another function

Question

I have to define a function that selects whoever has sales related job title from a data frame, and it has to include the function below:

def isSales(job):
    a = job.lower()
    if 'sales' in a:
        return 'True'

Basically it is filtering of a data frame, but the function will go through another function and will include the ones with job titles that has 'sales' in it.

here is a fake dataframe that you can use.

employee = {'EmployeeID' : [0,1,2,3,4,5,6,7,8,9],
         'FirstName' : ['a','b','c','d','e','f','g','h','i','j'],
         'LastName' : ['a','b','c','d','e','f','g','h','i','j'],
         'MiddleName' : ['a','b','c','d','e','f','g','h','i',None]
         'JobTitle : ['Production Supervisor', 'Technician', 'Buyer', 'Sales Manager', 'Data Scientist', 'President', 'Vice President of Sales', 'Manager', 'Stocker', 'Sales Accountant'}

employee_df = pd.DataFrame(employee)

after I define it, I have to check with this code and see if it works properly

sales_df = filterSales(employees_df)
print "Number of rows: %d\nNumber of cols: %d\n" % (sales_df.shape[0], sales_df.shape[1])
print "Head of index: %s\n" % (sales_df.index[:10])
print "Record of sales employee with ID=280\n"
print sales_df.loc[280]

Thank you so much guys.

jezrael · Accepted Answer · 2017-09-28 14:24:49Z

1

You can use boolean indexing with mask created by lower and contains:

print (employee_df[employee_df['JobTitle'].str.lower().str.contains('sales')])
   EmployeeID FirstName                 JobTitle LastName MiddleName
3           3         d            Sales Manager        d          d
6           6         g  Vice President of Sales        g          g
9           9         j         Sales Accountant        j       None

Also is possible create function, where job is column name

def isSales(job):
    return employee_df[employee_df[job].str.lower().str.contains('sales')]

print (isSales('JobTitle'))
   EmployeeID FirstName                 JobTitle LastName MiddleName
3           3         d            Sales Manager        d          d
6           6         g  Vice President of Sales        g          g
9           9         j         Sales Accountant        j       None

If input is DataFrame and need all columns and all rows contains sales:

def isSales(df):
    bool_df = df.astype(str).apply(lambda x: x.str.lower().str.contains('sales'))
    return df.loc[bool_df.any(axis=1), bool_df.any()]
    #for return all columns
    #return df[bool_df.any(axis=1)]

print (isSales(employee_df))
                  JobTitle
3            Sales Manager
6  Vice President of Sales
9         Sales Accountant

If need use your function and create another for filtering use applymap for working with each element in DataFrame for return boolean DataFrame. Last for all rows with sales in any column use any:

But your function isSales failed, if some NaNs in data.

def isSales(job):
    a = str(job).lower()
    if 'sales' in a:
        return True

def filterSales(df):
    bool_df = df.applymap(isSales)
    return df[bool_df.any(axis=1)]

sales_df = filterSales(employee_df)
print (sales_df)
   EmployeeID FirstName                 JobTitle LastName MiddleName
3           3         d            Sales Manager        d          d
6           6         g  Vice President of Sales        g          g
9           9         j         Sales Accountant        j       None

edited Sep 28, 2017 at 14:24

answered Sep 28, 2017 at 13:20

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Yun Tae Hwang Over a year ago

Thanks buddy, but if I run your code with the checking code that I just edited, it gives me error saying "'NoneType' object has no attribute 'shape'"

jezrael Over a year ago

Maybe there is problem no rows are returned?

jezrael Over a year ago

But can you add your another function to question? Because if need return boolean maybe need change only return 'True' to return True for return boolean, not string True.

Yun Tae Hwang Over a year ago

I took the last set of code and tried to run but it gives me this error.

Yun Tae Hwang Over a year ago

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe7' in position 4: ordinal not in range(128)

|

Collectives™ on Stack Overflow

how to define a function that filters a data frame using another function

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related