Skip to content

03-02: Exercises — Pandas Data Manipulation

Notes reference: 03-02: Pandas — Data Manipulation and Analysis

Q1: GroupBy — basic aggregation

Group employees by department and compute mean and max salary per department.

Solution 

import pandas as pd

df = pd.DataFrame({
    "name":       ["Rahim", "Sara", "James", "Nadia", "Michael", "Amina"],
    "department": ["Engineering", "Marketing", "Engineering", "HR", "Marketing", "Engineering"],
    "city":       ["Dhaka", "New York", "Berlin", "Tokyo", "Nairobi", "Dhaka"],
    "salary":     [85000, 72000, 90000, 68000, 75000, 92000],
    "years":      [3, 5, 7, 2, 4, 6],
})

print(df.groupby("department")["salary"].mean())
print(df.groupby("department")["salary"].agg(["mean", "min", "max", "count"]))

Q2: GroupBy — multiple aggregations with named results

Use .agg() with custom result column names.

Solution 

import pandas as pd

df = pd.DataFrame({
    "department": ["Engineering", "Marketing", "Engineering", "HR", "Marketing", "Engineering"],
    "salary":     [85000, 72000, 90000, 68000, 75000, 92000],
    "years":      [3, 5, 7, 2, 4, 6],
})

result = df.groupby("department").agg(
    avg_salary = ("salary", "mean"),
    max_salary = ("salary", "max"),
    headcount  = ("salary", "count"),
    avg_years  = ("years",  "mean"),
)
print(result)

Q3: Merge DataFrames — inner and left join

Merge a students table with a departments table on dept_id.

Solution 

import pandas as pd

students = pd.DataFrame({
    "id":      [1, 2, 3, 4],
    "name":    ["Rahim", "Sara", "James", "Nadia"],
    "dept_id": [02, 20, 02, 30],
})

depts = pd.DataFrame({
    "dept_id": [02, 20],
    "dept":    ["Engineering", "Marketing"],
})

inner = pd.merge(students, depts, on="dept_id", how="inner")
left  = pd.merge(students, depts, on="dept_id", how="left")  # Nadia gets NaN

print("INNER:\n", inner)
print("\nLEFT:\n",  left)

Q4: Concatenate DataFrames

Combine two DataFrames of city populations (different cities) row-wise.

Solution 

import pandas as pd

bangladesh = pd.DataFrame({
    "city":   ["Dhaka", "Chittagong", "Sylhet"],
    "country":["Bangladesh"] * 3,
    "pop_M":  [21.0, 4.0, 3.5],
})

usa = pd.DataFrame({
    "city":   ["New York", "Los Angeles", "Chicago"],
    "country":["USA"] * 3,
    "pop_M":  [8.3, 4.0, 2.7],
})

combined = pd.concat([bangladesh, usa], ignore_index=True)
print(combined)

Q5: Pivot table

Create a pivot table showing average score per student per subject.

Solution 

import pandas as pd

data = pd.DataFrame({
    "student": ["Rahim", "Rahim", "Sara", "Sara", "James", "James"],
    "subject": ["Math", "Science", "Math", "Science", "Math", "Science"],
    "score":   [82, 91, 90, 85, 74, 78],
})

pivot = pd.pivot_table(data, values="score", index="student",
                       columns="subject", aggfunc="mean")
print(pivot)

Q6: melt — wide to long

Reshape a wide student scores table into a long format.

Solution 

import pandas as pd

wide = pd.DataFrame({
    "name":    ["Rahim", "Sara", "James"],
    "Math":    [82, 90, 74],
    "English": [75, 88, 80],
    "Science": [91, 85, 78],
})

long = wide.melt(id_vars="name", var_name="subject", value_name="score")
print(long)

# Pivot back
pivoted = long.pivot(index="name", columns="subject", values="score")
print(pivoted)

Q7: String operations with .str

Clean and split a name column, extract email domains.

Solution 

import pandas as pd

df = pd.DataFrame({
    "full_name": ["  Rahim Hossain ", "SARA KHAN", "james berlin"],
    "email":     ["rahim@gmail.com", "sara@company.org", "james@outlook.com"],
})

# Clean and normalize name
df["full_name"] = df["full_name"].str.strip().str.title()

# Split into first and last
df[["first", "last"]] = df["full_name"].str.split(" ", expand=True)

# Extract email domain
df["domain"] = df["email"].str.split("@").str[1]

print(df)

Q8: Missing values — detect, fill, drop

Introduce NaN values, detect them, fill with mean, and drop rows with remaining NaN.

Solution 

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name":   ["Rahim", "Sara", "James", "Nadia"],
    "score":  [85.0, np.nan, 78.0, np.nan],
    "city":   ["Dhaka", np.nan, "Berlin", "Tokyo"],
})

print("Null counts:\n", df.isnull().sum())

# Fill numeric with mean
df["score"] = df["score"].fillna(df["score"].mean())

# Drop rows where city is still NaN
df = df.dropna(subset=["city"])

print("\nCleaned:\n", df)

Q9: Apply a custom function

Use .apply() to assign a letter grade based on score.

Solution 

import pandas as pd

df = pd.DataFrame({
    "name":  ["Rahim", "Sara", "James", "Nadia", "Michael"],
    "score": [82, 91, 68, 75, 95],
})

def letter_grade(score):
    if score >= 90: return "A"
    if score >= 80: return "B"
    if score >= 70: return "C"
    return "F"

df["grade"] = df["score"].apply(letter_grade)
print(df)

Q10: Complete mini-analysis

Load a CSV, filter, group, sort, and export the result.

Solution 

import pandas as pd

# Create sample data
df = pd.DataFrame({
    "name":       ["Rahim", "Sara", "James", "Nadia", "Michael", "Amina", "Karim", "Farida"],
    "department": ["Eng", "Mkt", "Eng", "HR", "Mkt", "Eng", "HR", "Mkt"],
    "city":       ["Dhaka", "New York", "Berlin", "Tokyo", "Nairobi", "Dhaka", "Tokyo", "New York"],
    "salary":     [85000, 72000, 90000, 68000, 75000, 92000, 65000, 78000],
})

# 1. Filter — only Engineering and Marketing
dept_filter = df[df["department"].isin(["Eng", "Mkt"])].copy()

# 2. Add bonus column
dept_filter["bonus"] = (dept_filter["salary"] * 0.02).round(0).astype(int)

# 3. Group — average salary per department
summary = dept_filter.groupby("department")["salary"].agg(
    avg=("mean"), max=("max"), count=("count")
)
print("Department summary:\n", summary)

# 4. Sort by salary
top = dept_filter.sort_values("salary", ascending=False).head(5)
print("\nTop 5 earners:\n", top[["name", "department", "salary"]])

# 5. Save
dept_filter.to_csv("dept_report.csv", index=False)
print("\nSaved to dept_report.csv")

⬅️ Previous: 03-01: Exercises — Pandas Basics 📚 Back to Notes Index