Pandas Chained Indexing — How One Assignment Cost $30k

import pandas as pd
import io

raw_csv = """
order_id,customer,amount,order_date
1001,Alice,250.00,2024-01-15
1002,Bob,89.50,2024-01-17
1003,Alice,,2024-02-03
1004,Dana,430.00,2024-02-11
1005,Bob,175.25,2024-03-05
"""
df = pd.read_csv(io.StringIO(raw_csv), parse_dates=['order_date'])

print('1. Shape:', df.shape)
print('\n2. Dtypes:')
print(df.dtypes)
print('\n3. Head:')
print(df.head())
print('\n4. Info:')
df.info()
print('\n5. Null count:')
print(df.isnull().sum())
print('\nBonus: Describe:')
print(df.describe())

import pandas as pd
import io

raw_csv = """
order_id,customer_name,order_date,amount_usd,region,is_returned
1001,Alice Martin,2024-01-15,250.00,North,False
1002,Bob Chen,2024-01-17,89.50,South,False
1003,Alice Martin,2024-02-03,310.95,North,True
1004,Dana Patel,2024-02-11,430.00,East,False
1005,Bob Chen,2024-03-05,175.25,South,True
1006,Eve Torres,2024-03-18,610.00,West,False
"""

sales_df = pd.read_csv(io.StringIO(raw_csv), parse_dates=['order_date'])

# Set order_id as the index so .loc makes semantic sense
sales_df = sales_df.set_index('order_id')

print('=== Full DataFrame ===')
print(sales_df)

# --- iloc: positional (0-based integers, like a list) ---
print('\n=== .iloc: First 2 rows, first 3 columns (positional) ===')
print(sales_df.iloc[0:2, 0:3])  # rows 0-1, columns 0-2

# --- loc: label-based (use actual index values and column names) ---
print('\n=== .loc: Row with order_id 1004, two specific columns ===')
print(sales_df.loc[1004, ['customer_name', 'amount_usd']])

# --- Boolean filtering: the real power move ---
# Step 1: build a boolean mask — this is a Series of True/False values
high_value_mask = sales_df['amount_usd'] > 200
print('\n=== Boolean mask (True where amount > $200) ===')
print(high_value_mask)

# Step 2: pass the mask to .loc to select only matching rows
high_value_orders = sales_df.loc[high_value_mask]
print('\n=== Orders over $200 ===')
print(high_value_orders)

# Combine multiple conditions with & (AND) or | (OR)
# Each condition MUST be wrapped in parentheses — Python operator precedence gotcha!
north_high_value = sales_df.loc[
    (sales_df['amount_usd'] > 200) & (sales_df['region'] == 'North')
]
print('\n=== North region orders over $200 ===')
print(north_high_value)

# Real-world pattern: not returned AND high value — the good orders
good_orders = sales_df.loc[
    (sales_df['is_returned'] == False) & (sales_df['amount_usd'] > 150)
]
print('\n=== High-value, non-returned orders ===')
print(good_orders[['customer_name', 'amount_usd', 'region']])

import pandas as pd
import io

raw_csv = """
order_id,customer_name,order_date,amount_usd,region,is_returned
1001,Alice Martin,2024-01-15,250.00,North,False
1002,Bob Chen,2024-01-17,89.50,South,False
1003,Alice Martin,2024-02-03,310.95,North,True
1004,Dana Patel,2024-02-11,430.00,East,False
1005,Bob Chen,2024-03-05,175.25,South,True
1006,Eve Torres,2024-03-18,610.00,West,False
1007,Alice Martin,2024-03-22,90.00,North,False
1008,Dana Patel,2024-03-30,220.00,East,False
"""

sales_df = pd.read_csv(io.StringIO(raw_csv), parse_dates=['order_date'])

# --- Basic groupby: total revenue per region ---
# split by 'region', apply sum to 'amount_usd', combine into a Series
revenue_by_region = sales_df.groupby('region')['amount_usd'].sum()
print('=== Total Revenue by Region ===')
print(revenue_by_region.sort_values(ascending=False))

# --- Multiple aggregations in one pass with .agg() ---
# For each customer: count their orders, total spend, average order, and biggest order
customer_summary = sales_df.groupby('customer_name')['amount_usd'].agg(
    order_count='count',   # how many orders
    total_spent='sum',     # lifetime value
    avg_order='mean',      # average basket size
    largest_order='max'    # biggest single purchase
).round(2)  # tidy up the decimals

print('\n=== Customer Purchase Summary ===')
print(customer_summary.sort_values('total_spent', ascending=False))

