{ "cells": [ { "cell_type": "markdown", "id": "3d1f66b5", "metadata": {}, "source": [ "# Data Cleaning and Preparation Project\n", "\n", "**Catalogue portfolio work sample**\n", "\n", "This notebook shows a complete data cleaning workflow using real public sales order data from a GitHub Gist. The goal is to transform raw records into a clean, validated dataset that can support dashboards, reporting, and business insight.\n", "\n", "**Workflow covered:**\n", "\n", "1. Load and inspect the raw dataset.\n", "2. Explore fields, missing values, duplicates, and summary statistics.\n", "3. Clean headers, text values, dates, and numeric fields.\n", "4. Validate business rules and financial formulas.\n", "5. Create calculated reporting fields.\n", "6. Build exploratory charts.\n", "7. Export the cleaned dataset, audit log, summary metrics, and chart files." ] }, { "cell_type": "code", "execution_count": 1, "id": "7bf5fac3", "metadata": {}, "outputs": [], "source": [ "from pathlib import Path\n", "import json\n", "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "\n", "OUTPUT_DIR = Path(\"outputs\")\n", "CHART_DIR = OUTPUT_DIR / \"charts\"\n", "OUTPUT_DIR.mkdir(exist_ok=True)\n", "CHART_DIR.mkdir(parents=True, exist_ok=True)\n", "\n", "pd.set_option(\"display.max_columns\", 50)\n", "pd.set_option(\"display.width\", 120)" ] }, { "cell_type": "markdown", "id": "fc43ce7d", "metadata": {}, "source": [ "## 1. Load the raw data\n", "\n", "The raw CSV is loaded directly from the same folder as the notebook. This keeps the notebook simple to run after upload." ] }, { "cell_type": "code", "execution_count": 3, "id": "b81ad009", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Raw dataset shape: 100 rows x 14 columns\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionCountryItem TypeSales ChannelOrder PriorityOrder DateOrder IDShip DateUnits SoldUnit PriceUnit CostTotal RevenueTotal CostTotal Profit
0Australia and OceaniaTuvaluBaby FoodOfflineH5/28/20106691659336/27/20109925255.28159.422533654.001582243.50951410.50
1Central America and the CaribbeanGrenadaCerealOnlineC8/22/20129638814809/15/20122804205.70117.11576782.80328376.44248406.36
2EuropeRussiaOffice SuppliesOfflineL5/2/20143414171575/8/20141779651.21524.961158502.59933903.84224598.75
3Sub-Saharan AfricaSao Tome and PrincipeFruitsOnlineC6/20/20145143217927/5/201481029.336.9275591.6656065.8419525.82
4Sub-Saharan AfricaRwandaOffice SuppliesOfflineL2/1/20131154567122/6/20135062651.21524.963296425.022657347.52639077.50
\n", "
" ], "text/plain": [ " Region Country Item Type Sales Channel Order Priority Order Date \\\n", "0 Australia and Oceania Tuvalu Baby Food Offline H 5/28/2010 \n", "1 Central America and the Caribbean Grenada Cereal Online C 8/22/2012 \n", "2 Europe Russia Office Supplies Offline L 5/2/2014 \n", "3 Sub-Saharan Africa Sao Tome and Principe Fruits Online C 6/20/2014 \n", "4 Sub-Saharan Africa Rwanda Office Supplies Offline L 2/1/2013 \n", "\n", " Order ID Ship Date Units Sold Unit Price Unit Cost Total Revenue Total Cost Total Profit \n", "0 669165933 6/27/2010 9925 255.28 159.42 2533654.00 1582243.50 951410.50 \n", "1 963881480 9/15/2012 2804 205.70 117.11 576782.80 328376.44 248406.36 \n", "2 341417157 5/8/2014 1779 651.21 524.96 1158502.59 933903.84 224598.75 \n", "3 514321792 7/5/2014 8102 9.33 6.92 75591.66 56065.84 19525.82 \n", "4 115456712 2/6/2013 5062 651.21 524.96 3296425.02 2657347.52 639077.50 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "raw_df = pd.read_csv(\"01_raw_github_sales_records.csv\")\n", "print(f\"Raw dataset shape: {raw_df.shape[0]} rows x {raw_df.shape[1]} columns\")\n", "raw_df.head()" ] }, { "cell_type": "markdown", "id": "c06cba19", "metadata": {}, "source": [ "## 2. Initial exploration\n", "\n", "Before cleaning, we review column names, data types, missing values, duplicate rows, and basic summary statistics. This shows the starting condition of the data." ] }, { "cell_type": "code", "execution_count": 3, "id": "59849aab", "metadata": { "execution": { "iopub.execute_input": "2026-06-01T12:16:02.249776Z", "iopub.status.busy": "2026-06-01T12:16:02.249372Z", "iopub.status.idle": "2026-06-01T12:16:02.306709Z", "shell.execute_reply": "2026-06-01T12:16:02.304848Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Column names:\n", "['Region', 'Country', 'Item Type', 'Sales Channel', 'Order Priority', 'Order Date', 'Order ID', 'Ship Date', 'Units Sold', 'Unit Price', 'Unit Cost', 'Total Revenue', 'Total Cost', 'Total Profit']\n", "\n", "Data types:\n", "Region object\n", "Country object\n", "Item Type object\n", "Sales Channel object\n", "Order Priority object\n", "Order Date object\n", "Order ID int64\n", "Ship Date object\n", "Units Sold int64\n", "Unit Price float64\n", "Unit Cost float64\n", "Total Revenue float64\n", "Total Cost float64\n", "Total Profit float64\n", "dtype: object\n", "\n", "Missing values by column:\n", "Region 0\n", "Country 0\n", "Item Type 0\n", "Sales Channel 0\n", "Order Priority 0\n", "Order Date 0\n", "Order ID 0\n", "Ship Date 0\n", "Units Sold 0\n", "Unit Price 0\n", "Unit Cost 0\n", "Total Revenue 0\n", "Total Cost 0\n", "Total Profit 0\n", "dtype: int64\n", "\n", "Exact duplicate rows: 0\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionCountryItem TypeSales ChannelOrder PriorityOrder DateOrder IDShip DateUnits SoldUnit PriceUnit CostTotal RevenueTotal CostTotal Profit
count1001001001001001001.000000e+02100100.000000100.000000100.0000001.000000e+021.000000e+021.000000e+02
unique7761224100NaN99NaNNaNNaNNaNNaNNaN
topSub-Saharan AfricaThe GambiaCosmeticsOfflineH5/28/2010NaN11/17/2010NaNNaNNaNNaNNaNNaN
freq3641350301NaN2NaNNaNNaNNaNNaNNaN
meanNaNNaNNaNNaNNaNNaN5.550204e+08NaN5128.710000276.761300191.0480001.373488e+069.318057e+054.416820e+05
stdNaNNaNNaNNaNNaNNaN2.606153e+08NaN2794.484562235.592241188.2081811.460029e+061.083938e+064.385379e+05
minNaNNaNNaNNaNNaNNaN1.146066e+08NaN124.0000009.3300006.9200004.870260e+033.612240e+031.258020e+03
25%NaNNaNNaNNaNNaNNaN3.389225e+08NaN2836.25000081.73000035.8400002.687212e+051.688680e+051.214436e+05
50%NaNNaNNaNNaNNaNNaN5.577086e+08NaN5382.500000179.880000107.2750007.523144e+053.635664e+052.907680e+05
75%NaNNaNNaNNaNNaNNaN7.907551e+08NaN7369.000000437.200000263.3300002.212045e+061.613870e+066.358288e+05
maxNaNNaNNaNNaNNaNNaN9.940222e+08NaN9925.000000668.270000524.9600005.997055e+064.509794e+061.719922e+06
\n", "
" ], "text/plain": [ " Region Country Item Type Sales Channel Order Priority Order Date Order ID Ship Date \\\n", "count 100 100 100 100 100 100 1.000000e+02 100 \n", "unique 7 76 12 2 4 100 NaN 99 \n", "top Sub-Saharan Africa The Gambia Cosmetics Offline H 5/28/2010 NaN 11/17/2010 \n", "freq 36 4 13 50 30 1 NaN 2 \n", "mean NaN NaN NaN NaN NaN NaN 5.550204e+08 NaN \n", "std NaN NaN NaN NaN NaN NaN 2.606153e+08 NaN \n", "min NaN NaN NaN NaN NaN NaN 1.146066e+08 NaN \n", "25% NaN NaN NaN NaN NaN NaN 3.389225e+08 NaN \n", "50% NaN NaN NaN NaN NaN NaN 5.577086e+08 NaN \n", "75% NaN NaN NaN NaN NaN NaN 7.907551e+08 NaN \n", "max NaN NaN NaN NaN NaN NaN 9.940222e+08 NaN \n", "\n", " Units Sold Unit Price Unit Cost Total Revenue Total Cost Total Profit \n", "count 100.000000 100.000000 100.000000 1.000000e+02 1.000000e+02 1.000000e+02 \n", "unique NaN NaN NaN NaN NaN NaN \n", "top NaN NaN NaN NaN NaN NaN \n", "freq NaN NaN NaN NaN NaN NaN \n", "mean 5128.710000 276.761300 191.048000 1.373488e+06 9.318057e+05 4.416820e+05 \n", "std 2794.484562 235.592241 188.208181 1.460029e+06 1.083938e+06 4.385379e+05 \n", "min 124.000000 9.330000 6.920000 4.870260e+03 3.612240e+03 1.258020e+03 \n", "25% 2836.250000 81.730000 35.840000 2.687212e+05 1.688680e+05 1.214436e+05 \n", "50% 5382.500000 179.880000 107.275000 7.523144e+05 3.635664e+05 2.907680e+05 \n", "75% 7369.000000 437.200000 263.330000 2.212045e+06 1.613870e+06 6.358288e+05 \n", "max 9925.000000 668.270000 524.960000 5.997055e+06 4.509794e+06 1.719922e+06 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "print(\"Column names:\")\n", "print(raw_df.columns.tolist())\n", "\n", "print(\"\\nData types:\")\n", "print(raw_df.dtypes)\n", "\n", "print(\"\\nMissing values by column:\")\n", "print(raw_df.isna().sum())\n", "\n", "print(\"\\nExact duplicate rows:\", raw_df.duplicated().sum())\n", "raw_df.describe(include=\"all\")" ] }, { "cell_type": "markdown", "id": "f5115cb1-8b94-4cd7-a086-f99be0d1817b", "metadata": {}, "source": [ "## 3. Create a data quality audit log\n", "\n", "The audit log records each check, the issue being tested, the action taken, the status, and the number of affected records. This makes the cleaning process transparent for this project." ] }, { "cell_type": "code", "execution_count": 5, "id": "832e2094", "metadata": {}, "outputs": [], "source": [ "quality_log = []\n", "\n", "def log(check, issue, action, status, records_affected):\n", " quality_log.append({\n", " \"quality_check\": check,\n", " \"issue_found\": issue,\n", " \"action_taken\": action,\n", " \"status\": status,\n", " \"records_affected\": int(records_affected) if isinstance(records_affected, (int, np.integer, float, np.floating)) else records_affected\n", " })" ] }, { "cell_type": "markdown", "id": "918017d3", "metadata": {}, "source": [ "## 4. Standardize column names and text fields\n", "\n", "Column names are converted to snake_case, and key text fields are trimmed. This helps avoid errors when filtering, grouping, joining, or building dashboards." ] }, { "cell_type": "code", "execution_count": 6, "id": "8f635c6c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
regioncountryitem_typesales_channelorder_priorityorder_dateorder_idship_dateunits_soldunit_priceunit_costtotal_revenuetotal_costtotal_profit
0Australia and OceaniaTuvaluBaby FoodOfflineH5/28/20106691659336/27/20109925255.28159.422533654.001582243.50951410.50
1Central America and the CaribbeanGrenadaCerealOnlineC8/22/20129638814809/15/20122804205.70117.11576782.80328376.44248406.36
2EuropeRussiaOffice SuppliesOfflineL5/2/20143414171575/8/20141779651.21524.961158502.59933903.84224598.75
3Sub-Saharan AfricaSao Tome and PrincipeFruitsOnlineC6/20/20145143217927/5/201481029.336.9275591.6656065.8419525.82
4Sub-Saharan AfricaRwandaOffice SuppliesOfflineL2/1/20131154567122/6/20135062651.21524.963296425.022657347.52639077.50
\n", "
" ], "text/plain": [ " region country item_type sales_channel order_priority order_date \\\n", "0 Australia and Oceania Tuvalu Baby Food Offline H 5/28/2010 \n", "1 Central America and the Caribbean Grenada Cereal Online C 8/22/2012 \n", "2 Europe Russia Office Supplies Offline L 5/2/2014 \n", "3 Sub-Saharan Africa Sao Tome and Principe Fruits Online C 6/20/2014 \n", "4 Sub-Saharan Africa Rwanda Office Supplies Offline L 2/1/2013 \n", "\n", " order_id ship_date units_sold unit_price unit_cost total_revenue total_cost total_profit \n", "0 669165933 6/27/2010 9925 255.28 159.42 2533654.00 1582243.50 951410.50 \n", "1 963881480 9/15/2012 2804 205.70 117.11 576782.80 328376.44 248406.36 \n", "2 341417157 5/8/2014 1779 651.21 524.96 1158502.59 933903.84 224598.75 \n", "3 514321792 7/5/2014 8102 9.33 6.92 75591.66 56065.84 19525.82 \n", "4 115456712 2/6/2013 5062 651.21 524.96 3296425.02 2657347.52 639077.50 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "clean_df = raw_df.copy()\n", "original_rows, original_cols = clean_df.shape\n", "\n", "old_cols = clean_df.columns.tolist()\n", "clean_df.columns = (\n", " clean_df.columns\n", " .str.strip()\n", " .str.lower()\n", " .str.replace(\" \", \"_\", regex=False)\n", " .str.replace(\"-\", \"_\", regex=False)\n", ")\n", "log(\"Column names\", \"Raw headers used spaces and title case.\", \"Converted headers to clean snake_case names.\", \"Completed\", len(old_cols))\n", "\n", "text_cols = [\"region\", \"country\", \"item_type\", \"sales_channel\", \"order_priority\"]\n", "for col in text_cols:\n", " clean_df[col] = clean_df[col].astype(str).str.strip()\n", "\n", "log(\"Text fields\", \"Categorical fields can contain hidden spaces or inconsistent casing.\", \"Trimmed text values in region, country, item_type, sales_channel, and order_priority.\", \"Completed\", len(text_cols))\n", "clean_df.head()" ] }, { "cell_type": "markdown", "id": "74ebafbc", "metadata": {}, "source": [ "## 5. Check missing values, duplicates, and unique order IDs\n", "\n", "Missing values and duplicates can cause incorrect totals, repeated revenue, and unreliable dashboard numbers. Order IDs are also checked to make sure every transaction has a unique identifier." ] }, { "cell_type": "code", "execution_count": 7, "id": "0d442c71", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Missing values: 0\n", "Duplicate rows: 0\n", "Duplicate order IDs: 0\n" ] } ], "source": [ "missing_before = int(clean_df.isna().sum().sum())\n", "log(\"Missing values\", \"Blank cells can break analysis and reporting.\", \"Checked all fields for missing values.\", \"Passed\" if missing_before == 0 else \"Needs review\", missing_before)\n", "\n", "dup_rows = int(clean_df.duplicated().sum())\n", "if dup_rows:\n", " clean_df = clean_df.drop_duplicates()\n", "log(\"Duplicate rows\", \"Repeated rows inflate revenue, cost, units, and profit.\", \"Checked and removed exact duplicate rows.\", \"Passed\" if dup_rows == 0 else \"Completed\", dup_rows)\n", "\n", "dup_ids = int(clean_df[\"order_id\"].duplicated().sum())\n", "log(\"Duplicate order IDs\", \"Each order_id should identify one transaction.\", \"Checked order_id uniqueness.\", \"Passed\" if dup_ids == 0 else \"Needs review\", dup_ids)\n", "\n", "print(\"Missing values:\", missing_before)\n", "print(\"Duplicate rows:\", dup_rows)\n", "print(\"Duplicate order IDs:\", dup_ids)" ] }, { "cell_type": "markdown", "id": "ec80e7aa", "metadata": {}, "source": [ "## 6. Convert dates and numeric fields\n", "\n", "Dates are converted to datetime values, while units and financial columns are converted to numeric values. This prevents calculation and plotting errors." ] }, { "cell_type": "code", "execution_count": 8, "id": "26d2a3e0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Invalid date values: 0\n", "Invalid numeric values: 0\n" ] }, { "data": { "text/plain": [ "region object\n", "country object\n", "item_type object\n", "sales_channel object\n", "order_priority object\n", "order_date datetime64[ns]\n", "order_id int64\n", "ship_date datetime64[ns]\n", "units_sold int64\n", "unit_price float64\n", "unit_cost float64\n", "total_revenue float64\n", "total_cost float64\n", "total_profit float64\n", "dtype: object" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "for col in [\"order_date\", \"ship_date\"]:\n", " clean_df[col] = pd.to_datetime(clean_df[col], errors=\"coerce\")\n", "\n", "invalid_dates = int(clean_df[[\"order_date\", \"ship_date\"]].isna().sum().sum())\n", "log(\"Date parsing\", \"Dates must be real dates for time analysis.\", \"Converted order_date and ship_date to datetime fields.\", \"Passed\" if invalid_dates == 0 else \"Needs review\", invalid_dates)\n", "\n", "numeric_cols = [\"units_sold\", \"unit_price\", \"unit_cost\", \"total_revenue\", \"total_cost\", \"total_profit\"]\n", "for col in numeric_cols:\n", " clean_df[col] = pd.to_numeric(clean_df[col], errors=\"coerce\")\n", "\n", "invalid_numbers = int(clean_df[numeric_cols].isna().sum().sum())\n", "log(\"Numeric conversion\", \"Financial and quantity fields must be numeric.\", \"Converted units, price, cost, revenue, and profit fields to numeric values.\", \"Passed\" if invalid_numbers == 0 else \"Needs review\", invalid_numbers)\n", "\n", "print(\"Invalid date values:\", invalid_dates)\n", "print(\"Invalid numeric values:\", invalid_numbers)\n", "clean_df.dtypes" ] }, { "cell_type": "markdown", "id": "6f47dbc8", "metadata": {}, "source": [ "## 7. Validate business rules and financial formulas\n", "\n", "The cleaned dataset is checked against rules that matter for sales data: positive units and prices, valid shipping dates, and correct revenue, cost, and profit calculations." ] }, { "cell_type": "code", "execution_count": 9, "id": "6b5a7aac", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
checkresult
0Non-positive units/price/cost0
1Negative profit records0
2Date sequence errors0
3Revenue formula errors0
4Cost formula errors0
5Profit formula errors0
\n", "
" ], "text/plain": [ " check result\n", "0 Non-positive units/price/cost 0\n", "1 Negative profit records 0\n", "2 Date sequence errors 0\n", "3 Revenue formula errors 0\n", "4 Cost formula errors 0\n", "5 Profit formula errors 0" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "non_positive = int((clean_df[[\"units_sold\", \"unit_price\", \"unit_cost\"]] <= 0).sum().sum())\n", "log(\"Positive values\", \"Units, unit price, and unit cost should be above zero.\", \"Checked units_sold, unit_price, and unit_cost.\", \"Passed\" if non_positive == 0 else \"Needs review\", non_positive)\n", "\n", "negative_profit = int((clean_df[\"total_profit\"] < 0).sum())\n", "log(\"Profit review\", \"Negative profit can be valid, but should be reviewed.\", \"Checked records with negative total profit.\", \"Passed\" if negative_profit == 0 else \"Reviewed\", negative_profit)\n", "\n", "date_sequence_errors = int((clean_df[\"ship_date\"] < clean_df[\"order_date\"]).sum())\n", "log(\"Date sequence\", \"Ship date should not be earlier than order date.\", \"Checked ship_date against order_date.\", \"Passed\" if date_sequence_errors == 0 else \"Needs review\", date_sequence_errors)\n", "\n", "rev_err = int((~np.isclose(clean_df[\"total_revenue\"], clean_df[\"units_sold\"] * clean_df[\"unit_price\"], atol=0.01)).sum())\n", "cost_err = int((~np.isclose(clean_df[\"total_cost\"], clean_df[\"units_sold\"] * clean_df[\"unit_cost\"], atol=0.01)).sum())\n", "profit_err = int((~np.isclose(clean_df[\"total_profit\"], clean_df[\"total_revenue\"] - clean_df[\"total_cost\"], atol=0.01)).sum())\n", "\n", "log(\"Revenue formula\", \"Total revenue should equal units_sold multiplied by unit_price.\", \"Validated revenue formula with a small rounding tolerance.\", \"Passed\" if rev_err == 0 else \"Needs review\", rev_err)\n", "log(\"Cost formula\", \"Total cost should equal units_sold multiplied by unit_cost.\", \"Validated cost formula with a small rounding tolerance.\", \"Passed\" if cost_err == 0 else \"Needs review\", cost_err)\n", "log(\"Profit formula\", \"Total profit should equal total_revenue minus total_cost.\", \"Validated profit formula with a small rounding tolerance.\", \"Passed\" if profit_err == 0 else \"Needs review\", profit_err)\n", "\n", "validation_results = pd.DataFrame({\n", " \"check\": [\"Non-positive units/price/cost\", \"Negative profit records\", \"Date sequence errors\", \"Revenue formula errors\", \"Cost formula errors\", \"Profit formula errors\"],\n", " \"result\": [non_positive, negative_profit, date_sequence_errors, rev_err, cost_err, profit_err]\n", "})\n", "validation_results" ] }, { "cell_type": "markdown", "id": "e9589ade", "metadata": {}, "source": [ "## 8. Create calculated reporting fields\n", "\n", "New fields are added for dashboarding and reporting, including processing days, order month, gross margin, profit margin percentage, and revenue per unit." ] }, { "cell_type": "code", "execution_count": 10, "id": "38ac62be", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
regioncountryitem_typesales_channelorder_priorityorder_dateorder_idship_dateunits_soldunit_priceunit_costtotal_revenuetotal_costtotal_profitprocessing_daysorder_yearorder_monthgross_marginprofit_margin_pctrevenue_per_unit
0Australia and OceaniaTuvaluBaby FoodOfflineH2010-05-286691659332010-06-279925255.28159.422533654.001582243.50951410.503020102010-05951410.500.375509255.28
1Central America and the CaribbeanGrenadaCerealOnlineC2012-08-229638814802012-09-152804205.70117.11576782.80328376.44248406.362420122012-08248406.360.430676205.70
2EuropeRussiaOffice SuppliesOfflineL2014-05-023414171572014-05-081779651.21524.961158502.59933903.84224598.75620142014-05224598.750.193870651.21
3Sub-Saharan AfricaSao Tome and PrincipeFruitsOnlineC2014-06-205143217922014-07-0581029.336.9275591.6656065.8419525.821520142014-0619525.820.2583079.33
4Sub-Saharan AfricaRwandaOffice SuppliesOfflineL2013-02-011154567122013-02-065062651.21524.963296425.022657347.52639077.50520132013-02639077.500.193870651.21
\n", "
" ], "text/plain": [ " region country item_type sales_channel order_priority order_date \\\n", "0 Australia and Oceania Tuvalu Baby Food Offline H 2010-05-28 \n", "1 Central America and the Caribbean Grenada Cereal Online C 2012-08-22 \n", "2 Europe Russia Office Supplies Offline L 2014-05-02 \n", "3 Sub-Saharan Africa Sao Tome and Principe Fruits Online C 2014-06-20 \n", "4 Sub-Saharan Africa Rwanda Office Supplies Offline L 2013-02-01 \n", "\n", " order_id ship_date units_sold unit_price unit_cost total_revenue total_cost total_profit processing_days \\\n", "0 669165933 2010-06-27 9925 255.28 159.42 2533654.00 1582243.50 951410.50 30 \n", "1 963881480 2012-09-15 2804 205.70 117.11 576782.80 328376.44 248406.36 24 \n", "2 341417157 2014-05-08 1779 651.21 524.96 1158502.59 933903.84 224598.75 6 \n", "3 514321792 2014-07-05 8102 9.33 6.92 75591.66 56065.84 19525.82 15 \n", "4 115456712 2013-02-06 5062 651.21 524.96 3296425.02 2657347.52 639077.50 5 \n", "\n", " order_year order_month gross_margin profit_margin_pct revenue_per_unit \n", "0 2010 2010-05 951410.50 0.375509 255.28 \n", "1 2012 2012-08 248406.36 0.430676 205.70 \n", "2 2014 2014-05 224598.75 0.193870 651.21 \n", "3 2014 2014-06 19525.82 0.258307 9.33 \n", "4 2013 2013-02 639077.50 0.193870 651.21 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "clean_df[\"processing_days\"] = (clean_df[\"ship_date\"] - clean_df[\"order_date\"]).dt.days\n", "clean_df[\"order_year\"] = clean_df[\"order_date\"].dt.year\n", "clean_df[\"order_month\"] = clean_df[\"order_date\"].dt.to_period(\"M\").astype(str)\n", "clean_df[\"gross_margin\"] = clean_df[\"total_revenue\"] - clean_df[\"total_cost\"]\n", "clean_df[\"profit_margin_pct\"] = np.where(clean_df[\"total_revenue\"] != 0, clean_df[\"total_profit\"] / clean_df[\"total_revenue\"], np.nan)\n", "clean_df[\"revenue_per_unit\"] = clean_df[\"total_revenue\"] / clean_df[\"units_sold\"]\n", "\n", "log(\"Feature engineering\", \"Reporting needs clear calculated fields.\", \"Added processing_days, order_year, order_month, gross_margin, profit_margin_pct, and revenue_per_unit.\", \"Completed\", 6)\n", "clean_df.head()" ] }, { "cell_type": "markdown", "id": "0a471546", "metadata": {}, "source": [ "## 9. Exploratory data analysis\n", "\n", "The following views summarize the cleaned data and show how it can support business reporting." ] }, { "cell_type": "code", "execution_count": 11, "id": "48f06594", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
regionorderstotal_unitstotal_revenuetotal_profitavg_margin_pct
6Sub-Saharan Africa3618287039672031.4312183211.400.350904
3Europe229811733368932.1111082938.630.375785
0Asia115996721347091.026113845.870.364543
1Australia and Oceania116832514094265.134722160.030.341595
4Middle East and North Africa104867814052706.585761191.860.393661
2Central America and the Caribbean7357719170385.492846907.850.392348
5North America3191435643356.551457942.760.287079
\n", "
" ], "text/plain": [ " region orders total_units total_revenue total_profit avg_margin_pct\n", "6 Sub-Saharan Africa 36 182870 39672031.43 12183211.40 0.350904\n", "3 Europe 22 98117 33368932.11 11082938.63 0.375785\n", "0 Asia 11 59967 21347091.02 6113845.87 0.364543\n", "1 Australia and Oceania 11 68325 14094265.13 4722160.03 0.341595\n", "4 Middle East and North Africa 10 48678 14052706.58 5761191.86 0.393661\n", "2 Central America and the Caribbean 7 35771 9170385.49 2846907.85 0.392348\n", "5 North America 3 19143 5643356.55 1457942.76 0.287079" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "region_summary = clean_df.groupby(\"region\", as_index=False).agg(\n", " orders=(\"order_id\", \"count\"),\n", " total_units=(\"units_sold\", \"sum\"),\n", " total_revenue=(\"total_revenue\", \"sum\"),\n", " total_profit=(\"total_profit\", \"sum\"),\n", " avg_margin_pct=(\"profit_margin_pct\", \"mean\")\n", ").sort_values(\"total_revenue\", ascending=False)\n", "region_summary" ] }, { "cell_type": "code", "execution_count": 12, "id": "130de33d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
item_typeorderstotal_revenuetotal_profitavg_processing_days
4Cosmetics1336601509.6014556048.6623.461538
6Household929889712.297412605.7123.222222
8Office Supplies1230585380.075929583.7520.083333
3Clothes137787292.805233334.4029.923077
0Baby Food710350327.603886643.7025.714286
2Cereal75322898.902292443.4321.714286
11Vegetables63089057.061265819.6324.833333
9Personal Care103980904.841220622.4819.000000
1Beverages82690794.60888047.2822.500000
10Snacks32080733.46751944.189.000000
\n", "
" ], "text/plain": [ " item_type orders total_revenue total_profit avg_processing_days\n", "4 Cosmetics 13 36601509.60 14556048.66 23.461538\n", "6 Household 9 29889712.29 7412605.71 23.222222\n", "8 Office Supplies 12 30585380.07 5929583.75 20.083333\n", "3 Clothes 13 7787292.80 5233334.40 29.923077\n", "0 Baby Food 7 10350327.60 3886643.70 25.714286\n", "2 Cereal 7 5322898.90 2292443.43 21.714286\n", "11 Vegetables 6 3089057.06 1265819.63 24.833333\n", "9 Personal Care 10 3980904.84 1220622.48 19.000000\n", "1 Beverages 8 2690794.60 888047.28 22.500000\n", "10 Snacks 3 2080733.46 751944.18 9.000000" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "item_summary = clean_df.groupby(\"item_type\", as_index=False).agg(\n", " orders=(\"order_id\", \"count\"),\n", " total_revenue=(\"total_revenue\", \"sum\"),\n", " total_profit=(\"total_profit\", \"sum\"),\n", " avg_processing_days=(\"processing_days\", \"mean\")\n", ").sort_values(\"total_profit\", ascending=False)\n", "item_summary.head(10)" ] }, { "cell_type": "markdown", "id": "002da57e", "metadata": {}, "source": [ "## 10. Charts\n", "\n", "These charts show revenue, profit, channel mix, trends, relationships, and correlations in the cleaned dataset." ] }, { "cell_type": "code", "execution_count": 12, "id": "1cef4db2", "metadata": { "execution": { "iopub.execute_input": "2026-06-01T12:16:02.514098Z", "iopub.status.busy": "2026-06-01T12:16:02.513740Z", "iopub.status.idle": "2026-06-01T12:16:04.430196Z", "shell.execute_reply": "2026-06-01T12:16:04.428204Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Charts saved in outputs/charts\n" ] } ], "source": [ "def savefig(path):\n", " plt.tight_layout()\n", " plt.savefig(path, dpi=160, bbox_inches=\"tight\")\n", " plt.close()\n", "\n", "plt.figure(figsize=(9,5))\n", "clean_df.groupby(\"region\")[\"total_revenue\"].sum().sort_values(ascending=True).plot(kind=\"barh\")\n", "plt.title(\"Total Revenue by Region\")\n", "plt.xlabel(\"Total Revenue\")\n", "plt.ylabel(\"Region\")\n", "savefig(CHART_DIR / \"01_total_revenue_by_region.png\")\n", "\n", "plt.figure(figsize=(9,5))\n", "clean_df.groupby(\"item_type\")[\"total_profit\"].sum().sort_values(ascending=False).plot(kind=\"bar\")\n", "plt.title(\"Total Profit by Item Type\")\n", "plt.xlabel(\"Item Type\")\n", "plt.ylabel(\"Total Profit\")\n", "plt.xticks(rotation=45, ha=\"right\")\n", "savefig(CHART_DIR / \"02_total_profit_by_item_type.png\")\n", "\n", "plt.figure(figsize=(7,5))\n", "clean_df[\"sales_channel\"].value_counts().plot(kind=\"bar\")\n", "plt.title(\"Sales Channel Mix\")\n", "plt.xlabel(\"Sales Channel\")\n", "plt.ylabel(\"Order Count\")\n", "savefig(CHART_DIR / \"03_sales_channel_mix.png\")\n", "\n", "plt.figure(figsize=(9,5))\n", "clean_df.groupby(\"order_month\")[\"total_revenue\"].sum().sort_index().plot(kind=\"line\", marker=\"o\")\n", "plt.title(\"Monthly Revenue Trend\")\n", "plt.xlabel(\"Order Month\")\n", "plt.ylabel(\"Total Revenue\")\n", "plt.xticks(rotation=45, ha=\"right\")\n", "savefig(CHART_DIR / \"04_monthly_revenue_trend.png\")\n", "\n", "plt.figure(figsize=(8,5))\n", "plt.scatter(clean_df[\"units_sold\"], clean_df[\"total_revenue\"])\n", "plt.title(\"Units Sold vs Total Revenue\")\n", "plt.xlabel(\"Units Sold\")\n", "plt.ylabel(\"Total Revenue\")\n", "savefig(CHART_DIR / \"05_units_vs_revenue_scatter.png\")\n", "\n", "corr_cols = [\"units_sold\", \"unit_price\", \"unit_cost\", \"total_revenue\", \"total_cost\", \"total_profit\", \"processing_days\", \"profit_margin_pct\"]\n", "plt.figure(figsize=(7,6))\n", "plt.imshow(clean_df[corr_cols].corr(numeric_only=True))\n", "plt.xticks(range(len(corr_cols)), corr_cols, rotation=45, ha=\"right\")\n", "plt.yticks(range(len(corr_cols)), corr_cols)\n", "plt.colorbar(label=\"Correlation\")\n", "plt.title(\"Correlation Matrix\")\n", "savefig(CHART_DIR / \"06_correlation_matrix.png\")\n", "\n", "print(\"Charts saved in outputs/charts\")" ] }, { "cell_type": "markdown", "id": "bf926f56", "metadata": {}, "source": [ "## 11. Export final project files\n", "\n", "The cleaned dataset, data quality audit log, summary metrics, and data dictionary are exported for catalogue upload and project review." ] }, { "cell_type": "code", "execution_count": 13, "id": "1cfe295e", "metadata": { "execution": { "iopub.execute_input": "2026-06-01T12:16:04.434352Z", "iopub.status.busy": "2026-06-01T12:16:04.433996Z", "iopub.status.idle": "2026-06-01T12:16:04.475468Z", "shell.execute_reply": "2026-06-01T12:16:04.473175Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
metricvalue
0Raw rows100
1Raw columns14
2Final rows100
3Final columns20
4Missing values remaining0
5Duplicate rows remaining0
6Duplicate order IDs remaining0
7Date sequence errors0
8Revenue formula errors0
9Cost formula errors0
10Profit formula errors0
11Charts created6
\n", "
" ], "text/plain": [ " metric value\n", "0 Raw rows 100\n", "1 Raw columns 14\n", "2 Final rows 100\n", "3 Final columns 20\n", "4 Missing values remaining 0\n", "5 Duplicate rows remaining 0\n", "6 Duplicate order IDs remaining 0\n", "7 Date sequence errors 0\n", "8 Revenue formula errors 0\n", "9 Cost formula errors 0\n", "10 Profit formula errors 0\n", "11 Charts created 6" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "clean_df = clean_df.sort_values([\"order_date\", \"order_id\"]).reset_index(drop=True)\n", "clean_df.to_csv(\"02_cleaned_sales_records.csv\", index=False)\n", "\n", "quality_df = pd.DataFrame(quality_log)\n", "quality_df.to_csv(OUTPUT_DIR / \"data_quality_audit_log.csv\", index=False)\n", "\n", "summary = pd.DataFrame({\n", " \"metric\": [\"Raw rows\", \"Raw columns\", \"Final rows\", \"Final columns\", \"Missing values remaining\", \"Duplicate rows remaining\", \"Duplicate order IDs remaining\", \"Date sequence errors\", \"Revenue formula errors\", \"Cost formula errors\", \"Profit formula errors\", \"Charts created\"],\n", " \"value\": [original_rows, original_cols, clean_df.shape[0], clean_df.shape[1], int(clean_df.isna().sum().sum()), int(clean_df.duplicated().sum()), int(clean_df[\"order_id\"].duplicated().sum()), date_sequence_errors, rev_err, cost_err, profit_err, 6]\n", "})\n", "summary.to_csv(OUTPUT_DIR / \"cleaning_summary_metrics.csv\", index=False)\n", "\n", "data_dictionary = pd.DataFrame([\n", " (\"region\", \"Sales region for the order.\"),\n", " (\"country\", \"Country where the order was placed.\"),\n", " (\"item_type\", \"Product category sold.\"),\n", " (\"sales_channel\", \"Sales channel for the order.\"),\n", " (\"order_priority\", \"Priority category assigned to the order.\"),\n", " (\"order_date\", \"Date the order was placed.\"),\n", " (\"order_id\", \"Unique order identifier.\"),\n", " (\"ship_date\", \"Date the order shipped.\"),\n", " (\"units_sold\", \"Number of units sold.\"),\n", " (\"unit_price\", \"Selling price per unit.\"),\n", " (\"unit_cost\", \"Cost per unit.\"),\n", " (\"total_revenue\", \"units_sold multiplied by unit_price.\"),\n", " (\"total_cost\", \"units_sold multiplied by unit_cost.\"),\n", " (\"total_profit\", \"total_revenue minus total_cost.\"),\n", " (\"processing_days\", \"Number of days between order_date and ship_date.\"),\n", " (\"order_year\", \"Year extracted from order_date.\"),\n", " (\"order_month\", \"Month extracted from order_date.\"),\n", " (\"gross_margin\", \"total_revenue minus total_cost.\"),\n", " (\"profit_margin_pct\", \"total_profit divided by total_revenue.\"),\n", " (\"revenue_per_unit\", \"total_revenue divided by units_sold.\")\n", "], columns=[\"field\", \"description\"])\n", "data_dictionary.to_csv(OUTPUT_DIR / \"data_dictionary.csv\", index=False)\n", "\n", "final_validation = {\n", " \"missing_values_remaining\": int(clean_df.isna().sum().sum()),\n", " \"duplicate_rows_remaining\": int(clean_df.duplicated().sum()),\n", " \"duplicate_order_ids_remaining\": int(clean_df[\"order_id\"].duplicated().sum()),\n", " \"date_sequence_errors\": date_sequence_errors,\n", " \"revenue_formula_errors\": rev_err,\n", " \"cost_formula_errors\": cost_err,\n", " \"profit_formula_errors\": profit_err\n", "}\n", "(OUTPUT_DIR / \"final_validation_results.json\").write_text(json.dumps(final_validation, indent=2))\n", "\n", "summary" ] }, { "cell_type": "markdown", "id": "c6b7c09a", "metadata": {}, "source": [ "## 12. Final validation conclusion\n", "\n", "The dataset is now clean, consistent, and validated for dashboards, reporting, and deeper analysis. Final checks show zero missing values, zero duplicate rows, zero duplicate order IDs, zero date sequence errors, and zero financial formula errors." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.7" } }, "nbformat": 4, "nbformat_minor": 5 }