Programmer's Picnic · Pandas 0 to Infinity · Level 4

Sort, Group and Summarise DataFrames

Move beyond individual records: rank students, compare cities, calculate course averages and produce clear reports using sort_values(), groupby() and agg().

Beginner → Intermediate · 12 guided steps · No CSV or database needed

What you will build

A student performance report containing an overall summary, top-five leaderboard, city and course summaries, city–course breakdown, and top two performers in each city.

All records are created directly in Python. There are 12 students, and one student has no recorded mark. That missing value is intentional: it helps demonstrate that count() and size() answer different questions.

City	Number of students	Students with marks	Mean marks
Patna	3	2	90.50
Ranchi	4	4	62.75
Varanasi	5	5	80.40

Prerequisite: Level 3 covered cleaning. This level assumes scores have been validated. Missing scores are left as missing, never silently replaced with zero. Install Pandas with python -m pip install pandas.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

print(students)

Learning route

Sorting Quick summaries GroupBy Multiple groups Transform Reports Mini-project Exercises

Copy any example to your Python editor. Every step recreates the same sample table, so the snippets work independently.

Step 1 of 12

Sort a DataFrame with sort_values()

Sort ascending or descending by a column. The returned table is sorted; the original is not changed unless you assign the result.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

by_marks = students.sort_values("Marks", ascending=False, na_position="last")
print(by_marks[["Name", "Marks"]].head(5))
print("Original first student:", students.loc[0, "Name"])

Remember: Arjun's missing mark stays at the bottom with na_position="last". Highest is Pooja with 95.

Step 2 of 12

Sort by more than one column

First sort by City alphabetically; within each city sort marks descending; use name to break ties consistently.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

ordered = students.sort_values(
    ["City", "Marks", "Name"],
    ascending=[True, False, True],
    na_position="last"
)
print(ordered[["City", "Name", "Marks"]])

Remember: The ascending list corresponds, in order, to the listed sort columns.

Step 3 of 12

Sort by index and reset row labels

Sorting values preserves each original row label. You can reset the labels for presentation, or use sort_index() to recover index order.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

ranked = students.sort_values("Marks", ascending=False)
print(ranked[["Name", "Marks"]].head(3))
print(ranked.sort_index()[["Name", "Marks"]].head(3))
print(ranked.reset_index(drop=True)[["Name", "Marks"]].head(3))

Remember: sort_index() orders row labels, not values. reset_index(drop=True) creates new consecutive labels.

Step 4 of 12

Compute whole-column statistics

You already know how to select columns. Now summarise them using sum(), mean(), median(), min(), max() and describe().

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

print("Mean marks:", round(students["Marks"].mean(), 2))
print("Median:", students["Marks"].median())
print("Highest:", students["Marks"].max())
print("Lowest:", students["Marks"].min())
print("Study hours:", students["Hours"].sum())
print(students[["Marks", "Hours"]].describe().round(2))

Remember: For numeric data, these summary functions skip missing values by default. Mean marks are approximately 75.82.

Step 5 of 12

Understand count() versus size()

len(df) and groupby().size() count rows; count() counts nonmissing entries in the selected column.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

print("All rows:", len(students))             # 12
print("Marks recorded:", students["Marks"].count())  # 11
print("Missing marks:", students["Marks"].isna().sum())  # 1
print("City row counts:")
print(students.groupby("City").size())
print("City nonmissing marks:")
print(students.groupby("City")["Marks"].count())

Remember: Patna has 3 students but only 2 recorded scores. Its average uses 2 scores—not a made-up zero for Arjun.

Step 6 of 12

See how many students belong to each category

Use value_counts() for simple frequencies. The example also introduces quick proportions.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

print(students["City"].value_counts())
print(students["Course"].value_counts())
print("City proportions:")
print((students["City"].value_counts(normalize=True) * 100).round(1))

Remember: The category counts include only nonmissing category values unless you set dropna=False.

Step 7 of 12

Group by a column

