Pandas GroupBy Operations (groupby)

groupbyIt is one of Pandas' most powerful features, allowing you to group data by one or more columns, and then perform aggregation, transformation, or filtering operations on each group.

groupby Basic Usage

Grouping by a Single Column

Example

import pandas as pd

# Create sample data
df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations", "Sales", "Technology"],
    "Name": ["Zhang San", "Li Si", "Wang Wu", "Zhao Liu", "Qian Qi", "Sun Ba"],
    "Salary": [12000, 15000, 11000, 18000, 14000, 13000]
})

print("Original data:")
print(df)
print()

# Group by department
grouped = df.groupby("Department")
print(f"Grouped object: {type(grouped)}")
print(f"Number of groups: {len(grouped)}")
print()

# View the groups
print("Data for each group:")
for name, group in grouped:
    print(f"\nDepartment: {name}")
    print(group)

Grouping and Aggregating

Example

import pandas as pd

df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations", "Sales"],
    "Salary": [12000, 15000, 11000, 18000, 14000]
})

print("Average salary by department:")
print(df.groupby("Department")["Salary"].mean())
print()

print("Sum by department:")
print(df.groupby("Department")["Salary"].sum())
print()

print("Count by department:")
print(df.groupby("Department").size())

Grouping by Multiple Columns

Example

import pandas as pd

df = pd.DataFrame({
    "Year": ["2023", "2023", "2024", "2024"],
    "Department": ["Technology", "Sales", "Technology", "Sales"],
    "Salary": [12000, 15000, 13000, 16000]
})

print("Group by year and department:")
print(df.groupby(["Year", "Department"])["Salary"].sum())
print()

# Output as DataFrame
result = df.groupby(["Year", "Department"]).agg({
    "Salary": "sum"
}).reset_index()
print("Result DataFrame:")
print(result)

Common Aggregation Functions

Single Aggregation

Function Description
sum() Sum
mean() Mean
median() Median
std() Standard deviation
min() / max() Min/Max
count() Count
first() / last() First/Last value

Example

import pandas as pd

df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations"],
    "Salary": [12000, 15000, 11000, 18000]
})

# Chain multiple aggregations
print("Chained aggregation:")
print(df.groupby("Department")["Salary"].agg(["sum", "mean", "max", "min"]))

Custom Aggregation

Example

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations"],
    "Salary": [12000, 15000, 11000, 18000]
})

# Custom aggregation function
def range_func(x):
    return x.max() - x.min()

print("Using a custom function:")
print(df.groupby("Department")["Salary"].agg(range_func))
print()

# Named aggregation (recommended)
print("Named aggregation:")
print(df.groupby("Department").agg(
Minimum salary=("Salary", "min"),
Maximum salary=("Salary", "max"),
Average salary=("Salary", "mean")
))

transform

transform can calculate on each group while keeping the original data shape, and return a result with the same shape as the original data.

Example

import pandas as pd

df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations", "Sales"],
    "Salary": [12000, 15000, 11000, 18000, 14000]
})

print("Original data:")
print(df)
print()

# Calculate the average salary for each department and broadcast it to each row
df["Department average salary"] = df.groupby("Department")["Salary"].transform("mean")
print("After adding department average salary:")
print(df)
print()

# Calculate each person's salary as a percentage of the department
df["Salary percentage"] = df["Salary"] / df["Department average salary"]
print("Salary proportion:")
print(df)

filter Filtering

filter can filter data based on group conditions.

Example

import pandas as pd

df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations", "Sales", "Technology"],
    "Salary": [12000, 15000, 11000, 18000, 14000, 13000]
})

print("Original data:")
print(df)
print()

# Filter departments whose average salary is greater than 13000
print("Department average salary > 13000:")
filtered = df.groupby("Department").filter(lambda x: x["Salary"].mean() > 13000)
print(filtered)
print()

# Filter groups with count greater than 2
print("Number of members > 2:")
filtered2 = df.groupby("Department").filter(lambda x: len(x) > 2)
print(filtered2)

apply Custom Functions

apply allows applying custom functions to each group.

Example

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "Department": ["Technology", "Sales", "Technology", "Operations"],
    "Salary": [12000, 15000, 11000, 18000]
})

# Apply a custom function to each group
result = df.groupby("Department").apply(
    lambda x: pd.Series({
        "Sum": x["Salary"].sum(),
        "Mean": x["Salary"].mean()
    })
)
print("Custom aggregation result:")
print(result)

Hands-on: Sales Data Analysis

Example

import pandas as pd

# Simulate sales data
sales = pd.DataFrame({
    "Date": pd.date_range("2024-01-01", periods=30, freq="D"),
    "Product": ["Mobile", "Computer", "Tablet"] * 10,
    "Channel": ["Online"] * 15 + ["Offline"] * 15,
    "Sales amount": [1000, 2000, 1500] * 10
})

print("=== Sales Data Analysis ===\n")

# 1. Statistics by product
print("1. Statistics by product:")
product_summary = sales.groupby("Product")["Sales amount"].agg(["sum", "mean", "count"])
print(product_summary)
print()

# 2. Statistics by channel
print("2. Statistics by channel:")
channel_summary = sales.groupby("Channel")["Sales amount"].sum()
print(channel_summary)
print()

# 3. Cross statistics by product and channel
print("3. Product × Channel cross statistics:")
cross_summary = sales.groupby(["Product", "Channel"])["Sales amount"].sum().unstack()
print(cross_summary)
print()

# 4. Calculate the proportion of sales for each product
print("4. Sales proportion:")
total = sales["Sales amount"].sum()
product_pct = sales.groupby("Product")["Sales amount"].sum() / total * 100
print(product_pct.round(2))

groupby is a core operation in data analysis; mastering it allows you to efficiently complete various statistical analysis tasks.

Other Extensions