Notebook 3: High-Performance Data Manipulation

Module: Data Analytics Level: Intermediate Prerequisites: Notebook 2 (Pandas Fundamentals) Estimated Time: 4 hours

---

Table of Contents#

1. Why Performance Deserves Its Own Notebook
2. Vectorization vs. apply vs. Loops
3. The groupby Mechanic: Split-Apply-Combine
4. Aggregation, Transformation, and Filtration
5. Merging and Joining DataFrames
6. Join Types in Depth
7. Concatenation
8. Method Chaining
9. Benchmarking Your Own Code
10. Hands-On: A Full High-Performance Pipeline
11. Case Studies
12. Common Misconceptions and Pitfalls
13. Glossary
14. Exercises
15. Quiz
16. Summary
17. Further Reading

---

Learning Objectives#

By the end of this notebook, you will be able to:

• Rank vectorized operations, apply, and explicit loops by performance and explain why the gap exists
• Explain the split-apply-combine model underlying groupby and apply it to real aggregation problems
• Distinguish aggregation, transformation, and filtration operations on grouped data
• Choose the correct join type (inner, left, right, outer) for a given merging problem, and diagnose row-count changes after a join
• Concatenate DataFrames correctly along both axes
• Write readable, maintainable method chains instead of many intermediate variable assignments
• Benchmark your own code using timeit to make evidence-based performance decisions

---

1. Why Performance Deserves Its Own Notebook#

Notebook 1 established that vectorized NumPy operations dramatically outperform Python loops (Notebook 1, Section 10), and Notebook 2's case study (Section 11.3) showed that even how you load data affects performance. This notebook extends that theme specifically to the operations that dominate real analytics work at scale: applying custom logic to a column, grouping and aggregating data, and combining multiple datasets together. Each of these has a fast, idiomatic way and one or more slow, tempting-but-inefficient ways, and knowing the difference is often what separates an analysis that runs in seconds from one that runs for hours.

---

2. Vectorization vs. apply vs. Loops#

2.1 The Three Approaches Ranked#

For nearly any row-wise or element-wise transformation, there are typically three ways to implement it, in order from slowest to fastest:

1. Explicit Python loop (e.g., for index, row in df.iterrows()) — slowest, since it pays the full per-element Python interpreter overhead described in Notebook 1, Section 10.2, and additionally reconstructs a Series object for every single row.
2. .apply() — faster than a raw loop, but still calls a Python function once per row (or column) internally, so it does not achieve true vectorized performance.
3. Vectorized operations (built-in Pandas/NumPy operators and functions) — fastest, since the entire operation is pushed into compiled, array-level code.

2.2 Demonstrating the Gap#

Python
CopyAsk AI
import pandas as pd
import numpy as np
import time

n = 200_000
df = pd.DataFrame({
    "price": np.random.uniform(10, 500, n),
    "quantity": np.random.randint(1, 20, n)
})

# Approach 1: iterrows (slowest)
start = time.perf_counter()
totals = []
for _, row in df.iterrows():
    totals.append(row["price"] * row["quantity"])
time_iterrows = time.perf_counter() - start

# Approach 2: apply
start = time.perf_counter()
totals_apply = df.apply(lambda row: row["price"] * row["quantity"], axis=1)
time_apply = time.perf_counter() - start

# Approach 3: vectorized
start = time.perf_counter()
totals_vectorized = df["price"] * df["quantity"]
time_vectorized = time.perf_counter() - start

print(f"iterrows: {time_iterrows:.3f}s")
print(f"apply:    {time_apply:.3f}s")
print(f"vectorized: {time_vectorized:.4f}s")
print(f"Vectorized speedup over iterrows: {time_iterrows / time_vectorized:.0f}x")

Expected output (approximate, will vary by hardware):

code
Ask AICopy
iterrows: 4.812s
apply:    0.891s
vectorized: 0.0021s
Vectorized speedup over iterrows: 2291x

2.3 When apply Is Still Reasonable#

.apply() remains useful, and sometimes necessary, when the per-row logic genuinely cannot be expressed with vectorized operations, for example, calling an external library function that only accepts scalar inputs one at a time, or implementing complex conditional branching that would require deeply nested np.where calls to vectorize. Even then, it is worth checking whether NumPy's np.select (for multi-condition branching) or a vectorized string/datetime method (Section 2.4) can replace it before reaching for .apply().