groupby("City") creates groups of rows that share the same city. Select Marks and calculate a separate average for each.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

by_city = students.groupby("City")["Marks"].mean()
print(by_city.round(2))
print("Students per course:")
print(students.groupby("Course").size())

Remember: groupby() constructs grouped operations; it does not create multiple independent Python DataFrames by itself.

Step 8 of 12

Calculate several statistics using agg()

Named aggregation gives descriptive column names without complex renaming. With as_index=False, the group label stays a normal column.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

city_report = (
    students.groupby("City", as_index=False)
    .agg(
        Students=("StudentID", "size"),
        ValidMarks=("Marks", "count"),
        AverageMarks=("Marks", "mean"),
        BestMarks=("Marks", "max"),
        TotalHours=("Hours", "sum")
    )
    .round({"AverageMarks": 2})
    .sort_values("AverageMarks", ascending=False)
)
print(city_report)

Remember: size is a row count, while count excludes missing values in the specified column.

Step 9 of 12

Group by two columns

Summaries can answer narrower questions such as: How many students took each course in each city?

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

city_course = (
    students.groupby(["City", "Course"], as_index=False)
    .agg(
        Students=("StudentID", "size"),
        AverageMarks=("Marks", "mean")
    )
    .round({"AverageMarks": 2})
)
print(city_course)
print("Total city-course combinations:", len(city_course))

Remember: When a group has only missing marks, its mean is missing; do not treat it as zero.

Step 10 of 12

Keep every row using transform()

agg() usually reduces each group to a summary row. transform() produces a value aligned to each original row—great for comparing people with their city average.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

students["CityAverage"] = (
    students.groupby("City")["Marks"].transform("mean").round(2)
)
students["AboveCityAverage"] = (
    students["Marks"].gt(students["CityAverage"])
    .where(students["Marks"].notna(), pd.NA)
)
print(students[["Name", "City", "Marks", "CityAverage", "AboveCityAverage"]])

Remember: Arjun has no recorded mark: his comparison should remain missing instead of being classified as above or below average.

Step 11 of 12

Find top performers in each group

To get the top two in each city, sort first and then use groupby().head(2). This is not the same as taking the top two in the entire table.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

top_two = (
    students.sort_values(
        ["City", "Marks", "Name"],
        ascending=[True, False, True],
        na_position="last"
    )
    .groupby("City", sort=False)
    .head(2)[["City", "Name", "Marks"]]
    .reset_index(drop=True)
)
print(top_two)

Remember: The head(2) method returns up to two rows per group; if a group has fewer than two valid marks, filter them out first if required.

Step 12 of 12

Put summaries together into a report

Build separate tables for overall statistics, city averages and course averages. Sort each summary in a useful order.

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

overview = {
    "Students": len(students),
    "RecordedMarks": students["Marks"].count(),
    "AverageMarks": round(students["Marks"].mean(), 2),
    "TotalHours": students["Hours"].sum(),
}
courses = (
    students.groupby("Course", as_index=False)
    .agg(Students=("StudentID", "size"), AverageMarks=("Marks", "mean"))
    .round({"AverageMarks": 2})
    .sort_values("AverageMarks", ascending=False)
)
print("Overview:", overview)
print(courses)

Remember: You can build a report entirely in memory. Data export and visualisation come later in the course.

Complete beginner project

Student Performance Report — runnable Python project

Run one standalone program to produce seven results: overview, top-five leaderboard, city summary, course summary, city-course groups, top two per city, and every student's comparison with their city average. It retains the original DataFrame and checks important input rules.

Open student_report.py · View unit tests

Python · pandas

"""Pandas 0 to Infinity — Level 4: sorting and summarising DataFrames.

Run directly: python python/pandas/04-sort-group-summarize/student_report.py
No CSV, Excel or database file is required.
"""

import pandas as pd


