pandas for Excel users: VLOOKUP, pivot tables and SUMIFS in Python

uv add pandas openpyxl     # or: python -m pip install pandas openpyxl

import pandas as pd

sales = pd.read_excel("sales.xlsx", sheet_name="Orders")
products = pd.read_excel("sales.xlsx", sheet_name="Products")

sales.head()        # first five rows, like glancing at the top of a sheet
sales.info()        # column names, types and missing values
sales.describe()    # quick statistics for numeric columns

# Excel: filter the Region column to "North" and Amount greater than 100
north_big = sales[(sales["region"] == "North") & (sales["amount"] > 100)]

# Several values, like ticking boxes in a filter drop-down
selected = sales[sales["region"].isin(["North", "West"])]

sales["revenue"] = sales["quantity"] * sales["unit_price"]

# Excel IF: =IF(revenue > 500, "Large", "Standard")
sales["size"] = sales["revenue"].where(sales["revenue"] <= 500, "Large")
sales.loc[sales["revenue"] <= 500, "size"] = "Standard"

import numpy as np

conditions = [sales["revenue"] >= 1000, sales["revenue"] >= 500]
choices = ["Large", "Medium"]
sales["size"] = np.select(conditions, choices, default="Small")

# Bring product name and category into the sales table, matched on product_id
sales = sales.merge(
    products[["product_id", "product_name", "category"]],
    on="product_id",
    how="left",            # keep every sale, like VLOOKUP returning #N/A
    validate="many_to_one" # raises an error if products has duplicate IDs
)

missing = sales[sales["product_name"].isna()]   # your #N/A rows

by_region = (
    sales.groupby("region", as_index=False)
         .agg(revenue=("revenue", "sum"),
              orders=("order_id", "nunique"),
              avg_order=("revenue", "mean"))
         .sort_values("revenue", ascending=False)
)

pivot = pd.pivot_table(
    sales,
    index="region",
    columns="category",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
    margins=True,           # adds the Grand Total row and column
    margins_name="Total"
)

sales["order_date"] = pd.to_datetime(sales["order_date"], dayfirst=True)
sales["month"] = sales["order_date"].dt.to_period("M")
sales["weekday"] = sales["order_date"].dt.day_name()

monthly = sales.groupby("month", as_index=False)["revenue"].sum()
monthly["change_pct"] = monthly["revenue"].pct_change().mul(100).round(1)

sales = sales.drop_duplicates(subset="order_id")          # Remove Duplicates
sales["customer"] = sales["customer"].str.strip().str.title()  # TRIM and PROPER
sales["discount"] = sales["discount"].fillna(0)          # blanks become zero
sales = sales.rename(columns={"Cust Ref": "customer_ref"})

with pd.ExcelWriter("monthly_report.xlsx") as writer:
    by_region.to_excel(writer, sheet_name="By region", index=False)
    pivot.to_excel(writer, sheet_name="Pivot")
    monthly.to_excel(writer, sheet_name="Monthly", index=False)