2.4 Vectorized String and Datetime Operations#

A common trap is looping or applying to perform string or date operations that Pandas already vectorizes natively via the .str and .dt accessors:

Python
CopyAsk AI
# Slow: applying a Python string method row by row
df["name_upper"] = df["name"].apply(lambda x: x.upper())

# Fast: vectorized string accessor
df["name_upper"] = df["name"].str.upper()

# Slow: applying a lambda to extract the year
df["year"] = df["order_date"].apply(lambda x: x.year)

# Fast: vectorized datetime accessor
df["year"] = df["order_date"].dt.year

Both pairs compute identical results, but the .str and .dt accessor versions operate on the entire column at once using compiled code, following exactly the same principle as np.sin() or np.sqrt() in Notebook 1, Section 5.2.

---

3. The groupby Mechanic: Split-Apply-Combine#

3.1 The Conceptual Model#

groupby operations follow a three-stage model called split-apply-combine:

1. Split: the data is divided into groups based on some key (e.g., every row with the same region value forms one group)
2. Apply: a function is applied independently to each group (e.g., compute the mean revenue within that group)
3. Combine: the per-group results are combined back into a single result structure

Architecture & Data Flow
Copy
        Split                  Apply                Combine
   ---------------      -----------------      ------------------
   region=North  ---->  mean(revenue)=700  ---->
   region=South  ---->  mean(revenue)=705  ----> region-indexed
   region=East   ---->  mean(revenue)=950  ----> result Series/DataFrame
   region=West   ---->  mean(revenue)=660  ---->

3.2 Basic Syntax#

Python
CopyAsk AI
import pandas as pd

df = pd.DataFrame({
    "region": ["North", "South", "East", "West", "North", "South", "East", "West"],
    "revenue": [125000, 98000, 143000, 110000, 118000, 76000, 151000, 108000]
})

grouped = df.groupby("region")
print(grouped["revenue"].mean())

Expected output:

code
Ask AICopy
region
East     147000.0
North    121500.0
South     87000.0
West     109000.0
Name: revenue, dtype: float64

Critically, calling .groupby("region") alone does not immediately compute anything; it returns a DataFrameGroupBy object that lazily represents the "split" stage, deferring actual computation until an aggregation or other method (like .mean()) is called, which triggers the "apply" and "combine" stages together.

3.3 Grouping by Multiple Keys#

Python
CopyAsk AI
df["quarter"] = ["Q1", "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q2"]
multi_grouped = df.groupby(["region", "quarter"])["revenue"].sum()
print(multi_grouped)

Expected output:

code
Ask AICopy
region  quarter
East    Q1         143000
        Q2         151000
North   Q1         125000
        Q2         118000
South   Q1          98000
        Q2          76000
West    Q1         110000
        Q2         108000
Name: revenue, dtype: int64

This produces a result with a MultiIndex (a hierarchical index with multiple levels), which can be reshaped into a more report-friendly wide format using .unstack(), covered in Notebook 5 on reshaping.

---

4. Aggregation, Transformation, and Filtration#

Grouped operations fall into three distinct categories, distinguished by the shape of their output relative to the input.

4.1 Aggregation: Many Rows to One Row Per Group#

Aggregation reduces each group to a single summary value, exactly as shown in Section 3.2 and 3.3.

Python
CopyAsk AI
summary = df.groupby("region")["revenue"].agg(["mean", "sum", "count", "std"])
print(summary)

Multiple aggregation functions can be applied to multiple columns simultaneously using a dictionary:

Python
CopyAsk AI
summary_multi = df.groupby("region").agg(
    avg_revenue=("revenue", "mean"),
    total_revenue=("revenue", "sum"),
    num_records=("revenue", "count")
)
print(summary_multi)

This "named aggregation" syntax (using keyword arguments of the form new_column_name=("source_column", "function")) produces clean, immediately readable output column names, which is generally preferable to the older dictionary-based aggregation syntax for anything beyond a single quick check.

4.2 Transformation: Same Number of Rows, Group-Level Result Broadcast Back#

.transform() returns a result with the same shape as the original data, with each group's summary value broadcast back to every row belonging to that group — directly analogous to the broadcasting concept from Notebook 1, Section 6, but applied at the group level rather than the whole-array level.