def make_students():
    """Create clean, validated sample data with one legitimately missing mark."""
    return pd.DataFrame([
        (101, "Asha", "Varanasi", "Python", 88, 6),
        (102, "Ravi", "Ranchi", "Python", 42, 4),
        (103, "Meera", "Varanasi", "Pandas", 76, 8),
        (104, "Kabir", "Patna", "Pandas", 91, 7),
        (105, "Nina", "Ranchi", "Python", 84, 5),
        (106, "Ishan", "Varanasi", "SQL", 67, 3),
        (107, "Tara", "Patna", "SQL", 90, 9),
        (108, "Rahul", "Varanasi", "Pandas", 76, 6),
        (109, "Sita", "Ranchi", "Pandas", 55, 4),
        (110, "Arjun", "Patna", "Python", None, 2),
        (111, "Pooja", "Varanasi", "SQL", 95, 8),
        (112, "Dev", "Ranchi", "SQL", 70, 6),
    ], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])


def make_report(source):
    """Return independently useful report tables without changing input data."""
    df = source.copy(deep=True)
    required = {"StudentID", "Name", "City", "Course", "Marks", "Hours"}
    if not required.issubset(df.columns):
        raise ValueError("Missing required report columns")
    if df["StudentID"].duplicated().any():
        raise ValueError("StudentID must be unique; review duplicate records")
    if not pd.api.types.is_numeric_dtype(df["Marks"]):
        raise TypeError("Marks must be numeric; clean input before grouping")
    if not pd.api.types.is_numeric_dtype(df["Hours"]):
        raise TypeError("Hours must be numeric; clean input before grouping")
    if not df["Marks"].dropna().between(0, 100).all():
        raise ValueError("Marks must be between 0 and 100")
    if not df["Hours"].dropna().ge(0).all():
        raise ValueError("Hours cannot be negative")

    leaderboard = (
        df.sort_values(
            ["Marks", "Name"], ascending=[False, True], na_position="last"
        )[["Name", "City", "Course", "Marks"]]
        .head(5)
        .reset_index(drop=True)
    )

    city_report = (
        df.groupby("City", as_index=False)
        .agg(
            Students=("StudentID", "size"),
            ValidMarks=("Marks", "count"),
            AverageMarks=("Marks", "mean"),
            HighestMarks=("Marks", "max"),
            TotalHours=("Hours", "sum"),
        )
        .round({"AverageMarks": 2})
        .sort_values(["AverageMarks", "City"], ascending=[False, True])
        .reset_index(drop=True)
    )

    course_report = (
        df.groupby("Course", as_index=False)
        .agg(
            Students=("StudentID", "size"),
            ValidMarks=("Marks", "count"),
            AverageMarks=("Marks", "mean"),
            TotalHours=("Hours", "sum"),
        )
        .round({"AverageMarks": 2})
        .sort_values(["AverageMarks", "Course"], ascending=[False, True])
        .reset_index(drop=True)
    )

    city_course = (
        df.groupby(["City", "Course"], as_index=False)
        .agg(Students=("StudentID", "size"), AverageMarks=("Marks", "mean"))
        .round({"AverageMarks": 2})
        .sort_values(["City", "Course"])
        .reset_index(drop=True)
    )

    top_two_per_city = (
        df.sort_values(
            ["City", "Marks", "Name"],
            ascending=[True, False, True],
            na_position="last",
        )
        .groupby("City", sort=False)
        .head(2)[["City", "Name", "Marks"]]
        .reset_index(drop=True)
    )

    compared = df.copy()
    compared["CityAverage"] = (
        compared.groupby("City")["Marks"].transform("mean").round(2)
    )
    compared["AboveCityAverage"] = (
        compared["Marks"].gt(compared["CityAverage"])
        .where(compared["Marks"].notna(), pd.NA)
    )

    overview = {
        "total_students": int(len(df)),
        "marks_recorded": int(df["Marks"].count()),
        "marks_missing": int(df["Marks"].isna().sum()),
        "overall_average": float(round(df["Marks"].mean(), 2)),
        "total_study_hours": float(df["Hours"].sum()),
    }

    return {
        "overview": overview,
        "leaderboard": leaderboard,
        "city_report": city_report,
        "course_report": course_report,
        "city_course": city_course,
        "top_two_per_city": top_two_per_city,
        "compared": compared,
    }


