3.5 Transformation & Aggregation — GroupBy, Pivot Tables, Merge, Join & Concatenation

3.5 Data Transformation & Aggregation with Pandas

After cleaning a dataset, the next step is often to transform, summarize, and combine data.

In real-world Data Science projects, information is rarely stored in one perfectly organized table. You may need to calculate department-wise averages, summarize sales by region, combine customer and transaction data, or reshape a dataset for reporting.

1. Sample Dataset

We will use a student-performance dataset throughout this lesson.

import pandas as pd

data = {
    "Student": [
        "Alice",
        "Brian",
        "Clara",
        "David",
        "Eva",
        "Frank"
    ],

    "Department": [
        "Science",
        "Commerce",
        "Science",
        "Commerce",
        "Science",
        "Commerce"
    ],

    "Gender": [
        "Female",
        "Male",
        "Female",
        "Male",
        "Female",
        "Male"
    ],

    "Math": [
        85,
        78,
        92,
        74,
        88,
        81
    ],

    "Science": [
        90,
        72,
        95,
        68,
        91,
        75
    ]
}

df = pd.DataFrame(data)

print(df)

This DataFrame contains categorical columns such as Department and Gender, together with numerical columns such as Math and Science.

2. What Is Data Transformation?

Data transformation means changing data from one form into another form so that it becomes more useful for analysis.

Examples include:

• Creating a new calculated column
• Grouping records by category
• Reshaping data using pivot tables
• Combining multiple DataFrames
• Aggregating thousands of records into summary statistics

Example:

df["Total"] = (
    df["Math"] +
    df["Science"]
)

Here, a new Total column has been created from two existing columns.

3. What Is Data Aggregation?

Aggregation means combining multiple records to produce a summary value.

For example, instead of looking at every student's score individually, you may want to calculate the average score for each department.

4. Introduction to groupby()

The groupby() method divides a DataFrame into groups based on one or more columns.

General syntax:

df.groupby("column")

For example:

df.groupby("Department")

This creates groups based on the values in the Department column.

This is the fundamental idea behind pandas grouping operations.

5. Calculating Group-Wise Averages

To calculate the average score by department:

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

The operation:

1. Groups rows by Department.
2. Selects the Math column.
3. Calculates the mean for each department.

Example result:

Department
Commerce    77.67
Science     88.33
Name: Math, dtype: float64

6. Using sum() with groupby()

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

This calculates the total Mathematics score for each department.

The same pattern can be used for other aggregation functions.

df.groupby("Department")["Math"].min()

df.groupby("Department")["Math"].max()

df.groupby("Department")["Math"].median()

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

7. Grouping by Multiple Columns

You can group data using more than one column.

df.groupby(
    ["Department", "Gender"]
)["Math"].mean()

This calculates the average Mathematics score for each combination of department and gender.

df.groupby(
    ["Column1", "Column2"]
)

8. Grouping Multiple Numerical Columns

You can calculate statistics for multiple numerical columns at once.

df.groupby(
    "Department"
)[
    ["Math", "Science"]
].mean()

This returns the average Mathematics and Science scores for each department.

9. Multiple Aggregations with agg()

The agg() method allows multiple aggregation functions to be applied together.

df.groupby(
    "Department"
)["Math"].agg(
    ["mean", "min", "max"]
)

This produces a summary containing:

• Average score
• Minimum score
• Maximum score

10. Different Aggregations for Different Columns

A dictionary can specify different aggregation functions for different columns.

df.groupby("Department").agg({
    "Math": "mean",
    "Science": "max"
})

Here:

• Math → average
• Science → maximum

11. Named Aggregation

Named aggregation allows you to specify meaningful names for calculated columns.

summary = df.groupby(
    "Department"
).agg(
    average_math=("Math", "mean"),
    highest_science=("Science", "max"),
    student_count=("Student", "count")
)

print(summary)

This produces a clean, report-friendly summary.

12. Using as_index=False

By default, grouping columns often become the index of the resulting DataFrame.

If you want the grouping column to remain an ordinary column, use:

df.groupby(
    "Department",
    as_index=False
)["Math"].mean()

This can make the result easier to work with in later transformations.

13. Using reset_index()

Another common approach is to reset the resulting index.

summary = (
    df.groupby("Department")["Math"]
    .mean()
    .reset_index()
)

Now Department becomes a regular DataFrame column.

14. Introduction to Pivot Tables

A pivot table summarizes data across one or more dimensions.

pandas provides the pivot_table() function for this purpose.

General syntax:

pd.pivot_table(
    data,
    values=...,
    index=...,
    columns=...,
    aggfunc=...
)

15. Creating a Pivot Table

Calculate average scores by department and gender:

pivot = pd.pivot_table(
    df,
    values="Math",
    index="Department",
    columns="Gender",
    aggfunc="mean"
)

print(pivot)

Conceptually, the resulting table places:

• Department on rows
• Gender on columns
• Average Math score in the cells

16. Pivoting Multiple Values

Multiple numerical columns can be included.

pd.pivot_table(
    df,
    values=["Math", "Science"],
    index="Department",
    columns="Gender",
    aggfunc="mean"
)

This produces a more detailed summary.

17. Handling Missing Combinations in Pivot Tables

Some combinations may not exist in the source data. You can specify a replacement value using fill_value.

pd.pivot_table(
    df,
    values="Math",
    index="Department",
    columns="Gender",
    aggfunc="mean",
    fill_value=0
)

18. Counting Records with Pivot Tables

A pivot table can also be used for counting.

pd.pivot_table(
    df,
    values="Student",
    index="Department",
    columns="Gender",
    aggfunc="count",
    fill_value=0
)

This can show how many students belong to each department-gender combination.

19. groupby() vs pivot_table()

20. Combining DataFrames with merge()

The merge() function combines DataFrames using one or more common columns.

It is conceptually similar to a database JOIN.

Consider two DataFrames.

students = pd.DataFrame({
    "StudentID": [101, 102, 103, 104],
    "Name": [
        "Alice",
        "Brian",
        "Clara",
        "David"
    ]
})

scores = pd.DataFrame({
    "StudentID": [101, 102, 103, 104],
    "Score": [85, 78, 92, 88]
})

Both DataFrames contain StudentID.

21. Inner Merge

An inner merge keeps only matching keys in both DataFrames.

result = pd.merge(
    students,
    scores,
    on="StudentID",
    how="inner"
)

print(result)

This combines student names with their scores where StudentID exists in both tables.

22. Left Merge

A left merge keeps every row from the left DataFrame and matches corresponding rows from the right DataFrame where possible.

result = pd.merge(
    students,
    scores,
    on="StudentID",
    how="left"
)

23. Right Merge

A right merge keeps every row from the right DataFrame.

result = pd.merge(
    students,
    scores,
    on="StudentID",
    how="right"
)

Matching information from the left DataFrame is included where available.

24. Outer Merge

An outer merge keeps keys from both DataFrames.

result = pd.merge(
    students,
    scores,
    on="StudentID",
    how="outer"
)

If a matching record does not exist on one side, pandas represents the missing information with a missing value.

25. Types of Merge

26. Merging Columns with Different Names

Sometimes the key has a different name in each DataFrame.

students = pd.DataFrame({
    "StudentID": [101, 102, 103],
    "Name": [
        "Alice",
        "Brian",
        "Clara"
    ]
})

scores = pd.DataFrame({
    "ID": [101, 102, 103],
    "Score": [85, 78, 92]
})

result = pd.merge(
    students,
    scores,
    left_on="StudentID",
    right_on="ID"
)

Use:

• left_on for the key in the left DataFrame
• right_on for the key in the right DataFrame

27. Merging Using Multiple Keys

More than one column can be used as the matching key.

result = pd.merge(
    df1,
    df2,
    on=["StudentID", "Year"],
    how="inner"
)

A row must match according to the specified key combination.

28. Handling Duplicate Column Names

If both DataFrames contain columns with the same name that are not used as the merge key, pandas can add suffixes.

result = pd.merge(
    df1,
    df2,
    on="StudentID",
    suffixes=("_left", "_right")
)

This helps distinguish the two versions of the column.

29. Joining DataFrames with join()

The join() method combines DataFrames primarily based on their indexes.

Example:

left = pd.DataFrame(
    {
        "Name": [
            "Alice",
            "Brian",
            "Clara"
        ]
    },
    index=[101, 102, 103]
)

right = pd.DataFrame(
    {
        "Score": [
            85,
            78,
            92
        ]
    },
    index=[101, 102, 103]
)

result = left.join(right)

print(result)

30. Join Types

Like merging, join() can use different join strategies.

left.join(
    right,
    how="inner"
)

Common options include:

• left
• right
• inner
• outer

31. Joining Using a Column

join() can also be used with a column from the left DataFrame and the index of the right DataFrame.

result = left.join(
    right.set_index("StudentID"),
    on="StudentID"
)

In practice, merge() is often more natural when both sides use ordinary columns as keys.

32. Combining DataFrames with concat()

pd.concat() combines pandas objects along a specified axis.

It is commonly used to:

• Stack DataFrames vertically
• Combine DataFrames horizontally
• Append datasets with compatible structures

33. Concatenating Rows

Suppose two datasets contain records from different periods:

jan = pd.DataFrame({
    "Name": ["Alice", "Brian"],
    "Score": [85, 78]
})

feb = pd.DataFrame({
    "Name": ["Clara", "David"],
    "Score": [92, 88]
})

result = pd.concat(
    [jan, feb],
    axis=0
)

print(result)

axis=0 combines the DataFrames vertically.

34. Using ignore_index=True

When vertically concatenating DataFrames, the original indexes may be retained.

To create a fresh sequential index:

result = pd.concat(
    [jan, feb],
    ignore_index=True
)

35. Concatenating Columns

Set axis=1 to concatenate horizontally.

names = pd.DataFrame({
    "Name": [
        "Alice",
        "Brian",
        "Clara"
    ]
})

scores = pd.DataFrame({
    "Score": [
        85,
        78,
        92
    ]
})

result = pd.concat(
    [names, scores],
    axis=1
)

The DataFrames are aligned according to their indexes.

36. concat() vs merge() vs join()

37. Easy Way to Remember the Three

Example:

• January + February records → concat
• Student table + Score table using StudentID → merge
• DataFrames already indexed by StudentID → join

38. Practical Example — Department Analysis

Suppose an institution wants to determine the average and maximum scores for every department.

summary = df.groupby(
    "Department"
).agg(
    average_math=("Math", "mean"),
    maximum_math=("Math", "max"),
    average_science=("Science", "mean"),
    maximum_science=("Science", "max")
)

print(summary)

This produces a compact analytical report without manually calculating each department.

39. Practical Example — Department × Gender

A pivot table can provide a cross-sectional view:

report = pd.pivot_table(
    df,
    values="Math",
    index="Department",
    columns="Gender",
    aggfunc="mean"
)

print(report)

This makes it easier to compare groups across two dimensions.

40. Practical Example — Combining Student Information

students = pd.DataFrame({
    "StudentID": [101, 102, 103],
    "Name": [
        "Alice",
        "Brian",
        "Clara"
    ]
})

attendance = pd.DataFrame({
    "StudentID": [101, 102, 103],
    "Attendance": [
        95,
        88,
        97
    ]
})

student_report = pd.merge(
    students,
    attendance,
    on="StudentID",
    how="left"
)

print(student_report)

The result combines student identity information with attendance information.

41. Practical Example — Combining Monthly Data

january = pd.DataFrame({
    "Month": ["January", "January"],
    "Sales": [1200, 1500]
})

february = pd.DataFrame({
    "Month": ["February", "February"],
    "Sales": [1400, 1700]
})

sales = pd.concat(
    [january, february],
    ignore_index=True
)

print(sales)

This is appropriate when the DataFrames have the same structure and represent records that should be stacked.

42. Complete Transformation Pipeline

A realistic workflow may combine cleaning, transformation, aggregation, and merging.

import pandas as pd

# Load data
students = pd.read_csv(
    "students.csv"
)

scores = pd.read_csv(
    "scores.csv"
)

# Remove duplicate student records
students = students.drop_duplicates(
    subset=["StudentID"]
)

# Clean names
students["Name"] = (
    students["Name"]
    .str.strip()
    .str.title()
)

# Combine student information with scores
report = pd.merge(
    students,
    scores,
    on="StudentID",
    how="left"
)

# Create total score
report["Total"] = (
    report["Math"] +
    report["Science"]
)

# Department summary
summary = report.groupby(
    "Department",
    as_index=False
).agg(
    average_total=("Total", "mean"),
    highest_total=("Total", "max"),
    student_count=("StudentID", "count")
)

print(summary)

This demonstrates how individual pandas techniques can be combined into a practical analytical pipeline.

43. Grouped Analysis with filter()

In some situations, you may want to retain only groups satisfying a condition.

result = df.groupby(
    "Department"
).filter(
    lambda group: len(group) >= 2
)

This retains groups containing at least two records.

44. Group-Based Transformation with transform()

transform() is useful when you want a group-level calculation returned with the same row structure as the original DataFrame.

df["DepartmentAverage"] = (
    df.groupby("Department")["Math"]
    .transform("mean")
)

Each student receives the average Mathematics score of their own department.

45. Sorting Aggregated Results

After aggregation, you may want to rank groups.

summary = (
    df.groupby(
        "Department",
        as_index=False
    )["Math"]
    .mean()
    .sort_values(
        "Math",
        ascending=False
    )
)

This places the department with the highest average Mathematics score first.

46. Validating Merges

When combining datasets, unexpected duplicate keys or missing matches can create incorrect results.

For important workflows, pandas provides the validate parameter.

result = pd.merge(
    students,
    scores,
    on="StudentID",
    how="left",
    validate="one_to_one"
)

This can help verify that the relationship between the datasets matches the expected structure.

47. Beware of Row Multiplication in Merges

Suppose the same key occurs multiple times in both DataFrames. An ordinary merge can produce multiple combinations of matching records.

Therefore, always inspect:

df["StudentID"].duplicated().sum()

before assuming that StudentID uniquely identifies records.

48. Pandas Transformation Interview Questions

49. Examination Questions

Multiple Choice Questions

50. Practical Examination Questions

result = df.groupby(
    "Department"
)["Math"].mean()

print(result)

result = df.groupby(
    "Department"
)["Science"].agg(
    ["min", "max", "mean"]
)

print(result)

result = pd.pivot_table(
    df,
    values="Math",
    index="Department",
    columns="Gender",
    aggfunc="mean"
)

print(result)

result = pd.merge(
    students,
    scores,
    on="StudentID",
    how="inner"
)

result = pd.concat(
    [january, february],
    ignore_index=True
)

51. Scenario-Based Questions

52. Common Mistakes

53. Which Pandas Method Should You Use?

55. Key Takeaways

• groupby() is fundamental for grouped analysis.
• Grouped analysis follows the Split → Apply → Combine concept.
• agg() allows multiple statistics to be calculated efficiently.
• pivot_table() creates multidimensional summary tables.
• merge() combines DataFrames using matching keys.
• join() is particularly convenient for index-based combinations.
• concat() combines DataFrames along rows or columns.
• as_index=False keeps group labels as normal columns.
• transform() can add group-level calculations back to the original rows.
• Always inspect keys and relationships before merging DataFrames.