DataFrame Manipulation: Filtering, Sorting, and Grouping

After Completing This Topic

You will be able to extract, sort, and aggregate data by groups in pandas DataFrames based on conditions.

Preparing the Data

python

import pandas as pd

df = pd.DataFrame({
    "Name": ["Kim Hoon", "Lee Soo", "Park Jin", "Choi Young", "Jeong Min"],
    "Department": ["Development", "Marketing", "Development", "Marketing", "Development"],
    "Age": [30, 25, 35, 28, 32],
    "Salary": [5000, 3500, 6000, 4000, 5500]
})

You can master all the key techniques using this table with 5 rows.

Column Selection

python

# Single column — returns a Series
df["Name"]

# Multiple columns — returns a DataFrame
df[["Name", "Salary"]]

# Add a new column
df["NetSalary"] = df["Salary"] * 0.85

# Delete a column
df = df.drop(columns=["NetSalary"])

Pay attention to the difference between using a single bracket (df["Name"]) and double brackets (df[["Name", "Salary"]]). One returns a Series, and the other returns a DataFrame.

Filtering: Extracting Rows Based on Conditions

python

# Single condition
df[df["Age"] >= 30]
#      Name Department  Age  Salary
# 0  Kim Hoon  Development   30   5000
# 2  Park Jin  Development   35   6000
# 4  Jeong Min  Development   32   5500

# Compound condition — & (AND), | (OR)
df[(df["Department"] == "Development") & (df["Salary"] >= 5500)]
#      Name Department  Age  Salary
# 2  Park Jin  Development   35   6000
# 4  Jeong Min  Development   32   5500

# String contains search
df[df["Name"].str.contains("Kim")]

# Matches one of the values in a list
df[df["Department"].isin(["Development", "Planning"])]

Enclose the conditions in parentheses () and connect them with & or |. This is different from Python's and/or — when filtering DataFrames, you must use &/|.

Sorting

python

# Based on a single column
df.sort_values("Salary")              # Ascending order (low → high)
df.sort_values("Salary", ascending=False)  # Descending order

# Based on multiple columns
df.sort_values(["Department", "Salary"], ascending=[True, False])
# Department in ascending order → within the same department, salary in descending order

# Reset the index (after sorting, the index gets mixed up)
df_sorted = df.sort_values("Salary").reset_index(drop=True)

If you don't include reset_index(drop=True), the original index will remain, resulting in an order like 0, 3, 1, 4, 2.

Group Aggregation — groupby

This is one of the most powerful tools for data analysis. It answers questions like "What is the average salary by department?" in a single line.

python

# Basic — average by department
df.groupby("Department")["Salary"].mean()
# Department
# Development    5500.0
# Marketing      3750.0

# Multiple aggregation functions
df.groupby("Department")["Salary"].agg(["mean", "min", "max", "count"])
#           mean   min   max  count
# Department
# Development    5500  5000  6000      3
# Marketing      3750  3500  4000      2

# Aggregate multiple columns
df.groupby("Department").agg({
    "Salary": "mean",
    "Age": "max"
})
#           Salary  Age
# Department
# Development    5500   35
# Marketing      3750   28

groupby is the same concept as GROUP BY in SQL. It works with the split → apply → combine pattern.

Practical Pattern: Combined Manipulation

python

# "People in the Development department with a salary of 5000 or more, sorted by salary in descending order"
result = (
    df[df["Department"] == "Development"]
    .query("Salary >= 5000")
    .sort_values("Salary", ascending=False)
    .reset_index(drop=True)
)

# Number of people and average salary per department at once
summary = (
    df.groupby("Department")
    .agg(
        NumPeople=("Name", "count"),
        AverageSalary=("Salary", "mean"),
        MaxSalary=("Salary", "max")
    )
    .sort_values("AverageSalary", ascending=False)
)

.query() is a way to write conditions as strings. It is more readable than df[(df["a"] > 1) & (df["b"] < 5)] for complex conditions.

Commonly Used Methods Summary

Task	Method	Example
Conditional filtering	`df[condition]`	`df[df["age"] > 30]`
Column selection	`df[["a", "b"]]`	`df[["Name", "Salary"]]`
Sorting	`sort_values()`	`df.sort_values("Salary")`
Group aggregation	`groupby().agg()`	`df.groupby("Department")["Salary"].mean()`
Top N	`nlargest()` / `nsmallest()`	`df.nlargest(3, "Salary")`
Unique values	`unique()` / `nunique()`	`df["Department"].nunique()`
Value distribution	`value_counts()`	`df["Department"].value_counts()`

Key Takeaways

DataFrame manipulation is the process of expressing "what you want to see" in code. Filter (which rows?), sort (in what order?), group (by what criteria?), aggregate (what to calculate?) — you can answer most data questions using just these four verbs. If you know SQL, you can think of it as a 1:1 correspondence with SELECT ... WHERE ... GROUP BY ... ORDER BY.

BioPlayground

DataFrame Manipulation — Filtering, Sorting, Grouping

DataFrame Manipulation: Filtering, Sorting, and Grouping

After Completing This Topic

Preparing the Data

Column Selection

Filtering: Extracting Rows Based on Conditions

Sorting

Group Aggregation — groupby

Practical Pattern: Combined Manipulation

Commonly Used Methods Summary

Key Takeaways

💬 Questions & Comments