Stories by Engr. Md Monjur Bakth Mazumder on Medium

Pandas Advanced GroupBy: Multiple Grouping, Aggregation and Named Aggregation

Engr. Md Monjur Bakth Mazumder — Sat, 14 Nov 2026 14:01:02 GMT

A Practical Guide to groupby(), agg(), Multiple Grouping, Custom Aggregation and Named Aggregation in Python Pandas

Series: Data Analysis with Pandas
Part: 21

Introduction

In the previous article, we learned the basics of Pandas groupby().

We saw how grouping can answer simple questions such as:

What is the average salary by department?
What are the total sales by category?
How many employees are in each department?

But real-world data analysis usually requires more than one grouping column or one aggregation.

For example:

What are the total sales, average sales, highest sale and number of orders for each product category in each region?

This requires:

Multiple grouping columns
Multiple aggregations
Different aggregation functions
Custom calculations
Clearly named output columns

This is where Advanced GroupBy becomes useful.

In this article, we will explore:

groupby()
agg()
Multiple Grouping
Multiple Aggregations
Custom Aggregation
Named Aggregation
Business Analysis

The goal is not simply to learn more Pandas syntax.

The goal is to learn how to use GroupBy to answer more realistic business questions.

1. What Is Advanced GroupBy?

A basic GroupBy might look like this:

df.groupby("Department")["Salary"].mean()

This answers:

What is the average salary for each department?

But suppose we want:

Department
+ Region
    ↓
Total Sales
Average Sales
Maximum Sales
Minimum Sales
Number of Orders

Now we need a more advanced grouping and aggregation strategy.

A typical pattern is:

df.groupby(["Department", "Region"]).agg(...)

The mental model is:

Raw Data
   ↓
Group by one or more columns
   ↓
Apply one or more aggregations
   ↓
Produce summarized results

2. The groupby() Function

The general syntax is:

df.groupby(by)

For example:

df.groupby("Department")

For multiple columns:

df.groupby(["Department", "Region"])

However, groupby() itself does not usually produce the final summary.

It creates a GroupBy object that can then be aggregated.

For example:

grouped = df.groupby("Department")

print(grouped)

You might see something similar to:

The actual analysis happens when we apply an aggregation.

For example:

df.groupby("Department")["Salary"].mean()

3. Multiple Grouping

One of the most useful features of Pandas GroupBy is grouping by multiple columns.

Suppose we have:

import pandas as pd

df = pd.DataFrame({
    "Region": [
        "North", "North", "South", "South",
        "North", "South"
    ],
    "Category": [
        "Laptop", "Mobile", "Laptop",
        "Mobile", "Laptop", "Mobile"
    ],
    "Sales": [
        50000, 30000, 45000,
        28000, 55000, 35000
    ]
})

print(df)

We can group by both:

df.groupby(["Region", "Category"])["Sales"].sum()

The grouping hierarchy becomes:

Region
    ↓
Category
    ↓
Sales Summary

This allows us to answer:

How much did each product category sell in each region?

4. Understanding Multiple Grouping

Consider:

df.groupby(["Region", "Category"])["Sales"].sum()

Pandas first creates groups such as:

North + Laptop
North + Mobile
South + Laptop
South + Mobile

Then it calculates the total sales for each group.

This is very different from:

df.groupby("Region")["Sales"].sum()

The first gives a more detailed analysis.

The second gives only region-level analysis.

So:

groupby("Region")

means:

Analyze at the region level.

While:

groupby(["Region", "Category"])

means:

Analyze at the region + category level.

5. Multiple Aggregations with agg()

Suppose we want more than one statistic.

For example:

Total Sales
Average Sales
Maximum Sales
Minimum Sales

We can use:

df.groupby("Region")["Sales"].agg(
    ["sum", "mean", "max", "min"]
)

This is much more powerful than calling each aggregation separately.

Without agg():

df.groupby("Region")["Sales"].sum()

df.groupby("Region")["Sales"].mean()

df.groupby("Region")["Sales"].max()

df.groupby("Region")["Sales"].min()

With agg():

df.groupby("Region")["Sales"].agg(
    ["sum", "mean", "max", "min"]
)

This keeps related analysis together.

6. Multiple Aggregations on Multiple Columns

We can go one step further.

Suppose our dataset contains:

df = pd.DataFrame({
    "Region": ["North", "North", "South", "South"],
    "Sales": [50000, 30000, 45000, 28000],
    "Profit": [10000, 6000, 9000, 5000],
    "Orders": [50, 30, 45, 28]
})

We may want:

Sales:
    sum
    mean
    max

Profit:
    sum
    mean
    max

Orders:
    sum
    mean

We can write:

summary = df.groupby("Region").agg({
    "Sales": ["sum", "mean", "max"],
    "Profit": ["sum", "mean", "max"],
    "Orders": ["sum", "mean"]
})

print(summary)

This allows different columns to have different aggregation functions.

7. Why Use a Dictionary with agg()?

The dictionary approach is useful when different columns need different calculations.

For example:

df.groupby("Region").agg({
    "Sales": ["sum", "mean"],
    "Profit": ["sum", "max"],
    "Orders": "count"
})

This means:

Sales  → sum + mean
Profit → sum + max
Orders → count

This is useful for business dashboards and analytical summaries.

8. Multiple Grouping + Multiple Aggregations

Now let’s combine the concepts.

summary = (
    df.groupby(["Region", "Category"])
      .agg({
          "Sales": ["sum", "mean", "max"],
          "Profit": ["sum", "mean"],
          "Orders": "count"
      })
)

print(summary)

This is a powerful business-analysis pattern.

We are answering multiple questions at once:

For each Region + Category:

How much was sold?
What was the average sale?
What was the highest sale?
How much profit was generated?
What was the average profit?
How many records/orders exist?

9. Using agg() with Different Functions

Pandas allows many built-in aggregation functions.

Common examples include:

"sum"
"mean"
"median"
"min"
"max"
"count"
"std"
"var"
"first"
"last"

For example:

df.groupby("Region")["Sales"].agg([
    "count",
    "sum",
    "mean",
    "median",
    "min",
    "max",
    "std"
])

This creates a statistical summary for each region.

10. Named Aggregation

Multiple aggregations are powerful, but the default output column names can sometimes become difficult to read.

For example:

df.groupby("Region").agg({
    "Sales": ["sum", "mean", "max"]
})

may produce columns conceptually like:

Sales
    sum
    mean
    max

For production-quality analysis, we often want meaningful column names.

This is where Named Aggregation becomes useful.

11. Named Aggregation Syntax

Named aggregation allows us to define the final column names directly.

Example:

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    average_sales=("Sales", "mean"),
    highest_sale=("Sales", "max")
)

print(summary)

The syntax is:

new_column_name=("source_column", "aggregation_function")

For example:

total_sales=("Sales", "sum")

means:

Take Sales
    ↓
Apply sum()
    ↓
Store the result as total_sales

12. Named Aggregation with Multiple Columns

We can apply named aggregation to several columns.

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    average_sales=("Sales", "mean"),
    total_profit=("Profit", "sum"),
    average_profit=("Profit", "mean"),
    total_orders=("Orders", "sum")
)

print(summary)

This produces a clean analytical output.

Instead of nested column labels, we get meaningful names such as:

total_sales
average_sales
total_profit
average_profit
total_orders

This is often easier to use for:

Reporting
Dashboards
Visualization
Further analysis
Exporting results

13. Named Aggregation with Multiple Grouping

Named aggregation becomes even more useful with multiple grouping columns.

summary = (
    df.groupby(["Region", "Category"])
      .agg(
          total_sales=("Sales", "sum"),
          average_sales=("Sales", "mean"),
          maximum_sale=("Sales", "max"),
          total_profit=("Profit", "sum"),
          average_profit=("Profit", "mean")
      )
)

print(summary)

Now the analysis is performed for every:

Region + Category

combination.

This is one of the most useful patterns to remember.

14. Custom Aggregation

Built-in functions are not always enough.

Sometimes a business question requires custom logic.

For example:

What is the range between the highest and lowest sale?

There is no standard aggregation called:

"range"

We can create our own function.

def sales_range(series):
    return series.max() - series.min()

Then:

df.groupby("Region")["Sales"].agg(sales_range)

We can also use a lambda:

df.groupby("Region")["Sales"].agg(
    lambda x: x.max() - x.min()
)

The custom function approach is often easier to read and reuse.

15. Custom Aggregation with Named Aggregation

We can combine custom functions with named aggregation.

def sales_range(series):
    return series.max() - series.min()

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    average_sales=("Sales", "mean"),
    sales_range=("Sales", sales_range)
)

print(summary)

This produces a clean business summary.

16. Custom Percentage Calculation

Suppose we want to calculate the percentage contribution of a group’s total sales relative to its own total.

A simple custom aggregation can calculate a statistic within each group.

For example:

def sales_range(series):
    return series.max() - series.min()

But some calculations require information from multiple columns or from the whole dataset.

For those cases, it is important to distinguish between:

Aggregation
Transformation
Post-aggregation calculation

Not every business calculation belongs inside agg().

For example, a total profit margin can often be calculated more meaningfully from aggregated totals:

summary = (
    df.groupby("Region")
      .agg(
          total_sales=("Sales", "sum"),
          total_profit=("Profit", "sum")
      )
)

summary["profit_margin"] = (
    summary["total_profit"] / summary["total_sales"]
) * 100

This is often better than averaging individual row-level profit percentages.

17. Why Aggregation Order Matters

Consider these rows:

Sales   Profit
100     10
1000    100

Individual profit margins are:

10%
10%

The average margin is:

10%

Now imagine:

Sales   Profit
100     20
1000    100

Margins are:

20%
10%

The simple average is:

15%

But the overall margin is:

120 / 1100 × 100

which is approximately:

10.91%

These are answering different questions.

Therefore, in business analysis, always ask:

Should I aggregate the underlying values first, or aggregate the derived metric?

This distinction is extremely important.

18. Custom Aggregation for Business Rules

Suppose a company wants to know the number of high-value sales.

A high-value sale is defined as:

Sales >= 50,000

We can create a custom aggregation:

def high_value_sales(series):
    return (series >= 50000).sum()

Then:

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    high_value_orders=("Sales", high_value_sales)
)

print(summary)

Because:

(series >= 50000)

produces Boolean values:

True
False
True

and:

.sum()

counts the True values.

19. Custom Aggregation with Lambda

For small, simple rules, a lambda can be convenient.

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    high_value_orders=(
        "Sales",
        lambda x: (x >= 50000).sum()
    )
)

This is compact.

However, if the business rule becomes complex, a named function is usually easier to understand:

def high_value_sales(series):
    return (series >= 50000).sum()

Then:

high_value_orders=("Sales", high_value_sales)

Prefer readability when the logic is important.

20. Business Example: Regional Sales Analysis

Let’s build a more realistic dataset.

import pandas as pd

sales = pd.DataFrame({
    "Region": [
        "North", "North", "North",
        "South", "South", "South",
        "East", "East"
    ],
    "Category": [
        "Laptop", "Mobile", "Laptop",
        "Mobile", "Laptop", "Mobile",
        "Laptop", "Mobile"
    ],
    "Sales": [
        80000, 45000, 70000,
        60000, 90000, 40000,
        75000, 50000
    ],
    "Profit": [
        16000, 9000, 14000,
        12000, 18000, 8000,
        15000, 10000
    ]
})

Now suppose management asks:

Show sales and profit performance by region and product category.

We can write:

summary = (
    sales.groupby(["Region", "Category"])
         .agg(
             total_sales=("Sales", "sum"),
             average_sales=("Sales", "mean"),
             maximum_sale=("Sales", "max"),
             total_profit=("Profit", "sum"),
             average_profit=("Profit", "mean")
         )
)

print(summary)

This produces a much richer analysis than a simple groupby().sum().

21. Adding Profit Margin

After aggregation:

summary["profit_margin"] = (
    summary["total_profit"] /
    summary["total_sales"]
) * 100

Then round it:

summary["profit_margin"] = (
    summary["profit_margin"].round(2)
)

Now we have:

Region + Category
        ↓
Total Sales
Average Sales
Maximum Sale
Total Profit
Average Profit
Profit Margin

This is much closer to a real business report.

22. Business Example: Employee Analysis

GroupBy is not limited to sales.

Suppose we have:

employees = pd.DataFrame({
    "Department": [
        "IT", "IT", "HR",
        "HR", "Finance", "Finance"
    ],
    "Level": [
        "Senior", "Junior", "Senior",
        "Junior", "Senior", "Junior"
    ],
    "Salary": [
        90000, 50000, 75000,
        45000, 85000, 55000
    ],
    "Experience": [
        8, 2, 7, 1, 9, 3
    ]
})

We can analyze department and level together:

summary = (
    employees
    .groupby(["Department", "Level"])
    .agg(
        employee_count=("Salary", "count"),
        average_salary=("Salary", "mean"),
        maximum_salary=("Salary", "max"),
        average_experience=("Experience", "mean")
    )
)

print(summary)

This answers:

How many employees are at each level in each department, and what are their salary and experience characteristics?

23. Business Example: Customer Analysis

Suppose an e-commerce dataset contains:

Customer
Region
OrderValue

We can analyze customers by region.

customer_summary = (
    df.groupby("Region")
      .agg(
          total_revenue=("OrderValue", "sum"),
          average_order=("OrderValue", "mean"),
          largest_order=("OrderValue", "max"),
          order_count=("OrderValue", "count")
      )
)

Now management can identify:

High-revenue regions
Average order value
Largest transaction
Number of orders

This is the real purpose of advanced GroupBy.

24. GroupBy for Business Questions

Instead of thinking:

Which GroupBy syntax should I use?

Think:

What business question am I trying to answer?

For example:

Question 1

Which region generates the most revenue?

df.groupby("Region")["Sales"].sum()

Question 2

Which region and category combination generates the most revenue?

df.groupby(["Region", "Category"])["Sales"].sum()

Question 3

What are the average and maximum sales for each region?

df.groupby("Region")["Sales"].agg(["mean", "max"])

Question 4

What are the total sales and total profit by region and category?

df.groupby(["Region", "Category"]).agg(
    total_sales=("Sales", "sum"),
    total_profit=("Profit", "sum")
)

Question 5

What is the profit margin for each region?

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    total_profit=("Profit", "sum")
)

summary["profit_margin"] = (
    summary["total_profit"] /
    summary["total_sales"]
) * 100

The question determines the GroupBy design.

25. agg() vs Calling Aggregations Separately

You could write:

total_sales = df.groupby("Region")["Sales"].sum()

average_sales = df.groupby("Region")["Sales"].mean()

maximum_sales = df.groupby("Region")["Sales"].max()

But this repeats the grouping operation.

Instead:

summary = df.groupby("Region")["Sales"].agg(
    ["sum", "mean", "max"]
)

Or, for a production-friendly result:

summary = df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    average_sales=("Sales", "mean"),
    maximum_sales=("Sales", "max")
)

The named aggregation version is often the clearest when building a final analytical dataset.

26. count() vs size() in Advanced GroupBy

This distinction remains important.

Consider:

df.groupby("Region")["Sales"].count()

count() counts non-missing values in the selected column.

Whereas:

df.groupby("Region").size()

counts rows in each group.

For example:

summary = df.groupby("Region").agg(
    sales_count=("Sales", "count")
)

counts non-null Sales values.

If you want the number of records regardless of whether Sales is missing, use:

summary = df.groupby("Region").size()

Choose based on the business question.

27. Resetting the Index

When grouping by multiple columns, Pandas commonly creates a MultiIndex.

For example:

summary = (
    df.groupby(["Region", "Category"])
      .agg(
          total_sales=("Sales", "sum")
      )
)

The result has:

Region
Category

as index levels.

If you want them as regular columns:

summary = summary.reset_index()

Alternatively, you can use:

summary = (
    df.groupby(
        ["Region", "Category"],
        as_index=False
    )
    .agg(
        total_sales=("Sales", "sum")
    )
)

This is often convenient when the result will be used for:

Visualization
Export
Further DataFrame operations
Dashboard development

28. Custom Aggregation: When Should You Use It?

Custom aggregation is useful when:

Built-in aggregation functions are not enough
A specific business rule is required
A derived group-level statistic is needed
You need reusable analytical logic

For example:

def value_range(series):
    return series.max() - series.min()

Then:

df.groupby("Region").agg(
    sales_range=("Sales", value_range)
)

But do not create custom functions for calculations that Pandas already handles efficiently.

Prefer:

df.groupby("Region")["Sales"].mean()

instead of unnecessarily writing:

def calculate_mean(series):
    return series.sum() / series.count()

Use built-in Pandas operations whenever they already express the required logic.

29. Common Mistakes

Mistake 1: Grouping by too many columns

df.groupby([
    "Region",
    "Category",
    "Product",
    "Customer",
    "Salesperson"
])

This can create extremely granular groups.

Ask whether every grouping column is necessary.

Mistake 2: Using the wrong aggregation

For example:

df.groupby("Region")["Profit"].mean()

may not answer the same question as:

df.groupby("Region")["Profit"].sum()

Mean and sum represent different business concepts.

Mistake 3: Averaging percentages blindly

Suppose each transaction has a profit margin.

A simple:

df.groupby("Region")["ProfitMargin"].mean()

may not represent the actual overall regional margin.

Often you should aggregate:

Total Profit
Total Sales

and then calculate:

Total Profit / Total Sales

Mistake 4: Overusing custom functions

If Pandas already provides:

sum
mean
median
min
max
count
std

use them.

Custom functions should solve a real requirement.

Mistake 5: Ignoring missing values

Aggregation behavior depends on the function and the data.

Always inspect missing values before interpreting the result.

Useful checks include:

df.isna().sum()

and:

df.info()

Mistake 6: Forgetting the level of analysis

Before writing:

groupby(...)

ask:

What should one row of my final summary represent?

For example:

One row = one region

or:

One row = one region + category

This simple question prevents many analytical mistakes.

30. A Practical Advanced GroupBy Workflow

A reliable workflow looks like this:

1. Load Data
      ↓
2. Inspect Data
      ↓
3. Define Business Question
      ↓
4. Select Grouping Columns
      ↓
5. Select Analysis Columns
      ↓
6. Choose Aggregations
      ↓
7. Apply groupby()
      ↓
8. Apply agg()
      ↓
9. Add Derived Metrics
      ↓
10. Validate Results
      ↓
11. Sort / Filter
      ↓
12. Visualize or Export

For example:

summary = (
    df.groupby(["Region", "Category"])
      .agg(
          total_sales=("Sales", "sum"),
          average_sales=("Sales", "mean"),
          total_profit=("Profit", "sum")
      )
      .reset_index()
)

summary["profit_margin"] = (
    summary["total_profit"] /
    summary["total_sales"] * 100
)

summary["profit_margin"] = (
    summary["profit_margin"].round(2)
)

summary = summary.sort_values(
    "total_sales",
    ascending=False
)

print(summary)

This is a realistic data-analysis workflow.

31. Advanced GroupBy Mental Model

Think about Advanced GroupBy like this:

Raw Data
                     │
                     ▼
             Define the Question
                     │
                     ▼
              Choose Groups
                     │
             ┌───────┴────────┐
             │                │
          Region          Category
             │                │
             └───────┬────────┘
                     ▼
                  groupby()
                     │
                     ▼
                    agg()
                     │
       ┌─────────────┼─────────────┐
       ▼             ▼             ▼
      sum           mean          max
       │             │             │
       └─────────────┼─────────────┘
                     ▼
              Business Summary
                     │
                     ▼
             Derived Metrics
                     │
                     ▼
                Insights

This is more important than memorizing syntax.

32. Quick Reference

Single grouping

df.groupby("Region")

Multiple grouping

df.groupby(["Region", "Category"])

Single aggregation

df.groupby("Region")["Sales"].sum()

Multiple aggregations

df.groupby("Region")["Sales"].agg(
    ["sum", "mean", "max"]
)

Multiple columns

df.groupby("Region").agg({
    "Sales": ["sum", "mean"],
    "Profit": ["sum", "max"]
})

Named aggregation

df.groupby("Region").agg(
    total_sales=("Sales", "sum"),
    average_sales=("Sales", "mean")
)

Multiple grouping + named aggregation

df.groupby(["Region", "Category"]).agg(
    total_sales=("Sales", "sum"),
    average_sales=("Sales", "mean"),
    total_profit=("Profit", "sum")
)

Custom aggregation

def value_range(series):
    return series.max() - series.min()

df.groupby("Region").agg(
    sales_range=("Sales", value_range)
)

Keep grouping columns as regular columns

df.groupby(
    ["Region", "Category"],
    as_index=False
).agg(
    total_sales=("Sales", "sum")
)

33. Key Takeaways

Advanced GroupBy is about turning raw records into meaningful summaries.

The most important concepts are:

groupby()

Use it to define groups.

Multiple grouping

Use multiple columns when analysis requires more detailed segmentation.

agg()

Use it to apply multiple aggregation functions.

Custom aggregation

Use it when built-in functions cannot express your business rule.

Named aggregation

Use it to create clean, meaningful output column names.

And most importantly:

Choose the grouping level and aggregation based on the business question — not simply because a particular Pandas function exists.

A strong data analyst does not just know how to write:

df.groupby(...)

They know what should be grouped, what should be aggregated, and why the resulting metric matters.

What Comes Next?

In this article, we learned how to perform advanced aggregation with:

Multiple grouping
Multiple aggregations
agg()
Custom aggregation
Named aggregation
Business-oriented analysis

But GroupBy can do more than summarize data.

Sometimes we need to perform an operation within each group while keeping the original DataFrame structure.

That is where:

GroupBy.transform() vs GroupBy.filter()

becomes important.

In Post #22, we will explore:

Pandas GroupBy transform() vs filter(): When and Why to Use Them

About This Series

Data Analysis with Pandas is a practical, beginner-friendly series covering Pandas from fundamentals to advanced data analysis.

The series progresses through:

Pandas Basics
      ↓
Data Selection
      ↓
Data Cleaning
      ↓
Transformation
      ↓
Feature Engineering
      ↓
GroupBy & Aggregation
      ↓
Reshaping
      ↓
Date & Time Analysis
      ↓
Time Series
      ↓
EDA
      ↓
Real-World Projects
      ↓
Data Science & ML Preparation

The goal is to help beginners move from:

Learning Pandas

to:

Using Pandas to solve real data-analysis problems.

Author

Engr. Md. Monjur Bakth Mazumder

GitHub: Python-Data-Science-Tools

Final Thought

Learning advanced Pandas is not about memorizing more methods.

It is about learning how to transform a business question into a structured analytical operation.

When you can think:

Business Question
       ↓
Grouping Level
       ↓
Aggregation
       ↓
Derived Metric
       ↓
Insight

you are moving beyond Pandas syntax and starting to think like a data analyst.

Connect & Follow

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas GroupBy Basics: Grouping Data for Analysis

Engr. Md Monjur Bakth Mazumder — Fri, 13 Nov 2026 14:01:04 GMT

Series: Data Analysis with Pandas
Part: 20

A Practical Guide to Grouping Data, Applying Aggregations and Analyzing Groups with df.groupby()

When working with real-world datasets, looking at the entire DataFrame at once is often not enough.

Suppose we have sales data containing:

Product
Category
Region
Sales
Profit

Instead of asking:

What is the total sales of the entire dataset?

we may want to ask:

What is the total sales for each region?
What is the average sales for each product category?
How many orders came from each region?
What is the highest sale in each category?
What is the average student score for each class?

These questions require us to group related records together.

This is where Pandas groupby() becomes one of the most important tools for data analysis.

In this article, we will learn:

What grouping means
What df.groupby() does
How to group by one column
How to calculate aggregations
How to analyze groups
How to use count(), sum(), mean(), min() and max()
How groupby() works conceptually
Practical student and sales examples
Common mistakes
A simple GroupBy workflow

1. What Is Grouping?

Grouping means dividing rows into logical groups based on the value of one or more columns.

Consider this dataset:

import pandas as pd

df = pd.DataFrame({
    "Department": [
        "IT",
        "HR",
        "IT",
        "Sales",
        "HR",
        "Sales"
    ],
    "Salary": [
        70000,
        50000,
        80000,
        60000,
        55000,
        65000
    ]
})

We have three departments:

IT
HR
Sales

Instead of analyzing every employee individually, we can group employees by:

df.groupby("Department")

Conceptually:

Original Data
      ↓
Group by Department
      ↓
IT
HR
Sales

Each group contains the rows belonging to that department.

2. What Does groupby() Do?

The basic syntax is:

df.groupby("column")

For example:

df.groupby("Department")

But there is an important point:

groupby() itself does not usually produce the final summary you want.

It creates a grouped object that can then be combined with an aggregation or another operation.

Think of the process as:

Group
  ↓
Apply operation
  ↓
Combine results

This is commonly called the:

Split → Apply → Combine pattern.

3. The Split → Apply → Combine Pattern

This is the core mental model behind groupby().

Step 1 — Split

Divide the DataFrame into groups.

df.groupby("Department")

Step 2 — Apply

Perform an operation on each group.

For example:

.mean()

Step 3 — Combine

Pandas combines the results into a new summarized object.

So:

df.groupby("Department")["Salary"].mean()

means:

Split employees by Department
        ↓
Calculate Salary mean for each group
        ↓
Combine the results

This pattern is fundamental to data analysis.

4. Basic groupby() Syntax

The basic structure is:

df.groupby("column")

Usually, we then select a numerical column and apply an aggregation:

df.groupby("group_column")["value_column"].mean()

For example:

df.groupby("Department")["Salary"].mean()

This asks:

What is the average salary for each department?

5. Grouping by One Column

Let’s use a simple sales dataset:

sales = pd.DataFrame({
    "Region": [
        "North",
        "South",
        "North",
        "East",
        "South",
        "East"
    ],
    "Sales": [
        1000,
        1500,
        1200,
        900,
        1800,
        1100
    ]
})

Group by region:

sales.groupby("Region")

The groups are:

North
South
East

Each group contains the sales records belonging to that region.

6. GroupBy Needs an Analysis Operation

Simply writing:

sales.groupby("Region")

does not answer a business question.

We need to apply an operation.

For example:

sales.groupby("Region")["Sales"].sum()

Now we are asking:

What is the total sales for each region?

This produces a summarized result.

7. Aggregation

Aggregation means reducing multiple values into a summary value.

For example:

1000
1200

can become:

using:

sum()

Similarly:

1000
1200

can become:

using:

mean()

Common aggregation functions include:

count()
sum()
mean()
median()
min()
max()
std()

For GroupBy basics, the most important ones are:

count
sum
mean
min
max

8. GroupBy with sum()