# --- Groupby with multiple columns ---
# Revenue and return rate by region
region_stats = sales_df.groupby('region').agg(
    total_revenue=('amount_usd', 'sum'),
    order_count=('order_id', 'count'),
    returns=('is_returned', 'sum')  # True counts as 1, False as 0
)
region_stats['return_rate_pct'] = (
    (region_stats['returns'] / region_stats['order_count']) * 100
).round(1)

print('\n=== Region Performance Dashboard ===')
print(region_stats.sort_values('total_revenue', ascending=False))

# --- transform: add a group-level stat back to the original DataFrame ---
# This is powerful: add each customer's total spend as a new column on every row
sales_df['customer_lifetime_value'] = sales_df.groupby('customer_name')['amount_usd'].transform('sum')
print('\n=== Original DF with Customer LTV added ===')
print(sales_df[['customer_name', 'amount_usd', 'customer_lifetime_value']].sort_values('customer_name'))

import pandas as pd
import io

# Customers table
customers_csv = """
customer_id,name,region,join_date
C001,Alice Martin,North,2023-06-01
C002,Bob Chen,South,2023-07-15
C003,Dana Patel,East,2023-08-20
C004,Eve Torres,West,2023-09-10
C005,Frank Lee,North,2023-10-01
"""

# Orders table (some customers have no orders, some are not in customer list)
orders_csv = """
order_id,customer_id,amount,order_date
1001,C001,250.00,2024-01-15
1002,C002,89.50,2024-01-17
1003,C001,310.95,2024-02-03
1004,C003,430.00,2024-02-11
1006,C004,610.00,2024-03-18
1007,C006,99.00,2024-03-22
"""

customers = pd.read_csv(io.StringIO(customers_csv), parse_dates=['join_date'])
orders = pd.read_csv(io.StringIO(orders_csv), parse_dates=['order_date'])

print('=== Customers ===')
print(customers)
print('\n=== Orders ===')
print(orders)

# --- INNER JOIN: only customers with orders, and only orders with customers ---
# Default is inner, so we must specify how='inner' (or rely on default)
inner_joined = pd.merge(orders, customers, on='customer_id', how='inner')
print('\n=== Inner Join (orders x customers) ===')
print(inner_joined[['order_id', 'name', 'amount', 'region']])

# --- LEFT JOIN: keep all orders, even if customer missing ---
left_joined = pd.merge(orders, customers, on='customer_id', how='left')
print('\n=== Left Join (keep all orders) ===')
print(left_joined[['order_id', 'name', 'amount', 'region']])

# --- OUTER JOIN: keep all customers and all orders ---
outer_joined = pd.merge(customers, orders, on='customer_id', how='outer')
print('\n=== Outer Join (all customers + all orders) ===')
print(outer_joined[['name', 'order_id', 'amount']])

# --- concat: stacking multiple months of orders ---
orders_jan = orders[orders['order_date'].dt.month == 1].copy()
orders_feb = orders[orders['order_date'].dt.month == 2].copy()
orders_mar = orders[orders['order_date'].dt.month == 3].copy()

all_orders = pd.concat([orders_jan, orders_feb, orders_mar])
print(f'\n=== Concat monthly orders (total rows: {len(all_orders)}) ===')
print(all_orders[['order_id', 'amount', 'order_date']])

// io.thecodeforge
import pandas as pd

df = pd.read_csv("orders_2024.csv")
# Check missing counts per column before any drop
print(df.isnull().sum())

# Only drop rows where 'revenue' is missing, not entire row
clean_df = df.dropna(subset=["revenue"]).copy()

# Fill forecast gaps with forward fill, only after sorting
clean_df = clean_df.sort_values(["date", "store_id"])
clean_df["forecast"] = clean_df["forecast"].fillna(method="ffill")

# Remove exact duplicates, keep first occurrence
clean_df = clean_df.drop_duplicates(subset=["order_id"], keep="first")
print(clean_df.shape)  # (95000, 8) — confirmed clean

// io.thecodeforge
import pandas as pd

def clean_negative_revenue(df):
    return df[df["revenue"] >= 0]

def add_profit_margin(df):
    df["margin_pct"] = (df["revenue"] - df["cost"]) / df["revenue"] * 100
    return df

def summarize_by_region(df):
    return df.groupby("region")["margin_pct"].agg(["mean", "std"])

df = pd.read_csv("sales.csv")
result = (df.pipe(clean_negative_revenue)
            .pipe(add_profit_margin)
            .pipe(summarize_by_region))
print(result.head())