NumPy and pandas — the analysis workflow

NumPy in one minute

NumPy provides fast arrays and vectorised maths — operations apply to whole arrays without Python loops. pandas is built on it.

import numpy as np
prices = np.array([32.0, 55.0, 18.5, 79.0])
qty = np.array([3, 1, 10, 2])
print(prices * qty, (prices * qty).sum())
print(prices.mean(), np.median(prices), prices.std(ddof=1).round(2))

[ 96.  55. 185. 158.] 494.0
46.125 43.5 26.6

ddof=1 gives the sample standard deviation (divide by n − 1), matching Excel's STDEV.S.

Loading data

import pandas as pd
pd.set_option("display.width", 120)

orders = pd.read_csv("retail/orders.csv", parse_dates=["order_date"])
items = pd.read_csv("retail/order_items.csv")
products = pd.read_csv("retail/products.csv")
print(orders.shape)
print(orders.dtypes)
print(orders.head(3))

(37651, 9)
order_id                   int64
customer_id                int64
store_id                   int64
order_date        datetime64[us]
channel                      str
status                       str
payment_method               str
discount_pct             float64
campaign_id              float64
dtype: object
   order_id  customer_id  store_id  ... payment_method discount_pct campaign_id
0         1         1073         9  ...           card          0.0         NaN
1         2         3881         2  ...           cash          0.0         NaN
2         3           23         3  ...         wallet          0.0         NaN

[3 rows x 9 columns]

read_excel("file.xlsx", sheet_name="orders") reads Excel (needs openpyxl). Always check .shape, .dtypes and .head() first — the pandas version of profiling.

Selecting and filtering

completed_2025 = orders[(orders["status"] == "completed") & (orders["order_date"].dt.year == 2025)]
print(len(completed_2025))
print(completed_2025[["order_id", "store_id", "channel"]].head(3))
print(orders["status"].value_counts())

12713
       order_id  store_id channel
23679     23680         2   store
23680     23681         2   store
23681     23682         2   store
status
completed    34258
returned      2265
cancelled     1128
Name: count, dtype: int64

Combine conditions with & (and), | (or), ~ (not), each in parentheses — pandas' version of WHERE. .isin([...]) = IN, .between(a, b) = BETWEEN, .str.contains("Pro") = LIKE '%Pro%'.

Merging (joins)

lines = items.merge(orders, on="order_id", how="inner", validate="many_to_one")
lines = lines.merge(products[["product_id", "category", "unit_cost"]], on="product_id", how="left", validate="many_to_one")
lines["net_revenue"] = lines["quantity"] * lines["unit_price"] * (1 - lines["discount_pct_x"])
print(lines.shape)
print(lines.columns.tolist()[:12])

(75600, 17)
['order_item_id', 'order_id', 'product_id', 'quantity', 'unit_price', 'discount_pct_x', 'customer_id', 'store_id', 'order_date', 'channel', 'status', 'payment_method']

validate="many_to_one" makes pandas raise an error if the right table has duplicate keys — built-in fan-out protection. Both tables had a discount_pct column, so pandas added suffixes _x (left) and _y (right); in this dataset line and order discounts are equal, but rename columns deliberately in real work.

Grouping and aggregation

done = lines[lines["status"] == "completed"]
by_year = done.groupby(done["order_date"].dt.year).agg(
    revenue=("net_revenue", "sum"),
    orders=("order_id", "nunique"),
    units=("quantity", "sum"),
)
by_year["aov"] = by_year["revenue"] / by_year["orders"]
print(by_year.round(1))

              revenue  orders  units   aov
order_date                                
2023         917709.7   10268  29158  89.4
2024        1017608.4   11277  32052  90.2
2025        1144526.5   12713  35554  90.0

Named aggregation (new_name=(column, function)) is the pandas GROUP BY. These are the same totals you computed in SQL — 2025 net revenue ≈ 1.14M with ~12.7k orders.

pivot = done[done["order_date"].dt.year == 2025].pivot_table(
    index="category", columns="channel", values="net_revenue", aggfunc="sum").round(0)
pivot["share_online"] = (pivot["online"] / pivot.sum(axis=1)).round(3)
print(pivot.sort_values("store", ascending=False))

channel           online     store  share_online
category                                        
Electronics     115383.0  448428.0         0.205
Home & Kitchen   29261.0  120103.0         0.196
Beauty           29905.0  113057.0         0.209
Grocery          32655.0  108381.0         0.232
Apparel          22346.0   68912.0         0.245
Sports           12243.0   43853.0         0.218