Let’s calculate total sales by region:

sales.groupby("Region")["Sales"].sum()

Conceptually:

North
→ 1000 + 1200

South
→ 1500 + 1800

East
→ 900 + 1100

The result tells us the total sales generated by each region.

This is one of the most common uses of groupby().

9. GroupBy with mean()

Now calculate average sales:

sales.groupby("Region")["Sales"].mean()

The calculation becomes:

North
→ average of North sales

South
→ average of South sales

East
→ average of East sales

This is useful when comparing typical performance across groups.

10. GroupBy with count()

We can count observations in each group.

For example:

sales.groupby("Region")["Sales"].count()

This tells us how many non-missing Sales values each region has.

If the question is:

How many records belong to each region?

you can also use:

sales.groupby("Region").size()

This distinction is important:

count()
→ counts non-missing values in the selected column

size()
→ counts rows in each group

For clean data with no missing values, they may produce the same counts.

11. GroupBy with min()

We can find the minimum value in each group:

sales.groupby("Region")["Sales"].min()

This answers:

What was the smallest recorded sale in each region?

For example:

North → minimum North sale
South → minimum South sale
East  → minimum East sale

12. GroupBy with max()

Similarly:

sales.groupby("Region")["Sales"].max()

answers:

What was the largest recorded sale in each region?

This is useful for identifying peak performance.

13. GroupBy with median()

GroupBy also supports:

sales.groupby("Region")["Sales"].median()

Median can be useful when extreme values may distort the average.

For example:

The mean is strongly influenced by 50000.

The median provides a different view of the typical value.

14. GroupBy with std()

We can also calculate variation:

sales.groupby("Region")["Sales"].std()

This helps answer:

How much does sales vary within each region

Remember from earlier Pandas statistics content:

Series.std()

uses sample standard deviation by default.

15. Selecting the Column to Analyze

Consider:

sales.groupby("Region")

There may be several columns available.

If we only want to analyze Sales, write:

sales.groupby("Region")["Sales"].sum()

This is usually clearer than applying an aggregation to every compatible column.

The structure is:

df.groupby("group_column")["value_column"].aggregation()

For example:

df.groupby("Department")["Salary"].mean()

16. Grouping Student Data

Let’s use a student example:

students = pd.DataFrame({
    "Class": [
        "A",
        "A",
        "B",
        "B",
        "C",
        "C"
    ],
    "Score": [
        80,
        90,
        70,
        75,
        85,
        95
    ]
})

We can calculate the average score by class:

students.groupby("Class")["Score"].mean()

Now we can answer:

What is the average score for each class?

Instead of manually filtering every class, groupby() performs the operation systematically.

17. Total Score by Class

We can calculate total scores:

students.groupby("Class")["Score"].sum()

This answers:

What is the total score across students in each class?

This may be useful in some analyses, although average score is often more appropriate for comparing groups of different sizes.

18. Number of Students per Class

We can count students:

students.groupby("Class").size()

This answers:

How many students are in each class?

For example:

Class A → 2
Class B → 2
Class C → 2

If your DataFrame has one row per student, this is a direct row count.

19. Minimum and Maximum Score by Class

We can calculate:

students.groupby("Class")["Score"].min()

and:

students.groupby("Class")["Score"].max()

This allows us to compare:

Lowest score per class
Highest score per class

Together, these can provide a quick picture of group performance.

20. GroupBy Result and Index

When we run:

students.groupby("Class")["Score"].mean()

the grouping column becomes the index of the resulting Series.

Conceptually:

Class
A    ...
B    ...
C    ...

This is normal Pandas behavior.

If you want the result as a regular DataFrame column structure, you can use:

students.groupby("Class")["Score"].mean().reset_index()

Now Class becomes a regular column.

21. Using as_index=False

Another option is:

students.groupby(
    "Class",
    as_index=False
)["Score"].mean()

This keeps the grouping column as a normal column in the result.

This can be convenient when you plan to continue working with the result as a DataFrame.

For example:

summary = students.groupby(
    "Class",
    as_index=False
)["Score"].mean()

22. GroupBy with Multiple Aggregation Functions

Even at the basic level, we can calculate several summaries.

For example:

students.groupby("Class")["Score"].agg(
    ["count", "mean", "min", "max"]
)

This gives us several statistics for each class.

Conceptually:

Class
  ↓
count
mean
min
max

Although advanced aggregation will be explored more deeply in a later article, this pattern is useful to understand now.

23. Understanding agg()

The agg() method means:

Apply one or more aggregation functions.

For example:

students.groupby("Class")["Score"].agg(
    ["mean", "max"]
)

This calculates:

Average score
Maximum score

for each class.

The important idea is:

groupby()
    ↓
group
    ↓
agg()
    ↓
multiple summaries

More advanced agg() patterns will be covered later.

24. Group Analysis

GroupBy becomes powerful when we use it to answer analytical questions.

For example, with sales data:

sales.groupby("Region")["Sales"].sum()

answers:

Which regions generated the most total sales?

With:

sales.groupby("Region")["Sales"].mean()

we can ask:

Which region has the highest average sale?

With:

sales.groupby("Region")["Sales"].max()

we can ask:

Which region recorded the largest individual sale?

The same dataset can therefore answer different questions depending on the aggregation.

25. GroupBy Is About Questions, Not Just Syntax

A common beginner mistake is learning:

groupby()

as a syntax pattern without understanding the analytical purpose.

Instead, start with the question:

What do I want to compare?

Then identify:

Grouping column

Who or what defines the groups?

Region
Department
Category
Class

Analysis column

What numerical information do I want to analyze?

Sales
Salary
Score
Profit

Aggregation

What do I want to calculate?

sum
mean
count
min
max

Then construct:

df.groupby("group_column")["value_column"].aggregation()

26. A Business Example

Suppose:

sales = pd.DataFrame({
    "Region": [
        "North",
        "North",
        "South",
        "South",
        "East",
        "East"
    ],
    "Sales": [
        1000,
        1500,
        2000,
        2500,
        1200,
        1800
    ]
})

Total sales by region

sales.groupby("Region")["Sales"].sum()

Average sales by region

sales.groupby("Region")["Sales"].mean()

Number of sales records

sales.groupby("Region").size()

Largest sale

sales.groupby("Region")["Sales"].max()

Smallest sale

sales.groupby("Region")["Sales"].min()

Now we have several perspectives on the same dataset.

27. Combining GroupBy with Earlier Binning

In Post #19, we learned:

pd.cut()
pd.qcut()

Suppose we create:

students["Score_Group"] = pd.cut(
    students["Score"],
    bins=[0, 50, 60, 70, 80, 100],
    labels=[
        "Fail",
        "Pass",
        "Good",
        "Very Good",
        "Excellent"
    ]
)

We can then analyze the groups:

students.groupby(
    "Score_Group",
    observed=True
)["Score"].count()

This answers:

How many students belong to each performance group?

This is a natural example of how Pandas operations build on one another.

28. GroupBy with Categorical Data

When grouping by a categorical column created using pd.cut() or pd.qcut(), you may see categories that have no observations.

For example:

Fail
Pass
Good
Very Good
Excellent

If there are no students in Fail, Pandas may still know that Fail is a defined category.

Using:

observed=True

asks Pandas to focus the groupby result on categories actually observed in the data.

For example:

students.groupby(
    "Score_Group",
    observed=True
)["Score"].mean()

This is particularly useful when working with categorical bins.

29. GroupBy and Missing Values

Suppose the grouping column contains missing values:

df = pd.DataFrame({
    "Department": [
        "IT",
        "HR",
        None,
        "IT"
    ],
    "Salary": [
        70000,
        50000,
        60000,
        80000
    ]
})

By default, missing grouping keys are generally excluded from the groups.

If you want to include missing values as their own group, use:

df.groupby(
    "Department",
    dropna=False
)["Salary"].mean()

This is important when missing group labels themselves carry analytical meaning.

30. GroupBy vs Filtering

Filtering answers:

Which rows satisfy a condition?

For example:

students[
    students["Score"] > 80
]

GroupBy answers:

How do values behave within each group?

For example:

students.groupby("Class")["Score"].mean()

So:

Filtering
→ Select rows

GroupBy
→ Analyze groups

They are different operations, although they can be used together.

31. GroupBy vs Sorting

Sorting:

df.sort_values("Sales")

changes the order of records.

Grouping:

df.groupby("Region")["Sales"].sum()

summarizes records by category.

So:

sort_values()
→ Order the data

groupby()
→ Summarize the data by groups

This distinction is important when choosing the right Pandas operation.

32. GroupBy vs Binning

Binning creates groups.

For example:

df["Age_Group"] = pd.cut(...)

GroupBy analyzes those groups:

df.groupby("Age_Group", observed=True)["Salary"].mean()

So the relationship is:

Numerical Data
     ↓
pd.cut() / pd.qcut()
     ↓
Categorical Groups
     ↓
groupby()
     ↓
Aggregation
     ↓
Analysis

This is a powerful Pandas workflow.

33. GroupBy with count() vs size()

This distinction deserves special attention.

Suppose:

df = pd.DataFrame({
    "Department": [
        "IT",
        "IT",
        "HR"
    ],
    "Salary": [
        70000,
        None,
        50000
    ]
})

Using:

df.groupby("Department")["Salary"].count()

counts non-missing salary values.

For IT:

70000
None

only one value is non-missing.

But:

df.groupby("Department").size()

counts rows.

So IT has:

2 rows

Remember:

count()
→ non-null values in selected column

size()
→ rows in each group

34. GroupBy with Different Numerical Columns

Suppose:

employees = pd.DataFrame({
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR"
    ],
    "Salary": [
        70000,
        80000,
        50000,
        55000
    ],
    "Experience": [
        3,
        5,
        2,
        4
    ]
})

We can analyze salary:

employees.groupby(
    "Department"
)["Salary"].mean()

Or experience:

employees.groupby(
    "Department"
)["Experience"].mean()

The grouping column stays the same while the analytical column changes.

35. GroupBy with sum()

Suppose we want total experience by department:

employees.groupby(
    "Department"
)["Experience"].sum()

This may answer:

What is the combined experience represented by employees in each department?

Whether that statistic is useful depends on the analytical context.

This is why aggregation should always be tied to a meaningful question.

36. GroupBy with mean() for Comparison

Average values are often useful when groups have different sizes.

For example:

employees.groupby(
    "Department"
)["Salary"].mean()

can compare typical salary levels between departments.

If one department has many more employees than another, total salary may not be an appropriate measure for comparing individual compensation.

37. GroupBy with min() and max() Together

We can also inspect the range within each group:

employees.groupby(
    "Department"
)["Salary"].agg(
    ["min", "max"]
)

This gives:

Minimum salary
Maximum salary

for each department.

The difference between these values can provide a basic view of salary spread.

38. GroupBy Output as a DataFrame

Suppose:

summary = students.groupby(
    "Class",
    as_index=False
)["Score"].mean()

We can then inspect:

summary

and continue working with the result.

For example:

summary.sort_values(
    "Score",
    ascending=False
)

This combines two previously learned concepts:

GroupBy
    ↓
Aggregation
    ↓
Sorting

The result is a small analytical dataset.

39. GroupBy as a Data Analysis Workflow

A common workflow is:

Load Data
    ↓
Inspect Data
    ↓
Clean Data
    ↓
Create Features / Categories
    ↓
Group Data
    ↓
Aggregate
    ↓
Compare Groups
    ↓
Extract Insights

For example:

students["Performance"] = pd.cut(
    students["Score"],
    bins=[0, 50, 60, 70, 80, 100],
    labels=[
        "Fail",
        "Pass",
        "Good",
        "Very Good",
        "Excellent"
    ]
)

summary = students.groupby(
    "Performance",
    observed=True,
    as_index=False
)["Score"].agg(
    ["count", "mean", "min", "max"]
)

Each step contributes to the final analysis.

40. Common Mistakes

Mistake 1: Calling groupby() Without an Analysis

This:

df.groupby("Region")

only creates the grouping structure.

Usually, you still need:

.sum()
.mean()
.count()

or another operation.

Mistake 2: Grouping by the Wrong Column

Always ask:

What defines my groups?

If you want regional analysis:

df.groupby("Region")

not:

df.groupby("Sales")

unless grouping by exact sales values is genuinely the goal.

Mistake 3: Using count() When You Need Row Counts

Remember:

groupby()["Column"].count()

counts non-null values in that column.

If you need the number of rows:

groupby().size()

is often clearer.

Mistake 4: Using Sum for Every Question

A total is not always meaningful.

For example:

df.groupby("Department")["Salary"].sum()

may tell you total payroll, but not typical employee salary.

For comparison, you may need:

.mean()

or:

.median()

Choose the aggregation based on the question.

Mistake 5: Forgetting Missing Group Values

Missing grouping keys may be excluded by default.

If they matter:

dropna=False

can be useful.

41. A Simple GroupBy Mental Model

Whenever you see:

df.groupby("Category")["Sales"].mean()

read it in plain English:

Group the DataFrame by Category, take the Sales column, and calculate the mean for each category.

For:

df.groupby("Region")["Revenue"].sum()

read:

Group by Region, take Revenue, and calculate total revenue for each region.

This way, Pandas syntax becomes easier to understand.

42. Quick Reference

Basic grouping

df.groupby("Category")

Sum by group

df.groupby("Category")["Sales"].sum()

Mean by group

df.groupby("Category")["Sales"].mean()

Count non-null values

df.groupby("Category")["Sales"].count()

Count rows

df.groupby("Category").size()

Minimum

df.groupby("Category")["Sales"].min()

Maximum

df.groupby("Category")["Sales"].max()

Median

df.groupby("Category")["Sales"].median()

Standard deviation

df.groupby("Category")["Sales"].std()

Multiple aggregations

df.groupby("Category")["Sales"].agg(
    ["count", "mean", "min", "max"]
)

Keep group column as a regular column

df.groupby(
    "Category",
    as_index=False
)["Sales"].mean()

Include missing group labels

df.groupby(
    "Category",
    dropna=False
)["Sales"].mean()

43. Final Takeaways

groupby() is one of the most important Pandas tools for analytical work.

The core pattern is:

Split
  ↓
Apply
  ↓
Combine

In Pandas:

df.groupby("group_column")

creates groups.

Then an aggregation such as:

.sum()
.mean()
.count()
.min()
.max()

summarizes each group.

The general pattern is:

df.groupby(
    "group_column"
)["value_column"].aggregation()

For example:

df.groupby(
    "Department"
)["Salary"].mean()

means:

Calculate the average salary for each department.

The most important concepts to remember are:

groupby() divides data into logical groups.
Aggregation summarizes each group.
sum() calculates totals.
mean() calculates averages.
count() counts non-missing values in the selected column.
size() counts rows.
min() and max() identify group-level extremes.
as_index=False keeps the grouping key as a normal column.
dropna=False can include missing group labels.
GroupBy works naturally with categories created using pd.cut() and pd.qcut().

Most importantly:

GroupBy is not just a Pandas function. It is a way of asking structured questions about subsets of your data.

44. Where We Go Next

We now understand the foundation:

Data
 ↓
Group
 ↓
Aggregate
 ↓
Analyze

But real-world analysis often requires more than one grouping column and more flexible aggregation rules.

In Post #21, we will move into:

Pandas GroupBy Advanced: Multiple Groups, agg() and Custom Aggregation

We will learn how to:

Group by multiple columns
Apply multiple aggregations
Use different functions on different columns
Rename aggregation results
Build more detailed analytical summaries

This will take our basic groupby() knowledge into more realistic data-analysis workflows.

About This Series

This article is part of Data Analysis with Pandas, a practical series designed to help developers and beginners build strong foundations in Python data analysis.

The goal is not simply to memorize Pandas methods.

Instead, the series focuses on understanding how individual operations connect:

Load
  ↓
Inspect
  ↓
Clean
  ↓
Transform
  ↓
Feature Engineer
  ↓
Group
  ↓
Aggregate
  ↓
Analyze
  ↓
Visualize

Each article builds toward a complete data-analysis workflow.

Author

Engr. Md. Monjur Bakth Mazumder

GitHub:
https://github.com/monjurmbm404/Python-Data-Science-Tools

Final Thought

The real power of groupby() is not the syntax.

It is the ability to turn a large collection of individual records into meaningful comparisons.

Instead of asking:

What happened to every row?

we can ask:

What happened in each region?
What happened in each department?
What happened in each class?
What happened in each category?

That shift — from individual records to meaningful groups — is one of the foundations of practical data analysis.

And once you become comfortable with:

df.groupby()

Pandas becomes much more powerful for answering real-world analytical questions.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas Binning: cut() and qcut() for Categorical Data Analysis

Engr. Md Monjur Bakth Mazumder — Thu, 12 Nov 2026 14:01:03 GMT

Series: Data Analysis with Pandas
Part: 19

A Practical Guide to Converting Continuous Numerical Data into Meaningful Categories with pd.cut() and pd.qcut()

Real-world datasets often contain continuous numerical variables such as:

Age
Salary
Score
Income
Experience
Transaction Amount
Temperature

These values are useful in their original numerical form.

But sometimes, analyzing every individual number is not the easiest way to understand a dataset.

For example, instead of looking at:

we may want:

18–25   → Young Adult
26–35   → Adult
36–50   → Middle Age
51+     → Senior

This process is called binning.

Pandas provides two especially useful functions for this:

pd.cut()
pd.qcut()

They allow us to transform continuous numerical values into categorical groups.

In this article, we will learn:

What binning is
Why binning is useful
Continuous vs categorical data
pd.cut()
Equal-width binning
Age groups
Score groups
pd.qcut()
Quantile-based binning
cut() vs qcut()
Practical data-analysis examples
Common mistakes
When to use each method

1. What Is Binning?

Binning means dividing continuous numerical values into a set of intervals or groups.

Suppose we have student scores:

scores = [
    45,
    52,
    61,
    67,
    73,
    81,
    89,
    95
]

Instead of analyzing every individual score, we might create:

0–49    → Fail
50–59   → Pass
60–69   → Good
70–79   → Very Good
80–100  → Excellent

The original numerical variable:

Score

has now been transformed into a categorical representation:

Score_Group

Conceptually:

Continuous Numerical Data
            ↓
          Binning
            ↓
Categorical Groups

2. Continuous Data vs Categorical Data

Understanding this distinction is important.

Continuous numerical data

A numerical variable can contain many possible values.

Examples:

Age
Salary
Height
Weight
Temperature
Score

For example:

Age
21
21.5
21.8
22
22.3

Categorical data

Categorical data represents groups or labels.

Examples:

Department
Gender
City
Grade
Age_Group
Score_Group

For example:

Age_Group
Young
Adult
Middle Age
Senior

Binning provides a way to derive categorical information from numerical data.

3. Why Use Binning?

Binning can make numerical data easier to understand and analyze.

It can help with:

descriptive analysis
reporting
dashboards
segmentation
visualization
frequency analysis
business analysis
exploratory data analysis

For example, instead of asking:

What is the average score of every individual student?

we might ask:

How many students are in each performance category?

That can often be easier to communicate.

4. pd.cut()

The first important Pandas function is:

pd.cut()

pd.cut() divides numerical values according to explicitly defined intervals.

For example:

import pandas as pd

scores = pd.Series([
    45,
    52,
    61,
    67,
    73,
    81,
    89,
    95
])

We can create score groups:

score_groups = pd.cut(
    scores,
    bins=[0, 50, 60, 70, 80, 100]
)

Now each score belongs to one interval.

5. Understanding bins

The most important parameter in pd.cut() is:

bins=

For example:

bins=[
    0,
    50,
    60,
    70,
    80,
    100
]

These boundaries define the intervals.

Conceptually:

0 ─── 50 ─── 60 ─── 70 ─── 80 ─── 100

This creates five intervals.

By default, pd.cut() uses right-closed intervals:

(0, 50]
(50, 60]
(60, 70]
(70, 80]
(80, 100]

That means:

50 → belongs to (0, 50]
60 → belongs to (50, 60]

Understanding interval boundaries becomes important when values fall exactly on a boundary.

6. Adding Meaningful Labels

The interval representation is useful, but labels are often easier to understand.

scores = pd.Series([
    45,
    52,
    61,
    67,
    73,
    81,
    89,
    95
])

scores_group = pd.cut(
    scores,
    bins=[0, 50, 60, 70, 80, 100],
    labels=[
        "Fail",
        "Pass",
        "Good",
        "Very Good",
        "Excellent"
    ]
)

Now the result is easier to interpret:

45 → Fail
52 → Pass
61 → Good
67 → Good
73 → Very Good
81 → Excellent
89 → Excellent
95 → Excellent

This is one of the most common uses of pd.cut().

7. Binning a DataFrame Column

Instead of working with a standalone Series, we can add a categorical column to a DataFrame.

students = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David",
        "Emma"
    ],
    "Score": [
        45,
        58,
        67,
        82,
        94
    ]
})

Create a new column:

students["Score_Group"] = pd.cut(
    students["Score"],
    bins=[0, 50, 60, 70, 80, 100],
    labels=[
        "Fail",
        "Pass",
        "Good",
        "Very Good",
        "Excellent"
    ]
)

Now the DataFrame contains:

Name
Score
Score_Group

The original score is preserved.

This is generally preferable to replacing the original numerical feature immediately.

8. Continuous → Categorical

This transformation can be represented as:

Score
  ↓
45
58
67
82
94
  ↓
Binning
  ↓
Fail
Pass
Good
Excellent
Excellent

The important idea is:

Binning converts numerical ranges into categorical groups.

The original score contains precise numerical information.

The new category provides a higher-level interpretation.

9. Age Grouping with pd.cut()

Age is another common variable for binning.

Suppose:

df = pd.DataFrame({
    "Age": [
        18,
        22,
        27,
        34,
        41,
        49,
        56,
        68
    ]
})

We can define age groups:

df["Age_Group"] = pd.cut(
    df["Age"],
    bins=[
        0,
        18,
        30,
        45,
        60,
        100
    ],
    labels=[
        "Teen",
        "Young Adult",
        "Adult",
        "Middle Age",
        "Senior"
    ]
)

Now we have:

Age
↓
Age_Group

For example:

22 → Young Adult
34 → Adult
49 → Middle Age
68 → Senior

The exact age boundaries should always reflect the purpose of the analysis.

10. include_lowest=True

By default, the first interval may exclude its lower boundary.

If you want the lowest value to be included explicitly, use:

df["Age_Group"] = pd.cut(
    df["Age"],
    bins=[0, 18, 30, 45, 60, 100],
    labels=[
        "Teen",
        "Young Adult",
        "Adult",
        "Middle Age",
        "Senior"
    ],
    include_lowest=True
)

This is useful when your first boundary represents the actual minimum of the intended range.

11. Changing Interval Closure with right=False

By default:

pd.cut()

uses right-closed intervals.

If you want left-closed intervals instead, use:

pd.cut(
    df["Age"],
    bins=[0, 20, 30, 40, 50],
    right=False
)

The intervals become conceptually:

[0, 20)
[20, 30)
[30, 40)
[40, 50)

Now:

20 → belongs to [20, 30)

This can be useful when your business or analytical definition naturally uses left-closed ranges.

12. Equal-Width Binning

One important concept behind pd.cut() is equal-width binning.

Suppose the numerical range is:

0 → 100

and we want four bins.

The total range is:

Divide by:

and each bin has width:

So we get approximately:

Each interval has the same numerical width.

This is called:

Equal-width binning

13. Let pd.cut() Create Equal-Width Bins Automatically

You don’t always have to manually specify the boundaries.

For example:

scores = pd.Series([
    10,
    20,
    35,
    45,
    55,
    70,
    80,
    95
])

We can request four bins:

pd.cut(
    scores,
    bins=4
)

Pandas calculates the interval boundaries across the observed range.

This is useful when you want equal-width numerical intervals without manually defining every boundary.

14. Equal-Width Binning with Labels

We can also provide labels:

scores_group = pd.cut(
    scores,
    bins=4,
    labels=[
        "Low",
        "Medium",
        "High",
        "Very High"
    ]
)

Now the numerical range is divided into four equal-width intervals and represented using meaningful labels.

15. When Equal-Width Binning Is Useful

Equal-width bins work well when the numerical range itself has meaningful interpretation.

For example:

Temperature
0–10
10–20
20–30
30–40

or:

Age
0–20
20–40
40–60
60–80

The important characteristic is:

Each interval covers approximately the same numerical range.

16. A Problem with Equal-Width Binning

Equal-width bins do not guarantee that each group contains a similar number of observations.

Suppose:

Scores:
10, 11, 12, 13, 14, 15, 90, 95

If we create four equal-width bins, most observations may fall into the lower bins while only a few observations fall into the upper bins.

You might get something conceptually like:

Bin 1 → many values
Bin 2 → very few values
Bin 3 → 0 values
Bin 4 → 2 values

This is where:

pd.qcut()

becomes useful.

17. What Is pd.qcut()?

pd.qcut() performs quantile-based binning.

Instead of dividing the numerical range into equal-width intervals, it attempts to divide the observations into groups with approximately equal numbers of observations.

For example:

scores = pd.Series([
    10,
    20,
    30,
    40,
    50,
    60,
    70,
    80,
    90,
    100
])

We can create four quantile groups:

pd.qcut(
    scores,
    q=4
)

This creates approximately:

Q1 → lowest 25%
Q2 → next 25%
Q3 → next 25%
Q4 → highest 25%

18. Quantile-Based Binning

The word quantile is important here.

If we use:

q=4

we are asking for four groups based on the distribution of the observations.

Conceptually:

Lowest 25%
     ↓
Q1

Next 25%
     ↓
Q2

Next 25%
     ↓
Q3

Highest 25%
     ↓
Q4

The intervals themselves may have very different numerical widths.

That is the key difference from equal-width binning.

19. qcut() with Labels

We can give meaningful labels:

scores_group = pd.qcut(
    scores,
    q=4,
    labels=[
        "Q1",
        "Q2",
        "Q3",
        "Q4"
    ]
)

Or:

scores_group = pd.qcut(
    scores,
    q=4,
    labels=[
        "Low",
        "Medium-Low",
        "Medium-High",
        "High"
    ]
)

The labels describe the relative position of each observation in the distribution.

20. qcut() for Customer Segmentation

Suppose we have customer spending:

customers = pd.DataFrame({
    "Customer": [
        "A",
        "B",
        "C",
        "D",
        "E",
        "F",
        "G",
        "H"
    ],
    "Spending": [
        100,
        150,
        200,
        300,
        500,
        700,
        1000,
        2500
    ]
})

We can divide customers into four spending groups:

