Analyze E-Commerce Data with NumPy

Generate, clean, and analyze a simulated e-commerce dataset with NumPy and pandas.

Introduction

30 Second Summary

Every online store sits on a mountain of data. Customer purchases, product performance, regional trends. The businesses that thrive are the ones that can turn raw numbers into clear answers.

In this project, you will build a complete data analysis pipeline that simulates an e-commerce business from scratch. You'll generate messy data, clean and merge it, and answer real business questions using NumPy and pandas.

What You'll Build

A Jupyter Notebook that generates three related data tables with NumPy, cleans and merges them with pandas, engineers new features using vectorized operations, and produces business insights through groupby aggregations and pivot tables.

By the end of this project, you'll have:

  • A self-generated 3-table dataset built entirely with NumPy random distributions.
  • A cleaned, merged, and feature-engineered analytical DataFrame produced with pandas.
  • A complete business analysis answering 5 questions using groupby, pivot tables, and vectorized NumPy computations.
  • Secret Mission: Add time-series resampling with rolling averages and month-over-month growth analysis.

Are there any prerequisites?

You should be comfortable with Python basics: variables, loops, functions, and list comprehensions. No prior experience with NumPy or pandas is needed.

Generate the Dataset with NumPy

Your environment is ready. Now it's time to build the raw material your analysis will run on: three related tables representing a simulated e-commerce business.

Instead of downloading a CSV, you'll generate data from scratch using NumPy's random distributions. This gives NumPy a first-class role in your workflow and lets you control exactly how "messy" the data is.

In this step, get ready to:
  • Generate a customers table with 500 rows using normal distributions and weighted random choices.
  • Generate products (50 rows) and orders (5000 rows) tables with conditional discount logic.
  • Introduce intentional data quality issues (nulls, duplicates, wrong dtypes) for cleaning practice.
Generate the customers table

The customers table simulates 500 users spread across four regions. You'll use a seeded random number generator so that everyone working through this project gets identical results.

  • In your notebook, click into a new empty cell below your imports cell.
  • Paste the following code to create the random number generator, build customer arrays, and assemble the DataFrame:
rng = np.random.default_rng(42)

# Customers table (500 rows)
n_customers = 500
customer_ids = np.arange(1, n_customers + 1)
regions = rng.choice(
    ['North', 'South', 'East', 'West'],
    size=n_customers,
    p=[0.3, 0.25, 0.2, 0.25]
)
signup_dates = pd.date_range('2023-01-01', periods=n_customers, freq='D').to_numpy()
rng.shuffle(signup_dates)
ages = rng.normal(35, 10, size=n_customers).astype(int)
ages = np.clip(ages, 18, 70)

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'region': regions,
    'signup_date': signup_dates,
    'age': ages.astype(float)
})

# Introduce messiness: null ages and duplicate rows
null_mask = rng.random(n_customers) < 0.05
customers.loc[null_mask, 'age'] = np.nan
duplicates = customers.sample(10, random_state=42)
customers = pd.concat([customers, duplicates], ignore_index=True)

What does this code do?

  • np.random.default_rng(42) creates a reproducible random number generator. The seed 42 ensures everyone gets the same "random" data.
  • rng.choice with the p parameter assigns regions with weighted probabilities (30% North, 25% South, etc.).
  • rng.normal(35, 10, ...) draws ages from a bell curve centered at 35, and np.clip forces them into a valid 18-70 range.
  • The messiness section sets roughly 5% of ages to NaN using a boolean mask, then appends 10 duplicate rows with .sample().
  • Run the cell by pressing Shift+Enter.
  • The cell completes with no output below it. This is expected because you have not printed anything yet.

Seeing a NameError?

  • Make sure you ran the imports cell first (import numpy as np and import pandas as pd). Each cell must be executed in order.
  • Check that you typed np.random.default_rng exactly. A common mistake is np.random.default_rang (missing the trailing g).

help me debug a NameError in my Jupyter Notebook

Why generate data instead of downloading a CSV?

Generating data with NumPy gives the library a starring role in your project. You practice random distributions, array manipulation, and conditional logic right from the start.

It also eliminates external dependencies. No broken download URLs, no API keys, and no large files cluttering your machine.

Generate the products table and orders table

Now you'll create two more tables. The products table holds 50 items across five categories. The orders table connects customers to products with 5000 transaction rows.

  • Add a new cell below and paste the products table code:
# Products table (50 rows)
n_products = 50
product_ids = np.arange(1, n_products + 1)
categories = rng.choice(
    ['Electronics', 'Clothing', 'Home', 'Sports', 'Books'],
    size=n_products
)
base_prices = rng.uniform(5, 500, size=n_products).round(2)

products = pd.DataFrame({
    'product_id': product_ids,
    'category': categories,
    'base_price': base_prices
})

What does this code do?

  • rng.choice assigns each product one of five categories at random (no weighting this time).
  • rng.uniform(5, 500, ...) generates prices spread evenly between $5 and $500, then .round(2) keeps them to two decimal places.
  • Run the cell with Shift+Enter. No output is expected.
  • Add another new cell and paste the orders table code:
# Orders table (5000 rows)
n_orders = 5000
order_ids = np.arange(1, n_orders + 1)
order_customer_ids = rng.choice(customer_ids, size=n_orders)
order_product_ids = rng.choice(product_ids, size=n_orders)
quantities = rng.integers(1, 10, size=n_orders)
order_dates = pd.date_range('2024-01-01', '2025-12-31', periods=n_orders).to_numpy()
rng.shuffle(order_dates)

discounts = np.where(
    rng.random(n_orders) < 0.3,
    rng.choice([5, 10, 15, 20, 25, 30], size=n_orders),
    0
)