Python
CopyAsk AI
df["region_avg_revenue"] = df.groupby("region")["revenue"].transform("mean")
print(df[["region", "revenue", "region_avg_revenue"]])

Expected output:

code
Ask AICopy
  region  revenue  region_avg_revenue
0  North   125000            121500.0
1  South    98000             87000.0
2   East   143000            147000.0
3   West   110000            109000.0
4  North   118000            121500.0
5  South    76000             87000.0
6   East   151000            147000.0
7   West   108000            109000.0

This pattern is exactly what powers the row-centering operation previewed in Notebook 1, Section 6.4 and Notebook 1's case study in Section 12.3, but expressed properly with groupby and transform instead of a manual loop over groups: subtracting region_avg_revenue from revenue centers each row by its own group's mean in a single vectorized step.

4.3 Filtration: Keeping or Dropping Entire Groups#

.filter() keeps or drops entire groups based on a group-level condition, unlike row-level boolean filtering (Notebook 2, Section 6), which operates on individual rows regardless of group membership.

Python
CopyAsk AI
# Keep only regions where total revenue exceeds 200,000
high_performing_regions = df.groupby("region").filter(lambda g: g["revenue"].sum() > 200000)
print(high_performing_regions)

This retains every row belonging to a qualifying group (evaluated using the entire group's data), rather than filtering individual rows independently.

---

5. Merging and Joining DataFrames#

5.1 Why Merging Is Necessary#

Real analytics work almost always involves combining data from multiple sources: a transactions table, a customers table, a products table, each potentially maintained separately. Merging combines DataFrames based on shared key columns, analogous to a SQL join.

5.2 Basic Merge Syntax#

Python
CopyAsk AI
customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Carol", "David"]
})

orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105],
    "customer_id": [1, 2, 2, 3, 5],
    "amount": [50, 75, 120, 200, 60]
})

merged = pd.merge(customers, orders, on="customer_id", how="inner")
print(merged)

Expected output:

code
Ask AICopy
   customer_id   name  order_id  amount
0            1  Alice       101      50
1            2    Bob       102      75
2            2    Bob       103     120
3            3  Carol       104     200

Notice that customer 4 (David, who has no orders) and order 105 (belonging to customer 5, who does not exist in the customers table) are both dropped from this result, a direct consequence of the how="inner" join type, explained fully in Section 6.

---

6. Join Types in Depth#

6.1 The Four Standard Join Types#

6.2 Visualizing the Difference#

Python
CopyAsk AI
inner = pd.merge(customers, orders, on="customer_id", how="inner")
left = pd.merge(customers, orders, on="customer_id", how="left")
outer = pd.merge(customers, orders, on="customer_id", how="outer")

print(f"Inner join rows: {len(inner)}")   # 4 - only matched pairs
print(f"Left join rows: {len(left)}")     # 5 - all customers, David gets NaN order info
print(f"Outer join rows: {len(outer)}")   # 6 - all customers AND all orders, unmatched get NaN

Expected output:

code
Ask AICopy
Inner join rows: 4
Left join rows: 5
Outer join rows: 6

6.3 The Row-Count Sanity Check#

A critical, frequently-skipped practice: always check the row count before and after a merge. An unexpected row count after a merge is one of the most common and consequential silent errors in analytics work, usually signaling one of two problems, covered in the case study in Section 11.1: an unintended many-to-many relationship causing row duplication, or an unintended inner join silently dropping records that should have been retained.

Python
CopyAsk AI
print(f"Customers before merge: {len(customers)}")
print(f"Orders before merge: {len(orders)}")
result = pd.merge(customers, orders, on="customer_id", how="left")
print(f"Result after left merge: {len(result)}")
# Expect this to be >= len(customers), since a left join keeps every left row,
# possibly duplicated if a customer has multiple matching orders

6.4 Merging on Differently Named Columns#

Python
CopyAsk AI
# If the key columns have different names in each DataFrame
merged = pd.merge(customers, orders, left_on="customer_id", right_on="cust_id", how="inner")

6.5 Validating Merge Cardinality#

Pandas provides a validate parameter that raises an explicit error if the merge does not match the expected relationship type, which is a strong defensive practice for production analytics code:

Python
CopyAsk AI
# Raises an error if customer_id is not unique in `customers`,
# catching an unintended one-to-many or many-to-many situation early
merged = pd.merge(customers, orders, on="customer_id", how="left", validate="one_to_many")

