Aggregate with Sum, Mean, Count

You have a DataFrame with thousands of rows, and you need an answer to a simple question: What is the total sales for each region? Or the average score per student? Manually looping through every row is slow, error-prone, and leads to code that is hard to maintain. The pain is real: without proper aggregation, you will waste hours writing brittle scripts that break the moment your data changes. In this lesson, you will learn how to use pandas' sum, mean, and count methods to turn massive datasets into concise, meaningful summaries in just a few lines of code.

The problem this lesson solves

You have a dataset with thousands of rows and dozens of columns. You want to answer questions like:

• What are the total sales per product category?
• What is the average rating of each product?
• How many orders were placed per salesperson?

Doing this manually with for loops is not only tedious but also fragile. For example, a simple typo in a column name, a missing value, or a new category in the data can break your script. Moreover, the performance degrades quickly as your dataset grows.

pandas aggregation solves this by providing vectorized, built-in functions that operate on entire columns at once. Instead of writing custom loops, you call .sum(), .mean(), or .count() on a DataFrame or GroupBy object, and pandas does the heavy lifting in optimized C code.

In this lesson, you will learn how to aggregate data with sum, mean, and count — the three most common aggregation operations — and how to apply them in real-world data analysis scenarios.

Core concept / mental model

Think of aggregation as a summary lens for your data. When you aggregate, you reduce a set of values into a single representative value. The three functions you will learn are:

• sum() — adds up all values: useful for totals like revenue, quantity, or duration.
• mean() — averages the values: useful for typical values like average rating, average price, or average time.
• count() — counts the non-null entries: useful for volumes like number of orders, number of responses, or number of active users.

These functions work in two main contexts:

1. DataFrame-level aggregation: you call them directly on a DataFrame or a Series to get a summary of the entire dataset.
2. Grouped aggregation: you pair them with groupby() to compute summaries for each group (e.g., per category, per region, per employee).

Here is a quick analogy: imagine you have a stack of papers with sales records. Sum is like adding the totals of each paper, mean is like calculating the average per paper, and count is like counting how many papers have a value written. When you group by a column (say, region), you repeat these calculations for each stack of papers that belongs to that region.

Key concept: the combination of groupby() and an aggregation function is often called a split-apply-combine operation. You split the data into groups, apply the function to each group, and then combine the results back into a DataFrame.

How it works step by step

The workflow for aggregation is straightforward once you grasp the two contexts. Here is the step-by-step mental process:

1. Load your data into a pandas DataFrame

Start by importing pandas and reading your data from a CSV, Excel, or other source.

2. Decide what you want to aggregate

Ask yourself: Do I need a single summary for the whole dataset, or do I need summaries per group? This determines whether you use a direct method or groupby().

3. Choose the right aggregation function

• Use sum() when you need a total.
• Use mean() when you need the average.
• Use count() when you need the number of non-null values.

4. Apply the function to the correct columns

If you want to sum only a specific column, select it first (e.g., df['sales'].sum()). If you want to sum multiple columns, use the numeric_only parameter if needed.

5. (Optional) Group your data first

If you need per-category summaries, use df.groupby('category')['sales'].sum().

6. Review and interpret the result

The output is a Series or DataFrame with the aggregated values. Note that the index now reflects the groups or the original row labels.

7. Reset the index if you want the grouping column back as a column

After a groupby, the group column becomes the index. Calling .reset_index() will turn it back into a regular column, which is often useful for further analysis or plotting.

Hands-on walkthrough

Let's put this into practice with a concrete dataset. We'll use a small sales DataFrame to illustrate each aggregation type.

Example 1: Basic aggregation on a DataFrame

import pandas as pd

# Create a simple sales DataFrame
data = {
    'product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'USB Hub'],
    'price': [999.99, 19.99, 49.99, 199.99, 29.99],
    'units_sold': [12, 150, 80, 55, 120]
}
df = pd.DataFrame(data)
print(df)

Output:

     product   price  units_sold
0     Laptop  999.99          12
1      Mouse   19.99         150
2   Keyboard   49.99          80
3    Monitor  199.99          55
4    USB Hub   29.99         120

Now, compute the total revenue (price × units sold) and the average price:

# Add a revenue column
df['revenue'] = df['price'] * df['units_sold']

# Sum of revenue
total_revenue = df['revenue'].sum()
print('Total revenue:', total_revenue)