orders = pd.DataFrame({
    'order_id': order_ids,
    'customer_id': order_customer_ids,
    'product_id': order_product_ids,
    'quantity': quantities.astype(float),
    'order_date': order_dates,
    'discount_pct': discounts.astype(str)
})

# Introduce messiness: null quantities
null_qty_mask = rng.random(n_orders) < 0.02
orders.loc[null_qty_mask, 'quantity'] = np.nan

What does this code do?

  • rng.choice(customer_ids, size=n_orders) randomly assigns existing customer IDs to orders, simulating repeat purchases.
  • np.where applies conditional logic: 30% of orders get a random discount (5-30%), and the rest get 0.
  • discounts.astype(str) intentionally stores discounts as strings. This forces a type conversion during cleaning, simulating real-world messy exports.
  • The final two lines null out about 2% of quantities with another boolean mask.
  • Run the cell with Shift+Enter. No output is expected.

Seeing an IndexError or shape mismatch?

  • Confirm you ran the customers cell first. The orders cell uses customer_ids and product_ids from earlier cells.
  • If you restarted the kernel, re-run all cells from the top using Cell > Run All Above in the menu bar.

help me fix an IndexError or shape mismatch in my NumPy data generation

Validate the generated data

Before moving on, you need to confirm that your three tables exist with the correct shapes and that the intentional messiness is present.

  • Add a new cell and paste the following validation code:
print(f"Customers: {customers.shape}")
print(f"Products: {products.shape}")
print(f"Orders: {orders.shape}")
print(f"\nCustomers nulls:\n{customers.isnull().sum()}")
print(f"\nCustomers duplicates: {customers.duplicated(subset='customer_id').sum()}")
print(f"\nOrders nulls:\n{orders.isnull().sum()}")
print(f"\nOrders discount_pct dtype: {orders['discount_pct'].dtype}")

What should I see?

  • Customers: (510, 4) because the original 500 rows plus 10 duplicates gives 510.
  • Products: (50, 4) for the straightforward 50-product table.
  • Orders: (5000, 6) for the 5000 transactions.
  • Customers nulls should show a non-zero count for the age column (roughly 25 nulls from the 5% mask).
  • Customers duplicates should print 10.
  • Orders discount_pct dtype should show object (pandas' string type), confirming the intentional type mismatch.
  • Run the cell with Shift+Enter.
  • Confirm your output matches the expected shapes and null counts described above.

Shapes don't match?

  • If customers shows (500, 4) instead of (510, 4), the duplicate-append code did not run. Check that your customers cell includes the pd.concat line at the bottom.
  • If discount_pct dtype shows int64 instead of object, make sure you have .astype(str) on the discounts array when building the orders DataFrame.

help me figure out why my DataFrame shapes or dtypes are wrong

✔️ Awesome, I've got everything!

Great. Make sure you save your notebook with Ctrl+S (Windows) or Cmd+S (macOS).

ⓧ I'd like to double check the full code

Here is the full notebook up to this point. Compare it against your cells:

import numpy as np
import pandas as pd

# --- Step 2: Generate the Dataset with NumPy ---

rng = np.random.default_rng(42)

# Customers table (500 rows)
n_customers = 500
customer_ids = np.arange(1, n_customers + 1)
regions = rng.choice(
    ['North', 'South', 'East', 'West'],
    size=n_customers,
    p=[0.3, 0.25, 0.2, 0.25]
)
signup_dates = pd.date_range('2023-01-01', periods=n_customers, freq='D').to_numpy()
rng.shuffle(signup_dates)
ages = rng.normal(35, 10, size=n_customers).astype(int)
ages = np.clip(ages, 18, 70)

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'region': regions,
    'signup_date': signup_dates,
    'age': ages.astype(float)
})

# Introduce messiness: null ages and duplicate rows
null_mask = rng.random(n_customers) < 0.05
customers.loc[null_mask, 'age'] = np.nan
duplicates = customers.sample(10, random_state=42)
customers = pd.concat([customers, duplicates], ignore_index=True)

# Products table (50 rows)
n_products = 50
product_ids = np.arange(1, n_products + 1)
categories = rng.choice(
    ['Electronics', 'Clothing', 'Home', 'Sports', 'Books'],
    size=n_products
)
base_prices = rng.uniform(5, 500, size=n_products).round(2)

products = pd.DataFrame({
    'product_id': product_ids,
    'category': categories,
    'base_price': base_prices
})

# Orders table (5000 rows)
n_orders = 5000
order_ids = np.arange(1, n_orders + 1)
order_customer_ids = rng.choice(customer_ids, size=n_orders)
order_product_ids = rng.choice(product_ids, size=n_orders)
quantities = rng.integers(1, 10, size=n_orders)
order_dates = pd.date_range('2024-01-01', '2025-12-31', periods=n_orders).to_numpy()
rng.shuffle(order_dates)

discounts = np.where(
    rng.random(n_orders) < 0.3,
    rng.choice([5, 10, 15, 20, 25, 30], size=n_orders),
    0
)

orders = pd.DataFrame({
    'order_id': order_ids,
    'customer_id': order_customer_ids,
    'product_id': order_product_ids,
    'quantity': quantities.astype(float),
    'order_date': order_dates,
    'discount_pct': discounts.astype(str)
})

# Introduce messiness: null quantities
null_qty_mask = rng.random(n_orders) < 0.02
orders.loc[null_qty_mask, 'quantity'] = np.nan