---

7. Concatenation#

7.1 Stacking DataFrames Vertically#

pd.concat() combines DataFrames along an axis, most commonly stacking rows from multiple sources sharing the same columns (e.g., combining monthly files into one dataset):

Python
CopyAsk AI
jan_data = pd.DataFrame({"date": ["2024-01-15"], "revenue": [50000]})
feb_data = pd.DataFrame({"date": ["2024-02-15"], "revenue": [62000]})

combined = pd.concat([jan_data, feb_data], ignore_index=True)
print(combined)

The ignore_index=True argument resets the index of the combined result to a fresh RangeIndex, avoiding duplicate index labels that would otherwise result from each source DataFrame retaining its own original index (echoing the alignment concerns from Notebook 2, Section 4.3).

7.2 Concatenating Along Columns#

Python
CopyAsk AI
demographics = pd.DataFrame({"age": [25, 32], "city": ["NY", "LA"]})
purchases = pd.DataFrame({"total_spend": [450, 1200]})

combined_cols = pd.concat([demographics, purchases], axis=1)
print(combined_cols)

axis=1 concatenation aligns purely by index (Notebook 2, Section 4.3) rather than by any explicit key, which makes it appropriate only when you are certain both DataFrames share the same row order and meaning; for anything else, an explicit merge on a key column (Section 5) is the safer choice.

7.3 concat vs. merge#

---

8. Method Chaining#

8.1 The Problem With Many Intermediate Variables#

A common but hard-to-follow style creates a new named variable at every step:

Python
CopyAsk AI
df1 = pd.read_csv("sales.csv")
df2 = df1[df1["revenue"] > 0]
df3 = df2.assign(revenue_per_unit=df2["revenue"] / df2["units"])
df4 = df3.groupby("region")["revenue_per_unit"].mean()
df5 = df4.sort_values(ascending=False)
print(df5)

Each intermediate variable (df1 through df5) exists only to be passed to the next step, cluttering the namespace and making the actual data flow harder to follow at a glance.

8.2 The Method Chaining Alternative#

Because most Pandas methods return a new DataFrame or Series (rather than modifying in place), calls can be chained directly:

Python
CopyAsk AI
result = (
    pd.read_csv("sales.csv")
    .query("revenue > 0")
    .assign(revenue_per_unit=lambda d: d["revenue"] / d["units"])
    .groupby("region")["revenue_per_unit"]
    .mean()
    .sort_values(ascending=False)
)
print(result)

This produces an identical result to Section 8.1, but reads as a single, linear pipeline of transformations, with each line's purpose immediately visible, and no intermediate variables to name or accidentally reuse incorrectly.

8.3 The Role of assign and lambda#

.assign() creates new columns without mutating the original DataFrame, returning a new DataFrame instead, which makes it naturally chainable, unlike direct df["new_col"] = ... assignment (Notebook 2, Section 7.1), which requires a DataFrame to already exist as a named variable. The lambda d: ... pattern inside .assign() refers to the DataFrame as it exists at that point in the chain, allowing a new column to be computed from other columns, including ones created earlier in the same chain.

8.4 When Not to Chain#

Extremely long chains (much beyond 5-8 steps) can become difficult to debug, since an error deep in the chain does not immediately reveal which intermediate step produced unexpected data. A reasonable practice is to break exceptionally long or logically distinct pipelines into a few named, well-labeled intermediate variables representing meaningful checkpoints, rather than either one unbroken 15-step chain or fifteen individually named variables.

---

9. Benchmarking Your Own Code#

9.1 Why Guess When You Can Measure#

Rather than assuming one approach is faster, Python's timeit module (and the %timeit magic command in Jupyter) provides a reliable way to directly measure and compare implementations, following the same evidence-based principle used in Section 2.2's benchmark.

Python
CopyAsk AI
import timeit

setup_code = """
import pandas as pd
import numpy as np
df = pd.DataFrame({'a': np.random.rand(100000), 'b': np.random.rand(100000)})
"""

vectorized_time = timeit.timeit(
    "df['a'] * df['b']", setup=setup_code, number=100
)
apply_time = timeit.timeit(
    "df.apply(lambda row: row['a'] * row['b'], axis=1)", setup=setup_code, number=100
)

print(f"Vectorized (100 runs): {vectorized_time:.3f}s")
print(f"Apply (100 runs): {apply_time:.3f}s")