customers["Spending_Group"] = pd.qcut(
    customers["Spending"],
    q=4,
    labels=[
        "Low",
        "Medium-Low",
        "Medium-High",
        "High"
    ]
)

This creates relative customer segments.

For example:

Low
Medium-Low
Medium-High
High

The groups are based on the distribution of customers rather than fixed spending ranges.

21. Age Groups with Quantiles

Suppose we want to divide a population into four equally sized age segments.

df["Age_Quartile"] = pd.qcut(
    df["Age"],
    q=4,
    labels=[
        "Q1",
        "Q2",
        "Q3",
        "Q4"
    ]
)

Here:

Q1 → youngest group
Q2 → lower-middle group
Q3 → upper-middle group
Q4 → oldest group

Notice that the age boundaries are determined by the actual distribution of the data.

22. cut() vs qcut()

The easiest way to remember the difference is:

pd.cut()
→ Divide the numerical range

pd.qcut()
→ Divide the observations by quantiles

So:

cut()
→ Equal-width / manually defined intervals

qcut()
→ Quantile-based intervals

This is the core distinction.

23. Example: Same Data, Different Binning

Consider:

scores = pd.Series([
    10,
    12,
    15,
    18,
    20,
    22,
    80,
    85,
    90,
    95
])

Using cut()

pd.cut(
    scores,
    bins=4
)

The range is divided into equal-width intervals.

Because the data contains a large gap between 22 and 80, some bins may contain very few or no observations.

Using qcut()

pd.qcut(
    scores,
    q=4
)

The observations are divided according to their rank in the distribution.

The groups are therefore more balanced in terms of observation counts.

24. When Should You Use pd.cut()?

Use pd.cut() when the actual numerical boundaries matter.

Examples:

Age:
0–18
19–30
31–45
46–60
61+

Score:
0–49
50–59
60–69
70–79
80–100

Income:
0–30K
30K–60K
60K–100K
100K+

These categories have business or domain meaning.

In such situations, manually defined boundaries are often more meaningful than quantiles.

25. When Should You Use pd.qcut()?

Use pd.qcut() when you want groups based on relative position in the dataset.

Examples:

Top 10% customers
Bottom 25% students
Customer spending quartiles
Income percentiles
Ranking observations by distribution

For example:

df["Customer_Segment"] = pd.qcut(
    df["Revenue"],
    q=4,
    labels=[
        "Low",
        "Medium-Low",
        "Medium-High",
        "High"
    ]
)

Here the exact revenue boundaries are determined by the data.

26. Percentile-Based Binning

qcut() can also be used for more granular segmentation.

For example:

df["Revenue_Decile"] = pd.qcut(
    df["Revenue"],
    q=10,
    labels=False
)

This creates approximately ten groups:

0
1
2
...
9

These represent decile positions.

You can also provide labels:

df["Revenue_Decile"] = pd.qcut(
    df["Revenue"],
    q=10,
    labels=[
        "D1",
        "D2",
        "D3",
        "D4",
        "D5",
        "D6",
        "D7",
        "D8",
        "D9",
        "D10"
    ]
)

27. labels=False

Sometimes you don’t want text labels.

For example:

df["Score_Bin"] = pd.cut(
    df["Score"],
    bins=4,
    labels=False
)

Pandas returns integer-coded bin positions.

Similarly:

df["Score_Quartile"] = pd.qcut(
    df["Score"],
    q=4,
    labels=False
)

This can be useful for further numerical processing.

However, remember that these integers represent categories or ordered bins; they do not automatically imply equal numerical differences between categories.

28. Checking Bin Frequencies

After binning, one of the most important steps is checking how many observations fall into each group.

For example:

df["Score_Group"].value_counts()

You can also preserve the bin order:

df["Score_Group"].value_counts(
    sort=False
)

This is useful because the categories may have a natural order such as:

Fail
Pass
Good
Very Good
Excellent

29. Binning and value_counts()

Binning becomes particularly powerful when combined with frequency analysis.

For example:

score_distribution = (
    students["Score_Group"]
    .value_counts(sort=False)
)

Now we can answer:

How many students belong to each performance group?

This is often more useful for reporting than looking at every individual score.

30. Binning with GroupBy

Once a categorical bin has been created, it can be used with:

groupby()

For example:

students.groupby(
    "Score_Group",
    observed=True
)["Score"].mean()

This can answer:

What is the average score within each score group?

Similarly:

customers.groupby(
    "Spending_Group",
    observed=True
)["Spending"].mean()

can help compare average spending across customer segments.

Using observed=True is useful with categorical groupers when you want the result focused on categories actually observed in the data.

31. Binning and Aggregation

Suppose we have:

sales = pd.DataFrame({
    "Sales": [
        120,
        250,
        450,
        600,
        900,
        1200
    ]
})

Create sales categories:

sales["Sales_Group"] = pd.cut(
    sales["Sales"],
    bins=[
        0,
        300,
        700,
        1500
    ],
    labels=[
        "Low",
        "Medium",
        "High"
    ]
)

Then:

sales.groupby(
    "Sales_Group",
    observed=True
)["Sales"].agg(
    ["count", "mean", "sum"]
)

Now the bins become analytical groups.

This is where binning becomes more than just categorization.

32. Binning for Visualization

Binned data can also make visualization easier.

For example, instead of plotting hundreds of unique ages, you might analyze:

Age Group
18–25
26–35
36–45
46–55
56+

This can make patterns easier to communicate in:

bar charts
histograms
dashboards
reports

The appropriate visualization still depends on the analytical question.

33. Binning Is Not the Same as a Histogram

A histogram groups numerical observations into bins for visualization.

Binning with:

pd.cut()

or:

pd.qcut()

creates a new categorical representation that can be stored and analyzed.

For example:

df["Age_Group"] = pd.cut(...)

creates an actual DataFrame column.

So:

Histogram
→ primarily a visualization

Binning
→ data transformation / categorization

The two concepts are related but not identical.

34. Handling Boundary Values Carefully

Suppose:

df["Score"] = [
    50,
    60,
    70,
    80,
    90
]

and:

pd.cut(
    df["Score"],
    bins=[0, 50, 60, 70, 80, 100]
)

You need to understand which interval contains values exactly equal to the boundaries.

By default, the intervals are right-closed.

If your business rule says:

you need to define your bins and interval closure carefully.

Never assume boundary behavior without checking it.

35. Custom Business Categories

Binning is especially useful when categories come from business rules.

For example:

df["Customer_Value"] = pd.cut(
    df["Annual_Spending"],
    bins=[
        0,
        10000,
        50000,
        100000,
        float("inf")
    ],
    labels=[
        "Low Value",
        "Medium Value",
        "High Value",
        "Premium"
    ],
    include_lowest=True
)

This creates business-oriented customer segments.

The boundaries are meaningful because they were defined according to a business rule.

36. Using float("inf")

Sometimes the final category has no practical upper limit.

For example:

100,000+

Instead of choosing an arbitrary maximum, we can use:

float("inf")

Example:

bins = [
    0,
    10000,
    50000,
    100000,
    float("inf")
]

This means:

0–10K
10K–50K
50K–100K
100K+

This is useful for open-ended categories.

37. Handling Values Outside the Defined Range

Suppose:

df["Age"] = [
    18,
    25,
    40,
    70,
    110
]

and your bins only cover:

bins = [
    0,
    18,
    30,
    50,
    80
]

The value 110 does not belong to any interval.

pd.cut() will therefore produce:

NaN

for that value.

Always make sure your bin boundaries cover the expected data range.

38. duplicates="drop" with qcut()

qcut() depends on quantile boundaries.

If many values are identical, some quantile boundaries can become identical too.

For example:

scores = pd.Series([
    50,
    50,
    50,
    60,
    60,
    70,
    70,
    70
])

Trying to create many quantile bins can result in duplicate boundaries.

In such cases, you may encounter an error.

One option is:

pd.qcut(
    scores,
    q=4,
    duplicates="drop"
)

This tells Pandas to drop duplicate bin edges rather than failing because of them.

However, note that the requested number of bins may be reduced.

39. precision

For interval-based output, you can control displayed precision:

pd.cut(
    df["Age"],
    bins=4,
    precision=1
)

This mainly affects how interval boundaries are displayed.

It does not change the underlying conceptual binning logic.

40. Ordered Categories

The output of pd.cut() and pd.qcut() is typically categorical and ordered.

For example:

Low
Medium
High

has a natural order.

This is useful because:

df["Score_Group"].dtype

will show a categorical dtype.

You can inspect the categories:

df["Score_Group"].cat.categories

This helps when working with ordered categorical data.

41. Practical Example: Student Performance

Let’s build a more complete example.

students = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David",
        "Emma",
        "Frank"
    ],
    "Score": [
        45,
        58,
        67,
        74,
        86,
        95
    ]
})

Create performance groups:

students["Performance"] = pd.cut(
    students["Score"],
    bins=[
        0,
        50,
        60,
        70,
        80,
        100
    ],
    labels=[
        "Fail",
        "Pass",
        "Good",
        "Very Good",
        "Excellent"
    ],
    include_lowest=True
)

Now count students:

students["Performance"].value_counts(
    sort=False
)

This gives us a simple performance distribution.

42. Practical Example: Age Segmentation

people = pd.DataFrame({
    "Age": [
        19,
        22,
        28,
        34,
        42,
        51,
        63,
        72
    ]
})

Create groups:

people["Age_Group"] = pd.cut(
    people["Age"],
    bins=[
        0,
        18,
        30,
        45,
        60,
        float("inf")
    ],
    labels=[
        "Teen",
        "Young Adult",
        "Adult",
        "Middle Age",
        "Senior"
    ],
    include_lowest=True
)

Now we can analyze:

people["Age_Group"].value_counts(
    sort=False
)

This turns individual ages into meaningful segments.

43. Practical Example: Customer Quartiles

customers = pd.DataFrame({
    "Customer": [
        "A",
        "B",
        "C",
        "D",
        "E",
        "F",
        "G",
        "H"
    ],
    "Revenue": [
        100,
        150,
        200,
        250,
        400,
        600,
        1000,
        2000
    ]
})

Create revenue quartiles:

customers["Revenue_Group"] = pd.qcut(
    customers["Revenue"],
    q=4,
    labels=[
        "Low",
        "Medium-Low",
        "Medium-High",
        "High"
    ]
)

Now we can analyze customer groups based on relative revenue position.

44. Choosing Between cut() and qcut()

Ask yourself one question:

Do I care about fixed numerical ranges or balanced groups?

If you care about:

use:

pd.cut()

If you care about:

Bottom 25%
Next 25%
Next 25%
Top 25%

use:

pd.qcut()

A simple mental model:

cut()
→ "Where does this value fall on the numerical scale?"

qcut()
→ "Where does this value rank within the distribution?"

45. Common Mistakes

Mistake 1: Choosing Arbitrary Bins

Don’t create categories without understanding the analytical purpose.

Bad reasoning:

Let's divide everything into five groups because five looks good.

Better reasoning:

What question am I trying to answer?

Mistake 2: Forgetting Boundary Behavior

Always understand:

right=
include_lowest=

when exact boundaries matter.

Mistake 3: Assuming qcut() Creates Equal-Width Ranges

It does not.

qcut() creates quantile-based groups.

The numerical width of each group can be very different.

Mistake 4: Assuming cut() Creates Equal Number of Observations

It does not.

Equal-width intervals can contain very different numbers of observations.

Mistake 5: Ignoring Duplicate Values with qcut()

If many observations have the same value, quantile boundaries may overlap.

Consider:

duplicates="drop"

when appropriate.

Mistake 6: Throwing Away the Original Numerical Column

The original numerical variable often contains valuable information.

Prefer:

df["Age_Group"] = ...

instead of immediately replacing:

df["Age"]

unless you have a specific reason.

46. A Practical Binning Workflow

A useful workflow is:

Load Data
    ↓
Inspect Numerical Variable
    ↓
Understand Distribution
    ↓
Define Analytical Goal
    ↓
Choose cut() or qcut()
    ↓
Define Bins / Quantiles
    ↓
Create Categorical Column
    ↓
Check Value Counts
    ↓
Validate Boundaries
    ↓
Analyze by Group

For example:

df["Age_Group"] = pd.cut(...)

then:

df["Age_Group"].value_counts(
    sort=False
)

and finally:

df.groupby(
    "Age_Group",
    observed=True
)["Salary"].mean()

This turns a simple transformation into a useful analytical workflow.

47. Quick Reference

Manual intervals with cut()

df["Age_Group"] = pd.cut(
    df["Age"],
    bins=[0, 18, 30, 45, 60, float("inf")],
    labels=[
        "Teen",
        "Young Adult",
        "Adult",
        "Middle Age",
        "Senior"
    ],
    include_lowest=True
)

Equal-width bins

df["Score_Group"] = pd.cut(
    df["Score"],
    bins=4
)

Quantile-based bins

df["Score_Quartile"] = pd.qcut(
    df["Score"],
    q=4
)

Quantile bins with labels

df["Score_Quartile"] = pd.qcut(
    df["Score"],
    q=4,
    labels=[
        "Q1",
        "Q2",
        "Q3",
        "Q4"
    ]
)

Integer bin codes

df["Score_Bin"] = pd.cut(
    df["Score"],
    bins=4,
    labels=False
)

Count categories

df["Score_Group"].value_counts(
    sort=False
)

Analyze by bins

df.groupby(
    "Score_Group",
    observed=True
)["Score"].mean()

Handle duplicate quantile edges

df["Score_Quartile"] = pd.qcut(
    df["Score"],
    q=4,
    duplicates="drop"
)

48. Final Takeaways

Binning is a powerful technique for converting continuous numerical data into meaningful categories.

The two most important Pandas functions are:

pd.cut()
pd.qcut()

Remember the core distinction:

pd.cut()
→ fixed numerical intervals
→ manually defined or equal-width bins

pd.qcut()
→ quantile-based intervals
→ approximately balanced number of observations

Use pd.cut() when numerical boundaries have meaning.

For example:

Score:
0–49
50–59
60–69
70–79
80–100

Use pd.qcut() when relative position matters.

For example:

Bottom 25%
Next 25%
Next 25%
Top 25%

Binning is particularly useful for:

age segmentation
score categories
customer segmentation
income groups
sales ranges
percentile analysis
EDA
reporting
dashboards

But remember:

Binning simplifies numerical information by grouping values. That simplification should be intentional.

A precise numerical value can contain more information than its category.

For example:

Score = 79

contains more information than:

Score_Group = "Very Good"

So keep the original numerical feature whenever it remains useful.

49. Where We Go Next

We have now learned how to:

Create Features
      ↓
Transform Numerical Data
      ↓
Scale Features
      ↓
Create Categories

In Post #20, we will move into one of the most important concepts in Pandas data analysis:

Pandas GroupBy Basics: Grouping Data for Analysis

We will learn how to group records and calculate meaningful statistics such as:

count
sum
mean
min
max

This will connect naturally with the categorical groups we created using pd.cut() and pd.qcut().

About This Series

This article is part of Data Analysis with Pandas, a practical series designed to help developers and beginners build strong foundations in Python data analysis.

The goal is not simply to memorize Pandas syntax.

Instead, the focus is on understanding:

How to transform raw data into meaningful analytical information.

From data cleaning and transformation to grouping, aggregation, visualization, and machine-learning preparation, each article builds on the previous concepts.

Author

Engr. Md. Monjur Bakth Mazumder

GitHub:
https://github.com/monjurmbm404/Python-Data-Science-Tools

Final Thought

Good data analysis is often about choosing the right level of abstraction.

Sometimes you need the exact value:

Score = 87

Sometimes a category is more useful:

Score = Excellent

And sometimes the best approach is to keep both:

Score
Score_Group

That is the real power of binning.

With pd.cut() and pd.qcut(), Pandas gives us a flexible way to transform continuous numerical data into categories that can be easier to compare, summarize, visualize, and communicate.

The key is not simply knowing how to create bins.

It is knowing why those bins should exist in the first place.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Data Scaling with Pandas: Normalization, Min-Max Scaling and Numerical Transformation

Engr. Md Monjur Bakth Mazumder — Wed, 11 Nov 2026 14:01:02 GMT

Series: Data Analysis with Pandas
Part: 18

A Practical Guide to Scaling Numerical Data, Transforming Columns and Preparing Features for Machine Learning

Real-world datasets often contain numerical variables with very different ranges.

For example:

Age          → 18 to 70
Salary       → 20,000 to 500,000
Experience   → 0 to 30
Score        → 0 to 100

These variables are all numerical, but they are not on the same scale.

This difference can matter when performing:

numerical analysis
comparisons
visualization
statistical modeling
machine-learning preprocessing

Pandas gives us flexible tools for transforming numerical columns, while libraries such as scikit-learn provide dedicated preprocessing tools for machine learning.

In this article, we will learn:

What scaling means
What normalization means
Min-Max scaling
Numerical transformations
Rounding
Creating transformed columns
How scaling connects to machine-learning preprocessing
When scaling is useful and when it is not

1. What Is Data Scaling?

Data scaling means transforming numerical values so that they follow a different numerical range or scale.

Suppose we have:

import pandas as pd

df = pd.DataFrame({
    "Age": [20, 30, 40, 50],
    "Salary": [30000, 50000, 80000, 120000],
    "Score": [60, 70, 85, 95]
})

Notice the ranges:

Age
20 → 50

Salary
30,000 → 120,000

Score
60 → 95

The variables have completely different magnitudes.

A scaling transformation can convert them into a more comparable range.

2. Why Does Scaling Matter?

Consider:

Age = 30
Salary = 80,000
Score = 85

The salary value is numerically much larger than the other values.

If an algorithm relies on numerical magnitude or distance, this difference can influence the result.

For example, distance-based algorithms may be sensitive to feature scale.

Common examples include:

K-Nearest Neighbors
K-Means clustering
Support Vector Machines
some optimization-based machine-learning models

However, scaling is not equally important for every algorithm.

Many tree-based models, such as decision trees and random forests, are generally much less sensitive to feature scale.

So the correct principle is:

Scaling is useful when the model or analysis is sensitive to the scale of numerical features.

3. Scaling vs Transformation

These concepts are related but not identical.

Scaling

Scaling changes the numerical range or magnitude.

For example:

0 → 100

can become:

0 → 1

Transformation

Transformation changes the representation or distribution of the values.

Examples include:

log transformation
square-root transformation
power transformation
rounding

So:

Scaling
→ Change the scale

Transformation
→ Change how the values are represented

A transformation may also affect scale, but its primary purpose can be different.

4. What Is Normalization?

The word normalization is used in different contexts, so it is important to define what we mean.

In many machine-learning and data-analysis contexts, normalization refers to transforming numerical values to a common range, often:

0 → 1

A common method is Min-Max scaling.

The formula is:

x_scaled = (x - min(x)) / (max(x) - min(x))

This transforms:

minimum → 0
maximum → 1

and values between them are mapped proportionally into that range.

5. Min-Max Scaling

Let’s use a simple example:

df = pd.DataFrame({
    "Score": [50, 60, 70, 80, 100]
})

The minimum is:

The maximum is:

For the value 70:

(70 - 50) / (100 - 50)

which gives:

20 / 50 = 0.4

So:

70 → 0.4

6. Implementing Min-Max Scaling with Pandas

We can implement the formula directly:

df["Score_MinMax"] = (
    (df["Score"] - df["Score"].min())
    /
    (df["Score"].max() - df["Score"].min())
)

Now:

minimum value → 0
maximum value → 1

This creates a new transformed column while preserving the original data.

That is often a good practice during analysis:

Original column
      +
Transformed column

instead of immediately overwriting the original values.

7. Why Create a New Column?

Suppose we have:

df["Score"]

and create:

df["Score_MinMax"]

We now have both:

Score
Score_MinMax

This makes comparison and validation easier.

For example:

print(
    df[
        ["Score", "Score_MinMax"]
    ]
)

We can check whether the transformation produced the expected results.

Once the transformation is validated, you can decide whether the original column should remain.

8. Min-Max Scaling with a Different Range

Min-Max scaling does not have to be limited to:

0 → 1

A generalized formula can map values to another range:

x_scaled =
((x - min) / (max - min))
× (new_max - new_min)
+ new_min

For example, suppose we want:

0 → 100

We can write:

df["Score_100"] = (
    (
        (df["Score"] - df["Score"].min())
        /
        (df["Score"].max() - df["Score"].min())
    ) * 100
)

Now the minimum becomes approximately:

and the maximum:

This can be useful when a normalized percentage-like scale is easier to interpret.

9. Min-Max Scaling with a Reusable Function

If you need to apply Min-Max scaling repeatedly, defining a function can improve readability.

def min_max_scale(series):
    return (
        (series - series.min())
        /
        (series.max() - series.min())
    )

Then:

df["Score_Scaled"] = min_max_scale(
    df["Score"]
)

This is useful when working with several numerical columns.

10. Scaling Multiple Columns

Suppose:

df = pd.DataFrame({
    "Age": [20, 30, 40, 50],
    "Salary": [30000, 50000, 80000, 120000],
    "Score": [60, 70, 85, 95]
})

We can scale selected columns:

columns = [
    "Age",
    "Salary",
    "Score"
]

for column in columns:
    df[f"{column}_Scaled"] = (
        (df[column] - df[column].min())
        /
        (df[column].max() - df[column].min())
    )

This creates:

Age_Scaled
Salary_Scaled
Score_Scaled

The original columns remain available for interpretation.

11. Important Edge Case: Constant Columns

Consider:

df = pd.DataFrame({
    "Age": [30, 30, 30, 30]
})

Here:

min = max = 30

The Min-Max denominator becomes:

30 - 30 = 0

So the standard formula is undefined.

Before scaling, it is useful to check whether a column has any variation:

df["Age"].nunique()

If the result is:

then every value is identical.

Such a feature usually provides no variation for many analytical or modeling tasks.

12. Normalization Does Not Mean Making Data “Normal”

This is an important terminology point.

Normalization in the context of Min-Max scaling does not mean:

Make the data follow a normal/Gaussian distribution.

Min-Max scaling changes the range.

It does not automatically make the distribution normal.

For example:

Original distribution
      ↓
Min-Max scaling
      ↓
Range becomes 0 → 1

The overall shape of the distribution can remain similar.

13. Standardization Is Different

Another common preprocessing technique is standardization.

Standardization transforms values using:

z = (x - mean) / standard_deviation

The resulting values are centered around:

mean ≈ 0

with:

standard deviation ≈ 1

This is different from Min-Max scaling.

Conceptually:

Min-Max Scaling
→ fixed range, commonly 0 to 1

Standardization
→ centered around 0 with scale related to standard deviation

This distinction becomes important in machine-learning preprocessing.

14. Numerical Transformation

Scaling is only one type of numerical transformation.

We can also transform numerical columns using mathematical operations.

For example:

df["Salary_K"] = (
    df["Salary"] / 1000
)

This transforms:

into:

The underlying information remains the same, but the representation becomes easier to read.

15. Percentage Transformation

Suppose:

df = pd.DataFrame({
    "Passed": [80, 70, 95],
    "Total": [100, 100, 100]
})

We can create a percentage:

df["Pass_Percentage"] = (
    df["Passed"] /
    df["Total"]
) * 100

This converts a ratio into a percentage representation.

16. Log Transformation

Some numerical variables have highly skewed distributions.

For example:

Income
Revenue
Transaction Amount
Population

can sometimes contain a small number of very large values.

A logarithmic transformation can compress large values.

Using NumPy:

import numpy as np

df["Log_Revenue"] = np.log1p(
    df["Revenue"]
)

np.log1p(x) calculates:

log(1 + x)

and is often convenient when zero values may be present.

However, log transformation changes the interpretation of the variable.

It should therefore be used when there is a clear analytical reason.

17. Square-Root Transformation

Another possible transformation is:

df["Sqrt_Value"] = np.sqrt(
    df["Value"]
)

This can reduce the influence of larger values in some datasets.

But it requires non-negative values.

For example:

become:

Again, the transformation should be chosen based on the data and analytical objective.

18. Absolute Value Transformation

Suppose:

df = pd.DataFrame({
    "Change": [-10, 5, -7, 12]
})

We can create:

df["Absolute_Change"] = (
    df["Change"].abs()
)

Now:

This is useful when the magnitude matters more than the direction.

19. Rounding Numerical Values

Another common transformation is rounding.

Suppose:

df = pd.DataFrame({
    "Average": [
        87.45678,
        72.89123,
        91.23456
    ]
})

We can round to two decimal places:

df["Average_Rounded"] = (
    df["Average"].round(2)
)

Now the values become easier to display:

87.46
72.89
91.23

20. Rounding to Zero Decimal Places

You can also round to the nearest integer:

df["Rounded"] = (
    df["Average"].round(0)
)

Or:

df["Rounded"] = (
    df["Average"].round().astype(int)
)

Be careful with the second approach when missing values are present, because standard integer dtype cannot represent NaN in the same way as nullable Pandas integer types.

21. Rounding and Data Accuracy

Rounding is usually a presentation transformation, not something you should automatically do to the underlying analytical data.

For example:

87.45678

might be displayed as:

87.46

but the original precision may still be valuable for calculations.

A good practice is often:

Raw value
   ↓
Keep precision
   ↓
Round for presentation

rather than rounding too early and permanently losing information.

22. Decimal Transformation

Pandas can also round different columns to different decimal places.

For example:

df["Score"] = df["Score"].round(2)

Or:

df = df.round({
    "Score": 2,
    "Revenue": 0,
    "Profit_Margin": 3
})

This is useful when preparing an analytical output or report.

23. Scaling and Missing Values

Suppose:

df = pd.DataFrame({
    "Score": [50, 60, None, 80, 100]
})

Before scaling, inspect missing values:

df["Score"].isna().sum()

Then decide how to handle them.

Possible strategies include:

Drop
Fill with a meaningful value
Use an appropriate imputation strategy
Leave missing and use a preprocessing method that handles it

Do not automatically assume that missing values should become zero.

The correct strategy depends on what the missing value means.

24. Scaling and Outliers

Min-Max scaling is sensitive to extreme values.

Suppose:

The value 10000 becomes the maximum.

As a result, the smaller values may be compressed into a very narrow part of the 0 → 1 range.

For example:

10 → near 0
20 → near 0
30 → near 0
40 → near 0
10000 → 1

Therefore, before using Min-Max scaling, it is often useful to inspect the distribution and potential outliers.

25. Why Outliers Matter

Suppose we have:

df = pd.DataFrame({
    "Income": [
        30000,
        35000,
        40000,
        45000,
        1000000
    ]
})

Min-Max scaling uses:

min = 30000
max = 1000000

The very large value strongly influences the range.

This does not mean Min-Max scaling is wrong.

