In this practical lesson, we will learn GroupBy in Pandas by working with one realistic sales dataset from beginning to end. Instead of memorizing isolated commands, you will learn how a Data Analyst converts a business question into a Pandas GroupBy operation, checks the result, and turns the result into a useful business insight.
We will first create and inspect our dataset. Then we will answer practical questions such as total sales by city, average order value, number of orders, unique customers, sales by category, sales by salesperson, and city-category performance. Later, we will use agg(), transform(), percentages, and group-wise ranking.
The complete learning process is:
Sales Dataset
↓
Load Data into Pandas
↓
Understand Columns and Rows
↓
Ask a Business Question
↓
Choose Grouping Column
↓
Choose Measure
↓
Choose Aggregation
↓
Create Result
↓
Validate Result
↓
Interpret Business InsightBefore learning GroupBy, we need data on which the concept can actually be practiced. The accompanying CSV file for this lesson contains 60 sales transactions. You can download it and use it with your Python notebook.
One of the most important questions in Data Analytics is: What does one row represent?
In our dataset, one row represents one customer order. Therefore, if the dataset contains 60 rows, we have 60 order records.
This matters because all later calculations depend on the grain of the data. If one row represents an order, counting rows can represent the number of orders. If one row represented a customer, counting rows would mean something completely different.
The dataset contains these columns:
order_id — unique order identifier.order_date — date on which the order was placed.customer_id — customer identifier.customer_name — customer label.city — city associated with the order.state — state or territory.category — product category.product — individual product.salesperson — salesperson responsible for the order.quantity — number of units in the order.unit_price — price per unit.sales — total value of the order.payment_method — payment method used.Because the dataset contains several dimensions and measures, it is suitable for many GroupBy exercises.
Place the downloaded CSV file in your working folder. Then open Jupyter Notebook, VS Code, Google Colab, or another Python environment and run:
import pandas as pd
df = pd.read_csv(
"Chapter_3_Lesson_1_GroupBy_Sales_Dataset.csv"
)
print(df.head())The read_csv() function loads the CSV into a Pandas DataFrame named df.
Do not immediately start calculating. First understand the source.
print(df.shape)The dataset contains 60 rows and 13 columns.
Check the data types and non-null values:
df.info()Check the first ten records:
print(df.head(10))Check the column names:
print(df.columns.tolist())Check missing values:
print(df.isna().sum())This inspection step is not separate from analysis. It is part of analysis. If you do not understand the data before grouping it, you may produce a mathematically correct result that answers the wrong question.
GroupBy becomes much easier when you stop thinking of it as a complicated Pandas function and instead think about the business question.
Suppose the sales manager asks:
“What is the total sales generated by each city?”
Break the question into three parts:
Group:
city
Measure:
sales
Calculation:
sumThat gives us the structure:
groupby("city")["sales"].sum()This simple habit will help you solve many GroupBy problems without memorizing dozens of commands.
GroupBy follows a concept commonly described as split-apply-combine.
Split: divide the dataset into groups.
Apply: calculate something separately for each group.
Combine: place the results into a summary table.
For example, if our dataset contains orders from Dehradun, Delhi, Haridwar, Noida, and Chandigarh, GroupBy can logically split the rows into those five city groups.
When we use sum(), Pandas adds the sales values inside each group and returns one total for every city.
Businesses rarely ask for every individual transaction. They ask for summaries.
These are all grouped analytical questions.
Now we will perform our first real GroupBy analysis using the dataset.
Our question is:
Which cities generated the highest total sales?
We need:
Group = city
Measure = sales
Aggregation = sumcity_sales = (
df.groupby("city")["sales"]
.sum()
)
print(city_sales)Read the code from left to right.
df.groupby("city") tells Pandas to create groups based on city.
["sales"] tells Pandas that we want to analyze the sales column.
.sum() adds the sales values belonging to each city.
The result above is a Series. For reporting, a DataFrame is usually more convenient.
city_sales = (
df.groupby("city")["sales"]
.sum()
.reset_index(name="total_sales")
)
print(city_sales)Now the output has two columns:
city
total_salesEach row represents one city and its total sales.
To identify the highest-performing city:
city_sales = city_sales.sort_values(
by="total_sales",
ascending=False
)
print(city_sales)The first row is now the city with the highest total sales.
Business interpretation: This report gives management a city-level view of sales performance. It does not yet explain why one city performed better. For that, we need additional metrics such as order count, customer count, product mix, and average order value.
Now ask a different question:
“What is the average value of an order in each city?”
city_average = (
df.groupby("city")["sales"]
.mean()
.reset_index(name="average_order_value")
)
print(city_average)The grouping column is still city and the measure is still sales. Only the aggregation has changed.
sum()
↓
Total
mean()
↓
AverageSuppose City A has 100 orders and City B has 20 orders. City A may have higher total sales because it has much more transaction volume. But City B may have a higher average order value.
Therefore, total sales alone should not always be used to judge performance.
city_average = city_average.sort_values(
by="average_order_value",
ascending=False
)
print(city_average)Now the city with the highest average transaction value appears first.
Next question:
“How many orders did each city generate?”
orders_by_city = (
df.groupby("city")["order_id"]
.count()
.reset_index(name="orders")
)
print(orders_by_city)Because every order has an order ID, this counts the number of order records in each city.
orders_by_city = (
df.groupby("city")
.size()
.reset_index(name="orders")
)
print(orders_by_city)size() counts rows in each group. This can be useful when you want the number of records regardless of whether a particular column contains missing values.
count()
→ counts non-missing values in a selected column
size()
→ counts rows in each groupThis distinction becomes important when your source data contains missing values.
Now the question becomes:
“How many different customers purchased from each city?”
This is not the same as counting orders.
One customer can place multiple orders. If Customer 205 places five orders, the order count increases by five, but the unique customer count increases by only one.
Use nunique():
customers_by_city = (
df.groupby("city")["customer_id"]
.nunique()
.reset_index(name="unique_customers")
)
print(customers_by_city)count()
→ counts records
nunique()
→ counts distinct valuesThis difference is essential in customer analytics. If management asks “How many customers do we have?” you should clarify whether they mean transactions or distinct customers.
Instead of producing separate tables, we can calculate several metrics in one GroupBy operation.
city_report = (
df.groupby("city")
.agg(
total_sales=("sales", "sum"),
average_order_value=("sales", "mean"),
total_orders=("order_id", "count"),
unique_customers=("customer_id", "nunique")
)
.reset_index()
)
print(city_report)This creates one row per city and four important KPIs.
total_sales tells us the total sales value.
average_order_value tells us the typical order size.
total_orders tells us transaction volume.
unique_customers tells us how many distinct customers purchased.
We can create another KPI:
city_report["sales_per_customer"] = (
city_report["total_sales"] /
city_report["unique_customers"]
)
print(city_report)This is a derived metric. It tells us how much sales value is associated with each unique customer on average at the city level.
Again, do not confuse this with average order value. A customer may place multiple orders.
Now move from geography to product analysis.
Business question:
“Which product categories generate the highest sales?”
category_sales = (
df.groupby("category")["sales"]
.sum()
.reset_index(name="total_sales")
.sort_values(
by="total_sales",
ascending=False
)
)
print(category_sales)This tells us the total sales generated by Laptop, Mobile, Accessories, and Tablet categories in our dataset.
category_report = (
df.groupby("category")
.agg(
total_sales=("sales", "sum"),
total_quantity=("quantity", "sum"),
orders=("order_id", "count"),
average_order=("sales", "mean")
)
.reset_index()
)
print(category_report)Now we can compare sales value, unit volume, transaction count, and average order value together.
Sometimes a single grouping column is not enough.
Business question:
“Which product categories are performing well in each city?”
city_category = (
df.groupby(
["city", "category"]
)["sales"]
.sum()
.reset_index(name="total_sales")
)
print(city_category)Now each row represents a city-category combination.
For example, one row may represent Dehradun-Laptop, another Dehradun-Mobile, another Delhi-Laptop, and so on.
Suppose Delhi has high total sales. A city-level total does not tell us which products generated those sales. City plus category gives us the additional detail needed to investigate product mix.
This is a common principle in analytics:
More grouping dimensions
↓
More detailed result
↓
More specific questions can be answeredNow analyze employee performance.
salesperson_report = (
df.groupby("salesperson")
.agg(
total_sales=("sales", "sum"),
orders=("order_id", "count"),
average_order=("sales", "mean"),
customers=("customer_id", "nunique")
)
.reset_index()
.sort_values(
by="total_sales",
ascending=False
)
)
print(salesperson_report)This report can help answer questions such as who generated the highest sales and whether high sales came from many orders or larger orders.
A salesperson with the highest sales is not automatically the person with the highest average order value. Similarly, someone with fewer orders may have a higher average order.
GroupBy allows you to place these metrics together so that the analysis is more balanced.
Our dataset includes dates, so we can introduce time into the analysis.
df["order_date"] = pd.to_datetime(
df["order_date"]
)
df["year"] = df["order_date"].dt.yearNow we can group by both year and city.
year_city = (
df.groupby(
["year", "city"]
)["sales"]
.sum()
.reset_index(name="total_sales")
)
print(year_city)Grouping by year allows us to compare sales across periods. The same idea can be extended to month, quarter, or other time dimensions.
df["month"] = df["order_date"].dt.month
monthly_sales = (
df.groupby("month")["sales"]
.sum()
)
print(monthly_sales)In real-world analytics, always check whether your date range contains enough periods to make a meaningful comparison.
Now create a business-defined metric. Suppose the company considers an order of ₹100,000 or more to be a high-value order.
df["high_value_order"] = (
df["sales"] >= 100000
)
print(
df[
["order_id", "sales", "high_value_order"]
].head(10)
)Each row now contains either True or False.
high_value_orders = (
df.groupby("city")["high_value_order"]
.sum()
.reset_index(
name="high_value_orders"
)
)
print(high_value_orders)In Pandas, True behaves like 1 and False behaves like 0 for this numeric aggregation. Therefore, summing the Boolean column counts the True records.
high_value_rate = (
df.groupby("city")["high_value_order"]
.mean()
.mul(100)
.reset_index(
name="high_value_order_percentage"
)
)
print(high_value_rate)The mean of a Boolean column gives the proportion of True values. Multiplying by 100 converts it to a percentage.
Aggregation produces a smaller summary. But sometimes we want to bring a group-level value back to every original row.
df["city_average"] = (
df.groupby("city")["sales"]
.transform("mean")
)
print(
df[
["city", "sales", "city_average"]
].head(10)
)Every order now has the average sales for its city.
df["above_city_average"] = (
df["sales"] >
df["city_average"]
)
print(
df[
[
"city",
"sales",
"city_average",
"above_city_average"
]
].head(10)
)This is useful because we can evaluate individual transactions relative to their group.
Another useful question is:
“What percentage of the city’s sales came from each order?”
df["city_total_sales"] = (
df.groupby("city")["sales"]
.transform("sum")
)df["sales_share_percent"] = (
df["sales"] /
df["city_total_sales"] *
100
)
print(
df[
[
"city",
"sales",
"city_total_sales",
"sales_share_percent"
]
].head(10)
)The percentage represents the contribution of that particular order to its city’s total sales.
Now suppose management wants the highest-value order from every city.
df["city_rank"] = (
df.groupby("city")["sales"]
.rank(
ascending=False,
method="dense"
)
)
print(
df[
["city", "order_id", "sales", "city_rank"]
].head(15)
)top_orders = df.loc[
df["city_rank"] == 1
]
print(top_orders)This approach can be reused for top customers, top products, top salespeople, or top branches within their respective groups.
Professional analytics is not only about writing code. You must also check whether the output makes sense.
source_total = df["sales"].sum()
grouped_total = (
df.groupby("city")["sales"]
.sum()
.sum()
)
print("Source total:", source_total)
print("Grouped total:", grouped_total)These two totals should match when every record belongs to a city group.
source_rows = len(df)
grouped_rows = (
df.groupby("city")
.size()
.sum()
)
print("Source rows:", source_rows)
print("Grouped rows:", grouped_rows)If the counts do not reconcile, investigate missing grouping values, filters, duplicate records, or other transformations.
Now build one final report using everything learned so far.
city_dashboard = (
df.groupby("city")
.agg(
total_sales=("sales", "sum"),
average_order_value=("sales", "mean"),
total_orders=("order_id", "count"),
unique_customers=("customer_id", "nunique"),
total_quantity=("quantity", "sum"),
minimum_order=("sales", "min"),
maximum_order=("sales", "max")
)
.reset_index()
)
city_dashboard["sales_per_customer"] = (
city_dashboard["total_sales"] /
city_dashboard["unique_customers"]
)
city_dashboard = city_dashboard.sort_values(
by="total_sales",
ascending=False
)
print(city_dashboard)city_dashboard.to_csv(
"city_performance_report.csv",
index=False
)This report can now be opened in Excel or imported into Power BI for visualization.
Do not simply identify the first row and say that it is the best city. Look at all KPIs.
A city may have high total sales because it has many orders. Another city may have fewer orders but a higher average order value. Another city may have a large number of unique customers but relatively low sales per customer.
The purpose of GroupBy is therefore not just to produce a table. It is to create a structured summary from which useful questions and decisions can be made.
For the exercises below, use the same CSV file. Do not create a new dataset.
In this lesson, we learned GroupBy through one complete practical sales dataset rather than isolated examples.
The most important pattern is:
df.groupby("group_column")["measure"].aggregation()For example:
df.groupby("city")["sales"].sum()means: group the orders by city, select sales, and calculate the total for every city.
We also learned that different business questions require different aggregations:
sum() → total
mean() → average
count() → non-missing records
size() → rows
nunique() → unique values
min() → minimum
max() → maximumFor multiple metrics, we used agg(). For group-level values that need to remain aligned with the original rows, we used transform(). We also created Boolean business rules, percentages, group-wise rankings, and a final city performance report.
The most important habit is to start with the business question. Identify the grouping column, identify the measure, choose the correct aggregation, run the code, inspect the result, validate it, and then explain what the result means.
That is how GroupBy becomes a real Data Analytics skill rather than just a Pandas command.