9.2 In Jupyter Notebooks#

Python
CopyAsk AI
# %timeit df['a'] * df['b']
# %timeit df.apply(lambda row: row['a'] * row['b'], axis=1)

The %timeit magic command automatically runs a cell multiple times and reports the mean and standard deviation of execution time, providing a more statistically reliable estimate than a single time.perf_counter() measurement (Section 2.2), which can be affected by transient system conditions on any single run.

9.3 A General Principle#

Rather than assuming a technique described in this notebook is faster in every possible situation, it is good practice to benchmark on data that resembles your actual production scale, since relative performance differences between approaches (vectorized vs. apply vs. loop, merge vs. concat, and so on) can shift somewhat depending on data size, data types, and the specific operation involved.

---

10. Hands-On: A Full High-Performance Pipeline#

This example combines vectorization, groupby, merging, and method chaining into a single realistic analysis: computing each region's revenue growth after enriching transaction data with region metadata.

Python
CopyAsk AI
import pandas as pd
import numpy as np

transactions = pd.DataFrame({
    "region_id": [1, 2, 3, 1, 2, 3, 1, 2, 3],
    "quarter": ["Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3"],
    "revenue": [50000, 42000, 61000, 53000, 39000, 65000, 58000, 45000, 70000]
})

region_metadata = pd.DataFrame({
    "region_id": [1, 2, 3],
    "region_name": ["North", "South", "East"],
    "manager": ["Priya", "Alex", "Jordan"]
})

result = (
    transactions
    .merge(region_metadata, on="region_id", how="left", validate="many_to_one")
    .assign(
        region_avg=lambda d: d.groupby("region_name")["revenue"].transform("mean")
    )
    .assign(
        pct_of_region_avg=lambda d: ((d["revenue"] / d["region_avg"] - 1) * 100).round(1)
    )
    .sort_values(["region_name", "quarter"])
    .reset_index(drop=True)
)

print(result[["region_name", "manager", "quarter", "revenue", "pct_of_region_avg"]])

Expected output:

code
Ask AICopy
  region_name manager quarter  revenue  pct_of_region_avg
0        East  Jordan      Q1    61000               -6.2
1        East  Jordan      Q2    65000                0.0
2        East  Jordan      Q3    70000                7.7
3       North   Priya      Q1    50000               -8.2
4       North   Priya      Q2    53000               -2.7
5       North   Priya      Q3    58000               10.5
6       South    Alex      Q1    42000                0.0
7       South    Alex      Q2    39000               -7.1
8       South    Alex      Q3    45000                7.1

Every step here, the merge, the group-level transform, the percentage calculation, and the sort, executes as a vectorized or group-vectorized operation, with validate="many_to_one" (Section 6.5) providing a safety check that each transaction correctly maps to exactly one region.

---

11. Case Studies#

11.1 Case Study: A Merge That Silently Duplicated Revenue#

An analyst merges a transactions table against a customer table intending a simple one-to-one enrichment (adding customer name and signup date to each transaction), but the customer table unexpectedly contains two duplicate rows for a small number of customer IDs (due to an earlier, unrelated data entry issue). The resulting merged dataset has more rows than the original transactions table, and the total revenue reported afterward is inflated, since transactions belonging to the duplicated customer IDs were counted twice.

Diagnosis: This is exactly the row-count sanity check scenario described in Section 6.3: a merge that was assumed to be one-to-one was actually many-to-many for a subset of keys, silently duplicating rows. Because no row-count check or validate parameter (Section 6.5) was used, the problem went undetected until a downstream revenue total looked implausibly high.

Lesson: Always compare row counts before and after a merge, and use the validate parameter whenever the intended relationship (one-to-one, one-to-many, many-to-one) is known in advance; this converts a silent, hard-to-trace data quality problem into an immediate, explicit error at the point of the merge itself.

11.2 Case Study: An Overnight Job Replaced by a Single groupby Call#

A team originally computed per-customer average order value using a Python loop that iterated over each unique customer ID, filtered the full transactions DataFrame down to that customer's rows using boolean masking (Notebook 2, Section 6) inside the loop, and computed the mean manually, repeated for 50,000 unique customers against a 10-million-row transaction table. This process took roughly 3 hours nightly. Replacing the entire loop with a single transactions.groupby("customer_id")["order_value"].mean() call reduced runtime to under 10 seconds.