It means you should understand the data before choosing the transformation.

Depending on the problem, you might consider:

investigating the outlier
correcting data-quality issues
transforming the distribution
using a different scaling strategy

26. Creating a Transformed Dataset

Instead of modifying the original DataFrame, you can create a separate transformed version.

For example:

scaled_df = df.copy()

Then:

scaled_df["Score"] = (
    (scaled_df["Score"] - scaled_df["Score"].min())
    /
    (
        scaled_df["Score"].max()
        - scaled_df["Score"].min()
    )
)

This preserves the original dataset.

This approach can be useful when you need both:

Original data
+
Transformed data

for comparison.

27. Naming Transformed Columns Clearly

Good column names make transformations easier to understand.

For example:

df["Salary_K"]
df["Score_Scaled"]
df["Revenue_Log"]
df["Profit_Margin"]
df["Score_Rounded"]

These names communicate what happened to the original variable.

Avoid vague names such as:

df["New_Score"]

when the actual transformation is important.

A useful feature name should make the transformation obvious.

28. Min-Max Scaling Function for Multiple Columns

We can create a reusable function:

def min_max_scale(series):
    min_value = series.min()
    max_value = series.max()

    if min_value == max_value:
        return pd.Series(
            0.0,
            index=series.index
        )

    return (
        (series - min_value)
        /
        (max_value - min_value)
    )

Then:

columns = [
    "Age",
    "Salary",
    "Score"
]

for column in columns:
    df[f"{column}_Scaled"] = (
        min_max_scale(df[column])
    )

This makes the transformation reusable.

The handling of a constant column is an explicit design choice; in a real preprocessing pipeline, you may instead choose to remove constant features or handle them according to the downstream tool’s behavior.

29. ML Preprocessing Connection

Now we reach an important connection.

Data scaling is frequently used during machine-learning preprocessing.

Suppose a dataset contains:

Age
Salary
Experience
Score

Before training certain machine-learning models, you may transform these numerical features so that their scales are more comparable.

For example:

Raw Features
     ↓
Data Cleaning
     ↓
Feature Engineering
     ↓
Scaling / Transformation
     ↓
Train ML Model

This is part of the broader preprocessing workflow.

30. Pandas vs Scikit-Learn for Scaling

Pandas can absolutely perform a Min-Max calculation:

df["Score_Scaled"] = (
    (df["Score"] - df["Score"].min())
    /
    (df["Score"].max() - df["Score"].min())
)

But when preparing data for an actual machine-learning model, scikit-learn provides dedicated preprocessing tools.

For example:

from sklearn.preprocessing import MinMaxScaler

Then:

scaler = MinMaxScaler()

X_scaled = scaler.fit_transform(X)

The important distinction is:

Pandas
→ Data manipulation and transformation

Scikit-learn
→ ML-oriented preprocessing and modeling workflow

They often work together.

31. Why fit() and transform() Matter in ML

Machine-learning preprocessing introduces an important concept:

fit
transform

Suppose we have:

Training Data
Testing Data

A scaler should generally be fitted on the training data:

scaler.fit(X_train)

Then applied to both training and test data:

X_train_scaled = scaler.transform(X_train)
X_test_scaled = scaler.transform(X_test)

This helps prevent information from the test set from influencing the scaling parameters.

This is called avoiding data leakage.

32. Why Not Calculate Min and Max on the Entire Dataset?

Suppose you calculate:

df["Score"].min()
df["Score"].max()

using both training and test data before splitting.

Then the test data has influenced the scaling parameters.

In a machine-learning workflow, this can introduce information leakage.

A safer conceptual workflow is:

Raw Dataset
     ↓
Train / Test Split
     ↓
Fit scaler on Training Data
     ↓
Transform Training Data
     ↓
Transform Test Data using same scaler

This distinction is extremely important when scaling is used for predictive modeling.

33. Scaling Is Not Always Required

One common misconception is:

Every machine-learning dataset must be scaled.

That is not true.

Scaling is especially important for algorithms that depend on:

distances
feature magnitudes
gradient-based optimization
regularization

Examples can include:

KNN
K-Means
SVM
Logistic Regression
Linear Regression
Neural Networks

depending on the specific implementation and preprocessing setup.

Tree-based models are generally less sensitive to feature scaling:

Decision Tree
Random Forest
Gradient-Boosted Trees

The exact preprocessing pipeline should depend on the algorithm and dataset.

34. Practical Example: Student Dataset

Let’s create a realistic dataset:

students = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David",
        "Emma"
    ],
    "Age": [
        18,
        19,
        20,
        18,
        21
    ],
    "Study_Hours": [
        3,
        5,
        8,
        2,
        6
    ],
    "Score": [
        65,
        78,
        92,
        55,
        88
    ]
})

Suppose we want to scale:

Age
Study_Hours
Score

We can use:

numeric_columns = [
    "Age",
    "Study_Hours",
    "Score"
]

for column in numeric_columns:
    students[f"{column}_Scaled"] = (
        (
            students[column]
            - students[column].min()
        )
        /
        (
            students[column].max()
            - students[column].min()
        )
    )

Now we have both original and scaled versions.

35. Practical Example: Salary Transformation

Consider:

employees = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David"
    ],
    "Salary": [
        45000,
        60000,
        85000,
        120000
    ]
})

Create salary in thousands:

employees["Salary_K"] = (
    employees["Salary"] / 1000
)

Create Min-Max scaled salary:

employees["Salary_Scaled"] = (
    (
        employees["Salary"]
        - employees["Salary"].min()
    )
    /
    (
        employees["Salary"].max()
        - employees["Salary"].min()
    )
)

Round the scaled value:

employees["Salary_Scaled"] = (
    employees["Salary_Scaled"].round(3)
)

Now we have:

Salary
Salary_K
Salary_Scaled

Each representation serves a different purpose.

36. Transformation Should Match the Goal

Different transformations answer different questions.

Want comparable range?

Use:

Min-Max scaling

Want a centered standardized feature?

Consider:

Standardization

Want to reduce the influence of large positive values?

Consider:

Log transformation

when appropriate.

Want an easier-to-read representation?

Use:

Rounding

Want a ratio or percentage?

Use:

Arithmetic transformation

The transformation should always follow the analytical objective.

37. Common Mistakes

Mistake 1: Confusing Scaling with Normal Distribution

Min-Max scaling does not make data normally distributed.

It changes the range.

Mistake 2: Scaling Without Checking Outliers

Extreme values can strongly influence Min-Max scaling.

Inspect the data first.

Mistake 3: Scaling Before Handling Data Problems

If a column contains:

missing values
invalid values
incorrect data types
extreme data-quality errors

fix or understand those issues before applying transformations.

Mistake 4: Rounding Too Early

If you round raw numerical data before calculations, you may lose useful precision.

Prefer to keep the original precision and round the final presentation when appropriate.

Mistake 5: Fitting an ML Scaler on Test Data

Do not calculate preprocessing parameters from the test set.

Fit on training data and transform the test data using the same fitted transformation.

Mistake 6: Scaling Every Feature Automatically

Not every feature needs scaling.

First understand the algorithm and analytical objective.

38. A Practical Scaling Workflow

A good workflow is:

Load Data
    ↓
Inspect Data Types
    ↓
Handle Missing Values
    ↓
Check Outliers
    ↓
Select Numerical Features
    ↓
Choose Transformation
    ↓
Create / Fit Transformation
    ↓
Transform Data
    ↓
Validate Result
    ↓
Use for Analysis or ML

For machine learning:

Split Data
    ↓
Fit Preprocessor on Training Data
    ↓
Transform Training Data
    ↓
Transform Test Data
    ↓
Train Model

This separation is critical for avoiding leakage.

39. Quick Reference

Min-Max scaling

df["Score_Scaled"] = (
    (df["Score"] - df["Score"].min())
    /
    (df["Score"].max() - df["Score"].min())
)

Convert to percentage

df["Percentage"] = (
    df["Ratio"] * 100
)

Scale to 0–100

df["Score_100"] = (
    (
        (df["Score"] - df["Score"].min())
        /
        (df["Score"].max() - df["Score"].min())
    ) * 100
)

Round values

df["Score_Rounded"] = (
    df["Score"].round(2)
)

Log transformation

df["Log_Value"] = np.log1p(
    df["Value"]
)

Square-root transformation

df["Sqrt_Value"] = np.sqrt(
    df["Value"]
)

Absolute transformation

df["Absolute_Value"] = (
    df["Value"].abs()
)

Scikit-learn Min-Max scaling

from sklearn.preprocessing import MinMaxScaler

scaler = MinMaxScaler()

X_train_scaled = scaler.fit_transform(
    X_train
)

X_test_scaled = scaler.transform(
    X_test
)

40. Final Takeaways

Scaling and transformation are important parts of practical data preparation.

With Pandas, we can easily create transformed numerical columns using:

+
-
*
/

as well as functions such as:

round()
abs()
np.log1p()
np.sqrt()

Min-Max scaling can transform numerical values into a common range, commonly:

0 → 1

using:

(x - min) / (max - min)

But scaling is only one form of transformation.

The key ideas to remember are:

Scaling changes the numerical range or magnitude.
Min-Max scaling commonly maps values to 0–1.
Normalization in this context does not mean making data normally distributed.
Standardization is different from Min-Max scaling.
Numerical transformations can create more useful representations.
Rounding is usually better treated as a presentation step unless the analytical requirement says otherwise.
Outliers can strongly affect Min-Max scaling.
Missing values and invalid data should be considered before transformation.
Pandas is excellent for data manipulation and exploratory transformations.
Scikit-learn provides dedicated preprocessing tools for ML workflows.
In ML, preprocessing parameters should generally be learned from training data only.
Not every machine-learning algorithm requires feature scaling.

The most important principle is:

Choose the transformation based on the data, the analytical objective, and the downstream model — not simply because scaling is available.

41. Where We Go Next

Our Pandas workflow is becoming more powerful:

Load
  ↓
Inspect
  ↓
Select
  ↓
Filter
  ↓
Clean
  ↓
Transform
  ↓
Engineer Features
  ↓
Scale
  ↓
Analyze

In Post #19, we will explore another important technique for numerical data:

Pandas Binning: cut() and qcut() for Categorical Data Analysis

We will learn how to convert continuous numerical values into meaningful groups and understand the difference between fixed-range bins and quantile-based bins.

About This Series

This article is part of Data Analysis with Pandas, a practical series designed to help developers and beginners understand Pandas through real-world data-analysis workflows.

The goal is not simply to memorize syntax.

It is to understand:

What transformation should I use, why should I use it, and what effect will it have on my data?

That mindset is essential when moving from basic Pandas operations toward exploratory data analysis, visualization, and machine-learning preparation.

Author

Engr. Md. Monjur Bakth Mazumder

GitHub:
https://github.com/monjurmbm404/Python-Data-Science-Tools

Final Thought

Data transformation is not about changing numbers just for the sake of changing them.

Every transformation should have a reason.

Sometimes we need:

0 → 1

for comparable feature scales.

Sometimes we need:

Raw value → Percentage

for easier interpretation.

Sometimes we need:

Large skewed value → Log-transformed value

to make a distribution easier to analyze.

And sometimes we simply need:

87.45678 → 87.46

for clean presentation.

The important skill is knowing which transformation fits the problem.

That is what turns numerical preprocessing from a collection of formulas into a meaningful data-analysis practice.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Feature Engineering with Pandas: Creating and Transforming DataFrame Columns

Engr. Md Monjur Bakth Mazumder — Tue, 10 Nov 2026 14:01:02 GMT

Series: Data Analysis with Pandas
Part: 17

A Practical Guide to Creating New Columns, Conditional Features and Derived Variables with Python Pandas

Real-world datasets are rarely ready for analysis exactly as they arrive.

A dataset may contain:

Age
Salary
Quantity
Price
Score
Experience

But the questions we want to answer are often more meaningful:

What is the total sales amount?
Which customers are high-value?
Which students are performing well?
How should employees be categorized?
What is the profit margin?
How many years of experience does someone have?
Which customers should receive special attention?

To answer these questions, we often need to create new variables from existing data.

This process is commonly called:

Feature Engineering

In Pandas, feature engineering often means creating new DataFrame columns by transforming, combining, or categorizing existing columns.

In this article, we will learn how to create:

New columns
Arithmetic transformations
Conditional features
Combined features
Derived variables
Practical real-world features

We will also discuss an important principle:

A good feature should add useful information — not simply add more columns.

1. What Is Feature Engineering?

Feature engineering is the process of creating or transforming variables so that the data becomes more useful for analysis, reporting, or downstream modeling.

Suppose we have:

import pandas as pd

df = pd.DataFrame({
    "Product": ["Laptop", "Phone", "Tablet"],
    "Price": [1000, 600, 400],
    "Quantity": [2, 5, 3]
})

The dataset contains:

Price
Quantity

But we may want:

Total_Sales

We can create it:

df["Total_Sales"] = (
    df["Price"] * df["Quantity"]
)

Now we have a new variable derived from existing information.

This is a simple example of feature engineering.

2. Why Feature Engineering Matters

Raw columns often do not directly answer the business or analytical question.

For example:

Price = 1000
Quantity = 5

Individually, these values are useful.

But:

Price × Quantity = Total Sales

may be much more useful for analysis.

Similarly:

Date of Birth

can be transformed into:

Age

And:

Math Score
Science Score
English Score

can be transformed into:

Average Score

Feature engineering helps transform raw variables into information that better represents the problem we are trying to understand.

3. Creating a New Column

The simplest form of feature engineering is assigning a new column.

For example:

df["Discount"] = 0.10

This creates a new column named:

Discount

with the same scalar value assigned to every row.

You can also create a column from an existing column:

df["Price_With_Tax"] = (
    df["Price"] * 1.15
)

Now every product has a calculated price including 15% tax.

4. Arithmetic Transformation

One of the most common feature-engineering techniques is arithmetic transformation.

Suppose:

df = pd.DataFrame({
    "Product": ["Laptop", "Phone", "Tablet"],
    "Price": [1000, 600, 400],
    "Quantity": [2, 5, 3]
})

We can calculate total sales:

df["Total_Sales"] = (
    df["Price"] * df["Quantity"]
)

Conceptually:

Total Sales = Price × Quantity

This is a vectorized Pandas operation.

No loop is required.

No apply() is required.

5. Addition Between Columns

Suppose we have:

df = pd.DataFrame({
    "Product": ["A", "B", "C"],
    "Online_Sales": [1000, 1500, 1200],
    "Store_Sales": [800, 900, 1100]
})

We can calculate total sales:

df["Total_Sales"] = (
    df["Online_Sales"] +
    df["Store_Sales"]
)

This creates a new derived variable:

Total Sales

from two existing variables.

6. Subtracting Columns

Suppose we have:

df = pd.DataFrame({
    "Revenue": [10000, 15000, 12000],
    "Cost": [6000, 9000, 7000]
})

We can calculate profit:

df["Profit"] = (
    df["Revenue"] -
    df["Cost"]
)

The formula is:

Profit = Revenue - Cost

This is one of the most common derived variables in business analysis.

7. Multiplication Between Columns

Suppose:

df = pd.DataFrame({
    "Units": [10, 20, 15],
    "Unit_Price": [100, 80, 120]
})

Create revenue:

df["Revenue"] = (
    df["Units"] *
    df["Unit_Price"]
)

This is a good example of why understanding vectorized operations is important.

Pandas automatically performs the calculation element by element.

8. Division and Ratios

Feature engineering is also useful for creating ratios.

Suppose:

df = pd.DataFrame({
    "Profit": [2000, 3000, 2500],
    "Revenue": [10000, 15000, 12500]
})

We can calculate profit margin:

df["Profit_Margin"] = (
    df["Profit"] /
    df["Revenue"]
)

If we want percentage:

df["Profit_Margin_Percent"] = (
    df["Profit"] /
    df["Revenue"]
) * 100

Now we have a more interpretable business metric.

9. Conditional Features

Not every feature is created through arithmetic.

Sometimes we want to categorize records based on a condition.

For example:

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie", "David"],
    "Score": [92, 76, 58, 35]
})

We can create:

Pass / Fail

using:

df["Result"] = (
    df["Score"] >= 40
)

This produces Boolean values:

True
False

Boolean features can themselves be useful.

10. Conditional Categories with np.where()

For a text category, we can use NumPy’s where():

import numpy as np

df["Result"] = np.where(
    df["Score"] >= 40,
    "Pass",
    "Fail"
)

The logic is:

Score >= 40
    ↓
Pass

Otherwise
    ↓
Fail

This is a simple and efficient way to create a two-category feature.

11. Multiple Conditions with np.select()

Sometimes we need more than two categories.

For example:

90+  → Excellent
75+  → Good
50+  → Average
Below 50 → Needs Improvement

We can use:

conditions = [
    df["Score"] >= 90,
    df["Score"] >= 75,
    df["Score"] >= 50
]

choices = [
    "Excellent",
    "Good",
    "Average"
]

df["Performance_Level"] = np.select(
    conditions,
    choices,
    default="Needs Improvement"
)

This is often cleaner than writing a long nested conditional expression.

12. Conditional Features with pd.cut()

Pandas also provides:

pd.cut()

which is useful when we want to divide numerical values into ranges.

Suppose:

df = pd.DataFrame({
    "Age": [18, 25, 34, 42, 58, 67]
})

We can create age groups:

df["Age_Group"] = pd.cut(
    df["Age"],
    bins=[0, 18, 30, 45, 60, 100],
    labels=[
        "Teen",
        "Young Adult",
        "Adult",
        "Middle Age",
        "Senior"
    ]
)

Now a continuous numerical variable has been transformed into a categorical feature.

This is useful when ranges have analytical meaning.

13. Combining Multiple Columns

Feature engineering often involves combining several columns.

Suppose we have:

students = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Math": [90, 70, 85],
    "Science": [88, 75, 92],
    "English": [95, 68, 80]
})

We can create:

students["Total"] = (
    students["Math"] +
    students["Science"] +
    students["English"]
)

This creates a total score.

14. Creating an Average Feature

From the same dataset:

students["Average"] = (
    students[
        ["Math", "Science", "English"]
    ].mean(axis=1)
)

Here:

axis=1

means we calculate across the columns for each row.

This is generally preferable to using row-wise apply() for a simple mathematical operation.

15. Creating a Weighted Feature

Sometimes different variables have different importance.

For example:

Math      → 40%
Science   → 35%
English   → 25%

We can create a weighted score:

students["Weighted_Score"] = (
    students["Math"] * 0.40
    + students["Science"] * 0.35
    + students["English"] * 0.25
)

This creates a new feature representing a custom scoring system.

Weighted features are common in:

education
finance
business analytics
customer scoring
risk assessment

16. Combining Text Columns

Feature engineering is not limited to numerical data.

Suppose:

customers = pd.DataFrame({
    "First_Name": ["Alice", "Bob", "Charlie"],
    "Last_Name": ["Smith", "Jones", "Brown"]
})

We can combine them:

customers["Full_Name"] = (
    customers["First_Name"]
    + " "
    + customers["Last_Name"]
)

Now we have:

Full_Name

This can be useful for:

reporting
display
exporting
customer communication

17. Creating Features from Strings

Suppose we have email addresses:

customers = pd.DataFrame({
    "Email": [
        "alice@gmail.com",
        "bob@yahoo.com",
        "charlie@outlook.com"
    ]
})

We can extract the domain:

customers["Email_Domain"] = (
    customers["Email"]
    .str.split("@")
    .str[-1]
)

Now we have a new feature:

Email_Domain

This can help us analyze customer groups based on email providers.

This connects directly with the string operations covered earlier in the series.

18. Creating Features from Dates

Dates are another major source of derived variables.

Suppose:

df = pd.DataFrame({
    "Join_Date": [
        "2023-01-15",
        "2024-06-20",
        "2025-03-10"
    ]
})

First convert the column:

df["Join_Date"] = pd.to_datetime(
    df["Join_Date"]
)

Now we can extract:

df["Join_Year"] = (
    df["Join_Date"].dt.year
)

df["Join_Month"] = (
    df["Join_Date"].dt.month
)

df["Join_Day"] = (
    df["Join_Date"].dt.day
)

We can also extract:

df["Day_Name"] = (
    df["Join_Date"].dt.day_name()
)

Date-derived features are extremely useful in time-based analysis.

19. Creating an Experience Feature

Suppose we have:

df = pd.DataFrame({
    "Join_Year": [2018, 2021, 2023, 2015]
})

If the analysis year is 2026:

current_year = 2026

df["Experience"] = (
    current_year -
    df["Join_Year"]
)

Now we have:

Experience

as a derived variable.

In a real project, you would normally derive experience from an actual date and an explicitly defined reference date rather than hard-coding a year without context.

20. Feature Engineering from Business Rules

A feature becomes especially valuable when it represents a meaningful business concept.

Suppose a company has:

customers = pd.DataFrame({
    "Customer": [
        "A",
        "B",
        "C",
        "D"
    ],
    "Orders": [25, 8, 18, 3],
    "Revenue": [120000, 30000, 85000, 10000]
})

We may want a customer segment.

For example:

Revenue >= 100000 and Orders >= 20
→ Premium

Revenue >= 50000
→ Regular

Otherwise
→ Basic

We can create this feature using:

conditions = [
    (
        (customers["Revenue"] >= 100000)
        & (customers["Orders"] >= 20)
    ),
    customers["Revenue"] >= 50000
]

choices = [
    "Premium",
    "Regular"
]

customers["Segment"] = np.select(
    conditions,
    choices,
    default="Basic"
)

This is a meaningful derived variable because it represents a business concept.

21. Feature Engineering vs Simple Column Creation

Not every new column is necessarily useful feature engineering.

For example:

df["Copy"] = df["Score"]

creates a column, but it adds no new information.

Compare that with:

df["Pass_Rate"] = (
    df["Passed"] /
    df["Total_Exams"]
)

This creates a new metric with analytical meaning.

A useful feature should ideally:

represent useful information
simplify analysis
capture a meaningful relationship
support a specific business or analytical question

22. Avoiding Redundant Features

More columns do not automatically mean better analysis.

Suppose we already have:

Revenue
Cost
Profit

and:

Profit = Revenue - Cost

The Profit column is useful because it directly represents a business metric.

But creating many duplicate versions of the same information can make the dataset harder to understand.

Feature engineering should therefore be intentional.

Ask:

What question will this feature help answer?

23. Feature Engineering and Missing Values

Derived features can also produce missing values.

For example:

df["Profit_Margin"] = (
    df["Profit"] /
    df["Revenue"]
)

If:

Revenue = 0

the calculation can become problematic.

Therefore, before creating ratio-based features, check the underlying data.

For example:

df["Revenue"].isnull().sum()

and:

(df["Revenue"] == 0).sum()

Data quality should always be considered before feature creation.

24. Handling Division by Zero

Suppose:

df = pd.DataFrame({
    "Profit": [100, 200, 50],
    "Revenue": [1000, 0, 500]
})

Instead of blindly calculating:

df["Margin"] = (
    df["Profit"] /
    df["Revenue"]
)

we can explicitly handle zero revenue.

For example:

df["Margin"] = np.where(
    df["Revenue"] != 0,
    df["Profit"] / df["Revenue"],
    np.nan
)

Now rows with zero revenue receive a missing value rather than an invalid business metric.

This is an important feature-engineering habit:

Think about edge cases before creating derived variables.

25. Practical Example: Employee Dataset

Let’s combine several feature-engineering techniques.

employees = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David"
    ],
    "Salary": [
        85000,
        55000,
        95000,
        62000
    ],
    "Performance": [
        92,
        76,
        96,
        84
    ],
    "Years_Experience": [
        7,
        3,
        10,
        5
    ]
})

Step 1 — Salary in Thousands

employees["Salary_K"] = (
    employees["Salary"] / 1000
)

This creates a more compact representation for some analyses.

Step 2 — Performance Level

conditions = [
    employees["Performance"] >= 90,
    employees["Performance"] >= 80,
    employees["Performance"] >= 70
]

choices = [
    "Excellent",
    "Good",
    "Average"
]

employees["Performance_Level"] = np.select(
    conditions,
    choices,
    default="Needs Improvement"
)

Step 3 — Experience Level

employees["Experience_Level"] = pd.cut(
    employees["Years_Experience"],
    bins=[0, 2, 5, 10, float("inf")],
    labels=[
        "Junior",
        "Mid-Level",
        "Senior",
        "Expert"
    ]
)

Step 4 — Performance per Experience

We can create a ratio:

employees["Performance_per_Year"] = (
    employees["Performance"] /
    employees["Years_Experience"]
)

This is a derived metric.

However, it must be interpreted carefully because dividing performance by years of experience does not automatically produce a universally meaningful measure. Feature engineering should always be guided by domain context.

26. A More Realistic Business Example

Consider a sales dataset:

sales = pd.DataFrame({
    "Product": [
        "Laptop",
        "Phone",
        "Tablet",
        "Monitor"
    ],
    "Units": [
        10,
        25,
        15,
        8
    ],
    "Unit_Price": [
        1000,
        600,
        400,
        300
    ],
    "Unit_Cost": [
        750,
        400,
        250,
        180
    ]
})

We can build several meaningful features.

Revenue

sales["Revenue"] = (
    sales["Units"] *
    sales["Unit_Price"]
)

Total Cost

sales["Total_Cost"] = (
    sales["Units"] *
    sales["Unit_Cost"]
)

Profit

sales["Profit"] = (
    sales["Revenue"] -
    sales["Total_Cost"]
)

Profit Margin

sales["Profit_Margin"] = np.where(
    sales["Revenue"] != 0,
    sales["Profit"] /
    sales["Revenue"],
    np.nan
)

Now the original raw sales information has been transformed into a richer analytical dataset.

27. Creating a High-Value Product Feature

Suppose we want to identify products generating significant revenue.

We can create:

sales["High_Revenue"] = (
    sales["Revenue"] >= 10000
)

This produces:

True
False

Boolean features are often useful for filtering:

high_revenue_products = sales[
    sales["High_Revenue"]
]

This creates a simple analytical workflow:

Create Feature
    ↓
Filter Dataset
    ↓
Analyze Selected Records

28. Feature Engineering with assign()

Pandas also provides:

assign()

for creating new columns.

For example:

df = df.assign(
    Revenue=df["Units"] * df["Unit_Price"]
)

You can create multiple features:

df = df.assign(
    Revenue=df["Units"] * df["Unit_Price"],
    Total_Cost=df["Units"] * df["Unit_Cost"]
)

This can be useful when building transformation pipelines.

However, direct assignment remains perfectly valid:

df["Revenue"] = (
    df["Units"] *
    df["Unit_Price"]
)

