Python Pandas for Data Analysts — DataFrames, Groupby, Merge, Apply & Real-World Workflows

import pandas as pd
import numpy as np          # usually imported alongside pandas

# Load a CSV file
df = pd.read_csv('sales_data.csv')

# Load an Excel file (requires openpyxl: pip install openpyxl)
df = pd.read_excel('sales_report.xlsx', sheet_name='Sheet1')

# Load from a SQL query (requires sqlalchemy)
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:password@localhost/dbname')
df = pd.read_sql("SELECT * FROM orders WHERE status = 'completed'", engine)

# Load from a URL (e.g. public CSV on GitHub or a data portal)
url = 'https://example.com/data/sales.csv'
df = pd.read_csv(url)

# Always start by understanding your data
print(df.shape)          # (rows, columns)
print(df.dtypes)         # column data types
print(df.head(10))       # first 10 rows
print(df.info())         # shape + dtypes + null counts together
print(df.describe())     # statistics for numeric columns

# Select a single column (returns a Series)
revenue = df['Revenue']

# Select multiple columns (returns a DataFrame)
subset = df[['OrderID', 'Revenue', 'Quantity']]

# Select rows by position (iloc: integer location)
first_row = df.iloc[0]           # single row
rows_1_to_5 = df.iloc[0:5]       # slicing by index position
cell = df.iloc[2, 3]             # row 2, column 3

# Select rows by label (loc: label location — use when you have a named index)
df_named = df.set_index('OrderID')
row = df_named.loc['ORD-1001']

# Filter rows with boolean conditions
online_orders = df[df['Channel'] == 'Online']
high_value = df[df['Revenue'] > 50000]

# Multiple conditions — use & (and) | (or), parentheses are required
online_high = df[(df['Channel'] == 'Online') & (df['Revenue'] > 50000)]
north_or_west = df[(df['Region'] == 'North') | (df['Region'] == 'West')]

# isin — check if value is in a list
premium = df[df['Tier'].isin(['Gold', 'Platinum'])]

# Exclude rows — negate with ~
not_cancelled = df[~(df['Status'] == 'Cancelled')]

# String filtering
delhi_customers = df[df['City'].str.contains('Delhi', case=False, na=False)]
emails = df[df['Email'].str.endswith('@gmail.com', na=False)]

# Null filtering
missing_phone = df[df['Phone'].isna()]
has_phone = df[df['Phone'].notna()]

# query() method — readable for complex filters
result = df.query("Revenue > 10000 and Channel == 'Online' and Region != 'South'")

# Check missing values
print(df.isnull().sum())                     # count per column
print(df.isnull().sum() / len(df) * 100)    # % missing per column

# Drop rows where ANY column is null
df_clean = df.dropna()

# Drop rows only where specific columns are null
df_clean = df.dropna(subset=['Revenue', 'OrderID'])

# Fill missing values
df['Discount'] = df['Discount'].fillna(0)                 # fill with a constant
df['City'] = df['City'].fillna('Unknown')
df['Revenue'] = df['Revenue'].fillna(df['Revenue'].mean()) # fill with mean
df['Revenue'] = df['Revenue'].fillna(method='ffill')       # forward fill

# Check for duplicates
print(df.duplicated().sum())               # total duplicate rows
print(df.duplicated(subset=['OrderID']).sum())  # duplicate on one column

# Drop duplicates
df = df.drop_duplicates()                              # fully identical rows
df = df.drop_duplicates(subset=['OrderID'])            # keep first occurrence
df = df.drop_duplicates(subset=['OrderID'], keep='last')  # keep most recent

# Fix data types
df['OrderDate'] = pd.to_datetime(df['OrderDate'])
df['Revenue'] = pd.to_numeric(df['Revenue'], errors='coerce')  # coerce turns invalid to NaN
df['Status'] = df['Status'].str.strip().str.lower()  # strip whitespace, lowercase
df['City'] = df['City'].str.strip().str.title()      # strip whitespace, title case

# Arithmetic on existing columns
df['Profit'] = df['Revenue'] - df['COGS']
df['Margin%'] = (df['Profit'] / df['Revenue']) * 100