Diagnosis: The original approach re-scanned and re-filtered the entire 10-million-row table from scratch for every one of the 50,000 customers, an extremely wasteful repeated-filtering pattern, in addition to paying ordinary Python loop overhead (Notebook 1, Section 10.2) on top of that. groupby's split-apply-combine model (Section 3.1) instead performs a single, efficient internal pass to group the data, then computes all 50,000 group means using vectorized operations within each group.

Lesson: Any pattern that loops over unique values of a column and separately filters the full dataset for each one is very likely a groupby operation in disguise, and should almost always be rewritten as one, following exactly the principle previewed in Notebook 1's case study, Section 12.3.

11.3 Case Study: A 20-Line Script Rewritten as a 6-Line Method Chain#

A junior analyst's script to compute top-performing products by region originally used 14 sequentially numbered intermediate variables (step1, step2, and so on through step14), making it difficult for a colleague reviewing the code to determine which variable represented the final, meaningful checkpoint versus a throwaway intermediate step, and an error partway through the script was hard to localize without manually inspecting each numbered variable.

Diagnosis: While functionally correct, the excessive number of arbitrarily-named intermediate variables, per Section 8.1, obscured the actual logical flow of the analysis, since variable names like step7 carry no information about what transformation occurred at that point.

Lesson: Rewriting the script as a single, well-formatted method chain (Section 8.2), broken across multiple lines with one transformation per line, made the entire pipeline readable top to bottom in a single pass, and, per Section 8.4, the team retained just two named checkpoints (the initially loaded raw data, and the final result) rather than either extreme of one unbroken chain or many arbitrarily named variables.

---

12. Common Misconceptions and Pitfalls#

---

13. Glossary#

---

14. Exercises#

Exercise 1 (Conceptual)#

Explain, using the concepts from Section 3.1 and the case study in Section 11.2, why a loop that iterates over unique values of a column and separately filters a large DataFrame for each value is almost always slower than the equivalent groupby call.

<details> <summary>Solution</summary>

The loop-based approach re-scans and re-filters the entire DataFrame from scratch for every unique value, an operation whose total cost grows with (number of unique values) times (size of the full dataset), and additionally pays ordinary Python-level loop overhead for each iteration. groupby, by contrast, performs a single efficient pass to partition the data into groups (the "split" stage, Section 3.1) and then applies the aggregation function to each group using internal, optimized routines, avoiding both the repeated full-dataset scanning and much of the Python-level overhead, exactly as demonstrated in the case study in Section 11.2 where a 3-hour loop-based job was reduced to under 10 seconds.

</details>

---

Exercise 2 (Conceptual)#

A team performs a left join between a 10,000-row orders table and a customers table, expecting the result to have exactly 10,000 rows, but the result has 10,347 rows. Using Section 6.3, explain the most likely cause and how it could have been caught earlier.

<details> <summary>Solution</summary>

The most likely cause is that the customers table contains duplicate rows for at least some customer IDs (an unintended one-to-many or many-to-many relationship rather than the assumed one-to-one relationship), causing some orders to be matched against more than one customer row and duplicated in the output, per the case study in Section 11.1. This could have been caught earlier by checking customers["customer_id"].duplicated().sum() before merging, by comparing row counts immediately after the merge against the expected count, or by using pd.merge(..., validate="many_to_one") (Section 6.5), which would have raised an explicit error at the moment of the merge rather than allowing the discrepancy to surface only later, downstream.

</details>

---

Exercise 3 (Coding)#

Given a DataFrame sales with columns store_id, month, and revenue, write a single method chain that: filters to rows where revenue is greater than 0, adds a column for each store's average monthly revenue using transform, adds a column showing each row's percentage difference from its store's average, and returns the result sorted by that percentage difference in descending order.

<details> <summary>Solution</summary>

Python
CopyAsk AI
import pandas as pd

sales = pd.DataFrame({
    "store_id": [1, 1, 1, 2, 2, 2],
    "month": ["Jan", "Feb", "Mar", "Jan", "Feb", "Mar"],
    "revenue": [10000, 12000, 0, 8000, 8500, 9200]
})

result = (
    sales
    .query("revenue > 0")
    .assign(store_avg=lambda d: d.groupby("store_id")["revenue"].transform("mean"))
    .assign(pct_diff=lambda d: ((d["revenue"] / d["store_avg"] - 1) * 100).round(1))
    .sort_values("pct_diff", ascending=False)
    .reset_index(drop=True)
)
print(result)