print(f"Customers: {customers.shape}")
print(f"Products: {products.shape}")
print(f"Orders: {orders.shape}")
print(f"\nCustomers nulls:\n{customers.isnull().sum()}")
print(f"\nCustomers duplicates: {customers.duplicated(subset='customer_id').sum()}")
print(f"\nOrders nulls:\n{orders.isnull().sum()}")
print(f"\nOrders discount_pct dtype: {orders['discount_pct'].dtype}"}

You now have three related tables sitting in memory, complete with realistic data quality issues. Next up, you'll use pandas to clean this mess and merge the tables into a single analytical DataFrame.

Clean and Merge with Pandas

You now have three messy tables living in memory. They have null values, wrong data types, and duplicate rows. This is exactly what real-world data looks like before anyone touches it.

In this step, you will use pandas to fix those problems and combine all three tables into a single analytical DataFrame. Cleaning and merging is the bridge between raw data and useful analysis.

In this step, get ready to:
  • Fix data types and handle missing values.
  • Remove duplicates and merge all three tables into one DataFrame.
Fix data types and handle missing values

Before you can do any math on your data, the columns need to be the right type. Right now, discount_pct is stored as a string (you forced this in Step 2), and the date columns need to be proper datetime objects.

  • In a new cell in your notebook, convert the string and date columns to their correct types by adding this code:
# --- Step 3: Clean and Merge with Pandas ---

# Fix dtypes
orders['discount_pct'] = pd.to_numeric(orders['discount_pct'])
orders['order_date'] = pd.to_datetime(orders['order_date'])
customers['signup_date'] = pd.to_datetime(customers['signup_date'])

What does this code do?

  • pd.to_numeric() converts the string discount_pct column into actual numbers so you can do arithmetic with it.
  • pd.to_datetime() converts date columns into datetime objects so pandas can understand time-based operations like resampling and extracting months.
  • Run the cell with Shift+Enter.
  • The cell should execute without any output or errors. No output means everything converted cleanly.

Seeing a ValueError?

If you see an error mentioning inability to convert a value, make sure you ran all the cells from Step 2 first. The orders and customers DataFrames must already exist in memory.

Try restarting the kernel and running all cells from the top.

Help me debug this dtype conversion error in my Jupyter notebook.

Now you need to deal with the null values you intentionally injected. The standard approach is to fill numeric nulls with a central value like the median, which is resistant to outliers.

  • In the same cell (or a new one below), fill nulls and fix the quantity dtype by adding this code:
# Handle missing values
orders['quantity'] = orders['quantity'].fillna(orders['quantity'].median())
orders['quantity'] = orders['quantity'].astype(int)
customers['age'] = customers['age'].fillna(customers['age'].median())

What does this code do?

  • .fillna() replaces every NaN in the column with the median value. The median is preferred over the mean because a single extreme outlier does not skew it.
  • .astype(int) casts quantity back to whole numbers. Pandas promoted the column to float when nulls were introduced (integers cannot represent NaN in NumPy).
  • Run the cell with Shift+Enter.
  • Again, no output means success. You can verify by running orders['quantity'].isnull().sum() in a scratch cell to confirm it returns 0.

Getting a TypeError on astype(int)?

This happens if the fillna() line did not run successfully. Nulls must be filled before casting to int because NaN cannot be represented as an integer.

Make sure both lines are in the same cell and run together.

Help me fix my astype int conversion error.

Remove duplicates and merge tables

Your customers table still has 10 duplicate rows from Step 2. You also need to combine all three tables into a single DataFrame so every order row carries its product info and customer info.

  • In a new cell, remove duplicates, merge all three tables, and print the validation output by adding this code:
# Remove duplicates
customers = customers.drop_duplicates(subset='customer_id', keep='first').reset_index(drop=True)

# Merge tables
df = orders.merge(products, on='product_id', how='left')
df = df.merge(customers, on='customer_id', how='left')

print(f"\nMerged DataFrame: {df.shape[0]} rows, {df.shape[1]} columns")
print(f"Null check:\n{df.isnull().sum()}")
print(df.head())

What does this code do?

  • drop_duplicates(subset='customer_id', keep='first') removes duplicate customer rows, keeping only the first occurrence of each customer_id. This brings the table back to 500 unique customers.
  • The two .merge() calls perform left joins. First, orders joins with products on product_id. Then that result joins with customers on customer_id. A left join keeps every order row even if a match is missing.
  • The print statements validate the merge by showing the final shape, any remaining nulls, and a preview of the combined data.

Why left joins?

A left join preserves every row from the left table (orders) regardless of whether a matching key exists in the right table. If a product or customer ID had no match, that row would show nulls in the joined columns instead of disappearing.

This is safer than an inner join for data analysis because you never silently lose rows. Your null check at the end catches any broken links.

  • Run the cell with Shift+Enter.
  • You should see output showing 5000 rows and 10 columns in the merged DataFrame, with zero (or near-zero) nulls across all columns.

Seeing unexpected nulls in the merge?

If you see nulls in columns like region or category, the join keys may not match between tables. Confirm that product_id and customer_id column names are spelled identically in both DataFrames.

Also make sure you ran the Step 2 cells that generate the products and customers tables before this cell.

Help me debug nulls appearing after my pandas merge.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Here is the complete notebook code up to this point. Compare it against your cells:

import numpy as np
import pandas as pd

# --- Step 2: Generate the Dataset with NumPy ---

rng = np.random.default_rng(42)

# Customers table (500 rows)
n_customers = 500
customer_ids = np.arange(1, n_customers + 1)
regions = rng.choice(
    ['North', 'South', 'East', 'West'],
    size=n_customers,
    p=[0.3, 0.25, 0.2, 0.25]
)
signup_dates = pd.date_range('2023-01-01', periods=n_customers, freq='D').to_numpy()
rng.shuffle(signup_dates)
ages = rng.normal(35, 10, size=n_customers).astype(int)
ages = np.clip(ages, 18, 70)

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'region': regions,
    'signup_date': signup_dates,
    'age': ages.astype(float)
})