# np.where — like Excel IF()
df['Is_High_Value'] = np.where(df['Revenue'] > 50000, 'High', 'Low')

# np.select — like Excel IFS() for multiple conditions
conditions = [
    df['Revenue'] >= 100000,
    df['Revenue'] >= 50000,
    df['Revenue'] >= 10000,
]
choices = ['Platinum', 'Gold', 'Silver']
df['Tier'] = np.select(conditions, choices, default='Bronze')

# apply() with a function — row-level or column-level custom logic
def categorise_margin(m):
    if m >= 40:   return 'High'
    elif m >= 20: return 'Medium'
    return 'Low'

df['Margin_Category'] = df['Margin%'].apply(categorise_margin)

# lambda for quick one-liners
df['Revenue_K'] = df['Revenue'].apply(lambda x: round(x / 1000, 1))

# apply() across multiple columns (axis=1 = row-wise)
df['Label'] = df.apply(
    lambda row: f"{row['City']} — {row['Tier']}", axis=1
)

# Basic groupby + aggregation
region_summary = df.groupby('Region')['Revenue'].sum()
region_summary = df.groupby('Region')['Revenue'].agg(['sum', 'mean', 'count'])

# Group by multiple columns
channel_region = df.groupby(['Region', 'Channel'])['Revenue'].sum()

# Multiple aggregations on multiple columns using agg()
summary = df.groupby('Region').agg(
    Total_Revenue=('Revenue', 'sum'),
    Avg_Revenue=('Revenue', 'mean'),
    Order_Count=('OrderID', 'count'),
    Avg_Margin=('Margin%', 'mean')
).reset_index()
# reset_index() converts Region from index back to a regular column

# Transform: add group-level aggregation back as a column (no row reduction)
df['Region_Total'] = df.groupby('Region')['Revenue'].transform('sum')
df['Revenue_vs_Region'] = df['Revenue'] / df['Region_Total'] * 100  # % of region total

# Filter groups — e.g., regions with more than 100 orders
big_regions = df.groupby('Region').filter(lambda x: len(x) > 100)

# Sort results
summary = summary.sort_values('Total_Revenue', ascending=False)

# Top N per group (e.g., top 3 products per region)
top3 = (
    df.sort_values('Revenue', ascending=False)
    .groupby('Region')
    .head(3)
)

# Sample data
orders = pd.DataFrame({'OrderID': [1,2,3,4], 'CustomerID': [101,102,101,103], 'Revenue': [5000,8000,3000,12000]})
customers = pd.DataFrame({'CustomerID': [101,102,104], 'Name': ['Priya','Amit','Raj'], 'City': ['Delhi','Mumbai','Pune']})

# Inner join — only rows with matching keys in BOTH DataFrames
inner = pd.merge(orders, customers, on='CustomerID', how='inner')
# Result: orders 1, 2, 3 (CustomerID 103 not in customers, 104 not in orders)

# Left join — all rows from left (orders), matched rows from right (customers)
left = pd.merge(orders, customers, on='CustomerID', how='left')
# Result: all 4 orders; order 4 (CustomerID 103) gets NaN for Name and City

# Outer join — all rows from both
outer = pd.merge(orders, customers, on='CustomerID', how='outer')

# Join on columns with different names
merged = pd.merge(orders, customers,
                  left_on='CustomerID', right_on='CustID', how='left')

# Stack DataFrames vertically (append rows)
combined = pd.concat([df_jan, df_feb, df_mar], ignore_index=True)

# Stack DataFrames horizontally (join by position, rare)
wide = pd.concat([df_left, df_right], axis=1)

# Merge validation — catch data quality issues
validated = pd.merge(orders, customers, on='CustomerID', how='left',
                     validate='many_to_one')
# Raises MergeError if CustomerID is not unique in customers

# Parse dates on load (faster than converting later)
df = pd.read_csv('orders.csv', parse_dates=['OrderDate', 'ShipDate'])

# Manual conversion
df['OrderDate'] = pd.to_datetime(df['OrderDate'], dayfirst=True)   # for DD/MM/YYYY format