This follows the exact pattern demonstrated in Section 10's hands-on pipeline: filtering first (Section 6, Notebook 2), then a group-level transform (Section 4.2) to compute a per-group baseline without collapsing the row count, then a vectorized percentage computation, all expressed as a single readable chain (Section 8.2).

</details>

---

Exercise 4 (Applied Thinking)#

You need to combine 12 monthly CSV files, each with identical columns (date, store_id, revenue), into a single yearly DataFrame, and then compute each store's total annual revenue. Describe which Pandas operations from this notebook you would use, in order, and why each one is the right tool for that step.

<details> <summary>Solution</summary>

First, load each of the 12 files (Notebook 2, Section 8) and combine them using pd.concat() (Section 7.1) rather than pd.merge(), since the files share identical columns and simply need to be stacked as additional rows, not matched by a key column; ignore_index=True should be used to avoid duplicate index labels across the 12 originally separate files. Second, compute each store's total annual revenue using combined_df.groupby("store_id")["revenue"].sum() (Section 4.1), an aggregation that reduces all rows for each store down to a single total, which is exactly what groupby combined with an aggregation function is designed for, and is far preferable to writing a manual loop over unique store IDs (Section 11.2).

</details>

---

15. Quiz#

Q1. Which of the following is generally the fastest way to apply a mathematical operation to every element of a Pandas column? a) .iterrows() b) .apply() c) A direct vectorized operation (e.g., df['col'] * 2) d) All three perform identically

Q2. Which groupby operation returns a result with the same number of rows as the original DataFrame? a) Aggregation b) Transformation c) Filtration d) None of the above

Q3. Which join type keeps only rows with matching keys present in both DataFrames? a) Left join b) Right join c) Outer join d) Inner join

Q4. What is the primary purpose of the validate parameter in pd.merge()? a) To check that column names match exactly b) To explicitly enforce and check an expected relationship type between the two DataFrames' keys c) To automatically fix duplicate rows d) To sort the merged result

Q5. True or False: pd.concat(axis=1) matches rows between DataFrames based on the values in a shared key column, similar to pd.merge().

<details> <summary>Answers</summary>

1. c) A direct vectorized operation
2. b) Transformation
3. d) Inner join
4. b) To explicitly enforce and check an expected relationship type between the two DataFrames' keys
5. False — pd.concat(axis=1) aligns purely by index, not by matching key column values, per Section 7.3; pd.merge() is needed for explicit key-based matching

</details>

---

16. Summary#

• Vectorized operations outperform .apply(), which in turn outperforms explicit loops like .iterrows(), often by one or more orders of magnitude, because vectorized code avoids per-element Python interpreter overhead.
• groupby follows a split-apply-combine model, and its three operation types (aggregation, transformation, filtration) differ specifically in the shape of their output relative to the input.
• Merging combines DataFrames by matching key column values, with four standard join types (inner, left, right, outer) differing in how unmatched rows are handled; checking row counts before and after a merge, and using the validate parameter, are essential defensive practices.
• Concatenation stacks DataFrames along an axis without key-based matching, and should not be confused with merging when row alignment actually depends on matching values.
• Method chaining expresses a sequence of transformations as one linear, readable pipeline, avoiding both excessive intermediate variables and overly long unbroken chains.
• timeit and %timeit provide an evidence-based way to compare the actual performance of alternative implementations on your own data, rather than relying on general assumptions.

---

17. Further Reading#

• Pandas official documentation on groupby: https://pandas.pydata.org/docs/user_guide/groupby.html
• Pandas official documentation on merging: https://pandas.pydata.org/docs/user_guide/merging.html
• Wes McKinney, Python for Data Analysis, Chapters 8 and 10 (Data Wrangling; Data Aggregation and Group Operations)
• Pandas official documentation on enhancing performance: https://pandas.pydata.org/docs/user_guide/enhancingperf.html

---

Next Up#

Notebook 4: Data Cleaning and Preparation — missing data strategies, duplicate detection, outlier handling, type conversion, and string and datetime cleaning, applying the vectorized and groupby techniques from this notebook directly to the practical task of turning messy raw data into an analysis-ready dataset.

---

Course: Data Analytics | Notebook 3 of 13