null_mask = rng.random(n_customers) < 0.05
customers.loc[null_mask, 'age'] = np.nan
duplicates = customers.sample(10, random_state=42)
customers = pd.concat([customers, duplicates], ignore_index=True)

n_products = 50
product_ids = np.arange(1, n_products + 1)
categories = rng.choice(
    ['Electronics', 'Clothing', 'Home', 'Sports', 'Books'],
    size=n_products
)
base_prices = rng.uniform(5, 500, size=n_products).round(2)

products = pd.DataFrame({
    'product_id': product_ids,
    'category': categories,
    'base_price': base_prices
})

n_orders = 5000
order_ids = np.arange(1, n_orders + 1)
order_customer_ids = rng.choice(customer_ids, size=n_orders)
order_product_ids = rng.choice(product_ids, size=n_orders)
quantities = rng.integers(1, 10, size=n_orders)
order_dates = pd.date_range('2024-01-01', '2025-12-31', periods=n_orders).to_numpy()
rng.shuffle(order_dates)

discounts = np.where(
    rng.random(n_orders) < 0.3,
    rng.choice([5, 10, 15, 20, 25, 30], size=n_orders),
    0
)

orders = pd.DataFrame({
    'order_id': order_ids,
    'customer_id': order_customer_ids,
    'product_id': order_product_ids,
    'quantity': quantities.astype(float),
    'order_date': order_dates,
    'discount_pct': discounts.astype(str)
})

null_qty_mask = rng.random(n_orders) < 0.02
orders.loc[null_qty_mask, 'quantity'] = np.nan

# --- Step 3: Clean and Merge with Pandas ---

# Fix dtypes
orders['discount_pct'] = pd.to_numeric(orders['discount_pct'])
orders['order_date'] = pd.to_datetime(orders['order_date'])
customers['signup_date'] = pd.to_datetime(customers['signup_date'])

# Handle missing values
orders['quantity'] = orders['quantity'].fillna(orders['quantity'].median())
orders['quantity'] = orders['quantity'].astype(int)
customers['age'] = customers['age'].fillna(customers['age'].median())

# Remove duplicates
customers = customers.drop_duplicates(subset='customer_id', keep='first').reset_index(drop=True)

# Merge tables
df = orders.merge(products, on='product_id', how='left')
df = df.merge(customers, on='customer_id', how='left')

print(f"\nMerged DataFrame: {df.shape[0]} rows, {df.shape[1]} columns")
print(f"Null check:\n{df.isnull().sum()}")
print(df.head())

Your data is now clean, unified, and ready for analysis. Next up, you will engineer new features like revenue, profit, and customer segments using vectorized NumPy operations.

Engineer Features with NumPy

Your cleaned, merged DataFrame is ready. You have 5,000 orders connected to their products and customers with no data quality issues remaining.

Raw data alone cannot answer business questions. You need to derive new columns like revenue, profit, and customer value segments. In this step, you'll use NumPy's vectorized operations to compute these features across all 5,000 rows without writing a single loop.

In this step, get ready to:
  • Compute revenue and profit using vectorized element-wise arithmetic.
  • Segment customers into value tiers using NumPy percentiles.
  • Verify all engineered features with summary statistics.
Compute revenue and profit using vectorized operations

Vectorized operations apply a calculation to every row simultaneously, using the entire column as a single array. This is how NumPy achieves speed. Instead of looping over 5,000 rows, you write one formula and NumPy broadcasts it across all elements at once.

  • In your notebook, create a new cell below your Step 3 code and add a section comment followed by the revenue and profit calculations:
# --- Step 4: Feature Engineering with NumPy ---

# Revenue: vectorized element-wise calculation
df['revenue'] = df['base_price'] * df['quantity'] * (1 - df['discount_pct'] / 100)

# Profit using np.select for category-based cost ratios
cost_ratios = {'Electronics': 0.6, 'Clothing': 0.4, 'Home': 0.5, 'Sports': 0.45, 'Books': 0.3}
conditions = [df['category'] == cat for cat in cost_ratios.keys()]
choices = list(cost_ratios.values())
df['cost_ratio'] = np.select(conditions, choices, default=0.5)
df['profit'] = df['revenue'] * (1 - df['cost_ratio'])

What does this code do?

The revenue formula multiplies three columns element-wise across all 5,000 rows in one operation. The discount adjustment (1 - discount_pct / 100) converts a percentage like 20 into a multiplier like 0.80.

For profit, each product category has a different cost ratio (Electronics costs 60% of revenue to produce, Books only 30%). np.select takes a list of conditions and a matching list of values, then assigns the correct cost ratio to each row based on which condition is true.

Why np.select instead of a loop or if/else?

A Python for loop checking each row's category would work, but it processes one row at a time. np.select evaluates all conditions across the entire array in one pass using compiled C code under the hood. For 5,000 rows the difference is small, but for millions of rows it is orders of magnitude faster.

  • Run the cell by pressing Shift+Enter.

The cell executes silently with no output. The verification substep at the end of this section confirms the calculations worked.

Seeing a KeyError or TypeError?

  • A KeyError on discount_pct or base_price means the merge from Step 3 did not run successfully. Go back and re-run your Step 3 cells first.
  • A TypeError about unsupported operand types means discount_pct is still a string. Confirm your Step 3 cell with pd.to_numeric() ran.

Why is my revenue or profit calculation returning errors?

Segment customers by value using percentiles