Choose the style that keeps your workflow readable.

29. Feature Engineering with apply()

Some features require custom logic.

For example:

def customer_type(row):
    if row["Revenue"] >= 100000 and row["Orders"] >= 20:
        return "Premium"
    elif row["Revenue"] >= 50000:
        return "Regular"
    return "Basic"

Then:

df["Customer_Type"] = df.apply(
    customer_type,
    axis=1
)

This is a legitimate use of apply() because the feature depends on multiple columns and custom business rules.

But if a vectorized approach can express the same logic clearly, it is often preferable.

30. Feature Engineering and Data Science

Feature engineering is an important concept in data science and machine learning.

For example, a raw dataset might contain:

Date
Price
Quantity
Customer_ID

After feature engineering, we may create:

Year
Month
Revenue
Customer_Order_Count
Average_Order_Value
Customer_Segment

These derived variables can make patterns easier to analyze.

However:

Feature engineering is not limited to machine learning.

It is equally useful for:

exploratory data analysis
business reporting
dashboards
descriptive analytics
data visualization
decision support

31. Good Feature Engineering Principles

When creating a new feature, ask:

1. What does this feature represent?

It should have a clear meaning.

2. Why do I need it?

It should answer a real analytical question.

3. Is the formula correct?

Validate the transformation.

4. What happens with missing values?

Consider NaN and incomplete records.

5. What happens with zero or negative values?

Check edge cases.

6. Is the feature redundant?

Avoid unnecessary duplication.

7. Is the feature interpretable?

A useful feature should be understandable to the people using the analysis.

32. Common Mistakes

Mistake 1: Creating Columns Without a Purpose

Adding many columns does not automatically improve a dataset.

Always ask:

What analytical question does this feature answer?

Mistake 2: Ignoring Data Types

Before performing arithmetic, make sure numerical columns are actually numeric.

For example:

df["Price"].dtype

If a numeric-looking column is stored as strings, arithmetic may fail or behave unexpectedly.

Mistake 3: Ignoring Missing Values

For example:

df["Profit_Margin"] = (
    df["Profit"] /
    df["Revenue"]
)

may produce missing values or problematic results if revenue is missing or zero.

Mistake 4: Using apply() When Vectorization Is Enough

Avoid unnecessarily writing:

df["Revenue"].apply(
    lambda x: x * 1.15
)

when this is enough:

df["Revenue"] * 1.15

Mistake 5: Creating Features That Leak Future Information

This becomes especially important in predictive modeling.

A feature should only use information that would legitimately be available at the time of prediction.

For example, using a customer’s future purchase amount to predict whether they will make that purchase would introduce data leakage.

Feature engineering should respect the timing and context of the problem.

33. A Practical Feature Engineering Workflow

A strong workflow looks like:

Understand the Dataset
        ↓
Identify the Analytical Question
        ↓
Select Relevant Raw Columns
        ↓
Define the Feature Formula / Rule
        ↓
Create the Feature
        ↓
Handle Edge Cases
        ↓
Validate the Result
        ↓
Analyze the New Feature

For example:

df["Revenue"] = (
    df["Price"] *
    df["Quantity"]
)

df["Profit"] = (
    df["Revenue"] -
    df["Cost"]
)

df["Profit_Margin"] = np.where(
    df["Revenue"] != 0,
    df["Profit"] / df["Revenue"],
    np.nan
)

Then validate:

df[[
    "Revenue",
    "Profit",
    "Profit_Margin"
]].head()

Feature engineering should always be followed by validation.

34. Quick Reference

Create a new column

df["New_Column"] = value

Arithmetic transformation

df["Total"] = (
    df["Price"] *
    df["Quantity"]
)

Add columns

df["Total"] = (
    df["Online"] +
    df["Store"]
)

Subtract columns

df["Profit"] = (
    df["Revenue"] -
    df["Cost"]
)

Ratio

df["Margin"] = (
    df["Profit"] /
    df["Revenue"]
)

Boolean feature

df["High_Value"] = (
    df["Revenue"] >= 100000
)

Two-category feature

df["Result"] = np.where(
    df["Score"] >= 40,
    "Pass",
    "Fail"
)

Multiple categories

df["Category"] = np.select(
    conditions,
    choices,
    default="Other"
)

Numeric binning

df["Age_Group"] = pd.cut(
    df["Age"],
    bins=[0, 18, 30, 45, 60, 100]
)

Row-wise average

df["Average"] = df[
    ["Math", "Science", "English"]
].mean(axis=1)

Date feature

df["Year"] = (
    df["Date"].dt.year
)

Multiple derived columns with assign()

df = df.assign(
    Revenue=df["Units"] * df["Price"],
    Profit=df["Revenue"] - df["Cost"]
)

35. Final Takeaways

Feature engineering is the process of transforming raw variables into more meaningful and useful features.

With Pandas, we can create features using:

+
-
*
/

and more advanced tools such as:

np.where()
np.select()
pd.cut()
assign()

We can also combine columns, transform text, extract date information, create ratios, build categories, and implement custom business rules.

The most important concepts are:

New columns can be created directly with df["New"] = ....
Arithmetic operations are usually handled efficiently through vectorized Pandas operations.
Conditional features can be created with Boolean logic, np.where(), or np.select().
Multiple columns can be combined to create meaningful derived variables.
Date and string columns are valuable sources of new features.
apply() can be useful for genuinely custom row-level logic.
Feature engineering should be driven by analytical questions, not by the desire to create more columns.
Missing values, zero values, data types, edge cases, and data leakage should be considered.
Every newly created feature should be validated.

The central idea is simple:

Raw data tells you what happened. Well-designed features can help you understand what it means.

36. Where We Go Next

We have now covered:

Pandas Basics
    ↓
Data Selection
    ↓
Filtering
    ↓
Missing Data
    ↓
Data Cleaning
    ↓
Data Types
    ↓
String Operations
    ↓
Statistics
    ↓
Sorting & Ranking
    ↓
apply() & Lambda
    ↓
Feature Engineering

The next step is to explore another important transformation technique:

Data Scaling with Pandas: Normalization, Min-Max Scaling and Transformation

We will look at how numerical values can be transformed to comparable scales and when scaling is useful for data analysis and machine-learning preparation.

About This Series

This article is part of Data Analysis with Pandas, a practical series designed to help beginners and developers move from Pandas fundamentals to real-world data analysis.

The goal is not simply to learn individual functions.

It is to understand how different Pandas operations work together:

Load
  ↓
Inspect
  ↓
Clean
  ↓
Transform
  ↓
Engineer Features
  ↓
Analyze
  ↓
Visualize
  ↓
Extract Insights

That workflow is at the heart of practical data analysis with Python.

Author

Engr. Md. Monjur Bakth Mazumder

GitHub:
https://github.com/monjurmbm404/Python-Data-Science-Tools

Final Thought

Feature engineering is not about creating as many variables as possible.

It is about creating the right variables for the right question.

A simple transformation such as:

Revenue = Price × Quantity

can turn two raw measurements into a meaningful business metric.

A date can become a month.

A score can become a performance category.

Revenue and orders can become a customer segment.

That is the real value of feature engineering:

Turning raw columns into meaningful information.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas apply(): Lambda Functions and Row-wise vs Column-wise Operations

Engr. Md Monjur Bakth Mazumder — Mon, 09 Nov 2026 14:01:03 GMT

Series: Data Analysis with Pandas
Part: 16

A Practical Guide to apply(), Lambda Functions, Custom Functions and Efficient Data Transformation in Pandas

When working with real-world data, built-in Pandas operations can solve many problems.

For example:

df["Salary"] * 1.10

can increase a salary column by 10%.

But what happens when your transformation requires custom logic?

For example:

Assign a category based on a score
Calculate a custom value from multiple columns
Convert a value using a business rule
Apply the same custom function to every value
Create a new column based on several conditions

This is where Pandas provides:

apply()

and Python’s:

lambda

functions.

In this article, we will learn how apply() works, how lambda functions fit into the workflow, how to perform column-wise and row-wise operations, and—most importantly—when apply() should and should not be used.

1. What Is apply() in Pandas?

The apply() method allows you to apply a function to values in a Series or along an axis of a DataFrame.

At a high level:

Data
 ↓
Function
 ↓
Transformed Data

For example:

import pandas as pd

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Score": [85, 72, 95]
})

Suppose we want to increase every score by 5.

We can write:

df["Score"] = df["Score"].apply(
    lambda x: x + 5
)

Now every value in the Score column has been transformed.

2. What Is a Lambda Function?

A lambda is a small anonymous function in Python.

Instead of writing:

def add_five(x):
    return x + 5

we can write:

lambda x: x + 5

The structure is:

lambda arguments: expression

For example:

lambda x: x * 2

means:

Take x and return x * 2.

Another example:

lambda x: x.upper()

means:

Take x and convert it to uppercase.

Lambda functions are often useful with methods such as:

apply()

because they allow us to define small transformations directly where they are needed.

3. Basic apply() with a Lambda

Let’s start with a simple example.

df = pd.DataFrame({
    "Score": [70, 80, 90, 60]
})

Apply a 10-point increase:

df["Score"] = df["Score"].apply(
    lambda x: x + 10
)

The transformation happens for each value in the Series.

Conceptually:

This is a simple example of element-wise application on a Series.

4. apply() on a Series

When you use:

df["Score"].apply(...)

the function is applied to each value in the Series.

For example:

df["Score"].apply(
    lambda x: x * 2
)

Each value is processed independently.

This is useful for custom transformations.

For example:

df["Score_Category"] = df["Score"].apply(
    lambda x: "Pass" if x >= 50 else "Fail"
)

Now each score is converted into a category.

5. Using a Custom Function with apply()

Lambda functions are useful for short logic.

But sometimes your logic becomes more complex.

Instead of:

lambda x: ...

you can define a normal Python function.

Example:

def classify_score(score):
    if score >= 80:
        return "Excellent"
    elif score >= 60:
        return "Good"
    elif score >= 40:
        return "Average"
    else:
        return "Poor"

Then:

df["Category"] = df["Score"].apply(
    classify_score
)

This is often easier to read when the business logic contains multiple conditions.

6. Lambda vs Custom Function

For very small transformations:

df["Score"].apply(
    lambda x: x + 5
)

can be concise.

For more complex logic:

def classify_score(score):
    if score >= 80:
        return "Excellent"
    elif score >= 60:
        return "Good"
    elif score >= 40:
        return "Average"
    else:
        return "Poor"

is usually easier to understand.

A useful rule is:

Short logic
    ↓
lambda

Complex or reusable logic
    ↓
named function

The goal is not to use lambda everywhere.

The goal is to make your transformation clear.

7. Column-wise Operation

There are two different ideas that beginners often confuse:

Applying a function to values in a column

and:

Applying a function to columns of a DataFrame

Let’s start with the first.

Suppose:

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Salary": [70000, 55000, 85000]
})

We can transform the Salary column:

df["Salary"] = df["Salary"].apply(
    lambda x: x * 1.10
)

Here the function receives each salary value.

8. Applying a Function to Each DataFrame Column

DataFrame apply() behaves differently.

Consider:

df = pd.DataFrame({
    "Math": [80, 90, 70],
    "Science": [85, 95, 75]
})

If we write:

df.apply(lambda column: column.max())

Pandas applies the function to each column.

The result gives the maximum value from each column.

This is a column-wise DataFrame operation.

The default axis is:

axis=0

So:

df.apply(function)

is conceptually:

Column 1 → function
Column 2 → function
Column 3 → function

9. Understanding axis=0

In DataFrame apply():

axis=0

means the function is applied along the columns.

For example:

df.apply(
    lambda column: column.mean(),
    axis=0
)

calculates the mean of each column.

You can think of it as:

Column → Function → Result

For many standard operations, however, you should prefer the direct Pandas method:

df.mean()

rather than:

df.apply(lambda column: column.mean())

The direct method is simpler and generally more idiomatic.

This distinction becomes important when deciding whether apply() is actually necessary.

10. Row-wise Operation with axis=1

Now suppose we want to calculate something using multiple columns from the same row.

Example:

df = pd.DataFrame({
    "Math": [80, 90, 70],
    "Science": [85, 95, 75]
})

We want the total score for each student.

We can write:

df["Total"] = df.apply(
    lambda row: row["Math"] + row["Science"],
    axis=1
)

Here:

axis=1

means the function receives one row at a time.

Conceptually:

Row 1 → Function
Row 2 → Function
Row 3 → Function

This is called a row-wise operation.

11. Why axis=1 Is Important

Suppose we have:

Name     Math    Science
Alice     80       85
Bob       90       95
Charlie   70       75

If we want:

Math + Science

for each student, the calculation needs values from multiple columns in the same row.

Therefore:

axis=1

is appropriate.

Example:

df["Total"] = df.apply(
    lambda row: row["Math"] + row["Science"],
    axis=1
)

This is one of the most common practical uses of DataFrame apply().

12. Row-wise Average

We can also calculate an average:

df["Average"] = df.apply(
    lambda row: (row["Math"] + row["Science"]) / 2,
    axis=1
)

Now each row gets its own average.

However, notice something important.

For simple arithmetic across numeric columns, there is usually a better direct Pandas approach.

For example:

df["Average"] = df[["Math", "Science"]].mean(axis=1)

This is generally preferable.

Why?

Because the operation is already supported directly by Pandas.

13. apply() vs Direct Vectorized Operation

This is one of the most important lessons in this article.

Suppose we want to add 10 to every value:

df["Score"].apply(
    lambda x: x + 10
)

This works.

But the direct Pandas operation is:

df["Score"] + 10

The second version is usually preferable.

Why?

Because Pandas and NumPy are designed to perform many operations in a vectorized way.

Vectorized operations are generally:

simpler
more readable
more idiomatic
often faster
easier to optimize

So:

apply(lambda x: x + 10)

is not automatically better just because it looks flexible.

14. Another Example: Multiplication

Instead of:

df["Salary"].apply(
    lambda x: x * 1.10
)

prefer:

df["Salary"] * 1.10

The direct expression clearly communicates the operation.

Similarly:

df["Score"].apply(
    lambda x: x * 2
)

can usually become:

df["Score"] * 2

This is an important habit to develop.

15. Direct Methods vs apply()

Suppose you want the absolute value.

You might write:

df["Value"].apply(
    lambda x: abs(x)
)

But Pandas provides:

df["Value"].abs()

If you want the lowercase version of text:

df["Name"].apply(
    lambda x: x.lower()
)

Pandas provides:

df["Name"].str.lower()

If you want to calculate a mean:

df["Score"].apply(
    lambda x: ...
)

is unnecessary when you can use:

df["Score"].mean()

The general principle is:

Use the most direct Pandas operation available before reaching for apply().

16. When apply() Is Useful

So when should we actually use apply()?

It becomes useful when the transformation is:

custom
conditional
difficult to express with existing vectorized methods
dependent on multiple values
based on a reusable Python function

For example:

def classify_salary(salary):
    if salary >= 80000:
        return "High"
    elif salary >= 60000:
        return "Medium"
    else:
        return "Low"

Then:

df["Salary_Level"] = df["Salary"].apply(
    classify_salary
)

This is a reasonable use of apply().

17. Practical Example: Salary Classification

Let’s create an employee dataset:

employees = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie", "David"],
    "Salary": [85000, 55000, 95000, 62000]
})

Define the business rule:

def salary_level(salary):
    if salary >= 90000:
        return "High"
    elif salary >= 60000:
        return "Medium"
    else:
        return "Low"

Apply it:

employees["Salary_Level"] = employees["Salary"].apply(
    salary_level
)

This creates a new categorical feature based on business logic.

Here apply() is useful because the transformation is custom and conditional.

18. Practical Example: Student Classification

Consider:

students = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie", "David"],
    "Score": [92, 74, 58, 35]
})

Define:

def classify(score):
    if score >= 80:
        return "Excellent"
    elif score >= 60:
        return "Pass"
    elif score >= 40:
        return "Needs Improvement"
    else:
        return "Fail"

Then:

students["Result"] = students["Score"].apply(
    classify
)

This is easy to understand because the business rule is separated from the DataFrame operation.

19. The Same Logic with Lambda

If the rule is short, we can use:

students["Result"] = students["Score"].apply(
    lambda x: "Pass" if x >= 40 else "Fail"
)

This is appropriate when the condition is simple.

For example:

score >= 40
    ↓
Pass

score < 40
    ↓
Fail

But if the classification contains many levels, a named function is usually clearer.

20. Multiple Conditions with Lambda

Lambda can also handle multiple conditions.

For example:

students["Category"] = students["Score"].apply(
    lambda x:
        "Excellent" if x >= 80
        else "Good" if x >= 60
        else "Average" if x >= 40
        else "Poor"
)

This works, but nested conditional expressions can become difficult to read.

If the business logic becomes complicated, use a normal function.

Readable code is more important than making everything one line.

21. Row-wise Custom Function

Now consider a dataset with multiple scores:

students = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Math": [90, 70, 85],
    "Science": [88, 75, 92],
    "English": [95, 68, 80]
})

Suppose we want to calculate a weighted score:

Math      → 40%
Science   → 35%
English   → 25%

A custom function can receive the complete row:

def weighted_score(row):
    return (
        row["Math"] * 0.40 +
        row["Science"] * 0.35 +
        row["English"] * 0.25
    )

Apply it row-wise:

students["Weighted_Score"] = students.apply(
    weighted_score,
    axis=1
)

This is a strong practical use case for row-wise apply().

The function needs multiple columns from the same row.

22. Why Not Always Use Row-wise apply()?

Although row-wise apply() is useful, it can be slower on large datasets.

For example:

df.apply(
    custom_function,
    axis=1
)

requires Pandas to repeatedly call the Python function for individual rows.

For small datasets, this may not matter.

For large datasets, it can become expensive.

Therefore:

Use row-wise apply() when the logic genuinely requires row-level custom processing—not simply because it is convenient.

23. Vectorized Alternative to Row-wise Calculation

Suppose we want:

Weighted_Score = (
    Math * 0.40
    + Science * 0.35
    + English * 0.25
)

Instead of:

students.apply(
    weighted_score,
    axis=1
)

we can directly write:

students["Weighted_Score"] = (
    students["Math"] * 0.40
    + students["Science"] * 0.35
    + students["English"] * 0.25
)

This is generally preferable because the calculation can be performed using vectorized column operations.

So the decision should be:

Can Pandas/NumPy express the calculation directly?
        ↓
Yes → Prefer vectorized operation
        ↓
No → Consider apply()

24. apply() with Built-in Functions

You are not limited to lambda or custom functions.

For example:

df["Name"].apply(len)

calculates the length of each name.

You could also use:

df["Name"].apply(str.upper)

However, when Pandas provides a dedicated vectorized accessor, it is often preferable.

For example:

df["Name"].str.upper()

is clearer for string operations.

25. apply() on Multiple Columns

Suppose:

df = pd.DataFrame({
    "Math": [80, 90, 70],
    "Science": [85, 95, 75],
    "English": [88, 92, 78]
})

We can calculate the maximum score for each column:

df.apply(
    lambda column: column.max()
)

But again, a direct method is simpler:

df.max()

This is a good example of why understanding apply() is not the same as using it everywhere.

26. axis=0 vs axis=1

This distinction is worth remembering.

axis=0

The function works column-wise.

df.apply(
    function,
    axis=0
)

Conceptually:

Column 1 → function
Column 2 → function
Column 3 → function

axis=1

The function works row-wise.

df.apply(
    function,
    axis=1
)

Conceptually:

Row 1 → function
Row 2 → function
Row 3 → function

A simple memory trick:

axis=0 → move down rows → process columns
axis=1 → move across columns → process rows

27. A Common axis Mistake

Beginners often write:

df.apply(
    lambda row: ...,
    axis=0
)

when they actually want information from multiple columns in each row.

If your function needs:

row["Math"]
row["Science"]
row["English"]

you usually need:

axis=1

because the function must receive a complete row.

28. apply() for Text Transformation

Suppose we have:

df = pd.DataFrame({
    "Name": ["alice", "BOB", "Charlie"]
})

We could write:

df["Name"].apply(
    lambda x: x.title()
)

This works.

But Pandas provides:

df["Name"].str.title()

which is the more direct vectorized string operation.

This connects to our previous article on Pandas string operations.

Whenever a dedicated .str method exists, it is usually the better first choice.

29. apply() for Custom Business Rules

One of the strongest use cases for apply() is business logic.

For example:

def customer_segment(row):
    if row["Revenue"] >= 100000 and row["Orders"] >= 20:
        return "Premium"
    elif row["Revenue"] >= 50000:
        return "Regular"
    else:
        return "Basic"

Then:

df["Segment"] = df.apply(
    customer_segment,
    axis=1
)

Here the classification depends on multiple columns.

This is exactly the kind of custom transformation where apply() can be useful.

30. A Practical Decision Framework

Before using apply(), ask:

Question 1

Can the operation be written directly?

For example:

df["Salary"] * 1.10

If yes, prefer the direct operation.

Question 2

Does Pandas already provide a method?

For example:

df["Score"].mean()

If yes, use the built-in method.

Question 3

Does the operation involve a custom business rule?

If yes, apply() may be appropriate.

Question 4

Does the logic require multiple columns from the same row?

If yes, consider:

axis=1

Question 5

Is the transformation simple enough for a lambda?

If yes:

lambda x: ...

can be convenient.

If the logic is complex or reusable, define a normal function.

31. A Better Mental Model

Think about Pandas transformations in this order:

1. Built-in Pandas method
        ↓
2. Vectorized operation
        ↓
3. Dedicated accessor
        ↓
4. apply()
        ↓
5. Custom Python function

This is not an absolute rule, but it is a useful practical guideline.

For example:

df["Salary"] * 1.10

is preferable to:

df["Salary"].apply(lambda x: x * 1.10)

And:

df["Name"].str.lower()

is preferable to:

df["Name"].apply(lambda x: x.lower())

But a complex business rule may genuinely need:

df.apply(custom_function, axis=1)

32. Common Mistakes

Mistake 1: Using apply() for Everything

Just because apply() can perform a transformation does not mean it should.

Avoid:

df["Salary"].apply(
    lambda x: x * 1.10
)

when this is enough:

df["Salary"] * 1.10

Mistake 2: Forgetting axis=1

If your function needs multiple columns from the same row:

row["Math"]
row["Science"]

you usually need:

axis=1

Mistake 3: Making Lambda Too Complicated

This:

lambda x: ...

should ideally remain easy to understand.

If you find yourself writing a long nested expression, use a named function instead.

Mistake 4: Ignoring Existing Pandas Methods

Before using:

apply()

check whether Pandas already provides:

sum()
mean()
max()
min()
abs()
astype()
str.*

or another dedicated operation.

Mistake 5: Assuming apply() Is Always Fast

apply() often involves Python-level function calls.

For large datasets, vectorized operations can be significantly more efficient.

Performance depends on the operation and data, but the general principle remains:

Prefer vectorized operations when they naturally express the task.

33. Complete Practical Example

Let’s combine the concepts.

import pandas as pd

employees = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David"
    ],
    "Department": [
        "IT",
        "HR",
        "IT",
        "Finance"
    ],
    "Salary": [
        85000,
        55000,
        95000,
        62000
    ],
    "Performance": [
        92,
        76,
        96,
        84
    ]
})

Step 1 — Direct vectorized calculation

Increase salary by 10%:

employees["Adjusted_Salary"] = (
    employees["Salary"] * 1.10
)

No apply() is necessary.

Step 2 — Custom classification

def performance_level(score):
    if score >= 90:
        return "Excellent"
    elif score >= 80:
        return "Good"
    elif score >= 70:
        return "Average"
    else:
        return "Needs Improvement"

Apply:

employees["Performance_Level"] = (
    employees["Performance"].apply(
        performance_level
    )
)

Here apply() makes sense because we are implementing a custom rule.

Step 3 — Row-wise custom logic

Suppose we want a simple employee score based on:

Performance → 70%
Salary      → 30%

We could define:

def employee_score(row):
    salary_score = row["Salary"] / 1000

    return (
        row["Performance"] * 0.70
        + salary_score * 0.30
    )

Then:

employees["Employee_Score"] = employees.apply(
    employee_score,
    axis=1
)

This demonstrates a genuine row-wise custom transformation.

34. apply() Workflow

A practical workflow is:

Understand the Transformation
        ↓
Check for Built-in Pandas Method
        ↓
Check for Vectorized Operation
        ↓
Check Dedicated Accessor
        ↓
Use apply() if Custom Logic Is Needed
        ↓
Choose Series-wise or Row-wise
        ↓
Use Lambda or Named Function
        ↓
Validate the Result

This prevents apply() from becoming the default solution to every problem.

35. Quick Reference

Apply a lambda to a Series

df["Score"].apply(
    lambda x: x + 5
)

Apply a custom function

def classify(x):
    return "Pass" if x >= 40 else "Fail"

df["Result"] = df["Score"].apply(classify)

DataFrame column-wise operation

df.apply(
    lambda column: column.max(),
    axis=0
)

DataFrame row-wise operation

df.apply(
    lambda row: row["Math"] + row["Science"],
    axis=1
)

Custom row-wise function

def calculate(row):
    return row["Math"] * 0.5 + row["Science"] * 0.5

df["Final"] = df.apply(
    calculate,
    axis=1
)

Prefer direct vectorization when possible

Instead of:

df["Score"].apply(
    lambda x: x * 2
)

prefer:

df["Score"] * 2

36. Final Takeaways

The apply() method is one of the most flexible tools in Pandas.

It allows you to apply custom Python logic to data and is particularly useful when a transformation cannot be expressed easily through standard vectorized operations.

The key concepts are:

Series.apply() can process values in a Series.
DataFrame.apply() can operate across columns or rows.
axis=0 represents column-wise application.
axis=1 represents row-wise application.
lambda is useful for short, simple functions.
Named functions are better for complex or reusable business logic.
Row-wise apply() is useful when logic depends on multiple columns from the same row.
Built-in Pandas methods should generally be preferred when they already solve the problem.
Vectorized operations are usually preferable to apply() for straightforward numerical transformations.
apply() should be a deliberate tool—not the default solution.

The most important principle is:

Do not use apply() just because you can. Use it when the problem actually requires custom logic.

Understanding this distinction will help you write Pandas code that is not only functional, but also cleaner, more readable, and often more efficient.

37. Where We Go Next

We have now covered an important part of the Pandas data-transformation workflow:

Selection
    ↓
Filtering
    ↓
Cleaning
    ↓
Data Type Conversion
    ↓
String Operations
    ↓
Statistics
    ↓
Sorting & Ranking
    ↓
Custom Transformation

In Post #17, we will move into a major data-analysis concept:

