Lecture 10
pandas Basics - Filtering by a Condition
February 19, 2025
Employment Data
Filtering by a Condition
Filtering by a Condition
We may often not know the index labels and positions of the observations we want to target.
We may want to target observations not by an index label but by a Boolean condition.
Filtering by a Single Condition
- To compare every value in
Serieswith a constant value, we place theSerieson one side of the equality operator (==) and the value on the other.Series == value
- The above example compares each
First Namevalue with “Donna”.- pandas performs a vectorized operation (element-by-element operation) on
Series.
- pandas performs a vectorized operation (element-by-element operation) on
Filtering by a Single Condition
- To filter observations, we provide the Boolean
Seriesbetween square brackets following theDataFrame.DataFrame[ Boolean_Series ]
Filtering by a Single Condition
- If the use of multiple square brackets is confusing, we can assign the Boolean
Seriesto an object and then pass it into the square brackets instead.
Filtering by a Single Condition
- What if we want to extract a subset of employees who are not on the “
Marketing” team?
Truedenotes that theTeamvalue for a given index is not “Marketing”, andFalseindicates theTeamvalue is “Marketing”
Filtering by a Single Condition
- What if we want to retrieve all the managers in the company?
- Managers have a value of
Truein theMgmtvariable.
- Managers have a value of
- We could execute
emp["Mgmt"] == True, but we do not need to.
Filtering by a Single Condition
- We can also use arithmetic operands to filter observations based on mathematical conditions.
Filtering by a Condition
sales = emp["Team"] == "Sales"
legal = emp["Team"] == "Legal"
fnce = emp["Team"] == "Finance"
emp[ sales | legal | fnce ] # '|' is 'or' opeartorWe could provide three separate Boolean
Seriesinside the square brackets and add the|symbol to declareORcriteria.What if our next report asked for employees from 30 teams instead of three?
Filtering with the isin() method
star_teams = ["Sales", "Legal", "Finance"]
on_star_teams = emp["Team"].isin(star_teams)
emp[ on_star_teams ]- A better solution is the
isin()method, which accepts an iterable (e.g.,list,tuple,array,Series) and returns a BooleanSeries.
Filtering by a Condition
- When working with numbers or dates, we often want to extract values that fall within a range.
- E.g., Identify all employees with a salary between $90,000 and $100,000.
higher_than_90k = emp["Salary"] >= 90000
lower_than_100k = emp["Salary"] < 100000
emp[ higher_than_90k & lower_than_100k ] # '&' is 'and' opeartorWe can create two Boolean
Series, one to declare the lower bound and one to declare the upper bound.Then we can use the
&operator to mandate that both conditions areTrue.
Filtering with the between() method
- A slightly cleaner solution is to use a method called
between().- It returns a Boolean Series where
Truedenotes that an observation’s value falls between the specified interval. - The first argument, the lower bound, is inclusive, and the second argument, the upper bound, is also inclusive.
- It returns a Boolean Series where
Filtering with the between() method
- We can also apply the
between()method to string variables.- The first argument, the lower bound, is inclusive, and the second argument, the upper bound, is exclusive.
Filtering by a Condition with the query() method!
emp.query("Salary >= 100000 & Team == 'Finance'")
emp.query("Salary >= 100000 & `First Name` == 'Douglas'")- The
query()method filters observations using a concise, string-based query syntax.query()accepts a string value that describes filtering conditions.
- When using the
query()method, if we have variable names with spaces, we can wrap the variable names in backtick (`)
Adding a Variable based on a Condition using np.where()
- We can use
np.wherefrom NumPy to add a new variable to a DataFrame based on a condition.
import numpy as np
# Using np.where to add the 'pass_fail' column
emp['high_salary'] = np.where(emp['Salary'] >= 100000, 'Yes', 'No')- We want to add a new variable
high_salary:- “Yes” if
scoreis greater than or equal to 100,000. - “No” otherwise.
- “Yes” if
Pandas Basics
Let’s do Questions 2-6 in Classwork 6!
Lecture 10 pandas Basics - Filtering by a Condition Byeong-Hak Choe bchoe@geneseo.edu SUNY Geneseo February 19, 2025