dataManipulationChallenge

A Mental Model for Method Chaining in Pandas import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns from datetime import datetime, timedelta

Load the shipment data

shipments_df = pd.read_csv( "https://raw.githubusercontent.com/flyaflya/persuasive/main/shipments.csv", parse_dates=['plannedShipDate', 'actualShipDate'] )

Load product line data

product_line_df = pd.read_csv( "https://raw.githubusercontent.com/flyaflya/persuasive/main/productLine.csv" )

Reduce dataset size for faster processing (4,000 rows instead of 96,805 rows)

shipments_df = shipments_df.head(4000)

print("Shipments data shape:", shipments_df.shape) print("\nShipments data columns:", shipments_df.columns.tolist()) print("\nFirst few rows of shipments data:") print(shipments_df.head(10))

print("\n" + "="*50) print("Product line data shape:", product_line_df.shape) print("\nProduct line data columns:", product_line_df.columns.tolist()) print("\nFirst few rows of product line data:") print(product_line_df.head(10)) Shipments data shape: (4000, 5)

Shipments data columns: ['shipID', 'plannedShipDate', 'actualShipDate', 'partID', 'quantity']

First few rows of shipments data: shipID plannedShipDate actualShipDate partID quantity 0 10001 2013-11-06 2013-10-04 part92b16c5 6 1 10002 2013-10-15 2013-10-04 part66983b 2 2 10003 2013-10-25 2013-10-07 part8e36f25 1 3 10004 2013-10-14 2013-10-08 part30f5de0 1 4 10005 2013-10-14 2013-10-08 part9d64d35 6 5 10006 2013-10-14 2013-10-08 part6cd6167 15 6 10007 2013-10-14 2013-10-08 parta4d5fd1 2 7 10008 2013-10-14 2013-10-08 part08cadf5 1 8 10009 2013-10-14 2013-10-08 part5cc4989 10 9 10010 2013-10-14 2013-10-08 part912ae4c 1

================================================== Product line data shape: (11997, 3)

Product line data columns: ['partID', 'productLine', 'prodCategory']

Simple assignment - calculate if shipment was late

shipments_with_lateness = ( shipments_df .assign( is_late=lambda df: df['actualShipDate'] > df['plannedShipDate'], days_late=lambda df: (df['actualShipDate'] - df['plannedShipDate']).dt.days ) )

print("Added lateness calculations:") print(shipments_with_lateness[['shipID', 'plannedShipDate', 'actualShipDate', 'is_late', 'days_late']].head())

Understanding Data Types (dtypes) in Pandas

What is the dtype of actualShipDate?

Answer: datetime64[ns]

How to Check Data Types Using Code

# Method 1: .dtype attribute (most direct)
print(shipments_df['actualShipDate'].dtype)
# Output: datetime64[ns]

# Method 2: .dtypes for all columns  
print(shipments_df.dtypes)

# Method 3: .info() method (comprehensive overview)
shipments_df.info()

# Method 4: type() on individual values
print(type(shipments_df['actualShipDate'].iloc[0]))
# Output: <class 'pandas._libs.tslibs.timestamps.Timestamp'>

Why Same Data Types Matter for Date Comparisons

Critical Reasons:

1. Proper Mathematical Operations

Same types (datetime64[ns]): Enable date arithmetic, comparisons, and time differences
Different types: Can lead to unexpected results or errors

2. Accurate Comparisons

# Example with test data
test_df = pd.DataFrame({
    'date_as_datetime': pd.to_datetime(['2023-01-01', '2023-01-02', '2023-01-03']),
    'date_as_string': ['2023-01-01', '2023-01-02', '2023-01-03'],
    'planned_date': pd.to_datetime(['2022-12-31', '2023-01-01', '2023-01-02'])
})

# Datetime vs Datetime (CORRECT)
result = test_df['date_as_datetime'] > test_df['planned_date']
# Result: [True, True, True] - Correct chronological comparison

# String vs String (PROBLEMATIC)  
result = test_df['date_as_string'] > '2023-01-01'
# Result: [False, True, True] - Lexicographic (alphabetical) comparison!

3. Time Calculations Work

# This only works because both are datetime64[ns]:
date_diff = shipments_df['actualShipDate'] - shipments_df['plannedShipDate']
# Result: timedelta64[ns] - can extract days, hours, etc.

# Real analysis results:
print(f"Early shipments: {(shipments_df['actualShipDate'] < shipments_df['plannedShipDate']).sum()}")
print(f"On-time shipments: {(shipments_df['actualShipDate'] == shipments_df['plannedShipDate']).sum()}")
print(f"Late shipments: {(shipments_df['actualShipDate'] > shipments_df['plannedShipDate']).sum()}")

What Goes Wrong with Mixed Types:

String dates compare alphabetically: "2023-12-01" < "2023-02-01" = True (wrong!)
Time calculations fail or give meaningless results
Sorting produces incorrect chronological order

Bottom Line: Same datetime64[ns] dtype ensures date comparisons and calculations are chronologically accurate rather than just alphabetically compared!

Question 2: String vs Date Comparison

The Problem with String Date Comparisons

When dates are stored as strings instead of proper datetime objects, comparisons can yield completely unintuitive and incorrect results. Let's see what happens when we compare "04-11-2025" and "05-20-2024" as strings versus as dates.

The Dates in Question:

Date 1: "04-11-2025" (April 11, 2025)
Date 2: "05-20-2024" (May 20, 2024)

Intuitive expectation: April 11, 2025 should be later than May 20, 2024 (about 10 months later).

But what actually happens with string comparison?

# Demonstrating String vs Date Comparison Problems
import pandas as pd
from datetime import datetime

# Create test data with the problematic dates
date1_str = "04-11-2025"
date2_str = "05-20-2024"

print("=== STRING COMPARISON (WRONG!) ===")
string_result = date1_str < date2_str
print(f"'{date1_str}' < '{date2_str}' = {string_result}")
print(f"Interpretation: {'April 11, 2025 is earlier than May 20, 2024' if string_result else 'April 11, 2025 is later than May 20, 2024'}")
print("❌ This is WRONG! String comparison is lexicographic (alphabetical)")

print(f"\n🔍 Why strings compare this way:")
print(f"   Character by character: '0' vs '0', '4' vs '5'")
print(f"   Since '4' < '5', the comparison stops here and returns True")

print("\n" + "="*60)

print("=== PROPER DATE COMPARISON (CORRECT!) ===")
# Convert to proper datetime objects
date1_dt = pd.to_datetime(date1_str)
date2_dt = pd.to_datetime(date2_str)

date_result = date1_dt < date2_dt
print(f"{date1_dt.strftime('%B %d, %Y')} < {date2_dt.strftime('%B %d, %Y')} = {date_result}")
print(f"Interpretation: {'April 11, 2025 is earlier than May 20, 2024' if date_result else 'April 11, 2025 is later than May 20, 2024'}")
print("✅ This is CORRECT! Chronological comparison")

print(f"\n📊 Time difference: {(date1_dt - date2_dt).days} days")

print("\n" + "="*60)
print("🎯 LESSON: Always use datetime objects for date comparisons!")
print("   String comparison: Alphabetical order")  
print("   Datetime comparison: Chronological order")

Output Results:

=== STRING COMPARISON (WRONG!) ===
'04-11-2025' < '05-20-2024' = True
Interpretation: April 11, 2025 is earlier than May 20, 2024
❌ This is WRONG! String comparison is lexicographic (alphabetical)

🔍 Why strings compare this way:
   Character by character: '0' vs '0', '4' vs '5'
   Since '4' < '5', the comparison stops here and returns True

============================================================
=== PROPER DATE COMPARISON (CORRECT!) ===
April 11, 2025 < May 20, 2024 = False
Interpretation: April 11, 2025 is later than May 20, 2024
✅ This is CORRECT! Chronological comparison

📊 Time difference: 326 days

============================================================
🎯 LESSON: Always use datetime objects for date comparisons!
   String comparison: Alphabetical order
   Datetime comparison: Chronological order

More Examples of String vs Date Comparison Problems

Common Problematic Scenarios:

Different Date Formats:
- "2024-12-01" vs "2024-02-15" → String comparison gives wrong order
- "12/01/2024" vs "02/15/2024" → Even worse with MM/DD/YYYY format!
Year Boundaries:
- "12-31-2023" vs "01-01-2024" → String says December 31, 2023 > January 1, 2024
Time Components:
- "2024-01-15 14:30" vs "2024-01-15 09:45" → May work, but fragile

Real-World Impact:

Data Analysis: Wrong insights about trends over time
Reporting: Incorrect chronological ordering
Business Logic: Faulty date range filters
Sorting: Data appears in wrong temporal sequence

Best Practices:

✅ Always use pd.to_datetime() when loading date columns
✅ Set parse_dates parameter in pd.read_csv()
✅ Check dtypes with .dtypes or .info()
✅ Convert strings to datetime before any date operations