# Extract date parts
df['Year']    = df['OrderDate'].dt.year
df['Month']   = df['OrderDate'].dt.month
df['Quarter'] = df['OrderDate'].dt.quarter
df['DayName'] = df['OrderDate'].dt.day_name()      # 'Monday', 'Tuesday', ...
df['WeekNum'] = df['OrderDate'].dt.isocalendar().week

# Date arithmetic
df['Days_to_Ship'] = (df['ShipDate'] - df['OrderDate']).dt.days
df['Week_Ago'] = pd.Timestamp.today() - pd.Timedelta(weeks=1)
df['Last_30'] = df[df['OrderDate'] >= pd.Timestamp.today() - pd.Timedelta(days=30)]

# Resample: group by time period
df_daily = df.set_index('OrderDate').resample('D')['Revenue'].sum()
df_monthly = df.set_index('OrderDate').resample('ME')['Revenue'].sum()
df_weekly = df.set_index('OrderDate').resample('W')['Revenue'].sum()

# Rolling average
df['Revenue_7d_avg'] = (
    df.set_index('OrderDate')['Revenue']
    .rolling(window=7)
    .mean()
    .values
)

# pivot_table — equivalent of Excel pivot table
pivot = df.pivot_table(
    values='Revenue',
    index='Region',
    columns='Channel',
    aggfunc='sum',
    fill_value=0,
    margins=True          # adds row/column totals
)

# Cross-tabulation — counts instead of a sum
ct = pd.crosstab(df['Region'], df['Channel'])
ct_pct = pd.crosstab(df['Region'], df['Channel'], normalize='index') * 100  # row %

# melt — wide to long (unpivot)
# Wide: columns Jan, Feb, Mar each hold monthly revenue
wide = pd.DataFrame({'Product': ['A','B'], 'Jan': [100,200], 'Feb': [150,180], 'Mar': [120,210]})
long = pd.melt(wide, id_vars=['Product'], var_name='Month', value_name='Revenue')
# Long: columns Product, Month, Revenue — one row per product-month

# pivot — long to wide
wide_again = long.pivot(index='Product', columns='Month', values='Revenue').reset_index()

import pandas as pd
import numpy as np

# 1. Load and inspect
df = pd.read_csv('raw_orders.csv', parse_dates=['OrderDate'])
print(f"Loaded: {df.shape[0]} rows, {df.shape[1]} columns")
print(df.isnull().sum())

# 2. Clean
df = df.drop_duplicates(subset=['OrderID'])
df = df.dropna(subset=['OrderDate', 'Revenue'])
df['Revenue'] = pd.to_numeric(df['Revenue'], errors='coerce')
df['Channel'] = df['Channel'].str.strip().str.title()
df['City']    = df['City'].str.strip().str.title()
print(f"After cleaning: {df.shape[0]} rows")

# 3. Enrich
df['Profit']  = df['Revenue'] - df['COGS']
df['Margin%'] = (df['Profit'] / df['Revenue'] * 100).round(2)
df['Month']   = df['OrderDate'].dt.to_period('M')

tiers = [df['Revenue'] >= 50000, df['Revenue'] >= 20000, df['Revenue'] >= 5000]
df['Value_Tier'] = np.select(tiers, ['High','Medium','Low'], default='Micro')

# 4. Aggregate
monthly = (
    df.groupby('Month')
    .agg(Revenue=('Revenue','sum'), Orders=('OrderID','count'), AvgMargin=('Margin%','mean'))
    .reset_index()
)
monthly['MoM_Growth%'] = monthly['Revenue'].pct_change() * 100

by_channel = df.groupby('Channel').agg(
    Revenue=('Revenue','sum'), Orders=('OrderID','count')
).sort_values('Revenue', ascending=False)

# 5. Export
with pd.ExcelWriter('sales_analysis.xlsx', engine='openpyxl') as writer:
    monthly.to_excel(writer, sheet_name='Monthly Trend', index=False)
    by_channel.to_excel(writer, sheet_name='By Channel')
    df.to_excel(writer, sheet_name='Clean Data', index=False)

print("Report exported to sales_analysis.xlsx")