Missing values and duplicates

raw = pd.read_csv("retail/customers_raw.csv")
print(raw.isna().sum()[raw.isna().sum() > 0])
print("rows:", len(raw), "exact duplicate rows:", raw.duplicated().sum(), "duplicate ids:", raw["customer_id"].duplicated().sum())
clean = raw.drop_duplicates()
print("after drop_duplicates:", len(clean))

email    69
dtype: int64
rows: 645 exact duplicate rows: 6 duplicate ids: 45
after drop_duplicates: 639

Only 6 rows are exact duplicates, but 45 rows repeat a customer_id: the other copies differ in city spelling or date format. That is why cleaning comes before de-duplicating on the key (drop_duplicates(subset=["customer_id"]) after standardising). .isna() finds missing values; decide per column whether to drop, fill (.fillna()), or keep and flag. drop_duplicates(subset=[...], keep="first") controls what counts as a duplicate.

Cleaning text and dates

clean = clean.copy()
clean["city"] = clean["city"].str.strip().str.title()
print(clean["city"].value_counts().head(4))

def parse_mixed(s):
    for fmt in ("%Y-%m-%d", "%Y/%m/%d", "%d.%m.%Y"):
        try:
            return pd.to_datetime(s, format=fmt)
        except ValueError:
            continue
    return pd.NaT

clean["signup_date"] = clean["signup_date"].map(parse_mixed)
print(clean["signup_date"].isna().sum(), "unparsed dates")
print(clean[["customer_id", "city", "signup_date"]].head(3))

city
Berlin     72
Warsaw     71
Ankara     70
Tbilisi    69
Name: count, dtype: int64
0 unparsed dates
   customer_id      city signup_date
0         3584  Sumqayit  2021-10-12
1          617  Istanbul  2024-08-03
2          197    Berlin  2022-09-27

Explicit formats beat guessing: pd.to_datetime(..., dayfirst=True) can silently misread ambiguous dates like 04.03.2025.

Feature creation and date manipulation

cust = done.groupby("customer_id").agg(
    first_order=("order_date", "min"), last_order=("order_date", "max"),
    frequency=("order_id", "nunique"), monetary=("net_revenue", "sum"))
analysis_date = pd.Timestamp("2025-12-31")
cust["recency_days"] = (analysis_date - cust["last_order"]).dt.days
cust["tenure_days"] = (cust["last_order"] - cust["first_order"]).dt.days
cust["avg_order_value"] = cust["monetary"] / cust["frequency"]
cust["first_month"] = cust["first_order"].dt.to_period("M")
print(cust.sort_values("monetary", ascending=False).head(3).round(1))

            first_order last_order  ...  avg_order_value  first_month
customer_id                         ...                              
2909         2023-01-02 2025-12-29  ...            117.8      2023-01
3680         2023-01-16 2025-12-31  ...             84.2      2023-01
3657         2023-02-25 2025-12-18  ...            129.9      2023-02

[3 rows x 8 columns]

These customer-level features feed RFM segmentation and churn models later.

Statistical analysis

print(cust[["frequency", "monetary", "recency_days"]].describe().round(1))
print(cust[["frequency", "monetary", "recency_days"]].corr().round(2))

       frequency  monetary  recency_days
count     3474.0    3474.0        3474.0
mean         9.9     886.5         165.3
std          9.9    1006.1         218.5
min          1.0       2.4           0.0
25%          3.0     172.6          24.0
50%          7.0     550.2          71.5
75%         13.0    1234.2         211.0
max         86.0    7536.1        1094.0
              frequency  monetary  recency_days
frequency          1.00      0.89         -0.42
monetary           0.89      1.00         -0.37
recency_days      -0.42     -0.37          1.00

describe() gives count, mean, standard deviation, min, quartiles and max — note how the mean of monetary exceeds its median (50%): a right-skewed distribution, typical of customer value. Frequency and monetary are strongly correlated; recency is negatively correlated with both (recent buyers buy more often).

Practice

Summary

• Inspect every DataFrame (shape, dtypes, head) before analysis.
• Boolean filters = WHERE; merge with validate= = safe joins; groupby().agg() = GROUP BY; pivot_table = pivots.
• Handle missing values and duplicates deliberately; parse dates with explicit formats.
• Build customer-level features with dates; summarise with describe() and corr().