Not all customers are equally valuable. Segmenting them into tiers based on how much they spend helps businesses target their highest-value groups differently.

  • Create a new cell and add the customer segmentation and time feature code:
# Customer segmentation using np.percentile
customer_revenue = df.groupby('customer_id')['revenue'].sum()
quartiles = np.percentile(customer_revenue, [25, 50, 75])
customer_segments = pd.cut(
    customer_revenue,
    bins=[-np.inf, quartiles[0], quartiles[1], quartiles[2], np.inf],
    labels=['Bronze', 'Silver', 'Gold', 'Platinum']
)
df['customer_segment'] = df['customer_id'].map(customer_segments.to_dict())

# Time features
df['order_month'] = df['order_date'].dt.to_period('M')
df['order_dayofweek'] = df['order_date'].dt.day_name()

What does this code do?

  • customer_revenue totals each customer's lifetime revenue using groupby and sum.
  • np.percentile(..., [25, 50, 75]) finds the boundaries that split all customers into four equally-sized groups based on their total spend.
  • pd.cut assigns each customer a label (Bronze, Silver, Gold, or Platinum) based on which quartile their total revenue falls into.
  • .map(customer_segments.to_dict()) attaches the segment label back to every order row so each order knows whether it belongs to a Bronze or Platinum customer.
  • The time features extract the month period and day name from each order date, useful for trend analysis later.
  • Run the cell by pressing Shift+Enter.

Again, no output appears. The next substep prints verification statistics for all engineered features at once.

Seeing a ValueError from pd.cut?

  • If the error mentions bins not being monotonically increasing, your revenue column may contain unexpected negative values. Re-run the revenue cell from the previous substep.
  • If you see a KeyError: 'revenue', the revenue cell did not run. Go back and run it first.

Why is my customer segmentation failing?

Verify engineered features

Before moving to analysis, you need to confirm the calculations produced reasonable values. Printing summary statistics surfaces obvious errors like all-zero columns or unexpected negative numbers.

  • Create a new cell and add the verification prints:
print(f"\nFeature-engineered columns: {df.columns.tolist()}")
print(f"\nRevenue stats:")
print(f"  Mean: ${df['revenue'].mean():.2f}")
print(f"  Median: ${np.median(df['revenue'].values):.2f}")
print(f"  Std: ${np.std(df['revenue'].values):.2f}")
print(f"\nCustomer segments:\n{df['customer_segment'].value_counts()}")

What does this code do?

This prints the full column list to confirm all new features exist. It then shows revenue statistics using both pandas (the .mean() method) and NumPy (np.median(), np.std()). Both approaches work on the same data. Using .values extracts the underlying NumPy array from the pandas Series.

The segment value counts confirm that customers are distributed across all four tiers (Bronze, Silver, Gold, Platinum).

  • Run the cell by pressing Shift+Enter.

You should see a list of all columns (including revenue, profit, cost_ratio, customer_segment, order_month, order_dayofweek), revenue statistics with dollar amounts, and a count of customers in each segment tier.

Seeing all NaN for customer segments?

  • This means the .map() step did not find matching customer IDs. Confirm that the segmentation cell ran before this verification cell.
  • If revenue statistics show $0.00 for all values, re-run the revenue calculation cell. The cells must run in order from top to bottom.

My verification output shows unexpected values. What went wrong?

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook with Cmd+S (macOS) or Ctrl+S (Windows).

ⓧ I'd like to double check the full code

Here is all the code your notebook should contain through this step. Compare yours against it:

import numpy as np
import pandas as pd

# --- Step 2: Generate the Dataset with NumPy ---

rng = np.random.default_rng(42)

n_customers = 500
customer_ids = np.arange(1, n_customers + 1)
regions = rng.choice(
    ['North', 'South', 'East', 'West'],
    size=n_customers,
    p=[0.3, 0.25, 0.2, 0.25]
)
signup_dates = pd.date_range('2023-01-01', periods=n_customers, freq='D').to_numpy()
rng.shuffle(signup_dates)
ages = rng.normal(35, 10, size=n_customers).astype(int)
ages = np.clip(ages, 18, 70)

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'region': regions,
    'signup_date': signup_dates,
    'age': ages.astype(float)
})

null_mask = rng.random(n_customers) < 0.05
customers.loc[null_mask, 'age'] = np.nan
duplicates = customers.sample(10, random_state=42)
customers = pd.concat([customers, duplicates], ignore_index=True)

n_products = 50
product_ids = np.arange(1, n_products + 1)
categories = rng.choice(
    ['Electronics', 'Clothing', 'Home', 'Sports', 'Books'],
    size=n_products
)
base_prices = rng.uniform(5, 500, size=n_products).round(2)

products = pd.DataFrame({
    'product_id': product_ids,
    'category': categories,
    'base_price': base_prices
})

n_orders = 5000
order_ids = np.arange(1, n_orders + 1)
order_customer_ids = rng.choice(customer_ids, size=n_orders)
order_product_ids = rng.choice(product_ids, size=n_orders)
quantities = rng.integers(1, 10, size=n_orders)
order_dates = pd.date_range('2024-01-01', '2025-12-31', periods=n_orders).to_numpy()
rng.shuffle(order_dates)

discounts = np.where(
    rng.random(n_orders) < 0.3,
    rng.choice([5, 10, 15, 20, 25, 30], size=n_orders),
    0
)

orders = pd.DataFrame({
    'order_id': order_ids,
    'customer_id': order_customer_ids,
    'product_id': order_product_ids,
    'quantity': quantities.astype(float),
    'order_date': order_dates,
    'discount_pct': discounts.astype(str)
})

null_qty_mask = rng.random(n_orders) < 0.02
orders.loc[null_qty_mask, 'quantity'] = np.nan