Quick Fix:

# Wrong way
df['date_col'] > '2024-01-01'  # String comparison

# Right way  
df['date_col'] > pd.to_datetime('2024-01-01')  # Datetime comparison

Question 3: Debug This Code

🐛 The Buggy Code

The following code has an error that will prevent it from running correctly. Can you spot the issue before we debug it?

# This code has an error - can you spot it?
shipments_with_lateness = (
    shipments_df
    .assign(
        is_late=lambda df: df['actualShipDate'] > df['plannedShipDate'],
        days_late=lambda df: (df['actualShipDate'] - df['plannedShipDate']).dt.days,
        lateStatement="Darn Shipment is Late" if shipments_df['is_late'] else "Shipment is on Time"
    )
)

🔍 Running the Buggy Code

Let's see what happens when we try to run this code:

# Attempting to run the buggy code
try:
    shipments_with_lateness = (
        shipments_df
        .assign(
            is_late=lambda df: df['actualShipDate'] > df['plannedShipDate'],
            days_late=lambda df: (df['actualShipDate'] - df['plannedShipDate']).dt.days,
            lateStatement="Darn Shipment is Late" if shipments_df['is_late'] else "Shipment is on Time"
        )
    )
    print("Code executed successfully!")
except Exception as e:
    print(f"❌ ERROR: {e}")
    print(f"Error type: {type(e).__name__}")

Output:

❌ ERROR: 'DataFrame' object has no attribute 'is_late'
Error type: AttributeError

🧐 Identifying the Bug

The Problem: In the third assignment within .assign():

lateStatement="Darn Shipment is Late" if shipments_df['is_late'] else "Shipment is on Time"

🚫 What's Wrong with the lateStatement Assignment:

Problem 1: Column Doesn't Exist Yet

shipments_df['is_late'] tries to access a column that's being created in the same .assign() call
The is_late column doesn't exist in the original shipments_df yet!
This causes: ❌ ERROR: 'DataFrame' object has no attribute 'is_late'

Problem 2: Wrong DataFrame Reference

Uses shipments_df['is_late'] (original DataFrame)
Should use df['is_late'] (the DataFrame being built inside .assign())
Even if the column existed, it would reference the wrong data

Problem 3: Single Evaluation Instead of Vectorized

The if/else evaluates once for the entire DataFrame, not per row
Trying to evaluate if Series would raise a ValueError about ambiguous truth values
Needs a vectorized operation that works row-by-row

Problem 4: Missing Lambda Function

Other assignments use lambda df: but this one doesn't
Breaks the pattern and prevents access to the intermediate DataFrame
Without lambda, can't reference columns being created in the same .assign()

🔑 Key Principles Violated:

Use lambda df: to access the DataFrame being built
Reference df['column'] not original_df['column']
Use vectorized operations (np.where, .map(), .apply()) for row-wise logic
Column dependencies flow left-to-right within .assign()

Root Cause: Trying to use regular Python if/else syntax on a pandas Series, when what's needed is a vectorized conditional operation that applies the logic to each row individually.

✅ The Corrected Code

Here are three different ways to fix this:

Solution 1: Using numpy.where() with lambda

import numpy as np

shipments_with_lateness = (
    shipments_df
    .assign(
        is_late=lambda df: df['actualShipDate'] > df['plannedShipDate'],
        days_late=lambda df: (df['actualShipDate'] - df['plannedShipDate']).dt.days,
        lateStatement=lambda df: np.where(df['is_late'], 
                                         "Darn Shipment is Late", 
                                         "Shipment is on Time")
    )
)

Solution 2: Using pandas.Series.map() with lambda

shipments_with_lateness = (
    shipments_df
    .assign(
        is_late=lambda df: df['actualShipDate'] > df['plannedShipDate'],
        days_late=lambda df: (df['actualShipDate'] - df['plannedShipDate']).dt.days,
        lateStatement=lambda df: df['is_late'].map({True: "Darn Shipment is Late", 
                                                   False: "Shipment is on Time"})
    )
)

Solution 3: Two-step approach (clearest for beginners)

# Step 1: Create the boolean columns
shipments_with_lateness = (
    shipments_df
    .assign(
        is_late=lambda df: df['actualShipDate'] > df['plannedShipDate'],
        days_late=lambda df: (df['actualShipDate'] - df['plannedShipDate']).dt.days
    )
)

# Step 2: Add the text column based on the boolean
shipments_with_lateness = shipments_with_lateness.assign(
    lateStatement=lambda df: np.where(df['is_late'], 
                                     "Darn Shipment is Late", 
                                     "Shipment is on Time")
)

📊 Testing the Fixed Code

# Let's test the corrected version
print("Fixed code results:")
print(shipments_with_lateness[['shipID', 'is_late', 'days_late', 'lateStatement']].head(10))
print(f"\nLate shipments: {shipments_with_lateness['is_late'].sum()}")
print(f"On-time shipments: {(~shipments_with_lateness['is_late']).sum()}")

🎯 Key Lessons

Column dependencies: When creating multiple columns in .assign(), later columns can reference earlier ones, but they must use the lambda df: syntax to access the dataframe being built.
Dataframe reference: Always use the df parameter inside lambda functions, not the original dataframe name.
Vectorized operations: Use np.where(), .map(), or similar vectorized functions for conditional assignments that need to be applied row-by-row.
Method chaining order: Be mindful of the order when columns depend on each other within the same .assign() call.

Query rows for late shipments and filter to keep specific columns