Feature Engineering with Pandas: Creating and Transforming DataFrame Columns

We will learn how to create meaningful new columns from existing data and turn raw variables into features that are more useful for analysis and machine-learning preparation.

About This Series

This article is part of Data Analysis with Pandas, a practical series designed to take you from Pandas fundamentals to real-world data analysis.

The goal is not simply to memorize functions.

The goal is to understand:

Which Pandas operation should you use, why you should use it, and when another approach is better.

That mindset becomes increasingly important as datasets become larger and analysis becomes more complex.

Author

Engr. Md. Monjur Bakth Mazumder

GitHub:
https://github.com/monjurmbm404/Python-Data-Science-Tools

Final Thought

Good Pandas code is not about using the most powerful function available.

It is about choosing the right level of abstraction for the problem.

If a simple vectorized expression solves the problem, use it.

If a dedicated Pandas method exists, use it.

If the logic is genuinely custom, apply() can be an excellent tool.

That distinction is what turns Pandas from a collection of functions into a practical data-analysis framework.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas Sorting and Ranking: sort_values(), sort_index() and rank()

Engr. Md Monjur Bakth Mazumder — Sun, 08 Nov 2026 14:01:01 GMT

Series: Data Analysis with Pandas
Part: 15

A Practical Guide to Sorting, Ranking and Finding Top or Bottom Records in Python Pandas

When working with real-world datasets, simply loading and inspecting data is not enough.

Sooner or later, you will need to answer questions such as:

Which employee has the highest salary?
Which students have the best scores?
Which products generated the most revenue?
Which records should appear first?
How can we sort by more than one column?
What is the rank of each employee?
Who belongs to the top 5 or bottom 5?

Pandas provides several powerful tools for these tasks.

The most important ones are:

sort_values()
sort_index()
rank()

These methods help us organize records, compare observations, identify top and bottom performers, and prepare datasets for further analysis.

In this article, we will learn how to use them in practical data-analysis workflows.

1. Why Sorting and Ranking Matter in Data Analysis

Imagine you have a dataset containing employee information:

import pandas as pd

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie", "David", "Emma"],
    "Department": ["IT", "HR", "IT", "Finance", "HR"],
    "Salary": [70000, 55000, 85000, 60000, 75000],
    "Performance": [88, 76, 95, 82, 91]
})

print(df)

Output:

Name Department  Salary  Performance
0    Alice         IT   70000           88
1      Bob         HR   55000           76
2  Charlie         IT   85000           95
3    David    Finance   60000           82
4     Emma         HR   75000           91

Suppose we want to answer:

Who has the highest salary?

We can sort the dataset:

df.sort_values("Salary", ascending=False)

Or:

Who has the highest performance score?

df.sort_values("Performance", ascending=False)

Or:

What is each employee’s performance rank?

df["Performance"].rank(ascending=False)

This demonstrates an important distinction:

Sorting changes the order of records.

Ranking assigns a relative position to each value.

2. Sorting with sort_values()

The most commonly used sorting method in Pandas is:

sort_values()

Basic syntax:

df.sort_values("column_name")

By default, sorting is ascending.

For example:

df.sort_values("Salary")

This places the lowest salary first.

Output conceptually:

Bob       55000
David     60000
Alice     70000
Emma      75000
Charlie   85000

3. Ascending Sorting

Ascending means:

small → large

For numbers:

For text:

Alice
Bob
Charlie
David

The following is equivalent to the default behavior:

df.sort_values("Salary", ascending=True)

You can explicitly specify:

ascending=True

This can make your code easier to understand when working with larger analysis pipelines.

4. Descending Sorting

Descending means:

large → small

Use:

df.sort_values("Salary", ascending=False)

Now the highest salary appears first.

For example:

Charlie   85000
Emma      75000
Alice     70000
David     60000
Bob       55000

This is especially useful when looking for:

highest salaries
highest scores
largest sales
most expensive products
highest revenue
best-performing employees

5. Sorting by Multiple Columns

Real-world datasets often require more than one sorting rule.

Suppose we have:

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie", "David", "Emma"],
    "Department": ["IT", "HR", "IT", "Finance", "HR"],
    "Salary": [70000, 55000, 85000, 60000, 75000],
    "Performance": [88, 76, 95, 82, 91]
})

Suppose we want:

Sort by Department alphabetically.
Within each department, sort by Salary from highest to lowest.

We can write:

df.sort_values(
    by=["Department", "Salary"],
    ascending=[True, False]
)

Here:

by=["Department", "Salary"]

defines the sorting priority.

And:

ascending=[True, False]

means:

Department → ascending
Salary → descending

This is extremely useful in business datasets.

6. Understanding Sorting Priority

Consider:

df.sort_values(
    by=["Department", "Performance"],
    ascending=[True, False]
)

Pandas first sorts by:

Department

Then, if multiple rows belong to the same department, it sorts those rows by:

Performance

in descending order.

Think of it as:

Primary sorting key
        ↓
Department

Secondary sorting key
        ↓
Performance

This concept becomes especially useful when analyzing grouped business data.

7. Sorting with the Same Direction

You can also sort multiple columns in the same direction:

df.sort_values(
    by=["Salary", "Performance"],
    ascending=False
)

This sorts both columns in descending order.

For clarity, you can also write:

df.sort_values(
    by=["Salary", "Performance"],
    ascending=[False, False]
)

The second version is often easier to read when your sorting rules become more complex.

8. Sorting the Index with sort_index()

sort_values() sorts based on column values.

But sometimes you want to sort the DataFrame according to its index.

That’s where:

sort_index()

is useful.

Example:

df = pd.DataFrame(
    {
        "Name": ["Alice", "Bob", "Charlie"],
        "Score": [88, 76, 95]
    },
    index=[103, 101, 102]
)

print(df)

The index is:

103
101
102

We can sort it:

df.sort_index()

Now the index becomes:

101
102
103

The associated rows move with their index labels.

9. Descending Index Sorting

Just like sort_values(), sort_index() supports:

ascending=False

Example:

df.sort_index(ascending=False)

Now the largest index label appears first.

This can be useful when working with:

custom indexes
date indexes
time-series data
hierarchical indexes
ordered identifiers

10. Sorting a Series

Sorting is not limited to DataFrames.

You can also sort a Series.

Example:

scores = pd.Series(
    [85, 72, 94, 68, 90],
    index=["Alice", "Bob", "Charlie", "David", "Emma"]
)

Sort by values:

scores.sort_values()

Descending:

scores.sort_values(ascending=False)

Sort by index:

scores.sort_index()

This gives you flexibility when working with both Series and DataFrames.

11. Finding the Top Records

One of the most common tasks in data analysis is finding the top records.

For example:

Find the top 3 employees by salary.

We could sort:

top_3 = df.sort_values(
    "Salary",
    ascending=False
).head(3)

print(top_3)

The workflow is:

Sort descending
      ↓
Take first N rows
      ↓
Top N records

For example:

df.sort_values("Salary", ascending=False).head(3)

This is a very common Pandas pattern.

12. Finding the Bottom Records

The same idea works for bottom records.

For example:

bottom_3 = df.sort_values(
    "Salary",
    ascending=True
).head(3)

This returns the three employees with the lowest salaries.

You can also use:

df.nsmallest(3, "Salary")

For the largest values:

df.nlargest(3, "Salary")

These methods can be convenient when your goal is specifically to find top or bottom N values.

13. nlargest() for Top Records

Instead of:

df.sort_values(
    "Performance",
    ascending=False
).head(3)

you can write:

df.nlargest(3, "Performance")

This directly communicates your intention:

Give me the three largest performance values.

For example:

top_students = df.nlargest(
    3,
    "Performance"
)

This is concise and readable.

14. nsmallest() for Bottom Records

Similarly:

df.nsmallest(3, "Performance")

returns the three smallest performance values.

This can be useful for:

identifying low-performing students
finding products with low sales
detecting low-revenue categories
identifying employees who may need support

15. Ranking with rank()

Sorting and ranking are related, but they are not the same.

Suppose:

df["Performance"]

contains:

We can calculate rankings using:

df["Performance"].rank(ascending=False)

Conceptually:

95 → Rank 1
91 → Rank 2
88 → Rank 3
82 → Rank 4
76 → Rank 5

The original rows do not need to be reordered.

Instead, Pandas creates a ranking value for each observation.

16. Adding a Rank Column

Ranking becomes especially useful when we store the result in a new column.

df["Performance_Rank"] = df["Performance"].rank(
    ascending=False
)

Now the DataFrame contains the original performance value and its rank.

You can then inspect:

print(df)

This allows you to answer:

What is each employee’s position based on performance?

without changing the original row order.

17. Sorting vs Ranking

This distinction is important.

Sorting

df.sort_values("Performance", ascending=False)

changes the order of the rows in the returned DataFrame.

Ranking

df["Performance"].rank(ascending=False)

assigns a rank to each value.

Think about it this way:

Sorting
→ Reorder the dataset

Ranking
→ Assign a position to each observation

Both are useful, but they solve different problems.

18. Ranking with Ties

Real-world data often contains equal values.

For example:

scores = pd.Series([95, 90, 90, 85])

Now two observations have the same score.

Pandas needs a rule for handling this tie.

By default:

scores.rank(ascending=False)

uses the average rank for tied values.

The two 90 values occupy positions 2 and 3, so both receive:

2.5

This is called average ranking.

19. Different Ranking Methods

The rank() method provides several ways to handle ties.

For example:

scores.rank(
    ascending=False,
    method="average"
)

This is the default.

Other useful methods include:

method="min"
method="max"
method="first"
method="dense"

method="min"

Tied values receive the lowest rank in the group.

method="max"

Tied values receive the highest rank in the group.

method="first"

Ties are ranked according to their original order.

method="dense"

Ties receive the same rank, and the next distinct value gets the next consecutive rank.

For example, conceptually:

Scores:
100
90
90
80

With dense ranking:

This can be useful when you want rankings without gaps after ties.

20. Ranking in Descending Order

For performance-based ranking, higher values usually mean better performance.

So we normally use:

df["Performance"].rank(
    ascending=False
)

This gives:

Highest value → Rank 1
Lowest value  → Last rank

For metrics where smaller values are better, such as:

error
response time
delivery time
defect count

you may instead use:

ascending=True

The correct direction depends on the meaning of the metric.

21. Ranking Salaries

Suppose we want to rank employees based on salary:

df["Salary_Rank"] = df["Salary"].rank(
    ascending=False
)

Now we have a new analytical feature:

Salary
Salary_Rank

This allows us to identify:

highest-paid employee
second-highest salary
lowest-paid employee
relative salary position

Ranking can therefore become part of a larger analytical workflow.

22. Ranking Within Groups

Ranking becomes even more powerful when combined with groupby().

Suppose we want to rank employees within their department.

We can write:

df["Department_Rank"] = (
    df.groupby("Department")["Performance"]
      .rank(ascending=False)
)

Now employees are ranked against others in the same department rather than against the entire company.

This is an important real-world analysis technique.

For example:

Company-wide ranking
vs.
Department-level ranking

These can answer different business questions.

23. Practical Example: Student Performance

Let’s create a student dataset:

students = pd.DataFrame({
    "Name": [
        "Alice",
        "Bob",
        "Charlie",
        "David",
        "Emma"
    ],
    "Math": [85, 72, 95, 68, 90],
    "Science": [88, 75, 92, 70, 94]
})

Suppose we want the average score:

students["Average"] = (
    students["Math"] +
    students["Science"]
) / 2

Now we can rank students:

students["Rank"] = students["Average"].rank(
    ascending=False,
    method="dense"
)

Then sort them:

students.sort_values(
    "Average",
    ascending=False
)

This gives us a practical student-performance ranking.

24. Finding the Top 3 Students

Once we have an average score:

top_students = students.nlargest(
    3,
    "Average"
)

This is usually more readable than manually sorting and slicing when the requirement is simply:

Find the top 3.

25. Finding the Bottom 3 Students

Similarly:

bottom_students = students.nsmallest(
    3,
    "Average"
)

This can help identify students who may need additional support.

However, remember that ranking alone does not explain why a student is performing poorly.

Further analysis may be necessary.

26. Practical Example: Sales Analysis

Consider a sales dataset:

sales = pd.DataFrame({
    "Product": [
        "Laptop",
        "Phone",
        "Tablet",
        "Monitor",
        "Keyboard"
    ],
    "Units_Sold": [
        120,
        250,
        180,
        90,
        300
    ],
    "Revenue": [
        120000,
        150000,
        90000,
        45000,
        30000
    ]
})

Find the products with the highest revenue:

sales.sort_values(
    "Revenue",
    ascending=False
)

Find the top three:

sales.nlargest(
    3,
    "Revenue"
)

Rank products by revenue:

sales["Revenue_Rank"] = sales["Revenue"].rank(
    ascending=False
)

Now we have both:

Revenue
Revenue Rank

This can be useful for business reporting.

27. Multiple-Column Sorting in Sales Analysis

Suppose two products have similar revenue.

We may want to sort using:

Revenue — highest first
Units sold — highest first

Use:

sales.sort_values(
    by=["Revenue", "Units_Sold"],
    ascending=[False, False]
)

This creates a clear priority:

Primary:
Revenue

Secondary:
Units Sold

This technique is useful whenever one column alone does not provide enough ordering information.

28. Sorting with Missing Values

Real datasets often contain missing values.

Pandas allows you to control where missing values appear during sorting.

For example:

df.sort_values(
    "Salary",
    ascending=False,
    na_position="last"
)

This places missing values at the end.

You can also use:

na_position="first"

to place missing values first.

This is useful when you want missing records to remain visible without allowing them to interfere with the main ordering.

29. Sorting Without Losing the Original Data

A common beginner question is:

Does sort_values() permanently change my DataFrame?

By default, it returns a sorted result without modifying the original DataFrame.

For example:

sorted_df = df.sort_values(
    "Salary",
    ascending=False
)

Now:

df

still has its original row order.

The sorted result is stored in:

sorted_df

This is often the safer approach when you want to preserve the original dataset.

30. Sorting with inplace=True

Pandas also supports:

df.sort_values(
    "Salary",
    ascending=False,
    inplace=True
)

This changes the DataFrame itself.

However, in analytical workflows, explicitly assigning the result is often easier to reason about:

sorted_df = df.sort_values(
    "Salary",
    ascending=False
)

This makes the transformation visible and reduces accidental changes to your working dataset.

31. Sorting and Resetting the Index

After sorting, you may notice that the original index remains attached to the rows.

For example:

sorted_df = df.sort_values(
    "Salary",
    ascending=False
)

You might see:

That is not an error.

The index identifies the original rows.

If you want a fresh sequential index:

sorted_df = (
    df.sort_values("Salary", ascending=False)
      .reset_index(drop=True)
)

Now the index becomes:

This is useful when the sorted result will be treated as a new ordered dataset.

32. A Practical Sorting and Ranking Workflow

A useful workflow is:

Load Data
    ↓
Inspect Data
    ↓
Choose the Relevant Metric
    ↓
Sort or Rank
    ↓
Find Top/Bottom Records
    ↓
Validate Results
    ↓
Analyze Insights

For example:

df.info()

df.sort_values(
    "Performance",
    ascending=False
)

df["Performance_Rank"] = df["Performance"].rank(
    ascending=False
)

top_5 = df.nlargest(
    5,
    "Performance"
)

This workflow can be reused across many datasets.

33. Common Mistakes

Mistake 1: Forgetting ascending=False

If you want the highest values first:

df.sort_values("Score")

will not do that.

Use:

df.sort_values(
    "Score",
    ascending=False
)

Mistake 2: Confusing Sorting with Ranking

This:

df.sort_values("Score")

reorders records.

This:

df["Score"].rank()

assigns ranks.

They are not interchangeable.

Mistake 3: Using the Wrong Sorting Priority

If you write:

df.sort_values(
    by=["Department", "Salary"]
)

Department becomes the primary sorting key.

Salary only determines order among rows with the same department.

Understanding this priority is important.

Mistake 4: Assuming keep-like behavior for rankings

Ranking ties require an explicit decision when business rules matter.

For example:

method="dense"

and:

method="average"

produce different results.

Always choose a ranking method appropriate to the analysis.

Mistake 5: Forgetting Missing Values

Missing values can affect how sorted results appear.

When necessary, explicitly control:

na_position="first"

or:

na_position="last"

Mistake 6: Assuming the Last Row Means the Latest Record

Sorting determines order.

If you are analyzing dates, make sure the data is actually sorted by the relevant date column before interpreting first/last records as earliest/latest.

34. Sorting vs Ranking: The Mental Model

A simple mental model is:

sort_values()
    ↓
Organize rows by column values

sort_index()
    ↓
Organize rows by index labels

rank()
    ↓
Assign relative positions to values

And for top/bottom analysis:

nlargest()
    ↓
Find highest N values

nsmallest()
    ↓
Find lowest N values

These methods solve closely related but different problems.

35. Quick Reference

Sort by one column

df.sort_values("Salary")

Sort descending

df.sort_values(
    "Salary",
    ascending=False
)

Sort by multiple columns

df.sort_values(
    by=["Department", "Salary"],
    ascending=[True, False]
)

Sort by index

df.sort_index()

Descending index

df.sort_index(
    ascending=False
)

Rank values

df["Score"].rank(
    ascending=False
)

Rank with dense method

df["Score"].rank(
    ascending=False,
    method="dense"
)

Top N records

df.nlargest(
    5,
    "Score"
)

Bottom N records

df.nsmallest(
    5,
    "Score"
)

Rank within groups

df["Rank"] = (
    df.groupby("Department")["Score"]
      .rank(ascending=False)
)

36. Final Takeaways

Sorting and ranking are fundamental operations in practical data analysis.

With Pandas, you can use:

sort_values()

to organize records according to column values.

Use:

sort_index()

when the ordering should be based on index labels.

Use:

rank()

when you need to assign relative positions to observations.

And when you specifically need the highest or lowest records,:

nlargest()
nsmallest()

can make the analysis more direct.

The key concepts to remember are:

sort_values() → sort by column values
sort_index() → sort by index
ascending=True → smallest to largest
ascending=False → largest to smallest
multiple columns → define primary and secondary sorting rules
rank() → assign relative positions
nlargest() → find top N records
nsmallest() → find bottom N records
ranking methods matter when values are tied
sorting and ranking should be chosen according to the analytical question

Sorting is not just about making a DataFrame look organized.

It helps you discover patterns.

Ranking is not just about assigning numbers.

It helps you understand relative performance.

Together, these operations turn a raw dataset into a more interpretable analytical view.

37. Where We Go Next

So far, the series has covered:

Loading datasets
Inspecting data
Selecting rows and columns
loc and iloc
Filtering
Missing values
Filling missing values
Replacing values
Duplicate handling
Data type conversion
String operations
Descriptive statistics
Sorting and ranking

The next step is to start applying functions to data more flexibly.

In Post #16, we will explore:

Pandas apply(): Lambda Functions and Row-wise vs Column-wise Operations

We will see how custom logic can be applied to Series and DataFrames — and also why built-in vectorized operations are often preferable when they are available.

About This Series

This article is part of Data Analysis with Pandas, a practical series covering Pandas from beginner fundamentals to advanced data-analysis workflows.

The goal is not simply to memorize Pandas syntax.

The goal is to understand how and why each operation is used when working with real-world data.

If you are building your foundation in Python Data Science, this series will gradually take you from:

Pandas Basics
    ↓
Data Cleaning
    ↓
Data Transformation
    ↓
Grouping & Aggregation
    ↓
Data Reshaping
    ↓
Time Series
    ↓
Exploratory Data Analysis
    ↓
Real-World Data Projects

Author

Engr. Md. Monjur Bakth Mazumder

GitHub:
https://github.com/monjurmbm404/Python-Data-Science-Tools

Final Thought

Good data analysis is not only about calculating numbers.

It is also about asking better questions:

Who is performing best?

Which records deserve attention?

What is the relative position of each observation?

Which values should appear first?

Pandas gives us simple tools to answer these questions efficiently.

Once you understand sorting, ranking, and top/bottom analysis, you have another essential building block for real-world data analysis with Python.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas Statistics: mean(), median(), mode(), min(), max(), sum() and std()

Engr. Md Monjur Bakth Mazumder — Sat, 07 Nov 2026 14:01:02 GMT

Series: Data Analysis with Pandas
Part: 14

A Practical Guide to Descriptive Statistics, Column-Level Analysis and Numerical Dataset Summary

After loading and cleaning a dataset, one of the next questions is:

What does the data actually tell us?

Before creating visualizations or building advanced analysis workflows, we often need a quick numerical summary.

For example:

What is the average salary?
What is the median age?
What is the highest score?
What is the lowest value?
How many records are there?
How much does a numeric variable vary?
Which values appear most frequently?

Pandas provides several simple statistical methods that make this type of analysis easy.

In this article, we will work with:

mean()
median()
mode()
min()
max()
sum()
std()

We will also learn how to perform:

descriptive statistics
column-level analysis
numerical dataset summaries
practical comparisons between statistical measures

1. What Is Descriptive Statistics?

Descriptive statistics is the process of summarizing the important characteristics of a dataset using numerical measures.

Instead of examining every row individually, we can calculate statistics such as:

Mean
Median
Mode
Minimum
Maximum
Sum
Standard Deviation

For example, suppose we have these scores:

70, 75, 80, 85, 90

We can summarize them with:

average score
middle score
highest score
lowest score
total score
variation in scores

This gives us a quick understanding of the dataset.

A useful mental model is:

Raw Data
   ↓
Statistical Summary
   ↓
Understand the Dataset
   ↓
Analyze
   ↓
Make Decisions

2. Creating a Sample Dataset

Let’s create a practical employee dataset:

import pandas as pd

df = pd.DataFrame({
    "Employee": [
        "Alice",
        "Bob",
        "Charlie",
        "David",
        "Eva",
        "Frank"
    ],
    "Age": [25, 30, 28, 35, 30, 32],
    "Salary": [45000, 60000, 52000, 75000, 62000, 58000],
    "Experience": [2, 5, 3, 10, 6, 7],
    "PerformanceScore": [78, 85, 82, 91, 88, 85]
})

print(df)

Our numerical columns are:

Age
Salary
Experience
PerformanceScore

These are the columns we can analyze statistically.

3. mean() — Finding the Average

The mean is commonly called the average.

For example:

df["Salary"].mean()

This calculates the average salary.

You can also calculate the mean of another column:

df["Age"].mean()

or:

df["PerformanceScore"].mean()

The basic formula is:

Mean = Sum of all values / Number of values

For example:

10 + 20 + 30 = 60

60 / 3 = 20

So the mean is:

4. Why Mean Is Useful

Mean gives us a single number representing the central level of a numerical variable.

For example:

average_salary = df["Salary"].mean()

print(average_salary)

This can answer:

What is the average salary in this dataset?

Similarly:

average_score = df["PerformanceScore"].mean()

answers:

What is the average performance score?

Mean is useful for:

salary analysis
exam scores
sales
revenue
customer spending
measurements
performance metrics

However, mean can be strongly affected by extreme values.

We will compare it with median shortly.

5. median() — Finding the Middle Value

The median is the middle value when data is ordered.

Example:

10, 20, 30, 40, 50

The median is:

In Pandas:

df["Salary"].median()

You can also calculate:

df["Age"].median()

or:

df["PerformanceScore"].median()

6. Mean vs Median

Mean and median both describe the center of the data, but they behave differently.

Consider:

20, 25, 30, 35, 1000

The value 1000 is an extreme value.

The mean becomes heavily influenced by it.

The median remains:

This is why median is often useful when a dataset contains:

extreme values
outliers
highly skewed distributions

For example, income data is often skewed because a small number of people may earn significantly more than the majority.

In such cases, median income can sometimes provide a more representative view of the typical observation than mean income.

7. mode() — Finding the Most Frequent Value

The mode is the value that occurs most frequently.

Example:

80, 85, 85, 90, 92

The mode is:

In Pandas:

df["PerformanceScore"].mode()

Notice that Pandas returns a Series, because a dataset can have more than one mode.

For example:

scores = pd.Series([70, 70, 80, 80, 90])

print(scores.mode())

Both 70 and 80 occur twice.

Therefore, both can be returned as modes.

If you expect one result but want to safely retrieve the first mode:

mode_value = df["PerformanceScore"].mode().iloc[0]

provided the Series is not empty.

8. When Is Mode Useful?

Mode is particularly useful for categorical or discrete data.

For example:

df = pd.DataFrame({
    "Department": [
        "IT",
        "HR",
        "IT",
        "Finance",
        "IT",
        "HR"
    ]
})

You can find the most frequent department:

df["Department"].mode()

The result is:

IT

Mode can therefore be useful for:

most common category
most frequent score
most common product
most common response
most common value

Unlike mean and median, mode can be used naturally with categorical data.

9. min() — Finding the Minimum

The min() method returns the smallest value.

For example:

df["Salary"].min()

This tells us the lowest salary.

Similarly:

df["Age"].min()

returns the youngest age.

And:

df["PerformanceScore"].min()

returns the lowest performance score.

Minimum is useful for understanding the lower boundary of a numerical variable.

10. max() — Finding the Maximum

The max() method returns the largest value.

Example:

df["Salary"].max()

This gives the highest salary.

You can also use:

df["Age"].max()

or:

df["PerformanceScore"].max()

Maximum helps us understand the upper boundary of the data.

11. Minimum and Maximum Together

Using both methods gives us a quick range.

minimum = df["Salary"].min()
maximum = df["Salary"].max()

print("Minimum:", minimum)
print("Maximum:", maximum)

You can also calculate the range directly:

salary_range = (
    df["Salary"].max()
    - df["Salary"].min()
)

print(salary_range)

The range tells us the distance between the smallest and largest value.

12. sum() — Calculating the Total

The sum() method adds numerical values.

For example:

df["Salary"].sum()

calculates the total salary represented by the rows in the dataset.

Similarly:

df["PerformanceScore"].sum()

calculates the total of all performance scores.

For business datasets, sum() is especially useful for:

total sales
total revenue
total expenses
total quantity
total hours
total salary

For example:

total_sales = df["Sales"].sum()

13. std() — Measuring Variation

Standard deviation measures how spread out values are around their mean.

In Pandas:

df["Salary"].std()

A smaller standard deviation generally means values are closer to the mean.

A larger standard deviation generally means values are more spread out.

For example, compare:

Dataset A:
48, 49, 50, 51, 52

Dataset B:
20, 35, 50, 65, 80

Both have the same mean:

But Dataset B has much more variation.

Standard deviation helps quantify that difference.

14. Understanding Standard Deviation Intuitively