# --- Step 3: Clean and Merge with Pandas ---

orders['discount_pct'] = pd.to_numeric(orders['discount_pct'])
orders['order_date'] = pd.to_datetime(orders['order_date'])
customers['signup_date'] = pd.to_datetime(customers['signup_date'])

orders['quantity'] = orders['quantity'].fillna(orders['quantity'].median())
orders['quantity'] = orders['quantity'].astype(int)
customers['age'] = customers['age'].fillna(customers['age'].median())

customers = customers.drop_duplicates(subset='customer_id', keep='first').reset_index(drop=True)

df = orders.merge(products, on='product_id', how='left')
df = df.merge(customers, on='customer_id', how='left')

# --- Step 4: Feature Engineering with NumPy ---

# Revenue: vectorized element-wise calculation
df['revenue'] = df['base_price'] * df['quantity'] * (1 - df['discount_pct'] / 100)

# Profit using np.select for category-based cost ratios
cost_ratios = {'Electronics': 0.6, 'Clothing': 0.4, 'Home': 0.5, 'Sports': 0.45, 'Books': 0.3}
conditions = [df['category'] == cat for cat in cost_ratios.keys()]
choices = list(cost_ratios.values())
df['cost_ratio'] = np.select(conditions, choices, default=0.5)
df['profit'] = df['revenue'] * (1 - df['cost_ratio'])

# Customer segmentation using np.percentile
customer_revenue = df.groupby('customer_id')['revenue'].sum()
quartiles = np.percentile(customer_revenue, [25, 50, 75])
customer_segments = pd.cut(
    customer_revenue,
    bins=[-np.inf, quartiles[0], quartiles[1], quartiles[2], np.inf],
    labels=['Bronze', 'Silver', 'Gold', 'Platinum']
)
df['customer_segment'] = df['customer_id'].map(customer_segments.to_dict())

# Time features
df['order_month'] = df['order_date'].dt.to_period('M')
df['order_dayofweek'] = df['order_date'].dt.day_name()

print(f"\nFeature-engineered columns: {df.columns.tolist()}")
print(f"\nRevenue stats:")
print(f"  Mean: ${df['revenue'].mean():.2f}")
print(f"  Median: ${np.median(df['revenue'].values):.2f}")
print(f"  Std: ${np.std(df['revenue'].values):.2f}")
print(f"\nCustomer segments:\n{df['customer_segment'].value_counts()}")

Your DataFrame now has revenue, profit, customer segments, and time features ready for analysis. Next up, you'll use groupby aggregations and pivot tables to answer real business questions about this data.

Analyze with GroupBy and Pivot Tables

Your DataFrame now has every column it needs: revenue, profit, customer segments, and time features. The data is clean, merged, and ready to answer real business questions.

In this step, you will use pandas groupby aggregations and pivot tables to extract insights from the data. These are the exact operations analysts run every day to understand category performance, identify high-value customers, and compare metrics across dimensions.

In this step, get ready to:
  • Answer three business questions using groupby with named aggregation.
  • Build pivot tables for cross-dimensional analysis.
Answer business questions with groupby

A groupby operation splits your data into groups based on a column, applies a function to each group, and combines the results. Named aggregation lets you define exactly which columns to aggregate and what to call the output.

  • In a new cell in your notebook, answer Q1 (revenue by category) by running this code:
# Q1: Revenue by category
category_stats = df.groupby('category').agg(
    total_revenue=('revenue', 'sum'),
    avg_order_value=('revenue', 'mean'),
    order_count=('order_id', 'count'),
    unique_customers=('customer_id', 'nunique')
).round(2).sort_values('total_revenue', ascending=False)

print("\n--- Q1: Revenue by Category ---")
print(category_stats)

What does this code do?

Named aggregation uses the pattern new_col=(source_col, func). Each tuple tells pandas which column to pull from and which function to apply.

The .round(2) cleans up floating-point output. The .sort_values() orders categories from highest to lowest revenue so the top performer is immediately visible.

  • Run the cell.
  • You should see a table with five category rows and four columns: total_revenue, avg_order_value, order_count, and unique_customers.

Seeing a KeyError?

Make sure the column names in your tuples match exactly what exists in df. Run df.columns.tolist() to check the available columns.

Also confirm you ran all cells from previous steps so that revenue and customer_segment columns exist.

Why is my groupby not working?

  • In a new cell, answer Q2 (top 10 customers by total spend) by running this code:
# Q2: Top 10 customers by total spend
top_customers = (
    df.groupby('customer_id')['revenue']
    .sum()
    .sort_values(ascending=False)
    .head(10)
    .reset_index()
    .rename(columns={'revenue': 'total_spend'})
)

print("\n--- Q2: Top 10 Customers ---")
print(top_customers)

What does this code do?

This chains multiple operations: group by customer, sum their revenue, sort descending, take the top 10, and rename the column to total_spend for clarity.

The .reset_index() converts the groupby result back into a regular DataFrame with customer_id as a column instead of an index.

  • Run the cell.
  • You should see a 10-row table with customer_id and total_spend columns, sorted from highest spender to lowest.
  • In a new cell, answer Q3 (discount effectiveness) by running this code:
# Q3: Discount effectiveness
discount_analysis = df.groupby(
    pd.cut(
        df['discount_pct'],
        bins=[-1, 0, 10, 20, 30],
        labels=['No Discount', '1-10%', '11-20%', '21-30%']
    )
).agg(
    order_count=('order_id', 'count'),
    avg_quantity=('quantity', 'mean'),
    avg_revenue=('revenue', 'mean')
).round(2)

print("\n--- Q3: Discount Effectiveness ---")
print(discount_analysis)

