Pandas Chained Indexing — How One Assignment Cost $30k

import pandas as pd

# --- SERIES: A single labelled column ---
# Think of this as one column of a product inventory sheet
product_prices = pd.Series(
    [29.99, 49.99, 9.99, 99.99],
    index=['notebook', 'headphones', 'pen', 'keyboard'],  # meaningful labels, not just 0,1,2,3
    name='price_usd'
)

print('=== SERIES ===')
print(product_prices)
print(f'\nType: {type(product_prices)}')
print(f'Look up headphones price: ${product_prices["headphones"]}')

# --- DATAFRAME: Multiple columns sharing the same index ---
# Build one from a dictionary — each key becomes a column
product_data = {
    'price_usd':   [29.99, 49.99, 9.99, 99.99],
    'stock_count': [150,   43,    500,  28],
    'category':    ['stationery', 'electronics', 'stationery', 'electronics']
}

product_df = pd.DataFrame(
    product_data,
    index=['notebook', 'headphones', 'pen', 'keyboard']  # same meaningful index
)

print('\n=== DATAFRAME ===')
print(product_df)
print(f'\nType: {type(product_df)}')

# A DataFrame column IS a Series
price_column = product_df['price_usd']
print(f'\nColumn type: {type(price_column)}')  # confirms it's a Series
print(f'Most expensive item: {price_column.idxmax()} at ${price_column.max()}')

import pandas as pd
import io

# Simulating a CSV file in memory so this example is fully self-contained
# In real life you'd use: pd.read_csv('sales_data.csv')
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,,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
"""

# parse_dates tells Pandas to convert that column to datetime, not leave it as a string
sales_df = pd.read_csv(
    io.StringIO(raw_csv),
    parse_dates=['order_date']
)

# --- STEP 1: Size ---
print('=== Shape (rows, columns) ===')
print(sales_df.shape)  # (6, 6)

# --- STEP 2: Column types ---
print('\n=== Data Types ===')
print(sales_df.dtypes)

# --- STEP 3: First look at values ---
print('\n=== First 3 Rows ===')
print(sales_df.head(3))

# --- STEP 4: Full health summary (non-null counts, memory) ---
print('\n=== Info ===')
sales_df.info()

# --- STEP 5: Where are the nulls? ---
print('\n=== Missing Values Per Column ===')
print(sales_df.isnull().sum())

# Fix: fill the missing amount with the column mean (a common real-world approach)
sales_df['amount_usd'] = sales_df['amount_usd'].fillna(sales_df['amount_usd'].mean())
print('\n=== After Filling Missing Amount ===')
print(sales_df[['order_id', 'customer_name', 'amount_usd']])

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']])