You do not always need to calculate the formula manually to use standard deviation effectively.

Think of it as:

How much do the values typically vary around their mean?

For example:

df["PerformanceScore"].std()

can help answer:

Are employee performance scores relatively similar, or are they widely spread?

This is useful in:

performance analysis
financial analysis
scientific measurements
sales analysis
quality control
statistical analysis

15. Population vs Sample Standard Deviation

This is an important statistical detail.

Pandas’ Series.std() uses:

ddof=1

by default.

That corresponds to the sample standard deviation.

For example:

df["Salary"].std()

uses the default sample calculation.

If you specifically want the population standard deviation:

df["Salary"].std(ddof=0)

The choice depends on what your data represents.

If your dataset is considered the complete population of interest, ddof=0 may be appropriate.

If the data is treated as a sample from a larger population, the default sample standard deviation is commonly used.

For everyday Pandas analysis, understanding that the default is sample standard deviation is usually enough.

16. Column-Level Statistical Analysis

One of the most common Pandas workflows is analyzing one numerical column at a time.

For example:

df["Salary"].mean()
df["Salary"].median()
df["Salary"].min()
df["Salary"].max()
df["Salary"].sum()
df["Salary"].std()

This gives us several perspectives on salary.

We can do the same for:

df["Age"]

or:

df["PerformanceScore"]

This is called column-level analysis.

17. Analyzing Multiple Columns

You can also select several numerical columns:

numeric_df = df[
    [
        "Age",
        "Salary",
        "Experience",
        "PerformanceScore"
    ]
]

Then calculate:

numeric_df.mean()

This returns the mean for each selected column.

Similarly:

numeric_df.median()

numeric_df.min()

numeric_df.max()

numeric_df.sum()

numeric_df.std()

This is useful when you want a quick statistical summary across multiple numerical variables.

18. Using describe()

Pandas also provides:

describe()

for a broader descriptive summary.

For example:

df.describe()

For numerical columns, you will typically see statistics such as:

count
mean
std
min
25%
50%
75%
max

This is one of the fastest ways to understand a numerical dataset.

For example:

print(df.describe())

can quickly show:

number of observations
average
standard deviation
minimum
quartiles
maximum

19. Understanding the describe() Output

Suppose:

df.describe()

returns a summary.

The key values mean:

count

Number of non-missing observations.

mean

Average value.

std

Standard deviation.

min

Smallest value.

25%

First quartile.

50%

Median.

75%

Third quartile.

max

Largest value.

The 50% value is the median.

So describe() gives you several important statistics in one operation.

20. Why describe() Does Not Replace Individual Methods

You may wonder:

If describe() already gives me statistics, why learn mean(), median(), min() and the others?

Because individual methods give you control.

For example:

average_salary = df["Salary"].mean()

is much more explicit when you need one particular statistic.

You may also want to perform additional logic:

if df["Salary"].mean() > 60000:
    print("Average salary is above the target.")

Individual methods are therefore important building blocks.

describe() is a convenient summary tool.

21. Working with Missing Values

Pandas statistical methods generally skip missing values by default.

Consider:

df = pd.DataFrame({
    "Score": [80, 85, None, 90, 95]
})

Then:

df["Score"].mean()

will calculate the mean using the available numeric values.

You can check the number of missing values with:

df["Score"].isnull().sum()

This is important because a statistical result may be based on fewer observations than the total number of rows.

For example:

df["Score"].count()

tells you how many non-missing values are included.

22. A Practical Numerical Summary

Let’s build a reusable summary for the salary column:

salary_summary = {
    "Mean": df["Salary"].mean(),
    "Median": df["Salary"].median(),
    "Minimum": df["Salary"].min(),
    "Maximum": df["Salary"].max(),
    "Total": df["Salary"].sum(),
    "Standard Deviation": df["Salary"].std()
}

print(salary_summary)

This creates a compact numerical profile of the column.

You can use the same pattern for:

Age
Experience
PerformanceScore
Sales
Revenue

or any other appropriate numerical variable.

23. Comparing Mean and Median

Comparing mean and median can reveal useful information.

For example:

mean_salary = df["Salary"].mean()
median_salary = df["Salary"].median()

print("Mean:", mean_salary)
print("Median:", median_salary)

If mean and median are close:

Mean ≈ Median

the distribution may be relatively balanced.

If they differ significantly, the data may be skewed or affected by extreme values.

This is not a complete test for skewness, but it is a useful first observation.

24. Example: Salary Analysis

Suppose we have:

df = pd.DataFrame({
    "Employee": [
        "Alice",
        "Bob",
        "Charlie",
        "David",
        "Eva",
        "Frank"
    ],
    "Salary": [
        45000,
        60000,
        52000,
        75000,
        62000,
        58000
    ]
})

Now calculate:

mean_salary = df["Salary"].mean()
median_salary = df["Salary"].median()
minimum_salary = df["Salary"].min()
maximum_salary = df["Salary"].max()
total_salary = df["Salary"].sum()
salary_std = df["Salary"].std()

You can print:

print("Mean:", mean_salary)
print("Median:", median_salary)
print("Minimum:", minimum_salary)
print("Maximum:", maximum_salary)
print("Total:", total_salary)
print("Standard Deviation:", salary_std)

Now we have a basic statistical profile of employee salaries.

25. Example: Student Performance Analysis

Statistics are also useful for student datasets.

students = pd.DataFrame({
    "Student": [
        "A",
        "B",
        "C",
        "D",
        "E"
    ],
    "Math": [75, 82, 90, 68, 85],
    "English": [80, 78, 88, 72, 91],
    "Science": [85, 80, 92, 70, 87]
})

Mean Mathematics score:

students["Math"].mean()

Median Mathematics score:

students["Math"].median()

Highest Mathematics score:

students["Math"].max()

Lowest Mathematics score:

students["Math"].min()

Total Mathematics score:

students["Math"].sum()

Standard deviation:

students["Math"].std()

Full summary:

students["Math"].describe()

This gives us a quick understanding of student performance.

26. Comparing Multiple Numerical Columns

You can calculate the mean of several subjects:

students[
    ["Math", "English", "Science"]
].mean()

This returns the average for each subject.

Similarly:

students[
    ["Math", "English", "Science"]
].median()

or:

students[
    ["Math", "English", "Science"]
].std()

This is useful when comparing multiple numerical variables.

27. Finding the Most Frequent Value

For categorical or discrete data, mode() can be particularly useful.

For example:

students = pd.DataFrame({
    "Grade": [
        "A",
        "B",
        "A",
        "A",
        "B",
        "C"
    ]
})

Find the most frequent grade:

students["Grade"].mode()

This can help answer:

Which grade appears most frequently?

The same idea can be used for:

most common city
most common category
most common product
most common response

28. Statistics by Column Type

Not every statistical method makes sense for every column.

For example:

Employee Name
Department
Salary
Age
Join Date

Mean salary makes sense.

Mean employee name does not.

Mean department does not.

This is why datatype awareness from the previous article is important.

A good workflow is:

Inspect dtype
     ↓
Identify numerical columns
     ↓
Apply numerical statistics
     ↓
Analyze categorical columns separately

29. Numerical Dataset Summary

A common data-analysis task is to summarize all numerical columns.

Suppose:

df = pd.DataFrame({
    "Age": [25, 30, 28, 35, 30],
    "Salary": [45000, 60000, 52000, 75000, 62000],
    "Experience": [2, 5, 3, 10, 6]
})

You can quickly inspect:

df.describe()

You can also calculate individual summaries:

df.mean()

df.median()

df.min()

df.max()

df.std()

These operations work column-wise by default.

30. Understanding axis

Pandas statistical methods often operate column-wise by default.

For example:

df.mean()

calculates the mean for each column.

You can also calculate across columns for each row:

df.mean(axis=1)

This produces a result for each row.

For example, in a student dataset:

students[
    ["Math", "English", "Science"]
].mean(axis=1)

can calculate the average score across subjects for each student.

This is a useful distinction:

axis=0 → calculate down each column
axis=1 → calculate across each row

Understanding axis becomes increasingly important as you work with Pandas.

31. Column-Level vs Row-Level Analysis

Consider:

students[
    ["Math", "English", "Science"]
]

Column-level mean:

students[
    ["Math", "English", "Science"]
].mean()

This answers:

What is the average score for each subject?

Row-level mean:

students[
    ["Math", "English", "Science"]
].mean(axis=1)

This answers:

What is the average score for each student?

These are two different analytical questions.

32. A Complete Descriptive Statistics Workflow

A practical workflow can look like this:

Load Dataset
      ↓
Inspect Columns
      ↓
Check Data Types
      ↓
Identify Numerical Columns
      ↓
Check Missing Values
      ↓
Calculate Descriptive Statistics
      ↓
Compare Columns
      ↓
Identify Patterns / Outliers
      ↓
Visualize for Deeper Analysis

For example:

print(df.dtypes)

print(df.isnull().sum())

print(df.describe())

print(df["Salary"].mean())

print(df["Salary"].median())

print(df["Salary"].std())

This provides a strong first numerical overview.

33. Common Mistakes

Mistake 1 — Using mean without checking the data

Mean can be affected by extreme values.

Always consider:

df["Column"].median()

alongside:

df["Column"].mean()

when appropriate.

Mistake 2 — Treating mode like a single scalar

Pandas returns a Series from:

df["Column"].mode()

because multiple modes are possible.

Do not assume there can only be one.

Mistake 3 — Ignoring missing values

Before interpreting a statistic, check:

df["Column"].isnull().sum()

and:

df["Column"].count()

You need to know how many valid observations support the result.

Mistake 4 — Applying statistics to inappropriate columns

Do not calculate averages for columns that represent:

names
categories
identifiers
arbitrary text

First understand what the column represents.

Mistake 5 — Looking only at the mean

A mean alone rarely tells the whole story.

Consider:

Mean
Median
Minimum
Maximum
Standard deviation
Count

together.

Mistake 6 — Assuming describe() is complete analysis

describe() provides a useful summary, but it does not explain:

why values differ
whether outliers are meaningful
relationships between variables
trends over time
business context

Descriptive statistics are a starting point for analysis, not the entire analysis.

34. Statistics as the First Analytical Layer

Descriptive statistics are often one of the first analytical steps after data cleaning.

For example:

Raw Data
   ↓
Cleaning
   ↓
Datatype Correction
   ↓
Descriptive Statistics
   ↓
Visualization
   ↓
Deeper Analysis

Statistics tell you what the numbers look like.

Visualization can then help you understand how those numbers are distributed and related.

This is why statistical analysis naturally connects to the next stages of exploratory data analysis.

35. Quick Reference

Mean:

df["Column"].mean()

Median:

df["Column"].median()

Mode:

df["Column"].mode()

Minimum:

df["Column"].min()

Maximum:

df["Column"].max()

Sum:

df["Column"].sum()

Standard deviation:

df["Column"].std()

Descriptive summary:

df["Column"].describe()

Numerical columns summary:

df.describe()

Column-wise mean:

df.mean()

Row-wise mean:

df.mean(axis=1)

Count of non-missing values:

df["Column"].count()

Missing values:

df["Column"].isnull().sum()

36. Final Takeaways

The most important Pandas statistical methods covered in this article are:

mean()
median()
mode()
min()
max()
sum()
std()

Each answers a different question.

Mean tells you the average.

Median tells you the middle value.

Mode tells you the most frequent value.

Min tells you the smallest value.

Max tells you the largest value.

Sum tells you the total.

Standard deviation tells you how spread out the values are.

Together, these measures provide a useful numerical summary of a dataset.

But remember:

A statistical summary is not the same as understanding the data.

You still need to consider:

data quality
missing values
outliers
data types
business context
relationships between variables
distributions

A strong analysis workflow is:

Inspect → Clean → Summarize → Compare → Visualize → Interpret

What Comes Next?

We have now learned how to summarize numerical data using descriptive statistics.

The next step is to understand how to sort values, rank observations and organize data based on numerical or categorical columns.

That brings us to:

Post 15 — Sorting and Ranking Data with Pandas: sort_values(), sort_index() and rank()

Series Navigation

Previous:
Post 13 — Pandas String Operations: lower(), upper(), contains(), split() and replace()

Next:
Post 15 — Sorting and Ranking Data with Pandas: sort_values(), sort_index() and rank()

Complete Series:
Data Analysis with Pandas — Beginner to Advanced

About the Author

Engr. Md. Monjur Bakth Mazumder

GitHub: Python-Data-Science-Tools

Final Thought

Before asking a dataset to tell you a complicated story, start with the basics.

Calculate the average.

Find the median.

Check the minimum and maximum.

Measure the spread.

Count the observations.

These simple statistical operations often reveal important characteristics of your data before you move into visualization or deeper analysis.

Understand the numbers first. Then investigate the story behind them.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas String Operations: lower(), upper(), contains(), split() and replace()

Engr. Md Monjur Bakth Mazumder — Fri, 06 Nov 2026 14:01:03 GMT

Series: Data Analysis with Pandas
Part: 13

A Practical Guide to Cleaning, Searching, Standardizing and Extracting Text with Pandas

Real-world datasets rarely contain perfectly clean text.

A customer name may contain unnecessary spaces.

A department may appear as:

IT
it
It
I.T.
Information Technology

An email column may contain inconsistent capitalization.

A product description may contain useful information hidden inside a longer string.

For example:

"Samsung Galaxy S24 - 256GB"

contains information about:

brand
product
storage

Pandas provides powerful string operations that make this type of data cleaning and transformation much easier.

In this article, we will focus on five important string methods:

str.lower()
str.upper()
str.contains()
str.split()
str.replace()

We will use them for:

text cleaning
searching
category normalization
extracting information from strings
preparing text columns for analysis

1. Why String Operations Matter in Pandas

Text columns are everywhere in real-world datasets.

You may have:

Name
Email
Department
City
Product
Address
Description
Category

The problem is that text data is often inconsistent.

For example:

"Dhaka"
"dhaka"
"DHAKA"
" Dhaka "

These values may represent the same city.

If you group them directly:

df.groupby("City").size()

Pandas may treat them as different categories.

This can produce misleading results.

String cleaning helps transform inconsistent text into a more standardized form.

A useful mindset is:

Clean the text before using it for analysis.

2. Creating a Sample DataFrame

Let’s start with a practical dataset:

import pandas as pd

df = pd.DataFrame({
    "Name": [
        " Alice Smith ",
        "BOB JOHNSON",
        "charlie brown",
        "David Wilson"
    ],
    "Department": [
        "IT",
        "it",
        "Human Resources",
        "HR"
    ],
    "Email": [
        "alice@example.com",
        "bob@gmail.com",
        "charlie@example.com",
        "david@company.com"
    ],
    "Product": [
        "Samsung Galaxy S24 - 256GB",
        "Apple iPhone 16 - 128GB",
        "Dell Laptop - 16GB RAM",
        "HP Laptop - 512GB SSD"
    ]
})

print(df)

This dataset contains several common text-cleaning situations.

3. Understanding the str Accessor

Pandas provides vectorized string operations through the .str accessor.

For example:

df["Name"].str.lower()

The important part is:

.str

Instead of processing every value with a Python loop, Pandas allows you to apply string operations to an entire Series.

For example:

df["Name"].str.upper()

applies the operation to every value in the Name column.

This makes string processing concise and convenient.

4. str.lower() — Convert Text to Lowercase

The first important operation is:

str.lower()

Example:

df["Name"] = df["Name"].str.lower()

Now:

Alice Smith

becomes:

alice smith

Similarly:

BOB JOHNSON

becomes:

bob johnson

Lowercase normalization is especially useful for:

email addresses
category values
search operations
comparisons
duplicate detection

For example:

df["Email"] = df["Email"].str.lower()

Now emails have a consistent case.

5. str.upper() — Convert Text to Uppercase

The opposite operation is:

str.upper()

Example:

df["Department"] = df["Department"].str.upper()

Values such as:

IT
it
It

can all become:

IT
IT
IT

This can be useful when a dataset uses uppercase codes or identifiers.

For example:

df["CountryCode"] = df["CountryCode"].str.upper()

could normalize:

bd
Bd
BD

into:

BD
BD
BD

6. str.lower() vs str.upper()

Both methods perform normalization.

Use lowercase when you want:

email@example.com
dhaka
technology

Use uppercase when you want:

BD
USA
IT
HR

The important thing is consistency.

For example, if you are standardizing a category column, choose one representation and apply it consistently.

7. Cleaning Extra Spaces

Although not one of the five main methods in this article, whitespace cleaning is an important part of string preprocessing.

For example:

df["Name"] = df["Name"].str.strip()

This removes unnecessary spaces from the beginning and end.

Before:

" Alice Smith "

After:

"Alice Smith"

A common cleaning pattern is:

df["Name"] = (
    df["Name"]
    .str.strip()
    .str.lower()
)

Now the values are both trimmed and normalized.

String operations can also be chained together.

8. str.contains() — Searching Inside Text

One of the most useful string methods in Pandas is:

str.contains()

It allows you to check whether a string contains a particular pattern.

For example:

df["Email"].str.contains("gmail")

This returns a Boolean Series:

False
True
False
False

You can use that Boolean result to filter rows:

df[df["Email"].str.contains("gmail")]

Now you can find users whose email address contains "gmail".

9. Searching for Text Case-Insensitively

Suppose your dataset contains:

Python
python
PYTHON
PyThOn

Searching with:

df["Skill"].str.contains("python")

may not match all variations because string matching is case-sensitive by default.

You can use:

df["Skill"].str.contains(
    "python",
    case=False,
    na=False
)

Now different capitalization patterns can be matched.

The na=False option is also useful when the column contains missing values.

It tells Pandas to treat missing values as False for this filtering operation.

10. Filtering Rows with str.contains()

Consider:

df = pd.DataFrame({
    "Name": [
        "Alice Smith",
        "Bob Johnson",
        "Charlie Brown",
        "David Wilson"
    ],
    "Department": [
        "Technology",
        "Finance",
        "Technology",
        "Human Resources"
    ]
})

Find employees from Technology:

df[df["Department"].str.contains(
    "technology",
    case=False,
    na=False
)]

This is a practical combination of:

String search
      ↓
Boolean condition
      ↓
DataFrame filtering

11. Searching for Multiple Patterns

Sometimes you want to search for more than one keyword.

For example:

df["Department"].str.contains(
    "IT|Technology",
    case=False,
    na=False
)

The | inside the pattern acts as an OR in regular-expression matching.

This can find values containing either:

IT

or:

Technology

For more advanced pattern matching, str.contains() supports regular expressions by default.

If you want literal text matching instead, you can use:

df["Column"].str.contains(
    "text",
    regex=False,
    na=False
)

This distinction becomes useful when your search text contains special regex characters.

12. Handling Missing Values with str.contains()

Consider:

df = pd.DataFrame({
    "Email": [
        "alice@gmail.com",
        None,
        "bob@example.com"
    ]
})

Using:

df["Email"].str.contains("gmail")

can produce a missing result for the None value.

For filtering, it is often safer to write:

df["Email"].str.contains(
    "gmail",
    na=False
)

Now missing values are treated as False.

This makes the result easier to use as a Boolean filter.

13. str.split() — Splitting Strings

Another powerful operation is:

str.split()

It splits a string into separate pieces.

For example:

df["Name"].str.split()

For:

Alice Smith

the result is conceptually:

["Alice", "Smith"]

This is useful when a single text field contains multiple pieces of structured information.

14. Extracting First and Last Names

Suppose:

df = pd.DataFrame({
    "FullName": [
        "Alice Smith",
        "Bob Johnson",
        "Charlie Brown"
    ]
})

You can split the names:

df["FullName"].str.split()

To extract the first name:

df["FirstName"] = df["FullName"].str.split().str[0]

Now:

Alice
Bob
Charlie

For the last name:

df["LastName"] = df["FullName"].str.split().str[-1]

Now:

Smith
Johnson
Brown

This is a simple example of extracting structured information from unstructured text.

15. Splitting with a Specific Separator

str.split() can also use a specific separator.

For example:

df["Email"].str.split("@")

For:

alice@example.com

the result becomes conceptually:

["alice", "example.com"]

You can extract the username:

df["Username"] = (
    df["Email"]
    .str.split("@")
    .str[0]
)

And the domain:

df["Domain"] = (
    df["Email"]
    .str.split("@")
    .str[1]
)

This is a useful example of extracting information from strings.

16. Splitting Product Information

Consider:

df = pd.DataFrame({
    "Product": [
        "Samsung Galaxy S24 - 256GB",
        "Apple iPhone 16 - 128GB",
        "Dell Laptop - 16GB RAM"
    ]
})

You can split the product name and specification:

df["Product"].str.split(" - ")

The result separates:

Product Name

from:

Specification

You can extract them:

df["ProductName"] = (
    df["Product"]
    .str.split(" - ")
    .str[0]
)

df["Specification"] = (
    df["Product"]
    .str.split(" - ")
    .str[1]
)

This transforms semi-structured text into separate analytical columns.

17. str.replace() — Replacing Text

The next important method is:

str.replace()

It allows you to replace part of a string with another value.

Example:

df["Department"] = df["Department"].str.replace(
    "IT",
    "Technology",
    regex=False
)

This changes:

IT

into:

Technology

18. Cleaning Unwanted Characters

Suppose a phone-number column contains:

+880-1711-123456
+880-1811-654321

You may want to remove the hyphens.

Use:

df["Phone"] = df["Phone"].str.replace(
    "-",
    "",
    regex=False
)

Now:

+8801711123456
+8801811654321

The same technique can be used for:

commas
currency symbols
unnecessary punctuation
formatting characters

19. Category Normalization

One of the most important real-world uses of string operations is category normalization.

Suppose:

df = pd.DataFrame({
    "Department": [
        "IT",
        "it",
        "It",
        "I.T.",
        "Information Technology",
        "HR",
        "hr"
    ]
})

These values may represent only two actual categories:

Technology
Human Resources

First normalize case:

df["Department"] = df["Department"].str.lower()

Then remove punctuation where appropriate:

df["Department"] = df["Department"].str.replace(
    ".",
    "",
    regex=False
)

Now values such as:

it
i.t.

can be standardized more easily.

Then apply explicit business rules:

df["Department"] = df["Department"].replace({
    "it": "Technology",
    "information technology": "Technology",
    "hr": "Human Resources"
})

This is an important distinction:

String methods help normalize the text; business rules determine what the normalized categories should mean.

20. A Better Category Cleaning Workflow

For category data, a useful workflow is:

Raw Categories
      ↓
Strip whitespace
      ↓
Normalize case
      ↓
Remove known formatting differences
      ↓
Map known variants
      ↓
Validate unique values

Example:

df["Department"] = (
    df["Department"]
    .str.strip()
    .str.lower()
    .str.replace(".", "", regex=False)
)

df["Department"] = df["Department"].replace({
    "it": "Technology",
    "information technology": "Technology",
    "hr": "Human Resources"
})

Then inspect:

print(df["Department"].unique())

This final validation step is important.

21. str.replace() for Standardizing Text

Suppose city values contain inconsistent formatting:

Dhaka
dhaka
DHAKA
Dhaka City

You might first normalize case:

df["City"] = df["City"].str.lower()

Then apply a known replacement:

df["City"] = df["City"].str.replace(
    "dhaka city",
    "dhaka",
    regex=False
)

Now multiple representations can be brought closer to a common category.

However, avoid blindly replacing text unless you understand the data.

For example, a word may appear as part of a larger legitimate value.

22. Cleaning Email Addresses

Email columns are a common place for text normalization.

Consider:

df = pd.DataFrame({
    "Email": [
        " Alice@Example.COM ",
        "bob@GMAIL.com",
        "CHARLIE@example.com "
    ]
})

A basic cleaning process:

df["Email"] = (
    df["Email"]
    .str.strip()
    .str.lower()
)

Now the values become standardized:

alice@example.com
bob@gmail.com
charlie@example.com

This can improve:

duplicate detection
searching
grouping
comparisons
validation

23. Extracting Information from Email Addresses

Once the email is normalized:

df["Email"] = (
    df["Email"]
    .str.strip()
    .str.lower()
)

you can extract the domain:

df["EmailDomain"] = (
    df["Email"]
    .str.split("@")
    .str[1]
)

Now:

alice@example.com

becomes:

example.com

You can then analyze:

df["EmailDomain"].value_counts()

This turns text into structured information that can be analyzed.

24. Extracting Numbers from Text

Sometimes useful numerical information is embedded inside strings.

For example:

df = pd.DataFrame({
    "Product": [
        "Laptop 16GB RAM",
        "Phone 8GB RAM",
        "Tablet 12GB RAM"
    ]
})

You can search for:

df["Product"].str.contains(
    "GB",
    case=False,
    na=False
)

For more advanced extraction, Pandas string methods can work with regular expressions.

For example:

df["RAM"] = (
    df["Product"]
    .str.extract(r"(\d+)\s*GB", expand=False)
)

This extracts:

16
8
12

You can then convert the result to numeric:

df["RAM"] = pd.to_numeric(
    df["RAM"],
    errors="coerce"
)

This is a powerful pattern:

Text
 ↓
Search / Extract
 ↓
Clean
 ↓
Convert
 ↓
Analyze

25. Searching vs Extracting

It is useful to understand the difference.

Searching

Use:

str.contains()

when you want to know:

Does this text contain a particular pattern?

Example:

df["Description"].str.contains(
    "laptop",
    case=False,
    na=False
)

This returns Boolean values.

Extracting

Use methods such as:

str.split()

or:

str.extract()

when you want to retrieve a piece of information from the text.

For example:

df["Email"].str.split("@").str[1]

extracts the email domain.

The distinction is simple:

contains() → Find
split()    → Separate
extract()  → Retrieve

26. Chaining String Operations

Pandas allows you to chain multiple string operations.

For example:

df["Name"] = (
    df["Name"]
    .str.strip()
    .str.lower()
)

Another example:

df["Department"] = (
    df["Department"]
    .str.strip()
    .str.lower()
    .str.replace(".", "", regex=False)
)

This makes a cleaning pipeline easy to read.

Instead of writing multiple intermediate variables, you can express the transformation as a sequence of steps.

27. A Complete Text-Cleaning Example

Let’s create a realistic dataset:

df = pd.DataFrame({
    "Name": [
        " Alice Smith ",
        "BOB JOHNSON",
        "charlie brown",
        " David Wilson "
    ],
    "Department": [
        "IT",
        "it ",
        "Human Resources",
        "HR"
    ],
    "Email": [
        " Alice@Example.COM ",
        "bob@gmail.com",
        "CHARLIE@example.com",
        "david@company.com "
    ]
})

Step 1 — Clean names