What does this code do?

This combines pd.cut with groupby. First, pd.cut bins continuous discount percentages into labeled ranges (No Discount, 1-10%, 11-20%, 21-30%). Then groupby aggregates each bin.

This answers whether higher discounts actually drive more purchases or just reduce revenue per order.

  • Run the cell.
  • You should see four rows (one per discount range) with order_count, avg_quantity, and avg_revenue for each bin.

Seeing empty or NaN rows?

Check that the bins parameter starts at -1 (not 0). Since many discounts are exactly 0, a bin starting at 0 would exclude them.

Why does my pd.cut result have NaN values?

Build pivot tables for cross-dimensional analysis

Pivot tables let you view one metric across two dimensions simultaneously. Instead of grouping by a single column, you can see how revenue breaks down by both region and category in a single grid.

  • In a new cell, answer Q4 (revenue by region and category) by running this code:
# Q4: Revenue by region x category (pivot table)
region_category_pivot = pd.pivot_table(
    df,
    values='revenue',
    index='region',
    columns='category',
    aggfunc='sum'
).round(2)

print("\n--- Q4: Revenue by Region x Category ---")
print(region_category_pivot)

What does this code do?

The pd.pivot_table() function reshapes your data into a grid. Regions become rows, categories become columns, and each cell contains the sum of revenue for that region-category combination.

This makes it easy to spot which regions dominate in which categories at a glance.

  • Run the cell.
  • You should see a 4x5 grid (4 regions as rows, 5 categories as columns) with revenue sums in each cell.
  • In a new cell, answer Q5 (customer segment performance) by running this code:
# Q5: Customer segment performance
segment_summary = df.groupby('customer_segment', observed=True).agg(
    customer_count=('customer_id', 'nunique'),
    total_revenue=('revenue', 'sum'),
    avg_order_value=('revenue', 'mean')
).round(2)

print("\n--- Q5: Customer Segment Performance ---")
print(segment_summary)

What does this code do?

This groups by the customer_segment column you created in the previous step. The observed=True parameter tells pandas to only show segments that actually exist in the data, avoiding empty rows.

The output shows how many customers are in each tier, how much total revenue they generate, and their average order value. This tells you whether your Platinum customers are worth significantly more per transaction.

  • Run the cell.
  • You should see four rows (Bronze, Silver, Gold, Platinum) with customer_count, total_revenue, and avg_order_value for each segment.

Seeing a FutureWarning about observed?

If you omit observed=True when grouping by a categorical column, pandas raises a warning. Make sure you included it in the .groupby() call.

Why am I getting a FutureWarning with groupby on categorical data?

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all five analysis outputs visible.

ⓧ I'd like to double check the full code

Here is the complete notebook code up to this point:

import numpy as np
import pandas as pd

# --- Step 2: Generate the Dataset with NumPy ---

rng = np.random.default_rng(42)

n_customers = 500
customer_ids = np.arange(1, n_customers + 1)
regions = rng.choice(
    ['North', 'South', 'East', 'West'],
    size=n_customers,
    p=[0.3, 0.25, 0.2, 0.25]
)
signup_dates = pd.date_range('2023-01-01', periods=n_customers, freq='D').to_numpy()
rng.shuffle(signup_dates)
ages = rng.normal(35, 10, size=n_customers).astype(int)
ages = np.clip(ages, 18, 70)

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'region': regions,
    'signup_date': signup_dates,
    'age': ages.astype(float)
})

null_mask = rng.random(n_customers) < 0.05
customers.loc[null_mask, 'age'] = np.nan
duplicates = customers.sample(10, random_state=42)
customers = pd.concat([customers, duplicates], ignore_index=True)

n_products = 50
product_ids = np.arange(1, n_products + 1)
categories = rng.choice(
    ['Electronics', 'Clothing', 'Home', 'Sports', 'Books'],
    size=n_products
)
base_prices = rng.uniform(5, 500, size=n_products).round(2)

products = pd.DataFrame({
    'product_id': product_ids,
    'category': categories,
    'base_price': base_prices
})

n_orders = 5000
order_ids = np.arange(1, n_orders + 1)
order_customer_ids = rng.choice(customer_ids, size=n_orders)
order_product_ids = rng.choice(product_ids, size=n_orders)
quantities = rng.integers(1, 10, size=n_orders)
order_dates = pd.date_range('2024-01-01', '2025-12-31', periods=n_orders).to_numpy()
rng.shuffle(order_dates)

discounts = np.where(
    rng.random(n_orders) < 0.3,
    rng.choice([5, 10, 15, 20, 25, 30], size=n_orders),
    0
)

orders = pd.DataFrame({
    'order_id': order_ids,
    'customer_id': order_customer_ids,
    'product_id': order_product_ids,
    'quantity': quantities.astype(float),
    'order_date': order_dates,
    'discount_pct': discounts.astype(str)
})

null_qty_mask = rng.random(n_orders) < 0.02
orders.loc[null_qty_mask, 'quantity'] = np.nan

# --- Step 3: Clean and Merge with Pandas ---

orders['discount_pct'] = pd.to_numeric(orders['discount_pct'])
orders['order_date'] = pd.to_datetime(orders['order_date'])
customers['signup_date'] = pd.to_datetime(customers['signup_date'])

orders['quantity'] = orders['quantity'].fillna(orders['quantity'].median())
orders['quantity'] = orders['quantity'].astype(int)
customers['age'] = customers['age'].fillna(customers['age'].median())

customers = customers.drop_duplicates(subset='customer_id', keep='first').reset_index(drop=True)

df = orders.merge(products, on='product_id', how='left')
df = df.merge(customers, on='customer_id', how='left')