if __name__ == "__main__":
    students = make_students()
    report = make_report(students)
    print("OVERVIEW")
    for label, value in report["overview"].items():
        print(f"{label}: {value}")
    for title in (
        "leaderboard", "city_report", "course_report",
        "city_course", "top_two_per_city",
    ):
        print("\n" + title.upper())
        print(report[title].to_string(index=False))
    print("\nCITY AVERAGE COMPARISON")
    print(report["compared"][["Name", "City", "Marks", "CityAverage", "AboveCityAverage"]].to_string(index=False))

Expected report highlights

Indicator	Expected result
Students	12
Marks recorded / missing	11 / 1
Overall average	75.82
Total study hours	68
Top performer	Pooja — 95
Highest city average	Patna — 90.50, based on 2 recorded marks
Highest course average	SQL — 80.50

Run: python python/pandas/04-sort-group-summarize/student_report.py. Tests: python -m unittest discover -s python/pandas/04-sort-group-summarize -p "test_*.py".

Practice tasks

Challenge 1 — Find the top three students

Sort by marks descending and show only names and marks. Do not discard the missing score from the source data.

Reveal a solution

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

answer = students.sort_values("Marks", ascending=False, na_position="last").head(3)
print(answer[["Name", "Marks"]])

Challenge 2 — Course attendance

For each course, show the number of students and total hours, with the busiest course first.

Reveal a solution

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

answer = (students.groupby("Course", as_index=False)
          .agg(Students=("StudentID", "size"), TotalHours=("Hours", "sum"))
          .sort_values("TotalHours", ascending=False))
print(answer)

Challenge 3 — Summaries with missing marks

Group by city and compare total rows versus recorded marks. What explains the difference for Patna?

Reveal a solution

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

answer = students.groupby("City").agg(
    Students=("StudentID", "size"), ValidMarks=("Marks", "count")
)
answer["MissingMarks"] = answer["Students"] - answer["ValidMarks"]
print(answer)  # Patna: 1 missing mark

Challenge 4 — Above the city average

Add each city's mean to individual rows and show students strictly above their city mean.

Reveal a solution

Python · pandas

import pandas as pd

students = pd.DataFrame([
    (101, "Asha", "Varanasi", "Python", 88, 6),
    (102, "Ravi", "Ranchi", "Python", 42, 4),
    (103, "Meera", "Varanasi", "Pandas", 76, 8),
    (104, "Kabir", "Patna", "Pandas", 91, 7),
    (105, "Nina", "Ranchi", "Python", 84, 5),
    (106, "Ishan", "Varanasi", "SQL", 67, 3),
    (107, "Tara", "Patna", "SQL", 90, 9),
    (108, "Rahul", "Varanasi", "Pandas", 76, 6),
    (109, "Sita", "Ranchi", "Pandas", 55, 4),
    (110, "Arjun", "Patna", "Python", None, 2),
    (111, "Pooja", "Varanasi", "SQL", 95, 8),
    (112, "Dev", "Ranchi", "SQL", 70, 6),
], columns=["StudentID", "Name", "City", "Course", "Marks", "Hours"])

students["CityMean"] = students.groupby("City")["Marks"].transform("mean")
print(students.loc[students["Marks"] > students["CityMean"],
                   ["Name", "City", "Marks", "CityMean"]])

What you have learned

Sort without changing the source; summarise numeric columns; distinguish row counts from valid entries; calculate category frequencies; group by one or more columns; create named aggregations; use transform(); and produce rankings for each group.

Read more in the official documentation: GroupBy guide · sort_values · GroupBy.agg.

Level 5 is next: reading and writing CSV files with read_csv() and to_csv(). The separate Pandas with SQL lesson remains available as an advanced reference.

← Level 3: Cleaning Course index Level 5: CSV I/O →