The Data Journey: Messy to Meaningful¶

Phase 2¶

Objective¶

Now that the data is clean, the goal is to:

  • Create meaningful features
  • Explore patterns in the data
  • Start understanding customer behavior & sales trends

This is where data starts speaking.

What We’ll Do

  • Create new variables (like Revenue)
  • Extract time-based features
  • Perform basic aggregations
  • Prepare data for visualization & ML

Load Dataset¶

In [2]:
import pandas as pd

df = pd.read_csv('Online Retail Phase 1 Output.csv',index_col = 0)
df.head()
Out[2]:
CustomerID InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice
0 17850 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 2010-12-01 08:26:00 2.55
1 17850 536365 71053 WHITE METAL LANTERN 6 2010-12-01 08:26:00 3.39
2 17850 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 2010-12-01 08:26:00 2.75
3 17850 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6 2010-12-01 08:26:00 3.39
4 17850 536365 84029E RED WOOLLY HOTTIE WHITE HEART. 6 2010-12-01 08:26:00 3.39

Load Country File¶

In [3]:
df_country = pd.read_csv('Customer_country.csv')
df_country = df_country.dropna(subset=["CustomerID"])
df_country["CustomerID"] = df_country["CustomerID"].astype(int)
df_country.head()
Out[3]:
CustomerID Country
0 17850 United Kingdom
1 13047 United Kingdom
2 12583 France
3 13748 United Kingdom
4 15100 United Kingdom

Merge Dataframes¶

Why Data Merging?

  • Real-world data is never in one table
  • Transactions, customers, products → all live separately
  • Merging helps create a single source of truth

👉 Without merging, you can’t answer:

  • Who bought what?
  • Which customers are high value?
  • Which products drive revenue?
In [4]:
merged_df = df.merge(df_country, on="CustomerID", how="left")
merged_df.head()
Out[4]:
CustomerID InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice Country
0 17850 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 2010-12-01 08:26:00 2.55 United Kingdom
1 17850 536365 71053 WHITE METAL LANTERN 6 2010-12-01 08:26:00 3.39 United Kingdom
2 17850 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 2010-12-01 08:26:00 2.75 United Kingdom
3 17850 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6 2010-12-01 08:26:00 3.39 United Kingdom
4 17850 536365 84029E RED WOOLLY HOTTIE WHITE HEART. 6 2010-12-01 08:26:00 3.39 United Kingdom

Why Feature Engineering?

This is where real data science begins

Raw columns ≠ useful insights

We create new features like:

  • Total Spend = Quantity × Price
  • Recency, Frequency, Monetary (RFM)
  • Basket size, purchase patterns

👉 Why it matters:

  • Models perform better with meaningful features
  • Business decisions come from interpretable metrics

Create Revenue Feature¶

In [5]:
merged_df["Revenue"] = merged_df["Quantity"] * merged_df["UnitPrice"]

Extract Time Features¶

In [6]:
merged_df["InvoiceDate"] = pd.to_datetime(merged_df["InvoiceDate"])

merged_df["Year"] = merged_df["InvoiceDate"].dt.year
merged_df["Month"] = merged_df["InvoiceDate"].dt.month
merged_df["Day"] = merged_df["InvoiceDate"].dt.day
merged_df["Hour"] = merged_df["InvoiceDate"].dt.hour
In [7]:
merged_df.head()
Out[7]:
CustomerID InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice Country Revenue Year Month Day Hour
0 17850 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 2010-12-01 08:26:00 2.55 United Kingdom 15.30 2010 12 1 8
1 17850 536365 71053 WHITE METAL LANTERN 6 2010-12-01 08:26:00 3.39 United Kingdom 20.34 2010 12 1 8
2 17850 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 2010-12-01 08:26:00 2.75 United Kingdom 22.00 2010 12 1 8
3 17850 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6 2010-12-01 08:26:00 3.39 United Kingdom 20.34 2010 12 1 8
4 17850 536365 84029E RED WOOLLY HOTTIE WHITE HEART. 6 2010-12-01 08:26:00 3.39 United Kingdom 20.34 2010 12 1 8

Analyze Data : Understanding what sells, when it sells, and where it sells.¶

Monthly Sales Trend¶

Shows seasonality and demand patterns — useful for planning and forecasting.

In [8]:
monthly_sales = merged_df.groupby("Month")["Revenue"].sum().reset_index()
monthly_sales
Out[8]:
Month Revenue
0 1 569047.930
1 2 448836.470
2 3 595430.150
3 4 470315.861
4 5 678683.480
5 6 662290.400
6 7 600204.681
7 8 645656.610
8 9 952228.032
9 10 1038943.770
10 11 1157712.570
11 12 1092126.460

Interpretation :¶

Revenue shows a clear upward trend through the year, peaking in the last quarter (Sep–Nov), indicating strong seasonal demand towards year-end.

Top Products¶

Highlights the products driving most revenue — helps focus on what truly sells.

In [9]:
top_products = (
    merged_df.groupby("Description")["Quantity"]
    .sum()
    .sort_values(ascending=False)
    .head(10)
)

top_products
Out[9]:
Description
PAPER CRAFT , LITTLE BIRDIE           80995
MEDIUM CERAMIC TOP STORAGE JAR        77916
WORLD WAR 2 GLIDERS ASSTD DESIGNS     54319
JUMBO BAG RED RETROSPOT               46098
WHITE HANGING HEART T-LIGHT HOLDER    36832
ASSORTED COLOUR BIRD ORNAMENT         35319
PACK OF 72 RETROSPOT CAKE CASES       33742
POPCORN HOLDER                        30919
RABBIT NIGHT LIGHT                    27513
MINI PAINT SET VINTAGE                26112
Name: Quantity, dtype: int64

Interpretation :¶

Top products are mostly low-cost, decorative and utility items, indicating high-volume, impulse-driven purchases driving overall sales.

Revenue by Country¶

Identifies key markets and growth opportunities across regions.

In [10]:
country_revenue = (
    merged_df.groupby("Country")["Revenue"]
    .sum()
    .sort_values(ascending=False)
    .head(10)
)

country_revenue
Out[10]:
Country
United Kingdom    7285024.644
Netherlands        285446.340
EIRE               265262.460
Germany            228678.400
France             208934.310
Australia          139843.950
Spain               66470.260
Switzerland         57222.850
Belgium             47971.210
Sweden              38367.830
Name: Revenue, dtype: float64
In [11]:
merged_df.to_csv('Online Retail Phase 2 Output.csv')

Interpretation :¶

The United Kingdom dominates revenue by a huge margin, indicating the business is highly dependent on a single primary market.

Conclusion :¶

In Phase 2, we moved beyond raw data to uncover meaningful insights—understanding what drives sales, when demand peaks, and where revenue comes from.

Coming Next: Phase 3 (Next Week)¶

In Phase 3, we’ll transform these insights into compelling visuals—using charts and dashboards to tell a clear, impactful story that makes data easy to understand and act upon.