# --- Step 4: Feature Engineering with NumPy ---

df['revenue'] = df['base_price'] * df['quantity'] * (1 - df['discount_pct'] / 100)

cost_ratios = {'Electronics': 0.6, 'Clothing': 0.4, 'Home': 0.5, 'Sports': 0.45, 'Books': 0.3}
conditions = [df['category'] == cat for cat in cost_ratios.keys()]
choices = list(cost_ratios.values())
df['cost_ratio'] = np.select(conditions, choices, default=0.5)
df['profit'] = df['revenue'] * (1 - df['cost_ratio'])

customer_revenue = df.groupby('customer_id')['revenue'].sum()
quartiles = np.percentile(customer_revenue, [25, 50, 75])
customer_segments = pd.cut(
    customer_revenue,
    bins=[-np.inf, quartiles[0], quartiles[1], quartiles[2], np.inf],
    labels=['Bronze', 'Silver', 'Gold', 'Platinum']
)
df['customer_segment'] = df['customer_id'].map(customer_segments.to_dict())

df['order_month'] = df['order_date'].dt.to_period('M')
df['order_dayofweek'] = df['order_date'].dt.day_name()

# --- Step 5: GroupBy and Pivot Analysis ---

# Q1: Revenue by category
category_stats = df.groupby('category').agg(
    total_revenue=('revenue', 'sum'),
    avg_order_value=('revenue', 'mean'),
    order_count=('order_id', 'count'),
    unique_customers=('customer_id', 'nunique')
).round(2).sort_values('total_revenue', ascending=False)

print("\n--- Q1: Revenue by Category ---")
print(category_stats)

# Q2: Top 10 customers by total spend
top_customers = (
    df.groupby('customer_id')['revenue']
    .sum()
    .sort_values(ascending=False)
    .head(10)
    .reset_index()
    .rename(columns={'revenue': 'total_spend'})
)

print("\n--- Q2: Top 10 Customers ---")
print(top_customers)

# Q3: Discount effectiveness
discount_analysis = df.groupby(
    pd.cut(
        df['discount_pct'],
        bins=[-1, 0, 10, 20, 30],
        labels=['No Discount', '1-10%', '11-20%', '21-30%']
    )
).agg(
    order_count=('order_id', 'count'),
    avg_quantity=('quantity', 'mean'),
    avg_revenue=('revenue', 'mean')
).round(2)

print("\n--- Q3: Discount Effectiveness ---")
print(discount_analysis)

# Q4: Revenue by region x category (pivot table)
region_category_pivot = pd.pivot_table(
    df,
    values='revenue',
    index='region',
    columns='category',
    aggfunc='sum'
).round(2)

print("\n--- Q4: Revenue by Region x Category ---")
print(region_category_pivot)

# Q5: Customer segment performance
segment_summary = df.groupby('customer_segment', observed=True).agg(
    customer_count=('customer_id', 'nunique'),
    total_revenue=('revenue', 'sum'),
    avg_order_value=('revenue', 'mean')
).round(2)

print("\n--- Q5: Customer Segment Performance ---")
print(segment_summary)

You have now completed a full business analysis using groupby and pivot tables. Your notebook answers five distinct questions about category performance, customer value, discount strategy, regional trends, and segment behavior. This is the exact analytical workflow used in data roles every day.

Secret mission

Time-Series Resampling and Rolling Windows

Your analysis answers what happened, but not when it happened. Add a time dimension to uncover monthly trends, smoothed patterns, and growth rates across your e-commerce data.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • The ecommerce-analysis/ project directory containing your notebook and all generated data.
  • The Jupyter Notebook server running in your terminal.

Keep everything running

No action needed. Choose this if you're still actively building or exploring the notebook.

  • Your notebook and generated data live in your local ecommerce-analysis/ directory on your Desktop.
  • The Jupyter Notebook server continues running in the terminal where you launched it.
  • NumPy and pandas remain installed and available for other Python projects.

Pause - I'll come back to this later

Shut down the Jupyter Notebook server to free up your terminal. Your files stay on disk.

  • Switch to the terminal where Jupyter is running.
  • Press Ctrl+C to stop the server. Confirm with y if prompted.
  • Your ecommerce-analysis/ directory and notebook remain on your Desktop for next time.
  • To resume later, open your terminal, navigate back to the directory, and run jupyter notebook again.

Delete - I don't want to use this again

Remove all project files and start fresh. This permanently deletes your notebook and analysis.

  • Stop the Jupyter Notebook server by pressing Ctrl+C in the terminal where it is running.
  • Remove the project directory by running:

macOS

cd ~/Desktop
rm -rf ecommerce-analysis/

Windows

cd ~\Desktop
rmdir /s /q ecommerce-analysis
  • Confirm the directory is gone by running ls (macOS) or dir (Windows) on your Desktop. You should no longer see ecommerce-analysis in the output.

Nice Work!

Nice Work!

You just built a complete data analysis pipeline from scratch using NumPy and pandas. From generating realistic messy data to answering real business questions, you now have hands-on experience with the exact workflow used in every data role.

You've learned how to:

  • Generated a realistic 3-table e-commerce dataset using NumPy random distributions, boolean masking, and np.where for conditional logic.
  • Cleaned, merged, and feature-engineered an analytical DataFrame using pandas dtype conversion, null handling, deduplication, left joins, and vectorized NumPy operations like np.select and np.percentile.
  • Answered 5 business questions using groupby with named aggregation and pivot tables to analyze category performance, customer value, and discount effectiveness.
  • Secret Mission: Built a time-series analysis with monthly resampling, rolling averages, and month-over-month growth rates.

Ready to quiz yourself?