df["Name"] = (
    df["Name"]
    .str.strip()
    .str.title()
)

The result becomes consistently formatted names.

Step 2 — Normalize departments

df["Department"] = (
    df["Department"]
    .str.strip()
    .str.lower()
)

Then apply business mapping:

df["Department"] = df["Department"].replace({
    "it": "Technology",
    "hr": "Human Resources"
})

Step 3 — Normalize emails

df["Email"] = (
    df["Email"]
    .str.strip()
    .str.lower()
)

Step 4 — Extract email domains

df["EmailDomain"] = (
    df["Email"]
    .str.split("@")
    .str[1]
)

Step 5 — Validate

print(df)
print(df["Department"].unique())
print(df["EmailDomain"].value_counts())

This is a small but realistic text-cleaning workflow.

28. String Operations and Data Quality

String operations are not only about making data look cleaner.

They can directly affect data quality.

Consider these values:

Dhaka
dhaka
 DHAKA
Dhaka

Without normalization, you may incorrectly count them as separate categories.

For example:

df["City"].value_counts()

could produce separate entries.

After:

df["City"] = (
    df["City"]
    .str.strip()
    .str.lower()
)

they can be grouped consistently.

This can improve:

frequency analysis
grouping
duplicate detection
filtering
reporting
visualization

29. Common Mistakes

Mistake 1 — Forgetting .str

This is incorrect:

df["Name"].lower()

For a Pandas Series, use:

df["Name"].str.lower()

Mistake 2 — Ignoring whitespace

These values are different:

"IT"
"IT "

Use:

df["Department"].str.strip()

when leading/trailing whitespace is not meaningful.

Mistake 3 — Case-sensitive searching

This:

df["Name"].str.contains("alice")

may not match:

Alice
ALICE
ALIce

Use:

df["Name"].str.contains(
    "alice",
    case=False,
    na=False
)

when the search should ignore capitalization.

Mistake 4 — Ignoring missing values

String operations on columns containing missing values can require special handling.

For filtering:

df["Email"].str.contains(
    "gmail",
    na=False
)

is often useful.

Mistake 5 — Blind replacement

Do not replace values simply because they look similar.

For example:

df["City"].str.replace("York", "New York")

could unintentionally alter values containing "York" as part of a larger legitimate string.

Always understand the data and define the intended cleaning rule.

Mistake 6 — Normalizing without a business rule

Changing:

HR
Human Resources
Human Resource

into one category may be correct.

But whether:

Recruitment

should also become "Human Resources" depends on the meaning of the dataset.

Technical normalization and business classification are not always the same thing.

30. A Practical String Cleaning Workflow

When working with text columns, use a deliberate process:

1. Inspect
      ↓
2. Identify inconsistencies
      ↓
3. Strip unnecessary whitespace
      ↓
4. Normalize case
      ↓
5. Search or filter
      ↓
6. Replace known variations
      ↓
7. Extract useful information
      ↓
8. Validate unique values
      ↓
9. Continue analysis

For example:

df["Category"] = (
    df["Category"]
    .str.strip()
    .str.lower()
)

Then:

print(df["Category"].unique())

Then apply known business mappings.

31. A Reusable String Operations Template

Here is a practical template:

# Lowercase
df["Name"] = df["Name"].str.lower()

# Uppercase
df["Code"] = df["Code"].str.upper()

# Remove surrounding whitespace
df["Name"] = df["Name"].str.strip()

# Search
df[df["Name"].str.contains(
    "alice",
    case=False,
    na=False
)]

# Split
df["Username"] = (
    df["Email"]
    .str.split("@")
    .str[0]
)

# Replace
df["Department"] = (
    df["Department"]
    .str.replace(
        "I.T.",
        "IT",
        regex=False
    )
)

The exact operations depend on the dataset, but the overall pattern remains similar.

32. The Bigger Picture

String operations become especially important when working with datasets collected from:

forms
websites
APIs
spreadsheets
databases
customer systems
surveys
business applications

Raw text often contains inconsistencies that are invisible at first glance.

Pandas gives you tools to turn that messy text into structured, analysis-ready information.

For example:

Raw Email
    ↓
strip()
    ↓
lower()
    ↓
split()
    ↓
Domain
    ↓
Analysis

Or:

Raw Category
    ↓
strip()
    ↓
lower()
    ↓
replace()
    ↓
Standard Category
    ↓
groupby()

This is where string processing becomes part of real data analysis rather than just text manipulation.

33. Quick Reference

Convert to lowercase:

df["Column"].str.lower()

Convert to uppercase:

df["Column"].str.upper()

Remove surrounding whitespace:

df["Column"].str.strip()

Search for text:

df["Column"].str.contains(
    "keyword",
    case=False,
    na=False
)

Split text:

df["Column"].str.split()

Split using a separator:

df["Column"].str.split("@")

Extract the first piece:

df["Column"].str.split("@").str[0]

Extract the second piece:

df["Column"].str.split("@").str[1]

Replace text:

df["Column"].str.replace(
    "old",
    "new",
    regex=False
)

Extract with a regular expression:

df["Column"].str.extract(
    r"(\d+)",
    expand=False
)

34. Final Takeaways

The most important string operations covered in this article are:

str.lower()
str.upper()
str.contains()
str.split()
str.replace()

But the real goal is not memorizing these methods.

The goal is learning how to use them as part of a data-cleaning workflow.

Remember:

Use str.lower() to normalize text to lowercase.
Use str.upper() for uppercase normalization.
Use str.contains() to search and filter text.
Use str.split() to separate structured pieces of information.
Use str.replace() to correct or standardize known text patterns.
Use str.strip() to remove unnecessary surrounding whitespace.
Use case=False when searches should ignore capitalization.
Use na=False when missing values should not match a text search.
Always inspect unique categories after normalization.
Do not blindly replace values without understanding their meaning.
Separate technical text normalization from business classification rules.

A strong text-cleaning mindset is:

Inspect → Normalize → Search → Replace → Extract → Validate

Once you become comfortable with these operations, many messy text columns become much easier to work with.

What Comes Next?

So far in the Pandas data-cleaning section, we have covered:

Missing data
Filling missing values
Replacing incorrect values
Duplicate data
Data types
String cleaning and transformation

The next step is to perform statistical analysis on numerical columns.

That brings us to:

Post 14 — Pandas Statistics: Mean, Median, Mode, Min, Max, Sum and Standard Deviation

Series Navigation

Previous:
Post 12 — Pandas Data Types: astype(), to_numeric() and to_datetime() Explained

Next:
Post 14 — Pandas Statistics: Mean, Median, Mode, Min, Max, Sum and Standard Deviation

Complete Series:
Data Analysis with Pandas — Beginner to Advanced

Author

Engr. Md. Monjur Bakth Mazumder

GitHub: Python-Data-Science-Tools

Final Thought

Real-world data is rarely clean when you first receive it.

Names have inconsistent capitalization. Categories contain multiple representations. Emails contain unnecessary spaces. Product descriptions contain useful information hidden inside larger strings.

Pandas string operations give you a practical way to transform this messy text into consistent, structured and analysis-ready data.

Clean the text → Extract the information → Validate the result → Analyze the data.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast

Pandas Data Types: astype(), to_numeric() and to_datetime() Explained

Engr. Md Monjur Bakth Mazumder — Thu, 05 Nov 2026 14:01:03 GMT

Series: Data Analysis with Pandas
Part: 12

A Practical Guide to Inspecting, Converting and Fixing Data Types in Python Pandas

When you load a real-world dataset with Pandas, the data may not always have the correct data types.

A salary column may contain numbers stored as strings.

A date column may contain text such as "2026-01-15".

A numeric column may contain invalid values such as "N/A" or "Unknown".

These problems can affect calculations, filtering, sorting, grouping, and analysis.

That is why understanding Pandas data types is an essential part of data cleaning.

In this article, we will learn how to inspect and convert data types using:

astype()
pd.to_numeric()
pd.to_datetime()

We will also look at common datatype problems and understand why choosing the correct dtype matters in real-world data analysis.

1. What Is a Data Type?

A data type tells Pandas what kind of values a column contains.

For example:

Name       → text
Age        → integer
Salary     → decimal number
Joined     → date/time
Active     → boolean

Consider this DataFrame:

import pandas as pd

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Age": [25, 30, 28],
    "Salary": [50000.0, 62000.0, 58000.0]
})

print(df)

You can inspect the data types with:

print(df.dtypes)

Output:

Name       object
Age         int64
Salary    float64
dtype: object

The exact dtype names can vary depending on the data and Pandas version, but the important idea is that each column has a type.

2. Why Data Types Matter

Correct data types are not just a technical detail.

They directly affect how Pandas interprets and processes your data.

For example, suppose a salary column contains:

df["Salary"] = ["50000", "62000", "58000"]

These values look numeric.

But they are actually strings.

If you try:

df["Salary"].mean()

you may get an error because Pandas cannot calculate a numeric mean from ordinary string values.

Similarly, a date stored as text cannot always be used correctly for:

date filtering
date subtraction
time differences
monthly analysis
yearly analysis
time-series operations

So a useful rule is:

Before analyzing a dataset, make sure important columns have appropriate data types.

3. Inspecting Data Types with dtypes

The first step is inspection.

Use:

df.dtypes

Example:

print(df.dtypes)

You can also inspect the DataFrame structure:

df.info()

For example:


RangeIndex: 3 entries, 0 to 2
Data columns (total 3 columns):
 Name      3 non-null object
 Age       3 non-null int64
 Salary    3 non-null float64

dtypes gives you a quick view of column types.

info() provides additional information such as:

number of rows
column names
non-null counts
data types
memory usage

For data cleaning, both are useful.

4. Common Pandas Data Types

You will commonly encounter types such as:

int64
float64
object
bool
datetime64[ns]

You may also encounter newer or specialized Pandas dtypes such as:

string
Int64
boolean

The important point is not memorizing every dtype name.

Instead, understand what the column represents.

For example:

Age       → integer
Salary    → numeric
Name      → string
Joined    → datetime
Active    → boolean

5. A Common Real-World Problem

Let’s create a messy DataFrame:

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie"],
    "Age": ["25", "30", "28"],
    "Salary": ["50000", "62000", "58000"],
    "JoinDate": ["2026-01-10", "2026-02-15", "2026-03-20"]
})

print(df.dtypes)

You may see:

Name        object
Age         object
Salary      object
JoinDate    object
dtype: object

Everything except the name is actually represented as text.

This is a typical data-cleaning problem.

The values look correct to a human, but their underlying types are not ideal for analysis.

6. Converting Data Types with astype()

One of the simplest ways to convert a column is:

astype()

For example:

df["Age"] = df["Age"].astype(int)

Now:

print(df["Age"].dtype)

Output:

int64

Similarly:

df["Salary"] = df["Salary"].astype(float)

Now salary can be treated as a numeric column.

7. Converting Strings to Numeric Values

Suppose:

df["Salary"] = ["50000", "62000", "58000"]

These are strings.

You can convert them using:

df["Salary"] = df["Salary"].astype(float)

or:

df["Salary"] = df["Salary"].astype(int)

depending on the desired representation.

After conversion:

print(df["Salary"].dtype)

You can now perform calculations:

print(df["Salary"].mean())

or:

print(df["Salary"].sum())

8. When astype() Is Useful

astype() is excellent when you already know the values are valid for the target type.

For example:

df["Age"] = df["Age"].astype(int)

is appropriate when every value is a valid integer.

But real-world datasets are often messy.

Consider:

df["Age"] = ["25", "30", "Unknown"]

Now:

df["Age"].astype(int)

will fail because "Unknown" cannot be converted into an integer.

This is where pd.to_numeric() becomes especially useful.

9. pd.to_numeric()

Pandas provides:

pd.to_numeric()

for converting values into numeric types.

Example:

df["Age"] = pd.to_numeric(df["Age"])

For clean numeric strings, this works well.

But you can also control what happens when invalid values are encountered.

10. Handling Invalid Numeric Values

Consider:

df = pd.DataFrame({
    "Age": ["25", "30", "Unknown", "28"]
})

Trying:

df["Age"] = pd.to_numeric(df["Age"])

will raise an error because "Unknown" is not numeric.

Instead, use:

df["Age"] = pd.to_numeric(df["Age"], errors="coerce")

Now the invalid value becomes missing:

25
30
NaN
28

This is extremely useful during data cleaning.

You can then inspect the missing values:

print(df["Age"].isnull())

or:

print(df["Age"].isnull().sum())

You can later decide whether to:

fill the missing value
remove the row
investigate the original source
use another cleaning strategy

11. errors="coerce" vs errors="raise"

By default:

pd.to_numeric(series)

raises an error when a value cannot be converted.

You can explicitly use:

errors="raise"

to preserve that behavior.

Or:

errors="coerce"

to convert invalid values into missing values.

There is also:

errors="ignore"

but for modern data-cleaning workflows, it is generally better to explicitly handle conversion and validation rather than silently leaving problematic values unchanged.

A good pattern is:

df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

Then inspect the resulting missing values.

12. Cleaning Numeric Strings

Real datasets can contain values such as:

"50000"
"$62000"
"58,000"
"Unknown"

Only some of these can be directly converted.

For example:

df["Salary"] = df["Salary"].str.replace(",", "", regex=False)

Then:

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

For currency symbols, you may need additional cleaning:

df["Salary"] = (
    df["Salary"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

The general workflow is:

Raw text
   ↓
Clean formatting
   ↓
Convert to numeric
   ↓
Handle invalid values
   ↓
Validate

13. Converting Strings to Datetime

Dates are another common datatype problem.

Suppose you have:

df = pd.DataFrame({
    "JoinDate": [
        "2026-01-10",
        "2026-02-15",
        "2026-03-20"
    ]
})

Initially:

print(df["JoinDate"].dtype)

may show:

object

These values look like dates, but Pandas is currently treating them as text.

Use:

df["JoinDate"] = pd.to_datetime(df["JoinDate"])

Now:

print(df["JoinDate"].dtype)

You should see a datetime dtype such as:

datetime64[ns]

14. Why pd.to_datetime() Is Important

Once a column is properly converted to datetime, you can perform date-based operations much more naturally.

For example:

df["Year"] = df["JoinDate"].dt.year

Month:

df["Month"] = df["JoinDate"].dt.month

Day:

df["Day"] = df["JoinDate"].dt.day

Day name:

df["DayName"] = df["JoinDate"].dt.day_name()

You can also filter dates:

df[df["JoinDate"] >= "2026-02-01"]

This becomes extremely useful in time-based data analysis.

15. Handling Invalid Dates

Real-world date columns can contain invalid values:

df = pd.DataFrame({
    "JoinDate": [
        "2026-01-10",
        "2026-02-15",
        "Not Available",
        "2026-03-20"
    ]
})

Using:

df["JoinDate"] = pd.to_datetime(
    df["JoinDate"],
    errors="coerce"
)

will convert the invalid date into a missing datetime value.

You can then inspect it:

print(df["JoinDate"].isnull())

or:

print(df["JoinDate"].isnull().sum())

This is similar to numeric conversion with pd.to_numeric().

16. astype() vs pd.to_numeric() vs pd.to_datetime()

These functions solve related but different problems.

astype()

Use it when you know the data is compatible with the target dtype.

df["Age"] = df["Age"].astype(int)

pd.to_numeric()

Use it when converting values into numeric data, especially when the source data may contain invalid values.

df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

pd.to_datetime()

Use it when converting values into dates or timestamps.

df["JoinDate"] = pd.to_datetime(
    df["JoinDate"],
    errors="coerce"
)

A simple mental model is:

Known compatible type
        ↓
    astype()

Possibly messy numeric data
        ↓
  to_numeric()

Date/time data
        ↓
 to_datetime()

17. Numeric Conversion: A Practical Example

Consider this dataset:

df = pd.DataFrame({
    "Employee": ["Alice", "Bob", "Charlie", "David"],
    "Age": ["25", "30", "Unknown", "35"],
    "Salary": ["50000", "62000", "58000", "N/A"]
})

print(df)

First inspect:

print(df.dtypes)

Convert Age:

df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

Convert Salary:

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

Now inspect:

print(df.dtypes)

The numeric columns can now participate in calculations.

For example:

print(df["Age"].mean())
print(df["Salary"].mean())

The invalid values have become missing values instead of stopping the conversion process.

18. Datatype Problems You Will See in Real Projects

Real-world datasets frequently contain datatype problems.

Problem 1: Numbers stored as strings

Example:

"100"
"200"
"300"

Solution:

df["Amount"] = pd.to_numeric(
    df["Amount"],
    errors="coerce"
)

Problem 2: Dates stored as strings

Example:

"2026-01-10"
"2026-02-15"

Solution:

df["Date"] = pd.to_datetime(
    df["Date"],
    errors="coerce"
)

Problem 3: Invalid numeric values

Example:

"50000"
"Unknown"
"N/A"
"62000"

Solution:

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

Then investigate the resulting missing values.

Problem 4: Currency formatting

Example:

"$50,000"
"$62,000"
"$58,000"

Clean the formatting first:

df["Salary"] = (
    df["Salary"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

Then convert:

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

Problem 5: Mixed data in one column

Example:

25
30
"Unknown"
35

A column containing mixed representations may be inferred as a general object/string-like dtype rather than a clean numeric dtype.

This can make analysis more difficult.

The solution is not simply to force a type.

First understand the invalid values, then clean and convert them appropriately.

19. Why Correct Dtypes Improve Data Analysis

Correct data types make many operations safer and more predictable.

For numeric columns:

df["Salary"].mean()

For sorting:

df.sort_values("Salary")

For grouping:

df.groupby("Department")["Salary"].mean()

For dates:

df["JoinDate"].dt.year

For date filtering:

df[df["JoinDate"] >= "2026-01-01"]

Correct dtypes also help you detect data-quality problems earlier.

If a salary column is stored as text, that is often a signal that the raw dataset needs cleaning.

20. A Subtle Problem: Sorting Strings vs Numbers

This is an important example.

Suppose:

df = pd.DataFrame({
    "Score": ["100", "20", "5", "80"]
})

If Score remains a string column, sorting may behave lexicographically:

df.sort_values("Score")

The result can be surprising because string ordering is different from numeric ordering.

Convert it first:

df["Score"] = pd.to_numeric(df["Score"])

Then:

df.sort_values("Score")

Now the values are ordered numerically.

This is one reason datatype correctness matters even when the values visually look fine.

21. Datatype Problems Can Affect Filtering

Consider:

df = pd.DataFrame({
    "Age": ["18", "25", "30", "40"]
})

If you want:

df[df["Age"] > 20]

the comparison may not work as intended because the values are strings.

Convert first:

df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

Now:

df[df["Age"] > 20]

works as a numeric comparison.

22. Datatype Problems Can Affect Date Analysis

Suppose:

df["Date"] = [
    "2026-01-10",
    "2026-03-15",
    "2026-02-20"
]

If these values remain strings, date-related operations are more limited.

Convert them:

df["Date"] = pd.to_datetime(
    df["Date"],
    errors="coerce"
)

Now you can use:

df["Date"].dt.year

df["Date"].dt.month

df["Date"].dt.day

and:

df.sort_values("Date")

This creates a much stronger foundation for time-series analysis.

23. A Complete Datatype Cleaning Workflow

Let’s combine everything.

import pandas as pd

df = pd.DataFrame({
    "Name": ["Alice", "Bob", "Charlie", "David"],
    "Age": ["25", "30", "Unknown", "35"],
    "Salary": ["$50,000", "$62,000", "N/A", "$58,000"],
    "JoinDate": [
        "2026-01-10",
        "2026-02-15",
        "Invalid Date",
        "2026-03-20"
    ]
})

Step 1 — Inspect

print(df.dtypes)

Step 2 — Clean salary formatting

df["Salary"] = (
    df["Salary"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

Step 3 — Convert numeric columns

df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

Step 4 — Convert dates

df["JoinDate"] = pd.to_datetime(
    df["JoinDate"],
    errors="coerce"
)

Step 5 — Inspect again

print(df.dtypes)

Step 6 — Check conversion failures

print(df.isnull().sum())

This final step is important.

Conversion is not the end of cleaning.

You must inspect what became missing during conversion.

24. Validate After Conversion

A common mistake is to convert data and immediately continue with analysis.

Instead, validate.

For numeric columns:

print(df["Age"].dtype)
print(df["Salary"].dtype)

For dates:

print(df["JoinDate"].dtype)

Check missing values:

print(df.isnull().sum())

Check the resulting values:

print(df.head())

A useful workflow is:

Inspect
   ↓
Identify datatype problems
   ↓
Clean formatting
   ↓
Convert
   ↓
Check conversion failures
   ↓
Validate dtype
   ↓
Continue analysis

25. Choosing the Right Conversion Strategy

There is no single conversion method that should always be used.

Think about the data first.

If the values are clean and compatible:

astype()

can be simple and direct.

If numeric values may contain invalid entries:

pd.to_numeric(
    errors="coerce"
)

is often safer for a cleaning workflow.

If values represent dates:

pd.to_datetime(
    errors="coerce"
)

is the appropriate tool.

The key is not memorizing syntax.

The key is understanding the data you are working with.

26. Common Mistakes

Mistake 1 — Assuming numeric-looking values are numeric

"100"
"200"
"300"

These are still strings.

Inspect:

df.dtypes

before assuming.

Mistake 2 — Using astype(int) on messy data

This can fail:

df["Age"].astype(int)

if values contain:

"Unknown"
"N/A"
""

Use a cleaning-aware conversion approach when necessary.

Mistake 3 — Treating dates as ordinary strings

If a column represents dates, convert it:

pd.to_datetime()

This enables proper date operations.

Mistake 4 — Using errors="coerce" without checking the result

This:

pd.to_numeric(
    df["Age"],
    errors="coerce"
)

is useful, but invalid values become missing.

Always inspect:

df["Age"].isnull().sum()

Otherwise, you may silently lose information.

Mistake 5 — Converting without understanding the data

A datatype conversion is not automatically a data-cleaning solution.

Before converting, ask:

What does this column represent?
Which values are valid?
Which values are invalid?
What should invalid values become?
Will missing values require further treatment?

27. Data Types and Data Cleaning Are Connected

Datatype conversion is often part of a larger cleaning process.

For example:

Raw Dataset
     ↓
Inspect
     ↓
Detect missing values
     ↓
Standardize values
     ↓
Clean formatting
     ↓
Convert data types
     ↓
Validate
     ↓
Analyze

This is why datatype handling should not be treated as an isolated technical operation.

It is part of preparing a dataset for reliable analysis.

28. A Practical Reusable Template

Here is a simple pattern you can adapt:

# Inspect
print(df.dtypes)
print(df.info())

# Numeric conversion
df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

# Datetime conversion
df["Date"] = pd.to_datetime(
    df["Date"],
    errors="coerce"
)

# Validate
print(df.dtypes)
print(df.isnull().sum())

If formatting must be cleaned first:

df["Salary"] = (
    df["Salary"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

29. The Bigger Picture

Data types are one of the foundations of Pandas data analysis.

A typical dataset may contain:

Text
Numeric values
Dates
Boolean values
Categories
Missing values

Pandas needs to understand what these values represent so that operations behave appropriately.

When data types are wrong, problems can appear in:

calculations
filtering
sorting
grouping
date analysis
feature engineering
visualization
machine learning preparation

When data types are correct, your analysis becomes more predictable and easier to validate.

30. Quick Reference

Inspect types:

df.dtypes

Inspect DataFrame structure:

df.info()

Convert known-compatible values:

df["Age"] = df["Age"].astype(int)

Convert numeric data:

df["Age"] = pd.to_numeric(
    df["Age"],
    errors="coerce"
)

Convert datetime data:

df["Date"] = pd.to_datetime(
    df["Date"],
    errors="coerce"
)

Check missing values after conversion:

df.isnull().sum()

Extract date components:

df["Date"].dt.year
df["Date"].dt.month
df["Date"].dt.day

31. Final Takeaways

Data types are not just metadata attached to a DataFrame.

They influence how Pandas interprets and processes your data.

The most important concepts from this article are:

Use df.dtypes and df.info() to inspect data types.
Use astype() when values are already compatible with the target type.
Use pd.to_numeric() for numeric conversion, especially when invalid values may exist.
Use pd.to_datetime() for date and time conversion.
Use errors="coerce" when you want invalid values converted into missing values for later inspection.
Clean formatting such as currency symbols and commas before numeric conversion.
Always validate the result after conversion.
Never assume that values that look numeric or date-like are already stored correctly.
Correct data types make filtering, sorting, calculations, grouping and time-based analysis more reliable.

A useful data-cleaning mindset is:

Inspect first. Clean deliberately. Convert carefully. Validate afterward.

What Comes Next?

We have now covered:

missing values
filling missing values
replacing incorrect values
duplicate data
data types and datatype conversion

The next step is to work with text data, where Pandas provides powerful string methods for cleaning, standardizing and extracting information from text columns.

That brings us to:

Post 13 — Pandas String Operations: lower(), upper(), contains(), split() and replace()

Series Navigation

Previous:
Post 11 — Pandas Duplicate Data: duplicated() and drop_duplicates() Explained

Next:
Post 13 — Pandas String Operations: lower(), upper(), contains(), split() and replace()

Complete Series:
Data Analysis with Pandas — Beginner to Advanced

Author

Engr. Md. Monjur Bakth Mazumder

GitHub: Python-Data-Science-Tools

Final Thought

Good data analysis does not begin with a chart or a machine learning model.

It begins with understanding the data.

And understanding the data includes knowing what type each value actually is.

Once your DataFrame has appropriate data types, many downstream operations become simpler, safer and more meaningful.

Inspect → Clean → Convert → Validate → Analyze

That is the foundation of reliable Pandas data analysis.

Connect & Follow

Professional Profiles

Portfolio: https://monjurmbm404.github.io
GitHub: https://github.com/monjurmbm404
LinkedIn: https://www.linkedin.com/in/monjurmbm404
Medium: https://medium.com/@monjurmbm404

Books & Publications

Amazon: https://www.amazon.com/author/monjurmbm404

Competitive Programming

Codeforces: https://codeforces.com/profile/monjurmbm404
CodeChef: https://www.codechef.com/users/monjurmbm404
LeetCode: https://leetcode.com/u/monjurmbm404/
VJudge: https://vjudge.net/user/monjurmbm404

Social Media

YouTube: https://youtube.com/@monjurmbm404
X: https://x.com/monjurmbm404
Facebook: https://www.facebook.com/monjurmbm404

Connect with Me

Email: monjurmbm404@gmail.com

— Engr. Md Monjur Bakth Mazumder
Software Engineer | Technical Author | Competitive Programmer | AI/ML Enthusiast