Pandas Text Filters: Count Rows, Sum Values and Find Extremes
Use str.contains() to create a text-match mask. Sum that mask to count matching rows, or apply it with df.loc[mask] before summarising a numeric column. The examples cover student marks and a validated sales selection.
Start with numeric summary methods 🔝
sum(), max(), min(), mean(), median() and mode() summarise numeric values. mode() returns a Series because more than one mode can exist.
import pandas as pd
df = pd.DataFrame({"NAME": ["Ravi", "Raju", "Alex"], "ID": [1, 2, 3], "MATH": [80, 40, 70]})
print("Sum:", df["MATH"].sum())
print("Maximum:", df["MATH"].max())
print("Minimum:", df["MATH"].min())
print("Mean:", round(df["MATH"].mean(), 2))
print("Modes:")
print(df["MATH"].mode())
print("Median:", df["MATH"].median())
assert df["MATH"].sum() == 190Expected output
Sum: 190
Maximum: 80
Minimum: 40
Mean: 63.33
Modes:
0 40
1 70
2 80
Name: MATH, dtype: int64
Median: 70.0Use the student practice dataset 🔝
The embedded data below match the student.xlsx download. Its columns are id, name, class, mark and gender. Use bracket notation for class. The notebook runs without downloading a file; for your own workbook, use read_excel() and inspect its columns before filtering.
students = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35], 'name': ['John Deo', 'Max Ruin', 'Arnold', 'Krish Star', 'John Mike', 'Alex John', 'My John Rob', 'Asruid', 'Tes Qry', 'Big John', 'Ronald', 'Recky', 'Kty', 'Bigy', 'Tade Row', 'Gimmy', 'Tumyu', 'Honny', 'Tinny', 'Jackly', 'Babby John', 'Reggid', 'Herod', 'Tiddy Now', 'Giff Tow', 'Crelea', 'Big Nose', 'Rojj Base', 'Tess Played', 'Reppy Red', 'Marry Toeey', 'Binn Rott', 'Kenn Rein', 'Gain Toe', 'Rows Noump'], 'class': ['Four', 'Three', 'Three', 'Four', 'Four', 'Four', 'Five', 'Five', 'Six', 'Four', 'Six', 'Six', 'Seven', 'Seven', 'Four', 'Four', 'Six', 'Five', 'Nine', 'Nine', 'Four', 'Seven', 'Eight', 'Seven', 'Six', 'Seven', 'Three', 'Seven', 'Seven', 'Six', 'Four', 'Seven', 'Six', 'Seven', 'Six'], 'mark': [75, 85, 55, 60, 60, 55, 78, 85, 78, 55, 89, 94, 88, 88, 88, 88, 54, 75, 18, 65, 69, 55, 79, 78, 88, 79, 81, 86, 55, 79, 88, 90, 96, 69, 88], 'gender': ['female', 'male', 'male', 'female', 'female', 'male', 'male', 'male', 'male', 'female', 'female', 'female', 'female', 'female', 'male', 'male', 'male', 'male', 'male', 'female', 'female', 'female', 'male', 'male', 'male', 'male', 'female', 'female', 'male', 'female', 'male', 'female', 'female', 'male', 'female']})
print(students.head())
print("Rows:", len(students))
assert len(students) == 35Expected output
id name class mark gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Star Four 60 female
4 5 John Mike Four 60 female
Rows: 35Optional: load the workbook instead 🔝
After uploading student.xlsx to Colab, the following optional lines replace the embedded table. Keep them commented to run the notebook without an upload. Check required columns rather than assuming old column names.
# Optional after uploading student.xlsx to Colab:
# students = pd.read_excel("student.xlsx")
required = {"id", "name", "class", "mark", "gender"}
assert required.issubset(students.columns)
print(students.columns.tolist())Expected output
['id', 'name', 'class', 'mark', 'gender']Count rows matching text 🔝
str.contains() returns a Boolean mask. Summing it counts matching rows; it does not total marks. Use regex=False for a literal search and na=False to treat missing class text as a non-match. For a single exact class, eq("Four") is often clearer.
four_mask = students["class"].str.contains("Four", regex=False, na=False)
print("Rows matching Four:", int(four_mask.sum()))
four = students.loc[four_mask].copy()
print("Total marks:", four["mark"].sum())
assert len(four) == 9 and four["mark"].sum() == 638Expected output
Rows matching Four: 9
Total marks: 638Find the maximum and all tied rows after filtering 🔝
Create each selection from the full source rather than repeatedly filtering an already reduced table. Equality with the maximum returns all tied students.
three_mask = students["class"].str.contains("Three", regex=False, na=False)
three = students.loc[three_mask].copy()
highest = three["mark"].max()
print("Maximum:", highest)
print(three.loc[three["mark"].eq(highest)])
assert highest == 85Expected output
Maximum: 85
id name class mark gender
1 2 Max Ruin Three 85 maleFind the minimum and its matching rows 🔝
Filter first and calculate the minimum from that selection. Preserve the source indices so row labels still identify the source records.
three = students.loc[students["class"].eq("Three")].copy()
lowest = three["mark"].min()
print("Minimum:", lowest)
print(three.loc[three["mark"].eq(lowest)])
assert lowest == 55Expected output
Minimum: 55
id name class mark gender
2 3 Arnold Three 55 maleCalculate mean, median and mode of the selection 🔝
Mean and median summarise numeric values. mode() may return multiple values when frequencies tie. Non-missing count reports known marks, while len() reports selected rows.
three = students.loc[students["class"].eq("Three")].copy()
print("Mean:", round(three["mark"].mean(), 2))
print("Median:", three["mark"].median())
print("Modes:")
print(three["mark"].mode())
print("Selected rows:", len(three), "Known marks:", three["mark"].count())
assert len(three) == 3 and three["mark"].median() == 81Expected output
Mean: 73.67
Median: 81.0
Modes:
0 55
1 81
2 85
Name: mark, dtype: int64
Selected rows: 3 Known marks: 3Use idxmax() with loc to retrieve a row 🔝
idxmax() returns the index label of the first maximum, not the student id or the maximum value. Check that numeric values exist and use loc with the returned label. Duplicate index labels can return multiple rows; use unique labels when retrieving a single record.
known = students.dropna(subset=["mark"])
if not known.empty:
label = known["mark"].idxmax()
print("Index label:", label)
print(known.loc[label])
assert known.loc[label, "mark"] == 96Expected output
Index label: 32
id 33
name Kenn Rein
class Six
mark 96
gender female
Name: 32, dtype: objectUse idxmin() for the first lowest row 🔝
The same index-label rule applies to idxmin(). The practice file has a minimum mark of 18. Do not compare the returned label to the id column; those are different identifiers.
known = students.dropna(subset=["mark"])
if not known.empty:
label = known["mark"].idxmin()
print("Index label:", label)
print(known.loc[label])
assert known.loc[label, "mark"] == 18Expected output
Index label: 18
id 19
name Tinny
class Nine
mark 18
gender male
Name: 18, dtype: objectSeparate unknown text from confirmed non-matches 🔝
na=False is a counting policy: missing descriptions are excluded from matches. Report them separately. Counting occurrences with str.count() is different from counting rows with contains().
text = pd.Series(["pen pen", "pencil", None, "Mug"], dtype="string")
mask = text.str.contains("pen", regex=False, na=False)
print("Matching rows:", int(mask.sum()))
print("Occurrences:", text.str.count("pen").sum())
print("Missing descriptions:", int(text.isna().sum()))
print("Known non-matches:", int((text.notna() & ~mask).sum()))
assert mask.sum() == 2 and text.str.count("pen").sum() == 3Expected output
Matching rows: 2
Occurrences: 3
Missing descriptions: 1
Known non-matches: 1Combine literal text searches with numeric conditions 🔝
Use & for AND, | for OR and parentheses around each condition. Literal terms can contain regex characters safely when regex=False. These conditions select rows rather than replacing their text.
sample = pd.DataFrame({"product": ["C++ guide", "Notebook", "C++ pocket", None], "amount": [20, 10, 5, 8]})
match = sample["product"].str.contains("C++", regex=False, na=False) & sample["amount"].ge(10)
print(sample.loc[match])
print("Matching rows:", int(match.sum()))
assert match.tolist() == [True, False, False, False]Expected output
product amount
0 C++ guide 20
Matching rows: 1Practical workflow: select paid notebook orders 🔝
This synthetic extract contains missing text and invalid amounts. Count text matches first, then distinguish paid matches from those with usable amounts. Missing numeric values must not silently become zero revenue.
sales = pd.DataFrame({"order_id": [101, 102, 103, 104, 105], "product": ["Notebook A5", "NOTEBOOK A4", None, "Notebook test", "Notebook A6"], "status": ["paid", "paid", "paid", "test", "paid"], "amount": ["12.50", "20", "9", "0", "bad"]})
product = sales["product"].astype("string").str.strip()
text_match = product.str.contains("notebook", case=False, regex=False, na=False)
paid_match = text_match & sales["status"].eq("paid")
amount = pd.to_numeric(sales["amount"], errors="coerce")
usable = paid_match & amount.notna() & amount.ge(0)
selected = sales.loc[usable].copy()
selected["amount"] = amount.loc[usable]
print("Text matches:", int(text_match.sum()))
print("Paid text matches:", int(paid_match.sum()))
print("Usable orders:", len(selected))
print("Known revenue:", selected["amount"].sum(min_count=1))
print(selected)
assert selected["order_id"].tolist() == [101, 102]
assert selected["amount"].sum() == 32.5Expected output
Text matches: 4
Paid text matches: 3
Usable orders: 2
Known revenue: 32.5
order_id product status amount
0 101 Notebook A5 paid 12.5
1 102 NOTEBOOK A4 paid 20.0Report excluded values and group accepted results 🔝
Reason counts may overlap and should not be added to calculate excluded rows. Group the selected rows only after validation. Keep invalid paid matches for review.
print("Missing product:", int(product.isna().sum()))
print("Paid matches with unusable amount:", int((paid_match & ~usable).sum()))
print("All excluded rows:", int((~usable).sum()))
print(sales.loc[paid_match & ~usable])
print(selected.groupby("status")["amount"].agg(["count", "sum", "min", "max"]))
# Optional Colab export:
# selected.to_csv("selected_notebook_orders.csv", index=False)Expected output
Missing product: 1
Paid matches with unusable amount: 1
All excluded rows: 3
order_id product status amount
4 105 Notebook A6 paid bad
count sum min max
status
paid 2 32.5 12.5 20.0Handle no matches and all-missing values 🔝
No matches and no known numeric values are different situations. sum(min_count=1) preserves an unknown total when there are no known values; max/min are missing for an empty numeric selection. Guard idxmax()/idxmin() with a non-empty known-value check.
no_match = sales.loc[product.str.contains("printer", regex=False, na=False)]
values = pd.to_numeric(no_match["amount"], errors="coerce").dropna()
print("Matched rows:", len(no_match))
print("Known values:", len(values))
print("Total requiring known data:", values.sum(min_count=1))
print("Maximum:", values.max())
assert values.empty
assert pd.isna(values.sum(min_count=1))Expected output
Matched rows: 0
Known values: 0
Total requiring known data: nan
Maximum: nanChoose a precise filter and summary 🔝
contains() finds text anywhere in a value. Use eq() for an exact category, isin() for several exact categories, and a validated numeric column for arithmetic. A Boolean mask sum counts matching rows; it is not str.count() and not a numeric total. See occurrence counting, non-missing counts and describe().
Exercise: count and total paid pen orders 🔝
Create products Blue pen, pencil, PEN refill and a missing value; statuses paid, paid, test and paid; amounts 5, 8, 2 and 4. Match pen as a whole word case-insensitively and include only paid rows. Expect one order with total five.
Exercise solution 🔝
Whole-word boundaries exclude pencil; the status excludes the test order. The missing description is a non-match.
import re
practice = pd.DataFrame({"product": ["Blue pen", "pencil", "PEN refill", None], "status": ["paid", "paid", "test", "paid"], "amount": [5, 8, 2, 4]})
mask = practice["product"].str.contains(r"\bpen\b", flags=re.IGNORECASE, na=False) & practice["status"].eq("paid")
answer = practice.loc[mask]
print(answer)
print("Orders:", int(mask.sum()), "Total:", answer["amount"].sum(min_count=1))
assert len(answer) == 1 and answer["amount"].sum() == 5Expected output
product status amount
0 Blue pen paid 5
Orders: 1 Total: 5Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change filters and inspect row counts, numeric totals and missing values. Save your own copy to keep edits. All sample tables are included in the notebook.
Continue with Data Cleaning and String Operations, Data Analysis, str.contains(), maximum values and minimum values.
Reference: Pandas Series.str.contains documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials