Pandas (Condensed Reference)

1. Introduction

Module 1, Topic 6 covered Pandas fundamentals — Series, DataFrames, loading data, filtering, and basic column operations. This condensed reference topic covers a set of genuinely NEW, powerful Pandas tools that come up constantly in real ML workflows but weren't part of that introductory lesson: groupby(), merging datasets, pivot_table(), and applying custom functions with apply().

2. What New Capabilities Does This Topic Add?

Simple definition: Beyond basic DataFrame operations, Pandas offers powerful tools for summarizing data by group, combining multiple datasets, reshaping data into pivot tables, and applying custom logic to columns — all common tasks during real-world data exploration and feature engineering.

Technical explanation: groupby() implements the "split-apply-combine" pattern (splitting data into groups, applying an aggregation, and combining results); merge()/join() combine multiple DataFrames based on shared key columns (similar to SQL joins); pivot_table() reshapes data into a cross-tabulated summary; and apply() lets you run any custom Python function across rows or columns.

3. Why is it Important?

• groupby() is one of the most frequently used Pandas operations in real-world data exploration and feature engineering.
• Real datasets often come from multiple sources/tables that need to be merged together before modeling.
• apply() provides the flexibility to implement custom feature engineering logic (Module 7, Topic 4) that built-in functions don't directly support.

4. Prerequisites

Full comfort with Pandas Basics (Module 1, Topic 6) is assumed.

5. Core Concepts

1. groupby() — split-apply-combine aggregation
2. merge() — combining DataFrames (like SQL joins)
3. pivot_table() — reshaping data into summarized cross-tabs
4. apply() — running custom functions across data

6. Detailed Explanation

a) `groupby()`

groupby() splits a DataFrame into groups based on a column's values (e.g., grouping by "city"), applies an aggregation function (like .mean(), .sum(), .count()) to each group separately, then combines the results into a summary — extremely useful for exploring how a metric varies across categories.

b) `merge()`

merge() combines two DataFrames based on a shared key column, similar to a SQL JOIN. Different how parameters ("inner", "left", "right", "outer") control exactly which rows are kept when keys don't perfectly match between the two DataFrames.

c) `pivot_table()`

pivot_table() reshapes data by turning unique values from one column into new columns, summarizing another column's values at each combination — useful for creating cross-tabulated summaries (e.g., average sales by region AND month, simultaneously).

d) `apply()`

apply() runs a custom Python function (often a lambda) across every row or column of a DataFrame/Series, enabling logic that built-in Pandas functions don't directly provide — a common tool during Feature Engineering (Module 7, Topic 4).

7. How It Works

1. `groupby()`: df.groupby("column").agg_function() — groups rows sharing the same value in "column", then applies the aggregation to each group.
2. `merge()`: pd.merge(df1, df2, on="key_column", how="inner") — combines rows from both DataFrames where the key column values match.
3. `pivot_table()`: df.pivot_table(values="...", index="...", columns="...", aggfunc="mean") — reshapes and summarizes data across two categorical dimensions.
4. `apply()`: df["column"].apply(lambda x: ...) — applies custom logic to every value in a column.

8. Real-World Example

A retail company has one DataFrame of orders and another of customer details (stored separately, sharing a customer_id column). Before analysis, these need to be merge()d into a single combined DataFrame. Then, groupby("region") could reveal average order value per region, and a pivot_table could show average order value broken down by BOTH region AND month simultaneously — insights that would be much harder to extract from either raw table alone.

9. Python Example

python
import pandas as pd

orders = pd.DataFrame({
    "customer_id": [1, 2, 1, 3, 2],
    "region": ["North", "South", "North", "East", "South"],
    "amount": [100, 150, 200, 80, 120]
})

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Aarav", "Meera", "Kabir"]
})

# Merging two DataFrames on customer_id
merged = pd.merge(orders, customers, on="customer_id", how="inner")
print("Merged data:")
print(merged)

# groupby - average order amount per region
region_avg = orders.groupby("region")["amount"].mean()
print("\nAverage amount per region:")
print(region_avg)

# apply - flagging large orders using a custom function
orders["is_large_order"] = orders["amount"].apply(lambda x: "Yes" if x > 100 else "No")
print("\nWith large-order flag:")
print(orders)

Expected Output:

text
Merged data:
   customer_id region  amount   name
0            1  North     100  Aarav
1            2  South     150  Meera
2            1  North     200  Aarav
3            3   East      80  Kabir
4            2  South     120  Meera

Average amount per region:
region
East     80.0
North    150.0
South    135.0
Name: amount, dtype: float64

With large-order flag:
   customer_id region  amount is_large_order
0            1  North     100             No
1            2  South     150            Yes
2            1  North     200            Yes
3            3   East      80             No
4            2  South     120            Yes

10. Code Explanation

• pd.merge(orders, customers, on="customer_id", how="inner") combines both tables, adding customer names directly to the orders data by matching on customer_id.
• orders.groupby("region")["amount"].mean() splits orders by region, then calculates the average amount within each group.
• orders["amount"].apply(lambda x: "Yes" if x > 100 else "No") runs a custom rule against every value in the "amount" column, creating a new categorical flag — a simple but common Feature Engineering pattern.

11. Advantages

• groupby() provides fast, flexible aggregation across categories, essential for exploratory data analysis.
• merge() enables combining data from multiple real-world sources, a near-universal requirement in practice.
• apply() provides unlimited flexibility for custom logic beyond built-in functions.

12. Limitations

• apply() can be significantly slower than built-in vectorized Pandas/NumPy operations, especially on large datasets — it should be used when no faster built-in alternative exists.
• Merging with mismatched or duplicate keys can unexpectedly inflate row counts if not carefully checked.
• pivot_table() can become difficult to read with too many index/column dimensions at once.

13. Common Mistakes

• Using apply() with a custom Python function when a faster, built-in vectorized alternative exists.
• Not specifying the correct how parameter in merge(), leading to unexpectedly dropped or duplicated rows.
• Forgetting that groupby() returns a special grouped object that requires an aggregation function (like .mean()) before it produces a usable result.

14. Best Practices

• Prefer built-in vectorized operations over apply() when possible, reserving apply() for genuinely custom logic.
• Always verify row counts after a merge() to check for unexpected duplication or data loss.
• Use groupby() combined with .agg() when you need MULTIPLE different aggregations at once (e.g., both mean and count).

15. Real-World Applications

• Combining customer, order, and product tables from a company's database before analysis.
• Summarizing sales, survey, or sensor data by category (region, time period, product type) using groupby().
• Creating custom engineered features (Module 7, Topic 4) using apply().

16. Interview-Oriented Points

• Be ready to explain the "split-apply-combine" pattern behind groupby().
• Understand the different how options in merge() (inner, left, right, outer) and what each keeps.
• Be able to explain why apply() is more flexible but often slower than built-in vectorized operations.

17. Exam-Oriented Points

• groupby() splits data by category, applies an aggregation, and combines results.
• merge() combines DataFrames based on shared key columns, similar to SQL joins.
• pivot_table() reshapes data into a cross-tabulated summary.
• apply() runs custom functions across rows/columns.

18. Comparison Table — merge() how Parameter Options

19. Quick Revision

• groupby() implements split-apply-combine: group by a category, apply an aggregation, combine results.
• merge() combines DataFrames on shared key columns, with how controlling which rows are kept.
• pivot_table() reshapes data into a cross-tabulated summary across two categorical dimensions.
• apply() runs custom functions across data, offering flexibility beyond built-in operations (at some performance cost).