late_shipments = ( shipments_with_lateness .query('is_late == True') # Query rows where is_late is True .filter(['shipID', 'partID', 'plannedShipDate', 'actualShipDate', 'days_late']) # Filter to keep specific columns )

print(f"Found {len(late_shipments)} late shipments out of {len(shipments_with_lateness)} total") print("\nLate shipments sample:") print(late_shipments.head())

Question 1: Query vs Boolean Indexing

What's the difference between `.query('is_late == True')` and `[df['is_late'] == True]`?

Both methods filter rows based on a boolean condition, but they have important syntax and readability differences:

Method 1: Using `.query()` (String-based)

# Clean, readable syntax
late_shipments = shipments_with_lateness.query('is_late == True')

# Can also simplify for boolean columns
late_shipments = shipments_with_lateness.query('is_late')  # Automatically == True

# Complex conditions are very readable
very_late_shipments = shipments_with_lateness.query('is_late and days_late > 10')

Method 2: Using Boolean Indexing (Bracket notation)

# More verbose, requires brackets
late_shipments = shipments_with_lateness[shipments_with_lateness['is_late'] == True]

# Can simplify boolean columns
late_shipments = shipments_with_lateness[shipments_with_lateness['is_late']]

# Complex conditions become harder to read
very_late_shipments = shipments_with_lateness[
    (shipments_with_lateness['is_late']) & 
    (shipments_with_lateness['days_late'] > 10)
]

Key Differences:

Aspect	`.query()`	Boolean Indexing `[]`
Syntax	String-based, SQL-like	Python expression-based
Readability	More readable, especially for complex conditions	Can become verbose and nested
Column References	Direct: `'column_name'`	Full reference: `df['column_name']`
Boolean Operators	Natural: `and`, `or`, `not`	Python: `&`, `\|`, `~` (with parentheses)
Performance	Slightly slower (string parsing)	Slightly faster (direct evaluation)
Method Chaining	Chains naturally with other methods	Breaks method chaining flow

Which approach is more readable and why?

.query() is generally more readable for several reasons:

🔤 Natural Language: Uses and/or instead of &/|
📝 Less Repetition: Don't repeat DataFrame name for each column
🔗 Method Chaining: Fits perfectly in pandas method chains
📖 SQL-like Syntax: Familiar to those who know SQL
🧹 Cleaner Complex Conditions: Multiple conditions remain readable

Example Comparison:

# Query: Clean and readable
result = (df
    .query('age > 25 and salary > 50000 and department == "Engineering"')
    .sort_values('salary')
)

# Boolean indexing: More verbose
result = (df[(df['age'] > 25) & 
            (df['salary'] > 50000) & 
            (df['department'] == "Engineering")]
         .sort_values('salary')
)

Recommendation: Use .query() for better readability, especially in method chains and complex conditions. Use boolean indexing when you need maximum performance or when working with computed boolean masks.

Question 2: Additional Row Querying

Can you show an example of using a variable like `late_threshold` to query rows for shipments that are at least `late_threshold` days late?

Absolutely! Here's how to use variables in pandas .query() method for dynamic filtering:

Example: Query shipments that are at least 5 days late

# Define the threshold variable
late_threshold = 5

# Method 1: Using @ symbol to reference variables in .query()
very_late_shipments = (
    shipments_with_lateness
    .query('days_late >= @late_threshold')  # @ symbol references external variable
    .filter(['shipID', 'partID', 'plannedShipDate', 'actualShipDate', 'days_late'])
    .sort_values('days_late', ascending=False)
)

print(f"Shipments that are at least {late_threshold} days late:")
print(f"Found {len(very_late_shipments)} out of {len(shipments_with_lateness)} total shipments")
print("\nMost delayed shipments:")
print(very_late_shipments.head(10))

Alternative Methods:

# Method 2: Using f-string (be careful with string data!)
late_threshold = 5
very_late_shipments = shipments_with_lateness.query(f'days_late >= {late_threshold}')

# Method 3: Boolean indexing with variables (works naturally)
very_late_shipments = shipments_with_lateness[
    shipments_with_lateness['days_late'] >= late_threshold
]

# Method 4: Multiple conditions with variables
min_days_late = 5
max_days_late = 30
moderate_delays = (
    shipments_with_lateness
    .query('@min_days_late <= days_late <= @max_days_late')
    .sort_values('days_late')
)

More Complex Variable Usage:

# Using multiple variables and complex conditions
late_threshold = 5
critical_parts = ['part123abc', 'part456def', 'part789ghi']
start_date = '2013-10-01'

critical_late_shipments = (
    shipments_with_lateness
    .query('days_late >= @late_threshold and '
           'partID in @critical_parts and '
           'plannedShipDate >= @start_date')
    .sort_values(['days_late', 'partID'], ascending=[False, True])
)

print(f"Critical parts that are ≥{late_threshold} days late since {start_date}:")
print(critical_late_shipments)

Key Points about Variables in `.query()`:

@ Symbol: Use @variable_name to reference external variables
Lists/Arrays: Can use variables containing lists for in operations
Dates: String dates work directly, datetime objects need @ symbol
F-strings: Work for simple cases but @ symbol is safer and more explicit
Performance: @ symbol method is efficient and doesn't require string formatting

Best Practice:

# Good: Clear, safe, and efficient
threshold = 10
result = df.query('days_late >= @threshold and is_late')

# Avoid: F-strings can be risky with user input or special characters
result = df.query(f'days_late >= {threshold}')  # Could break with string data

Create a cleaner dataset by dropping unnecessary columns

clean_shipments = ( shipments_with_lateness .drop(columns=['quantity']) # Drop quantity column (not needed for our analysis) .dropna(subset=['plannedShipDate', 'actualShipDate']) # Remove rows with missing dates )

print(f"Cleaned dataset: {len(clean_shipments)} rows, {len(clean_shipments.columns)} columns") print("Remaining columns:", clean_shipments.columns.tolist())

Question 1: Drop vs Filter Strategies

What's the difference between `.drop(columns=['quantity'])` and `.filter()` with a list of columns you want to keep?

Both methods control which columns remain in your DataFrame, but they work in opposite ways:

Method 1: `.drop(columns=[])` - Exclusion Strategy

# Remove specific columns (exclusion approach)
clean_shipments = shipments_with_lateness.drop(columns=['quantity'])

# Remove multiple columns
clean_shipments = shipments_with_lateness.drop(columns=['quantity', 'shipID'])

# Alternative syntax (older pandas versions)
clean_shipments = shipments_with_lateness.drop(['quantity'], axis=1)

Method 2: `.filter()` - Inclusion Strategy

# Keep only specific columns (inclusion approach)
clean_shipments = shipments_with_lateness.filter(['shipID', 'partID', 'plannedShipDate', 
                                                  'actualShipDate', 'is_late', 'days_late'])

# Using regex patterns
clean_shipments = shipments_with_lateness.filter(regex='.*Date$')  # Keep columns ending with 'Date'

# Using like parameter (substring matching)
clean_shipments = shipments_with_lateness.filter(like='Ship')  # Keep columns containing 'Ship'

Key Differences:

Aspect	`.drop(columns=[])`	`.filter([])`
Strategy	❌ Exclusion: Remove unwanted columns	✅ Inclusion: Keep desired columns
Mental Model	"Remove these specific columns"	"Keep only these columns"
Column Count	Removes few, keeps many	Keeps few, removes many
Maintenance	Add to list when removing more	Update list when adding/removing
Safety	Fails if column doesn't exist	Silently ignores missing columns
Pattern Matching	Not available	Supports regex, like, items

When would you choose to drop columns vs filter to keep specific columns?

🗑️ Use `.drop()` when:

Few columns to remove: You want to keep most columns, just remove 1-3

# Good: Remove just the quantity column
df.drop(columns=['quantity'])

Exploratory analysis: Temporarily removing columns that are causing issues

# Remove problematic columns for quick analysis
df.drop(columns=['notes', 'comments', 'metadata'])

Known unwanted columns: You know exactly which columns are unnecessary

# Remove known administrative columns
df.drop(columns=['created_by', 'modified_date', 'internal_id'])

🎯 Use `.filter()` when:

Few columns to keep: You want a small subset from a wide DataFrame

# Good: Keep only essential columns from a 50-column dataset
df.filter(['id', 'name', 'email', 'status'])

Creating focused datasets: Building specific views for analysis

# Create date-focused dataset
date_analysis = shipments.filter(['shipID', 'plannedShipDate', 'actualShipDate'])

# Create performance-focused dataset  
performance_analysis = shipments.filter(['shipID', 'is_late', 'days_late'])

Pattern-based selection: Using regex or substring matching

# Keep all date columns
df.filter(regex='.*[Dd]ate$')

# Keep all ID columns
df.filter(like='ID')

API/Export preparation: Creating specific column sets for external systems

# Prepare data for API export
api_ready = df.filter(['customer_id', 'order_date', 'total_amount', 'status'])

Real-World Example Comparison:

# Scenario: 15-column dataset, want to keep 4 columns for analysis

# Method 1: Drop (need to list 11 columns to remove) ❌ Verbose
result = df.drop(columns=['col1', 'col2', 'col3', 'col4', 'col5', 
                          'col6', 'col7', 'col8', 'col9', 'col10', 'col11'])

# Method 2: Filter (list 4 columns to keep) ✅ Clean
result = df.filter(['important_col1', 'important_col2', 'important_col3', 'important_col4'])

Recommendation: Use .filter() when you want a small subset of columns, use .drop() when you want to remove a few columns from many.

Question 2: Handling Missing Data

What happens if you use `.dropna()` without specifying subset? How is this different from `.dropna(subset=['plannedShipDate', 'actualShipDate'])`?

The subset parameter in .dropna() dramatically changes which rows get removed:

Method 1: `.dropna()` without subset - Checks ALL columns

# Removes rows that have ANY missing value in ANY column
strict_clean = shipments_with_lateness.dropna()

# Equivalent to checking every single column
strict_clean = shipments_with_lateness.dropna(subset=shipments_with_lateness.columns.tolist())

Method 2: `.dropna(subset=[...])` - Checks SPECIFIC columns

# Removes rows that have missing values ONLY in the specified columns
selective_clean = shipments_with_lateness.dropna(subset=['plannedShipDate', 'actualShipDate'])

# Only cares about these two columns, ignores NaN in other columns

Practical Impact Demonstration:

# Example DataFrame with missing data
import pandas as pd
import numpy as np

sample_data = pd.DataFrame({
    'shipID': [1, 2, 3, 4, 5],
    'plannedShipDate': ['2023-01-01', '2023-01-02', np.nan, '2023-01-04', '2023-01-05'],
    'actualShipDate': ['2023-01-01', np.nan, '2023-01-03', '2023-01-04', '2023-01-05'],
    'partID': ['A', 'B', 'C', np.nan, 'E'],
    'notes': [np.nan, 'Note', np.nan, 'Important', np.nan]
})

print("Original data:")
print(sample_data)
print(f"Original shape: {sample_data.shape}")

# Method 1: Drop ANY row with ANY missing value
all_clean = sample_data.dropna()
print(f"\nAfter .dropna(): {all_clean.shape[0]} rows remaining")
print(all_clean)

# Method 2: Drop rows with missing dates only
date_clean = sample_data.dropna(subset=['plannedShipDate', 'actualShipDate'])
print(f"\nAfter .dropna(subset=['plannedShipDate', 'actualShipDate']): {date_clean.shape[0]} rows remaining")
print(date_clean)

Expected Output:

Original shape: (5, 5)

After .dropna(): 1 rows remaining  # Only row 4 has no missing values anywhere!
After .dropna(subset=['plannedShipDate', 'actualShipDate']): 3 rows remaining  # Rows 1, 4, 5

Why might you want to be selective about which columns to check for missing values?

🎯 Reason 1: Preserve Important Data

# Scenario: Analysis focuses on shipping dates, but other columns often have missing data
customer_orders = df.dropna(subset=['shipped_date', 'delivered_date'])  # Keep rows for date analysis

# vs overly strict approach that loses too much data
customer_orders = df.dropna()  # Might remove 80% of data due to missing 'customer_notes'

🔍 Reason 2: Column Relevance to Analysis

# Financial analysis - only care about financial columns having data
financial_analysis = df.dropna(subset=['revenue', 'cost', 'profit'])

# Don't care if 'employee_comments' or 'optional_notes' are missing

📊 Reason 3: Different Data Quality Requirements

# Critical columns: Must have data for any meaningful analysis
critical_cols = ['customer_id', 'order_date', 'product_id']

# Optional columns: Nice to have but don't break analysis
optional_cols = ['customer_feedback', 'discount_code', 'referral_source']

# Clean based on critical columns only
clean_orders = df.dropna(subset=critical_cols)

⚡ Reason 4: Performance and Data Volume

# Large dataset: Being selective preserves more data for analysis
before_shape = df.shape[0]

# Too strict: Might lose 90% of data
overly_clean = df.dropna()  

# Just right: Lose only 20% of data, keep analysis valid
appropriately_clean = df.dropna(subset=['essential_col1', 'essential_col2'])

print(f"Original: {before_shape} rows")
print(f"Too strict: {overly_clean.shape[0]} rows ({overly_clean.shape[0]/before_shape:.1%} remaining)")
print(f"Selective: {appropriately_clean.shape[0]} rows ({appropriately_clean.shape[0]/before_shape:.1%} remaining)")

Best Practices:

🎯 Focus on analysis-critical columns: Only require non-null values where absolutely necessary
📋 Document your choices: Comment why certain columns are required vs optional
📊 Check impact: Always print before/after row counts to understand data loss
🔄 Consider alternatives: Sometimes .fillna() or .interpolate() is better than dropping
⚖️ Balance data quality vs quantity: Too strict = not enough data, too loose = unreliable results

Golden Rule: Only enforce non-null requirements on columns that are essential for your specific analysis.

Sort by days late (worst first)

sorted_by_lateness = ( clean_shipments .sort_values('days_late', ascending=False) # Sort by days_late, highest first .reset_index(drop=True) # Reset index to be sequential )

print("Shipments sorted by lateness (worst first):") print(sorted_by_lateness[['shipID', 'partID', 'days_late', 'is_late']].head(10))

Understanding Pandas Sorting

Difference between `ascending=False` and `ascending=True`

The ascending parameter controls the sort order:

# ascending=True (default): Smallest to largest
ascending_sort = clean_shipments.sort_values('days_late', ascending=True)
print("Ascending (smallest first):", ascending_sort['days_late'].head().tolist())
# Output: [-33, -18, -11, -6, -6]  # Most early shipments first

# ascending=False: Largest to smallest  
descending_sort = clean_shipments.sort_values('days_late', ascending=False)
print("Descending (largest first):", descending_sort['days_late'].head().tolist())
# Output: [25, 22, 19, 18, 17]  # Most late shipments first

Visual Comparison:

ascending=True  (Low → High):  [-33, -18, -11, -6, -6, 0, 1, 5, 10, 15, 20, 25]
ascending=False (High → Low):  [25, 20, 15, 10, 5, 1, 0, -6, -6, -11, -18, -33]

How to sort by multiple columns

Use lists for both the column names and the ascending parameters:

# Example: First by is_late (late shipments first), then by days_late (worst delays first)
multi_sort = (
    clean_shipments
    .sort_values(['is_late', 'days_late'], 
                 ascending=[False, False])  # False for both: late=True first, then highest days_late
)

print("Multi-column sort results:")
print(multi_sort[['shipID', 'is_late', 'days_late']].head(10))

Sorting Logic Explanation:

Primary sort: is_late with ascending=False → True values come before False values
Secondary sort: Within each group (True/False), sort by days_late with ascending=False → highest values first

Different Multi-Sort Scenarios:

# Scenario 1: Late shipments first, then by worst delays
sort1 = df.sort_values(['is_late', 'days_late'], ascending=[False, False])
# Result: is_late=True (highest days_late first), then is_late=False (highest days_late first)

# Scenario 2: Early shipments first, then by best performance (most early)
sort2 = df.sort_values(['is_late', 'days_late'], ascending=[True, True])
# Result: is_late=False (lowest days_late first), then is_late=True (lowest days_late first)

# Scenario 3: Late shipments first, then by best performance within late shipments
sort3 = df.sort_values(['is_late', 'days_late'], ascending=[False, True])
# Result: is_late=True (lowest days_late first), then is_late=False (lowest days_late first)

Complex Example with 3 columns:

# Sort by: late status, then part ID alphabetically, then by days late (worst first)
complex_sort = (
    clean_shipments
    .sort_values(['is_late', 'partID', 'days_late'], 
                 ascending=[False, True, False])
)
# 1st: Late shipments first (False = True values first)
# 2nd: Part IDs alphabetically (True = A to Z)  
# 3rd: Within each part, worst delays first (False = highest days_late first)

Why do we use `.reset_index(drop=True)` after sorting?

What happens to the original index when you sort?

When pandas sorts a DataFrame, it preserves the original row indices but reorders them according to the new sort:

# Original DataFrame with default index
print("Original DataFrame:")
original_df = pd.DataFrame({
    'days_late': [5, -2, 15, 1, -10],
    'shipID': ['A', 'B', 'C', 'D', 'E']
})
print(original_df)
#    days_late shipID
# 0          5      A
# 1         -2      B  
# 2         15      C
# 3          1      D
# 4        -10      E

# After sorting (without reset_index)
sorted_df = original_df.sort_values('days_late', ascending=False)
print("\nAfter sorting (notice the jumbled index):")
print(sorted_df)
#    days_late shipID
# 2         15      C  ← Index 2 is now first!
# 0          5      A  ← Index 0 is now second!
# 3          1      D  ← Index 3 is now third!
# 1         -2      B  ← Index 1 is now fourth!
# 4        -10      E  ← Index 4 is now last!

Why might this jumbled index be problematic?

🔢 Non-sequential numbering: Index becomes [2, 0, 3, 1, 4] instead of [0, 1, 2, 3, 4]
🐛 Iteration confusion: Loops expecting sequential indices break

# This will fail with jumbled index
for i in range(len(sorted_df)):
    print(f"Row {i}: {sorted_df.loc[i, 'shipID']}")  # KeyError when i=1!

📊 Visualization issues: Plot libraries expect sequential indices

# Matplotlib might create gaps in x-axis with non-sequential index
plt.plot(sorted_df.index, sorted_df['days_late'])  # Potential gaps/issues

🔗 Merging complications: Index-based operations become unreliable

# Index-based alignment gets confused
df1.loc[0]  # Might not be the "first" row anymore!

The solution: `.reset_index(drop=True)`

# Fix the jumbled index
properly_sorted = (
    original_df
    .sort_values('days_late', ascending=False)
    .reset_index(drop=True)  # Creates new sequential index [0,1,2,3,4]
)

print("After sorting with reset_index:")
print(properly_sorted)
#    days_late shipID
# 0         15      C  ← Clean sequential index
# 1          5      A
# 2          1      D  
# 3         -2      B
# 4        -10      E

`drop=True` vs `drop=False`:

# drop=True (recommended): Discards old index
reset_drop_true = sorted_df.reset_index(drop=True)
print("With drop=True:")
print(reset_drop_true.columns.tolist())  # ['days_late', 'shipID']

# drop=False: Keeps old index as a new column named 'index'
reset_drop_false = sorted_df.reset_index(drop=False)
print("With drop=False:")
print(reset_drop_false.columns.tolist())  # ['index', 'days_late', 'shipID']
print(reset_drop_false.head())
#    index  days_late shipID
# 0      2         15      C  ← Old index preserved as 'index' column
# 1      0          5      A
# 2      3          1      D

Best Practices for Sorting:

🎯 Always reset index after sorting (unless you specifically need the original index)
📋 Use meaningful column names in multi-column sorts
📝 Comment complex sorting logic to explain the business reasoning
⚡ Sort early in your pipeline to avoid repeated sorting operations

# Good practice: Clean, documented sorting
final_analysis = (
    raw_data
    .query('is_valid_shipment')           # Filter first
    .sort_values(['priority', 'days_late'], 
                 ascending=[False, False])  # High priority, worst delays first
    .reset_index(drop=True)               # Clean index
    .head(100)                            # Take top 100 for analysis
)

Calculate overall service level metrics

service_metrics = ( clean_shipments .agg({ 'is_late': ['count', 'sum', 'mean'], # Count total, count late, calculate percentage 'days_late': ['mean', 'max'] # Average and maximum days late }) .round(3) )

print("Overall Service Level Metrics:") print(service_metrics)

Calculate percentage on-time directly from the data

on_time_rate = (1 - clean_shipments['is_late'].mean()) * 100 print(f"\nOn-time delivery rate: {on_time_rate:.1f}%")

Boolean Aggregation

Why does `sum()` work on boolean values? What does it count?

Boolean values in pandas (and Python) are treated as numeric values where:

True = 1
False = 0

This means mathematical operations like sum(), mean(), max(), etc. work perfectly on boolean columns!

Understanding Boolean Arithmetic:

# Demonstrate boolean to numeric conversion
print("Boolean to numeric conversion:")
print(f"True as number: {int(True)}")    # Output: 1
print(f"False as number: {int(False)}")  # Output: 0

# Example boolean Series
is_late_sample = pd.Series([True, False, True, True, False])
print(f"\nBoolean Series: {is_late_sample.tolist()}")
print(f"As numbers: {is_late_sample.astype(int).tolist()}")  # [1, 0, 1, 1, 0]

What does `sum()` count on boolean values?

sum() counts the number of True values in the boolean Series:

# Using our shipment data example
late_shipments = clean_shipments['is_late']

print("Boolean aggregation examples:")
print(f"Total shipments: {late_shipments.count()}")      # Total non-null values
print(f"Late shipments: {late_shipments.sum()}")         # Count of True values (late)
print(f"On-time shipments: {(~late_shipments).sum()}")   # Count of False values (on-time)
print(f"Percentage late: {late_shipments.mean():.3f}")   # Mean of 1s and 0s = percentage

Real example output:

Total shipments: 4000
Late shipments: 1456          # Number of True values
On-time shipments: 2544       # Number of False values  
Percentage late: 0.364        # 1456/4000 = 36.4%

Why this works mathematically:

When you have boolean values [True, False, True, True, False]:

Convert to numbers: [1, 0, 1, 1, 0]
Sum operation: 1 + 0 + 1 + 1 + 0 = 3
Result: Count of True values = 3

Practical Applications in Data Analysis:

# 1. Count categories
print("Category counting:")
df['is_premium'].sum()           # How many premium customers?
df['has_discount'].sum()         # How many orders have discounts?
df['is_weekend'].sum()           # How many weekend transactions?

# 2. Calculate percentages  
print("Percentage calculations:")
df['is_late'].mean()             # What % of shipments are late?
df['passed_test'].mean() * 100   # What % passed the test?

# 3. Multiple boolean aggregations
metrics = df.agg({
    'is_late': ['count', 'sum', 'mean'],     # Total, count late, % late
    'is_priority': ['sum', 'mean'],          # Count priority, % priority  
    'has_issues': ['sum']                    # Count with issues
})

Understanding the `.agg()` output in our example:

service_metrics = clean_shipments.agg({
    'is_late': ['count', 'sum', 'mean'],  
    'days_late': ['mean', 'max']
})

What each aggregation means:

is_late['count']: Total number of shipments (non-null values)
is_late['sum']: Number of late shipments (count of True values)
is_late['mean']: Percentage of shipments that are late (sum/count)
days_late['mean']: Average delay across all shipments
days_late['max']: Worst delay (maximum days late)

Common Boolean Aggregation Patterns:

# Pattern 1: Success/Failure Analysis
success_rate = df['passed'].mean()           # % success rate
failure_count = (~df['passed']).sum()        # Count failures (note the ~ for NOT)

# Pattern 2: Category Analysis by Groups
category_stats = (
    df.groupby('department')
    .agg({
        'is_promoted': ['count', 'sum', 'mean'],    # Total, promoted count, promotion rate
        'meets_goals': ['sum', 'mean']              # Goal achievers, achievement rate
    })
)

# Pattern 3: Time-based Boolean Analysis
daily_metrics = (
    df.groupby('date')
    .agg({
        'is_sale': 'sum',           # Daily sales count
        'is_return': 'sum',         # Daily returns count  
        'is_new_customer': 'sum'    # Daily new customers
    })
)

Key Insights:

🔢 Boolean = Binary: True/False becomes 1/0 for mathematical operations
🧮 Sum = Count: sum() on boolean column counts True values
📊 Mean = Percentage: mean() on boolean column gives percentage as decimal
🎯 Efficient: No need for complex counting logic - use built-in aggregations
🔄 Invertible: Use ~ (NOT operator) to count False values

Pro Tip: Boolean aggregation is one of the most powerful features in pandas for converting categorical/binary data into meaningful metrics!

Merge shipment data with product line data

shipments_with_category = ( clean_shipments .merge(product_line_df, on='partID', how='left') # Left join to keep all shipments .assign( category_late=lambda df: df['is_late'] & df['prodCategory'].notna() # Only count as late if we have category info ) )

print("\nProduct categories available:") print(shipments_with_category['prodCategory'].value_counts())

Understanding Pandas Merges and Joins

Question 1: Join Types and Data Loss

Why does your professor think we should use `how='left'` in most cases?

The how='left' parameter preserves all records from your primary dataset (the left DataFrame), which is usually what you want in data analysis:

# Our merge example
shipments_with_category = clean_shipments.merge(product_line_df, on='partID', how='left')

Reasons to prefer how='left':

🛡️ No Data Loss from Primary Dataset: Every shipment record is preserved

print(f"Original shipments: {len(clean_shipments)}")           # e.g., 4000
print(f"After left merge: {len(shipments_with_category)}")     # Still 4000

🔍 Preserves Analysis Completeness: You can still analyze all shipments, even those without product category info

# Can still calculate total metrics
total_late = shipments_with_category['is_late'].sum()        # All shipments counted
category_late = shipments_with_category['category_late'].sum()  # Only those with category info

📊 Handles Missing Reference Data Gracefully: Missing product categories become NaN, not lost records

# Shipments without category info are preserved but marked as missing
missing_categories = shipments_with_category['prodCategory'].isna().sum()
print(f"Shipments missing category info: {missing_categories}")

Comparison of Join Types:

# Example data for demonstration
left_df = pd.DataFrame({'shipID': [1, 2, 3, 4], 'is_late': [True, False, True, False]})
right_df = pd.DataFrame({'shipID': [1, 2, 5], 'category': ['A', 'B', 'C']})

print("Left DataFrame (shipments):")
print(left_df)
#    shipID  is_late
# 0       1     True
# 1       2    False  
# 2       3     True   ← No matching category data
# 3       4    False  ← No matching category data

print("Right DataFrame (categories):")  
print(right_df)
#    shipID category
# 0       1        A
# 1       2        B
# 2       5        C  ← No matching shipment data

Different join results:

# LEFT JOIN (recommended): Keep all shipments
left_result = left_df.merge(right_df, on='shipID', how='left')
print("LEFT JOIN result:")
print(left_result)
#    shipID  is_late category
# 0       1     True        A
# 1       2    False        B  
# 2       3     True      NaN  ← Preserved with missing category
# 3       4    False      NaN  ← Preserved with missing category

# INNER JOIN: Only matching records
inner_result = left_df.merge(right_df, on='shipID', how='inner')  
print("INNER JOIN result:")
print(inner_result)
#    shipID  is_late category
# 0       1     True        A
# 1       2    False        B
# ❌ Lost shipments 3 and 4!

# RIGHT JOIN: Keep all categories  
right_result = left_df.merge(right_df, on='shipID', how='right')
print("RIGHT JOIN result:")
print(right_result)
#    shipID  is_late category
# 0       1     True        A
# 1       2    False        B
# 2       5      NaN        C  ← Category without shipment
# ❌ Lost shipments 3 and 4!

How can you check if any shipments were lost during the merge?

Method 1: Compare row counts

# Before merge
original_count = len(clean_shipments)
print(f"Original shipments: {original_count}")

# After merge  
merged_count = len(shipments_with_category)
print(f"After merge: {merged_count}")

# Check for loss
if merged_count < original_count:
    print(f"⚠️ WARNING: Lost {original_count - merged_count} shipments during merge!")
elif merged_count > original_count:
    print(f"⚠️ WARNING: Gained {merged_count - original_count} rows - possible duplicates!")
else:
    print("✅ No shipments lost during merge")

Method 2: Use merge indicator

# Add indicator to see merge results
merge_with_indicator = (
    clean_shipments
    .merge(product_line_df, on='partID', how='left', indicator=True)
)

print("Merge indicator results:")
print(merge_with_indicator['_merge'].value_counts())
# left_only      - Shipments without matching product info
# both           - Shipments with matching product info  
# right_only     - Product info without matching shipments (shouldn't happen with left join)

Method 3: Check for missing values

# Count missing product categories (indicates unmatched shipments)
missing_categories = shipments_with_category['prodCategory'].isna().sum()
total_shipments = len(shipments_with_category)

print(f"Shipments with missing category info: {missing_categories}")
print(f"Shipments with category info: {total_shipments - missing_categories}")
print(f"Category match rate: {((total_shipments - missing_categories) / total_shipments * 100):.1f}%")

Question 2: Key Column Matching

What happens if there are duplicate `partID` values in the `product_line_df`?

Duplicate keys in the right DataFrame create a "Cartesian product" effect - one record from the left can match multiple records from the right, creating multiple rows for each match.

Demonstration of the Problem:

# Example: Clean shipments (left)
shipments_sample = pd.DataFrame({
    'shipID': [101, 102, 103],
    'partID': ['partA', 'partB', 'partC'],
    'is_late': [True, False, True]
})

# Product line data with DUPLICATES (right)
product_line_with_dupes = pd.DataFrame({
    'partID': ['partA', 'partA', 'partB', 'partC'],  # partA appears twice!
    'productLine': ['line1', 'line2', 'line3', 'line4'],
    'prodCategory': ['Machines', 'Liquids', 'Machines', 'Marketables']
})

print("Original shipments:")
print(shipments_sample)
#    shipID partID  is_late
# 0     101  partA     True
# 1     102  partB    False
# 2     103  partC     True

print("Product data with duplicates:")
print(product_line_with_dupes)
#   partID productLine prodCategory
# 0  partA       line1     Machines
# 1  partA       line2      Liquids  ← Duplicate partA!
# 2  partB       line3     Machines
# 3  partC       line4  Marketables

Result of merge with duplicates:

# Merge with duplicate keys
problematic_merge = shipments_sample.merge(product_line_with_dupes, on='partID', how='left')
print("Merge result with duplicates:")
print(problematic_merge)
#    shipID partID  is_late productLine prodCategory
# 0     101  partA     True       line1     Machines
# 1     101  partA     True       line2      Liquids  ← Duplicate row created!
# 2     102  partB    False       line3     Machines
# 3     103  partC     True       line4  Marketables

print(f"Original rows: {len(shipments_sample)}")      # 3 rows
print(f"After merge: {len(problematic_merge)}")       # 4 rows - gained 1 row!

Problems caused by duplicate keys:

📈 Row Inflation: 3 shipments become 4 rows
📊 Incorrect Aggregations: Metrics get artificially inflated

# Wrong metrics due to duplicates
original_late_count = shipments_sample['is_late'].sum()    # 2 late shipments
inflated_late_count = problematic_merge['is_late'].sum()   # 3 "late" shipments (101 counted twice!)

print(f"Actual late shipments: {original_late_count}")     # 2
print(f"Inflated count: {inflated_late_count}")           # 3

🔄 Double-counting in Analysis: Same shipment affects multiple product categories

How to detect and handle duplicate keys:

Detection:

# Check for duplicates in the right DataFrame
duplicates = product_line_df['partID'].duplicated()
duplicate_count = duplicates.sum()

if duplicate_count > 0:
    print(f"⚠️ WARNING: {duplicate_count} duplicate partIDs found in product_line_df!")
    print("Duplicate partIDs:")
    print(product_line_df[product_line_df['partID'].duplicated(keep=False)]['partID'].unique())
else:
    print("✅ No duplicate partIDs found")

Solutions:

# Solution 1: Remove duplicates (keep first occurrence)
clean_product_line = product_line_df.drop_duplicates(subset=['partID'], keep='first')

# Solution 2: Remove duplicates (keep last occurrence)  
clean_product_line = product_line_df.drop_duplicates(subset=['partID'], keep='last')

# Solution 3: Aggregate duplicates (if they represent different aspects)
aggregated_product_line = (
    product_line_df
    .groupby('partID')
    .agg({
        'productLine': 'first',     # Take first product line
        'prodCategory': 'first'     # Take first category
    })
    .reset_index()
)

# Then perform safe merge
safe_merge = clean_shipments.merge(clean_product_line, on='partID', how='left')

Best Practices for Safe Merging:

🔍 Always check for duplicates in key columns before merging
📊 Compare row counts before and after merge
🛡️ Use how='left' to preserve your primary dataset
📋 Use merge indicator to understand match patterns
🧹 Clean reference data to remove unwanted duplicates

# Complete safe merge workflow
def safe_merge(left_df, right_df, on_column, how='left'):
    # Check for duplicates
    right_dupes = right_df[on_column].duplicated().sum()
    if right_dupes > 0:
        print(f"WARNING: {right_dupes} duplicate keys in right DataFrame")
    
    # Perform merge with indicator
    result = left_df.merge(right_df, on=on_column, how=how, indicator=True)
    
    # Check results
    original_rows = len(left_df)
    final_rows = len(result)
    
    if final_rows != original_rows:
        print(f"Row count changed: {original_rows} → {final_rows}")
    
    print("Merge results:")
    print(result['_merge'].value_counts())
    
    return result.drop('_merge', axis=1)  # Remove indicator column

Analyze service levels by product category

service_by_category = ( shipments_with_category .groupby('prodCategory') # Split by product category .agg({ 'is_late': ['any', 'count', 'sum', 'mean'], # Count, late count, percentage late 'days_late': ['mean', 'max'] # Average and max days late }) .round(3) )

print("Service Level by Product Category:") print(service_by_category)

Understanding GroupBy Operations

Question 1: GroupBy Mechanics

What does `.groupby('prodCategory')` actually do? How does it "split" the data?

The .groupby() operation implements the "Split-Apply-Combine" paradigm:

🔄 SPLIT: Logically divides the DataFrame into groups based on unique values
⚙️ APPLY: Performs operations on each group independently
🔗 COMBINE: Merges results back into a single output

# Conceptual demonstration of what groupby does
print("Original data sample:")
sample_data = pd.DataFrame({
    'shipID': [1, 2, 3, 4, 5, 6],
    'prodCategory': ['Machines', 'Liquids', 'Machines', 'Liquids', 'Machines', 'Marketables'],
    'is_late': [True, False, True, True, False, False],
    'days_late': [5, -2, 10, 3, -1, -5]
})
print(sample_data)

print("\nAfter .groupby('prodCategory') - Logical Split:")
grouped = sample_data.groupby('prodCategory')

# Show what each group contains
for name, group in grouped:
    print(f"\nGroup '{name}':")
    print(group)

Expected Output:

Group 'Liquids':
   shipID prodCategory  is_late  days_late
1       2      Liquids    False         -2
3       4      Liquids     True          3

Group 'Machines': 
   shipID prodCategory  is_late  days_late
0       1     Machines     True          5
2       3     Machines     True         10
4       5     Machines    False         -1

Group 'Marketables':
   shipID prodCategory  is_late  days_late
5       6  Marketables    False         -5

The GroupBy object is a "lazy" operation:

# groupby() creates a GroupBy object, not actual separate DataFrames
grouped = shipments_with_category.groupby('prodCategory')
print(f"Type: {type(grouped)}")  # <class 'pandas.core.groupby.generic.DataFrameGroupBy'>

# You can see the groups without processing them
print(f"Number of groups: {grouped.ngroups}")
print(f"Group keys: {list(grouped.groups.keys())}")
print(f"Group sizes: {grouped.size()}")

Why do we need to use `.agg()` after grouping? What happens if you don't?

Without .agg() - you just have a GroupBy object:

# This is just a GroupBy object - not very useful by itself
grouped = shipments_with_category.groupby('prodCategory')
print(grouped)  # Output: <pandas.core.groupby.generic.DataFrameGroupBy object at 0x...>

# You can't directly use it for analysis
try:
    print(grouped.mean())  # This works for simple operations
except Exception as e:
    print(f"Error: {e}")

With .agg() - you get actual results:

# .agg() applies functions to each group and combines results
results = (
    shipments_with_category
    .groupby('prodCategory')
    .agg({
        'is_late': ['count', 'sum', 'mean'],  # Multiple functions per column
        'days_late': ['mean', 'max']
    })
)
print("Aggregated results:")
print(results)

Different ways to aggregate after groupby:

# Method 1: Simple aggregation (single function)
simple_agg = shipments_with_category.groupby('prodCategory')['is_late'].mean()

# Method 2: Multiple columns, single function
multi_col = shipments_with_category.groupby('prodCategory')[['is_late', 'days_late']].mean()

# Method 3: Dictionary-based aggregation (different functions per column)
dict_agg = shipments_with_category.groupby('prodCategory').agg({
    'is_late': 'mean',
    'days_late': ['mean', 'max', 'std']
})

# Method 4: Named aggregations (pandas 0.25+)
named_agg = shipments_with_category.groupby('prodCategory').agg(
    late_percentage=('is_late', 'mean'),
    avg_delay=('days_late', 'mean'),
    max_delay=('days_late', 'max'),
    total_shipments=('is_late', 'count')
)

Question 2: Multi-Level Grouping

Explore grouping by `['shipID', 'prodCategory']`? What question does this answer versus grouping by `'prodCategory'` alone?

Single-level grouping vs Multi-level grouping answer different business questions:

Single-Level: `.groupby('prodCategory')`

Question: "What's the overall performance by product category?"

# Groups all shipments by product category
category_performance = (
    shipments_with_category
    .groupby('prodCategory')
    .agg({
        'is_late': ['count', 'mean'],  # Total shipments per category, % late
        'days_late': 'mean'            # Average delay per category
    })
)

print("Performance by Product Category:")
print(category_performance)

Sample Output:

                  is_late          days_late
                    count   mean        mean
prodCategory                              
Liquids              1200   0.35        2.1
Machines             1800   0.42        3.5  
Marketables          1000   0.28        1.8

Multi-Level: `.groupby(['shipID', 'prodCategory'])`

Question: "How many different product categories are in each shipment, and what's the performance within each shipment-category combination?"

# Groups by both shipment AND product category
shipment_category_analysis = (
    shipments_with_category
    .groupby(['shipID', 'prodCategory'])
    .agg({
        'partID': 'count',           # How many parts per shipment-category
        'is_late': 'mean',           # % late within this shipment-category  
        'days_late': 'mean'          # Average delay within this shipment-category
    })
    .rename(columns={'partID': 'parts_count'})
)

print("Analysis by Shipment and Category:")
print(shipment_category_analysis.head(10))

Sample Output:

                              parts_count  is_late  days_late
shipID prodCategory                                         
10001  Machines                        2     1.0        5.0
10002  Liquids                         1     0.0       -2.0
10003  Machines                        1     1.0       10.0
       Marketables                     1     0.0       -1.0
10004  Liquids                         2     0.5        1.5
       Machines                        1     1.0        8.0

Why multi-level grouping reveals more insights:

Understanding the hint about multiple partIDs per shipID:

# Demonstrate how one shipment can have multiple categories
shipment_composition = (
    shipments_with_category
    .groupby('shipID')
    .agg({
        'prodCategory': 'nunique',   # How many different categories per shipment
        'partID': 'count',           # Total parts per shipment
        'is_late': 'mean'            # Overall performance per shipment
    })
    .rename(columns={'prodCategory': 'categories_per_shipment'})
)

# Find shipments with multiple categories
multi_category_shipments = shipment_composition[
    shipment_composition['categories_per_shipment'] > 1
]

print("Shipments with multiple product categories:")
print(multi_category_shipments.head())
print(f"\nShipments with mixed categories: {len(multi_category_shipments)}")

Business Questions Answered by Each Approach:

Single-Level Grouping (prodCategory):

✅ Which product category has the worst delivery performance?
✅ What's the overall late percentage by category?
✅ Which categories should we focus improvement efforts on?

Multi-Level Grouping (['shipID', 'prodCategory']):

✅ How does performance vary within individual shipments by category?
✅ Do mixed-category shipments perform differently than single-category shipments?
✅ Which specific shipment-category combinations are problematic?
✅ How many parts of each category are typically in a shipment?

Advanced Multi-Level Analysis:

# Find the most complex shipments (most categories)
shipment_complexity = (
    shipments_with_category
    .groupby('shipID')
    .agg({
        'prodCategory': ['nunique', lambda x: ', '.join(sorted(x.unique()))],
        'partID': 'count',
        'is_late': 'mean',
        'days_late': 'mean'
    })
)
shipment_complexity.columns = ['num_categories', 'category_mix', 'total_parts', 'late_rate', 'avg_delay']

# Most complex shipments
most_complex = shipment_complexity.nlargest(5, 'num_categories')
print("Most complex shipments (most categories):")
print(most_complex)

# Performance comparison: single vs multi-category shipments
performance_by_complexity = (
    shipment_complexity
    .groupby('num_categories')
    .agg({
        'late_rate': 'mean',
        'avg_delay': 'mean',
        'total_parts': 'mean'
    })
    .round(3)
)

print("\nPerformance by shipment complexity:")
print(performance_by_complexity)

Key Insights from Multi-Level Grouping:

🎯 Granular Analysis: See performance within specific shipment-category combinations
🔍 Pattern Detection: Identify if mixed-category shipments perform differently
📊 Resource Planning: Understand typical category composition per shipment
⚖️ Complexity Impact: Analyze if shipment complexity affects performance
🎨 Targeted Improvements: Focus on specific shipment-category combinations that underperform

When to Use Each Approach:

Use Single-Level	Use Multi-Level
Overall category trends	Granular shipment analysis
High-level reporting	Operational troubleshooting
Strategic decisions	Tactical improvements
Simple comparisons	Complex relationship analysis

Pro Tip: Often you'll want to do both - start with single-level for overview, then drill down with multi-level for detailed insights!

Create a comprehensive analysis dataset

comprehensive_analysis = ( shipments_with_category .groupby(['shipID', 'prodCategory']) # Group by shipment and category .agg({ 'is_late': 'any', # True if any item in this shipment/category is late 'days_late': 'max' # Maximum days late for this shipment/category }) .reset_index() .assign( has_multiple_categories=lambda df: df.groupby('shipID')['prodCategory'].transform('nunique') > 1 ) )

print("Comprehensive analysis - shipments with multiple categories:") multi_category_shipments = comprehensive_analysis[comprehensive_analysis['has_multiple_categories']] print(f"Shipments with multiple categories: {multi_category_shipments['shipID'].nunique()}") print(f"Total unique shipments: {comprehensive_analysis['shipID'].nunique()}") print(f"Percentage with multiple categories: {multi_category_shipments['shipID'].nunique() / comprehensive_analysis['shipID'].nunique() * 100:.1f}%")

Business Question Analysis

Question 1: Business Question Analysis

What business question does this comprehensive analysis answer?

This comprehensive analysis answers several critical operational questions for ZappTech:

Primary Business Question: "How complex are our shipments in terms of product diversity, and how does this complexity impact our fulfillment operations?"

Specific Questions Addressed:

📦 Shipment Complexity: What percentage of our shipments contain multiple product categories?
🏭 Operational Planning: How should we organize our fulfillment processes around single vs. multi-category shipments?
📊 Performance Analysis: Do mixed-category shipments have different performance characteristics?
🎯 Risk Assessment: Are certain shipment-category combinations more prone to delays?
📈 Resource Allocation: How should we staff and equip our facilities based on shipment complexity?

How does grouping by `['shipID', 'prodCategory']` differ from grouping by just `'prodCategory'`?

Fundamental Difference: The unit of analysis changes completely:

Single-Level: .groupby('prodCategory')

Unit of Analysis: Product Category
Question: "How does each product category perform overall?"
Output: One row per category (3-5 rows total)

# Example output from single-level grouping
category_only = shipments_with_category.groupby('prodCategory').agg({
    'is_late': ['count', 'mean'],
    'days_late': 'mean'
})

# Result structure:
#                  is_late          days_late
#                    count   mean        mean
# prodCategory                              
# Liquids              1200   0.35        2.1  ← All Liquids across all shipments
# Machines             1800   0.42        3.5  ← All Machines across all shipments
# Marketables          1000   0.28        1.8  ← All Marketables across all shipments

Multi-Level: .groupby(['shipID', 'prodCategory'])

Unit of Analysis: Shipment-Category Combination
Question: "How does each category perform within each specific shipment?"
Output: One row per shipment-category pair (potentially thousands of rows)

# Example output from multi-level grouping
shipment_category = shipments_with_category.groupby(['shipID', 'prodCategory']).agg({
    'is_late': 'any',
    'days_late': 'max'
})

# Result structure:
#                              is_late  days_late
# shipID prodCategory                           
# 10001  Machines                True        5.0  ← Machines in shipment 10001
# 10002  Liquids                False       -2.0  ← Liquids in shipment 10002
# 10003  Machines                True       10.0  ← Machines in shipment 10003
#        Marketables            False       -1.0  ← Marketables in shipment 10003
# 10004  Liquids                False        1.5  ← Liquids in shipment 10004
#        Machines                True        8.0  ← Machines in shipment 10004

Key Analytical Differences:

Aspect	Single-Level (`prodCategory`)	Multi-Level (`['shipID', 'prodCategory']`)
Granularity	Category-wide aggregates	Shipment-specific category performance
Complexity Analysis	❌ Cannot detect mixed shipments	✅ Reveals shipment composition
Performance Patterns	Overall category trends	Category performance within shipments
Operational Insights	Strategic category decisions	Tactical fulfillment optimization
Business Focus	"Which categories are problematic?"	"Which shipment types are problematic?"

What insights can ZappTech's management gain from knowing the percentage of multi-category shipments?

The percentage of multi-category shipments is a critical operational metric that provides insights across multiple business dimensions:

1. 🏭 Operational Complexity Management

# If 45% of shipments have multiple categories:
multi_category_percentage = 45  # Example result

print(f"Operational Complexity Analysis:")
print(f"• {multi_category_percentage}% of shipments require multi-category handling")
print(f"• {100 - multi_category_percentage}% are simple, single-category shipments")

Management Insights:

Resource Planning: Nearly half of fulfillment operations require cross-category coordination
Training Needs: Staff must be trained on multiple product categories
Equipment Requirements: Facilities need diverse handling capabilities

2. 📦 Fulfillment Strategy Optimization

Strategy Implications Based on Multi-Category Percentage:

# Strategic decisions based on complexity percentage
if multi_category_percentage > 40:
    strategy = "Zone-based fulfillment with cross-training"
elif multi_category_percentage > 20:
    strategy = "Hybrid approach with specialized teams"
else:
    strategy = "Category-specialized fulfillment centers"

print(f"Recommended Strategy: {strategy}")

Specific Strategies:

High % (>40%): Invest in flexible, multi-category fulfillment systems
Medium % (20-40%): Hybrid approach with both specialized and flexible areas
Low % (<20%): Category-specialized warehouses may be efficient

3. 📊 Performance Risk Assessment

# Analyze performance differences
performance_comparison = comprehensive_analysis.groupby('has_multiple_categories').agg({
    'is_late': 'mean',
    'days_late': 'mean'
}).round(3)

print("Performance by Shipment Complexity:")
print(performance_comparison)
#                           is_late  days_late
# has_multiple_categories                     
# False                       0.320       2.1  ← Simple shipments
# True                        0.380       3.2  ← Complex shipments

Risk Insights:

If multi-category shipments perform worse: Focus improvement efforts on complex fulfillment
If performance is similar: Complexity doesn't inherently create delays
Identify patterns: Which category combinations are most problematic?

4. 💰 Cost and Efficiency Planning

Cost Structure Implications:

# Cost analysis framework
estimated_costs = {
    'single_category': 100,    # Base cost per shipment
    'multi_category': 135,     # 35% higher due to complexity
    'coordination_overhead': 15  # Additional coordination costs
}

total_shipments = 10000
multi_category_count = total_shipments * (multi_category_percentage / 100)
single_category_count = total_shipments - multi_category_count

total_cost = (single_category_count * estimated_costs['single_category'] + 
              multi_category_count * estimated_costs['multi_category'])

print(f"Estimated fulfillment cost impact:")
print(f"Multi-category premium: {(estimated_costs['multi_category'] / estimated_costs['single_category'] - 1) * 100:.0f}%")

5. 🎯 Strategic Business Decisions

Product Mix Strategy:

High multi-category %: Consider bundling strategies to reduce complexity
Customer segmentation: Different service levels for simple vs. complex orders
Pricing strategy: Potential complexity-based shipping fees

Inventory Management:

Co-location planning: Store frequently combined categories together
Safety stock: Higher buffers for categories in mixed shipments
Supplier coordination: Align delivery schedules across categories

6. 📈 Competitive Advantage Opportunities

Market Positioning:

# Competitive analysis framework
if multi_category_percentage > industry_average:
    opportunity = "Differentiation through complex order handling"
    investment_focus = "Advanced fulfillment technology"
else:
    opportunity = "Cost leadership through simplicity"
    investment_focus = "Streamlined single-category processes"

Innovation Opportunities:

Technology investment: Automated systems for multi-category picking
Process innovation: Streamlined workflows for common category combinations
Service differentiation: Faster fulfillment of complex orders as competitive advantage

Sample Management Dashboard:

# Key metrics for management reporting
dashboard_metrics = {
    'total_shipments': comprehensive_analysis['shipID'].nunique(),
    'multi_category_percentage': multi_category_percentage,
    'avg_categories_per_complex_shipment': 2.3,  # Example
    'performance_impact': '6% higher late rate for multi-category',
    'cost_impact': '35% higher fulfillment cost',
    'top_category_combinations': ['Machines + Liquids', 'Liquids + Marketables']
}

print("ZappTech Shipment Complexity Dashboard:")
for metric, value in dashboard_metrics.items():
    print(f"• {metric.replace('_', ' ').title()}: {value}")

Action Items for Management:

🎯 Immediate: Benchmark multi-category % against industry standards
📊 Short-term: Analyze performance differences between simple and complex shipments
🏭 Medium-term: Optimize fulfillment processes based on complexity patterns
💰 Long-term: Consider strategic changes to product mix or customer segmentation

Bottom Line: The multi-category percentage is a key operational KPI that directly impacts cost structure, service quality, and competitive positioning. It's not just a data point—it's a strategic compass for operational excellence.

Name		Name	Last commit message	Last commit date
README_files/libs		README_files/libs
.gitignore		.gitignore
README.html		README.html
README.md		README.md
data_manipulation_challenge.ipynb		data_manipulation_challenge.ipynb

Latest commit

History

Folders and files

Repository files navigation

dataManipulationChallenge

Load the shipment data

Load product line data

Reduce dataset size for faster processing (4,000 rows instead of 96,805 rows)

Simple assignment - calculate if shipment was late

Understanding Data Types (dtypes) in Pandas

What is the dtype of actualShipDate?

How to Check Data Types Using Code

Why Same Data Types Matter for Date Comparisons

Critical Reasons:

What Goes Wrong with Mixed Types:

Question 2: String vs Date Comparison

The Problem with String Date Comparisons

The Dates in Question:

More Examples of String vs Date Comparison Problems

Common Problematic Scenarios:

Real-World Impact:

Best Practices:

Quick Fix:

Question 3: Debug This Code

🐛 The Buggy Code

🔍 Running the Buggy Code

🧐 Identifying the Bug

🚫 What's Wrong with the lateStatement Assignment:

Problem 1: Column Doesn't Exist Yet

Problem 2: Wrong DataFrame Reference

Problem 3: Single Evaluation Instead of Vectorized

Problem 4: Missing Lambda Function

🔑 Key Principles Violated:

✅ The Corrected Code

Solution 1: Using numpy.where() with lambda

Solution 2: Using pandas.Series.map() with lambda

Solution 3: Two-step approach (clearest for beginners)

📊 Testing the Fixed Code

🎯 Key Lessons

Query rows for late shipments and filter to keep specific columns

Question 1: Query vs Boolean Indexing

What's the difference between .query('is_late == True') and [df['is_late'] == True]?

Method 1: Using .query() (String-based)

Method 2: Using Boolean Indexing (Bracket notation)

Key Differences:

Which approach is more readable and why?

Example Comparison:

Question 2: Additional Row Querying

Can you show an example of using a variable like late_threshold to query rows for shipments that are at least late_threshold days late?

Example: Query shipments that are at least 5 days late

Alternative Methods:

More Complex Variable Usage:

Key Points about Variables in .query():

Best Practice:

Create a cleaner dataset by dropping unnecessary columns

Question 1: Drop vs Filter Strategies

What's the difference between .drop(columns=['quantity']) and .filter() with a list of columns you want to keep?

Method 1: .drop(columns=[]) - Exclusion Strategy

Method 2: .filter() - Inclusion Strategy

Key Differences:

When would you choose to drop columns vs filter to keep specific columns?

🗑️ Use .drop() when:

🎯 Use .filter() when:

Real-World Example Comparison:

Question 2: Handling Missing Data

What happens if you use .dropna() without specifying subset? How is this different from .dropna(subset=['plannedShipDate', 'actualShipDate'])?

Method 1: .dropna() without subset - Checks ALL columns

Method 2: .dropna(subset=[...]) - Checks SPECIFIC columns

Practical Impact Demonstration:

Why might you want to be selective about which columns to check for missing values?

🎯 Reason 1: Preserve Important Data

🔍 Reason 2: Column Relevance to Analysis

📊 Reason 3: Different Data Quality Requirements

⚡ Reason 4: Performance and Data Volume

Best Practices:

Sort by days late (worst first)

Understanding Pandas Sorting

Difference between ascending=False and ascending=True

How to sort by multiple columns

Why do we use .reset_index(drop=True) after sorting?

What's the difference between `.query('is_late == True')` and `[df['is_late'] == True]`?

Method 1: Using `.query()` (String-based)

Can you show an example of using a variable like `late_threshold` to query rows for shipments that are at least `late_threshold` days late?

Key Points about Variables in `.query()`:

What's the difference between `.drop(columns=['quantity'])` and `.filter()` with a list of columns you want to keep?

Method 1: `.drop(columns=[])` - Exclusion Strategy

Method 2: `.filter()` - Inclusion Strategy

🗑️ Use `.drop()` when:

🎯 Use `.filter()` when:

What happens if you use `.dropna()` without specifying subset? How is this different from `.dropna(subset=['plannedShipDate', 'actualShipDate'])`?

Method 1: `.dropna()` without subset - Checks ALL columns

Method 2: `.dropna(subset=[...])` - Checks SPECIFIC columns

Difference between `ascending=False` and `ascending=True`

Why do we use `.reset_index(drop=True)` after sorting?

The solution: `.reset_index(drop=True)`

`drop=True` vs `drop=False`:

Why does `sum()` work on boolean values? What does it count?

What does `sum()` count on boolean values?

Understanding the `.agg()` output in our example:

Why does your professor think we should use `how='left'` in most cases?

What happens if there are duplicate `partID` values in the `product_line_df`?

What does `.groupby('prodCategory')` actually do? How does it "split" the data?

Why do we need to use `.agg()` after grouping? What happens if you don't?

Explore grouping by `['shipID', 'prodCategory']`? What question does this answer versus grouping by `'prodCategory'` alone?

Single-Level: `.groupby('prodCategory')`

Multi-Level: `.groupby(['shipID', 'prodCategory'])`

How does grouping by `['shipID', 'prodCategory']` differ from grouping by just `'prodCategory'`?