# Mean price
mean_price = df['price'].mean()
print('Mean price:', mean_price)

# Count of non-null prices
price_count = df['price'].count()
print('Price count:', price_count)

Output:

Total revenue: 16123.63
Mean price: 259.98999999999995
Price count: 5

Example 2: Grouped aggregation with groupby

Now, let's group by product category (we'll add a 'category' column) and compute sum, mean, and count for each group.

# Add a category column
df['category'] = ['Electronics', 'Accessories', 'Accessories', 'Electronics', 'Accessories']

# Group by category and aggregate
result = df.groupby('category').agg(
    total_units=('units_sold', 'sum'),
    avg_price=('price', 'mean'),
    product_count=('product', 'count')
)
print(result)

Output:

             total_units  avg_price  product_count
category                                            
Accessories          350   33.323333              3
Electronics           67  599.990000              2

Notice how groupby('category') groups the rows, and .agg() lets you specify different aggregations for different columns. The category becomes the index. To turn it back into a regular column, use reset_index():

result.reset_index(inplace=True)
print(result)

Example 3: Aggregate on a time series

Aggregations are extremely useful with datetime data. Suppose you have daily sales and want a monthly total:

# Create a date range
import pandas as pd

# Sample daily sales
dates = pd.date_range('2024-01-01', periods=90, freq='D')
sales = pd.Series(range(1, 91), name='sales')

df_daily = pd.DataFrame({'date': dates, 'sales': sales})

# Total sales per month
df_daily['month'] = df_daily['date'].dt.to_period('M')
monthly_total = df_daily.groupby('month')['sales'].sum()
print(monthly_total)

Output:

month
2024-01    496
2024-02    1416
2024-03    1683
Freq: M, Name: sales, dtype: int64

This pattern is invaluable for reporting and dashboards.

Compare options / when to choose what

There are several ways to compute aggregations in pandas. Here is a comparison of the most common approaches:

Recommendation: For most day-to-day analysis, start with groupby().agg() — it gives you the best balance of readability and flexibility. Use pivot_table when you need a pivot-style summary.

Troubleshooting & edge cases

Let's look at common pitfalls and how to fix them.

Problem: count() returns 0 or fewer than expected

count() skips NaN values by design. If your column has missing values, the count will reflect only non-null entries. If you want the total number of rows, use len(df) instead.

import pandas as pd
import numpy as np

df = pd.DataFrame({'A': [1, None, 3, None]})
print(df['A'].count())  # Output: 2
print(len(df))          # Output: 4

Problem: mean() returns NaN when all values are NaN

When every value in a group is missing, the mean is undefined and pandas returns NaN. To handle this, you can fill missing values before aggregation or use skipna=False to propagate NaN if that's your intention.

df = pd.DataFrame({'group': ['a', 'a', 'b'], 'value': [1, None, None]})
print(df.groupby('group')['value'].mean())
# Output:
# group
# a    1.0
# b    NaN
# Name: value, dtype: float64

Problem: sum() on a non-numeric column raises an error or concatenates

If a column contains strings, calling sum() will concatenate the strings, which is rarely what you want. Ensure that you are aggregating numeric columns. Use numeric_only=True to filter automatically:

df = pd.DataFrame({'name': ['Alice', 'Bob'], 'score': [95, 87]})
# This would concatenate names: 'AliceBob'
# Instead, use numeric_only
df.sum(numeric_only=True)  # Output: score   182

Problem: The index after groupby is not a column

After grouping, the group column becomes the index, which can be surprising. Use reset_index() to bring it back as a column:

result = df.groupby('category')['revenue'].sum().reset_index()
print(result)

What you learned & what's next

You now understand how to aggregate data with sum, mean, and count in pandas. You learned the mental model of aggregation as a summary lens, when to use each function, and how to combine them with groupby() for grouped summaries. You also walked through hands-on examples and explored common edge cases.

With these tools, you can confidently answer questions about totals, averages, and counts in any dataset — a foundational skill for any data analyst.

Next lesson: In the next step of the track, you will learn how to group data for deeper analysis — applying multiple aggregations and custom functions to reveal insights that a simple sum or mean can't provide. You'll build on the exact groupby skills you practiced here.

Now go ahead and try this on your own dataset. The more you practice aggregation, the more natural it becomes.