Supply_Chain_Analytics.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HWuNiP14K2f5"
      },
      "source": [
        "## Project Description\n",
        "The project provides a real-world dataset focusing on supply chain analytics. As the main data analyst for Just In Time, you will help solve key shipment and inventory management challenges, analyze supply chain inefficiencies, and create insightful dashboards to inform business stakeholders about potential problems and propose structural business improvements.\n",
        "\n",
        "## Objective\n",
        "In this project, my primary focus is on addressing key challenges related to shipment and inventory management within the supply chain. To achieve this goal efficiently, the project has been divided into few objectives:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1TeVZPm2bM4W"
      },
      "source": [
        "# Data Preparation"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "8P2SVZSEZRjr"
      },
      "outputs": [],
      "source": [
        "# import the libraries\n",
        "import pandas as pd\n",
        "import numpy as np"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "DVKl60M2Zxi7"
      },
      "outputs": [],
      "source": [
        "# import datasets\n",
        "df_orders = pd.read_csv('/content/sample_data/orders_and_shipments.csv',encoding='ISO-8859-1')\n",
        "df_inventory = pd.read_csv('/content/sample_data/inventory.csv')\n",
        "df_fulfillment = pd.read_csv('/content/sample_data/fulfillment.csv')"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_orders.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 377
        },
        "id": "007KSxs7DCTv",
        "outputId": "60a95c62-3429-4e4d-fbf1-ba68ae680d36"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    Order ID    Order Item ID    Order YearMonth    Order Year   \\\n",
              "0        3535             8793             201502          2015   \n",
              "1        4133            10320             201503          2015   \n",
              "2        7396            18517             201504          2015   \n",
              "3       11026            27608             201506          2015   \n",
              "4       11026            27609             201506          2015   \n",
              "\n",
              "    Order Month    Order Day  Order Time  Order Quantity Product Department  \\\n",
              "0              2           21      14:07               1           Fan Shop   \n",
              "1              3            2      07:37               1           Fan Shop   \n",
              "2              4           18      22:47               1           Fan Shop   \n",
              "3              6           10      22:32               1           Fan Shop   \n",
              "4              6           10      22:32               1           Fan Shop   \n",
              "\n",
              "  Product Category  ... Customer Country  Warehouse Country Shipment Year  \\\n",
              "0          Fishing  ...           Mexico        Puerto Rico          2015   \n",
              "1          Fishing  ...           Brazil        Puerto Rico          2015   \n",
              "2          Fishing  ...           Mexico        Puerto Rico          2015   \n",
              "3          Fishing  ...          Denmark        Puerto Rico          2015   \n",
              "4          Fishing  ...          Denmark        Puerto Rico          2015   \n",
              "\n",
              "  Shipment Month Shipment Day   Shipment Mode   Shipment Days - Scheduled   \\\n",
              "0              2           27  Standard Class                            4   \n",
              "1              3            6  Standard Class                            4   \n",
              "2              4           20  Standard Class                            4   \n",
              "3              6           12  Standard Class                            4   \n",
              "4              6           12  Standard Class                            4   \n",
              "\n",
              "    Gross Sales    Discount %   Profit   \n",
              "0            400          0.25      200  \n",
              "1            400          0.09      200  \n",
              "2            400          0.06      200  \n",
              "3            400          0.15      200  \n",
              "4            400          0.13      200  \n",
              "\n",
              "[5 rows x 24 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ee4ac6ba-057e-44e1-8f78-7a50e20a5c1a\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Order ID</th>\n",
              "      <th>Order Item ID</th>\n",
              "      <th>Order YearMonth</th>\n",
              "      <th>Order Year</th>\n",
              "      <th>Order Month</th>\n",
              "      <th>Order Day</th>\n",
              "      <th>Order Time</th>\n",
              "      <th>Order Quantity</th>\n",
              "      <th>Product Department</th>\n",
              "      <th>Product Category</th>\n",
              "      <th>...</th>\n",
              "      <th>Customer Country</th>\n",
              "      <th>Warehouse Country</th>\n",
              "      <th>Shipment Year</th>\n",
              "      <th>Shipment Month</th>\n",
              "      <th>Shipment Day</th>\n",
              "      <th>Shipment Mode</th>\n",
              "      <th>Shipment Days - Scheduled</th>\n",
              "      <th>Gross Sales</th>\n",
              "      <th>Discount %</th>\n",
              "      <th>Profit</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>3535</td>\n",
              "      <td>8793</td>\n",
              "      <td>201502</td>\n",
              "      <td>2015</td>\n",
              "      <td>2</td>\n",
              "      <td>21</td>\n",
              "      <td>14:07</td>\n",
              "      <td>1</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Fishing</td>\n",
              "      <td>...</td>\n",
              "      <td>Mexico</td>\n",
              "      <td>Puerto Rico</td>\n",
              "      <td>2015</td>\n",
              "      <td>2</td>\n",
              "      <td>27</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>400</td>\n",
              "      <td>0.25</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>4133</td>\n",
              "      <td>10320</td>\n",
              "      <td>201503</td>\n",
              "      <td>2015</td>\n",
              "      <td>3</td>\n",
              "      <td>2</td>\n",
              "      <td>07:37</td>\n",
              "      <td>1</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Fishing</td>\n",
              "      <td>...</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>Puerto Rico</td>\n",
              "      <td>2015</td>\n",
              "      <td>3</td>\n",
              "      <td>6</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>400</td>\n",
              "      <td>0.09</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>7396</td>\n",
              "      <td>18517</td>\n",
              "      <td>201504</td>\n",
              "      <td>2015</td>\n",
              "      <td>4</td>\n",
              "      <td>18</td>\n",
              "      <td>22:47</td>\n",
              "      <td>1</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Fishing</td>\n",
              "      <td>...</td>\n",
              "      <td>Mexico</td>\n",
              "      <td>Puerto Rico</td>\n",
              "      <td>2015</td>\n",
              "      <td>4</td>\n",
              "      <td>20</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>400</td>\n",
              "      <td>0.06</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11026</td>\n",
              "      <td>27608</td>\n",
              "      <td>201506</td>\n",
              "      <td>2015</td>\n",
              "      <td>6</td>\n",
              "      <td>10</td>\n",
              "      <td>22:32</td>\n",
              "      <td>1</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Fishing</td>\n",
              "      <td>...</td>\n",
              "      <td>Denmark</td>\n",
              "      <td>Puerto Rico</td>\n",
              "      <td>2015</td>\n",
              "      <td>6</td>\n",
              "      <td>12</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>400</td>\n",
              "      <td>0.15</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11026</td>\n",
              "      <td>27609</td>\n",
              "      <td>201506</td>\n",
              "      <td>2015</td>\n",
              "      <td>6</td>\n",
              "      <td>10</td>\n",
              "      <td>22:32</td>\n",
              "      <td>1</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Fishing</td>\n",
              "      <td>...</td>\n",
              "      <td>Denmark</td>\n",
              "      <td>Puerto Rico</td>\n",
              "      <td>2015</td>\n",
              "      <td>6</td>\n",
              "      <td>12</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>400</td>\n",
              "      <td>0.13</td>\n",
              "      <td>200</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 24 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ee4ac6ba-057e-44e1-8f78-7a50e20a5c1a')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-ee4ac6ba-057e-44e1-8f78-7a50e20a5c1a button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-ee4ac6ba-057e-44e1-8f78-7a50e20a5c1a');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-f63edd88-4999-439b-9da8-9ff881a34f87\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-f63edd88-4999-439b-9da8-9ff881a34f87')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-f63edd88-4999-439b-9da8-9ff881a34f87 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_inventory.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "I5jZ20_fC27K",
        "outputId": "f5c3d975-94c7-4daf-e911-8b6a1f5115e5"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                    Product Name   Year Month   \\\n",
              "0               Perfect Fitness Perfect Rip Deck        201712   \n",
              "1           Nike Men's Dri-FIT Victory Golf Polo        201712   \n",
              "2               O'Brien Men's Neoprene Life Vest        201712   \n",
              "3              Nike Men's Free 5.0+ Running Shoe        201712   \n",
              "4  Under Armour Girls' Toddler Spine Surge Runni        201712   \n",
              "\n",
              "    Warehouse Inventory   Inventory Cost Per Unit  \n",
              "0                      0                  0.69517  \n",
              "1                      2                  1.29291  \n",
              "2                      0                  0.56531  \n",
              "3                      1                  1.26321  \n",
              "4                      0                  1.47648  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1232c6b5-5aca-428f-9d5d-f839cadbcc15\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Product Name</th>\n",
              "      <th>Year Month</th>\n",
              "      <th>Warehouse Inventory</th>\n",
              "      <th>Inventory Cost Per Unit</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Perfect Fitness Perfect Rip Deck</td>\n",
              "      <td>201712</td>\n",
              "      <td>0</td>\n",
              "      <td>0.69517</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Nike Men's Dri-FIT Victory Golf Polo</td>\n",
              "      <td>201712</td>\n",
              "      <td>2</td>\n",
              "      <td>1.29291</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>O'Brien Men's Neoprene Life Vest</td>\n",
              "      <td>201712</td>\n",
              "      <td>0</td>\n",
              "      <td>0.56531</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Nike Men's Free 5.0+ Running Shoe</td>\n",
              "      <td>201712</td>\n",
              "      <td>1</td>\n",
              "      <td>1.26321</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Under Armour Girls' Toddler Spine Surge Runni</td>\n",
              "      <td>201712</td>\n",
              "      <td>0</td>\n",
              "      <td>1.47648</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1232c6b5-5aca-428f-9d5d-f839cadbcc15')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-1232c6b5-5aca-428f-9d5d-f839cadbcc15 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-1232c6b5-5aca-428f-9d5d-f839cadbcc15');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-e1658a04-ebbf-4ebd-b908-2d985f3685e6\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e1658a04-ebbf-4ebd-b908-2d985f3685e6')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-e1658a04-ebbf-4ebd-b908-2d985f3685e6 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_fulfillment.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "g9kzILWyC6Z7",
        "outputId": "7358bf11-9a03-4c60-f2c8-a0ee246d7a23"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                    Product Name  \\\n",
              "0               Perfect Fitness Perfect Rip Deck   \n",
              "1           Nike Men's Dri-FIT Victory Golf Polo   \n",
              "2               O'Brien Men's Neoprene Life Vest   \n",
              "3              Nike Men's Free 5.0+ Running Shoe   \n",
              "4  Under Armour Girls' Toddler Spine Surge Runni   \n",
              "\n",
              "    Warehouse Order Fulfillment (days)   \n",
              "0                                   8.3  \n",
              "1                                   6.6  \n",
              "2                                   5.5  \n",
              "3                                   9.4  \n",
              "4                                   6.3  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-645d066b-7718-482c-90bb-579849cced9b\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Product Name</th>\n",
              "      <th>Warehouse Order Fulfillment (days)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Perfect Fitness Perfect Rip Deck</td>\n",
              "      <td>8.3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Nike Men's Dri-FIT Victory Golf Polo</td>\n",
              "      <td>6.6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>O'Brien Men's Neoprene Life Vest</td>\n",
              "      <td>5.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Nike Men's Free 5.0+ Running Shoe</td>\n",
              "      <td>9.4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Under Armour Girls' Toddler Spine Surge Runni</td>\n",
              "      <td>6.3</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-645d066b-7718-482c-90bb-579849cced9b')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-645d066b-7718-482c-90bb-579849cced9b button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-645d066b-7718-482c-90bb-579849cced9b');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-7033ba0b-89dd-4226-93e1-1a3f83e5f5ad\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-7033ba0b-89dd-4226-93e1-1a3f83e5f5ad')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-7033ba0b-89dd-4226-93e1-1a3f83e5f5ad button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The dataset provides three data tables including order_and_shipment, inventory and fulfillment. After examining the data fields, I noticed that the dataset generally represents the following key information\n",
        "\n",
        "* Customer: General information about customers including identifiers and addresses\n",
        "\n",
        "* Order: Information about the order including date of order, product and quantity ordered, order value\n",
        "\n",
        "* Shipment: Shipping information including shipping date, shipping mode\n",
        "\n",
        "* Product: Specific information about the ordered item including product name, product category, product department\n",
        "\n",
        "* Warehouse Inventory: Information on inventory management for each product name including monthly inventory, warehouse location, storage costs, order fulfillment"
      ],
      "metadata": {
        "id": "IkGbz8942Ibr"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e9aP1k8FbQYh"
      },
      "source": [
        "# Data Cleaning"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l-JSmTVlbZqz"
      },
      "source": [
        "## Handling Missing Value"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ACBZbcGUbR_S",
        "outputId": "bc5c0893-ca8c-4ad7-d58c-ea9f47e846ae"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              " Order ID                      0\n",
              " Order Item ID                 0\n",
              " Order YearMonth               0\n",
              " Order Year                    0\n",
              " Order Month                   0\n",
              " Order Day                     0\n",
              "Order Time                     0\n",
              "Order Quantity                 0\n",
              "Product Department             0\n",
              "Product Category               0\n",
              "Product Name                   0\n",
              " Customer ID                   0\n",
              "Customer Market                0\n",
              "Customer Region                0\n",
              "Customer Country               0\n",
              "Warehouse Country              0\n",
              "Shipment Year                  0\n",
              "Shipment Month                 0\n",
              "Shipment Day                   0\n",
              "Shipment Mode                  0\n",
              " Shipment Days - Scheduled     0\n",
              " Gross Sales                   0\n",
              " Discount %                    0\n",
              " Profit                        0\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ],
      "source": [
        "df_orders.isna().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AbV1Qb1GbpSF",
        "outputId": "2e8ec046-8716-452f-cc44-8c44ac1a06ee"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Product Name               0\n",
              " Year Month                0\n",
              " Warehouse Inventory       0\n",
              "Inventory Cost Per Unit    0\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "df_inventory.isna().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BrLYGPqPbuDy",
        "outputId": "9faf0ece-d3f2-4d44-d3a8-599535d36057"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Product Name                            0\n",
              " Warehouse Order Fulfillment (days)     0\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ],
      "source": [
        "df_fulfillment.isna().sum()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5ISkNfYhclFc"
      },
      "source": [
        "All three datasets do not contain any missing values. So, there is no need to handle missing data imputation."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9H67p-6GuIWk"
      },
      "source": [
        "## Duplicated Data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ea8BLTrOuvbs",
        "outputId": "874bcfcb-3c82-41f8-bf87-d3b2cb333883"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "df_orders.duplicated().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "intdBkDcucxH",
        "outputId": "3ed35b97-30a9-40cb-90ea-b40d27fda8bc"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ],
      "source": [
        "df_inventory.duplicated().sum()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5RfKcWoVu6Hq",
        "outputId": "b1c365f7-53f6-4ae2-efdc-48ba7d2bf328"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ],
      "source": [
        "df_fulfillment.duplicated().sum()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y5Os5TeVvW94"
      },
      "source": [
        "Moreover, the absence of any duplicate values in all three datasets is also promising. This indicates that the data is fairly clean and ready for analysis."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "clsxJa3Kcq_o"
      },
      "source": [
        "# Data Wrangling"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BCGXvUa4piEA"
      },
      "source": [
        "## Leading and Trailing Space"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "01kvT6UniO1C",
        "outputId": "b21ea21c-7320-443a-e8d3-aa9f59c479df"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 30871 entries, 0 to 30870\n",
            "Data columns (total 24 columns):\n",
            " #   Column                       Non-Null Count  Dtype \n",
            "---  ------                       --------------  ----- \n",
            " 0    Order ID                    30871 non-null  int64 \n",
            " 1    Order Item ID               30871 non-null  int64 \n",
            " 2    Order YearMonth             30871 non-null  int64 \n",
            " 3    Order Year                  30871 non-null  int64 \n",
            " 4    Order Month                 30871 non-null  int64 \n",
            " 5    Order Day                   30871 non-null  int64 \n",
            " 6   Order Time                   30871 non-null  object\n",
            " 7   Order Quantity               30871 non-null  int64 \n",
            " 8   Product Department           30871 non-null  object\n",
            " 9   Product Category             30871 non-null  object\n",
            " 10  Product Name                 30871 non-null  object\n",
            " 11   Customer ID                 30871 non-null  int64 \n",
            " 12  Customer Market              30871 non-null  object\n",
            " 13  Customer Region              30871 non-null  object\n",
            " 14  Customer Country             30871 non-null  object\n",
            " 15  Warehouse Country            30871 non-null  object\n",
            " 16  Shipment Year                30871 non-null  int64 \n",
            " 17  Shipment Month               30871 non-null  int64 \n",
            " 18  Shipment Day                 30871 non-null  int64 \n",
            " 19  Shipment Mode                30871 non-null  object\n",
            " 20   Shipment Days - Scheduled   30871 non-null  int64 \n",
            " 21   Gross Sales                 30871 non-null  int64 \n",
            " 22   Discount %                  30871 non-null  object\n",
            " 23   Profit                      30871 non-null  int64 \n",
            "dtypes: int64(14), object(10)\n",
            "memory usage: 5.7+ MB\n"
          ]
        }
      ],
      "source": [
        "df_orders.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JF_kk5w9OBHx",
        "outputId": "24e046f4-7363-4ea3-e47c-9a23e41a20be"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 4200 entries, 0 to 4199\n",
            "Data columns (total 4 columns):\n",
            " #   Column                   Non-Null Count  Dtype  \n",
            "---  ------                   --------------  -----  \n",
            " 0   Product Name             4200 non-null   object \n",
            " 1    Year Month              4200 non-null   int64  \n",
            " 2    Warehouse Inventory     4200 non-null   int64  \n",
            " 3   Inventory Cost Per Unit  4200 non-null   float64\n",
            "dtypes: float64(1), int64(2), object(1)\n",
            "memory usage: 131.4+ KB\n"
          ]
        }
      ],
      "source": [
        "df_inventory.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LQ15zTHaOFww",
        "outputId": "63ef7013-899d-4e83-f9cf-7c34c329e011"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 118 entries, 0 to 117\n",
            "Data columns (total 2 columns):\n",
            " #   Column                                Non-Null Count  Dtype  \n",
            "---  ------                                --------------  -----  \n",
            " 0   Product Name                          118 non-null    object \n",
            " 1    Warehouse Order Fulfillment (days)   118 non-null    float64\n",
            "dtypes: float64(1), object(1)\n",
            "memory usage: 2.0+ KB\n"
          ]
        }
      ],
      "source": [
        "df_fulfillment.info()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LqbQ6WGij_ZP"
      },
      "source": [
        "Upon inspecting the data using `.info()`, it has come to our attention that there are anomalies present in the datasets. Specifically, we have discovered that some columns contain leading or trailing spaces, which are not intended to be part of the column names."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "RGVCF3Axihgg"
      },
      "outputs": [],
      "source": [
        "# using strip fn removing unwanted spaces from columns name\n",
        "dataframes = [df_inventory, df_orders, df_fulfillment]\n",
        "\n",
        "for df in dataframes:\n",
        "  df.columns = df.columns.str.strip()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QvKo71V9Ocj3",
        "outputId": "57835e63-6cab-4372-b896-7e4b08cb3283"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['Order ID', 'Order Item ID', 'Order YearMonth', 'Order Year',\n",
              "       'Order Month', 'Order Day', 'Order Time', 'Order Quantity',\n",
              "       'Product Department', 'Product Category', 'Product Name', 'Customer ID',\n",
              "       'Customer Market', 'Customer Region', 'Customer Country',\n",
              "       'Warehouse Country', 'Shipment Year', 'Shipment Month', 'Shipment Day',\n",
              "       'Shipment Mode', 'Shipment Days - Scheduled', 'Gross Sales',\n",
              "       'Discount %', 'Profit'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ],
      "source": [
        "df_orders.columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "I4-sFrZlPAIc",
        "outputId": "0d03d32e-8fb2-4130-a076-3fa63bd83c1b"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['Product Name', 'Year Month', 'Warehouse Inventory',\n",
              "       'Inventory Cost Per Unit'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ],
      "source": [
        "df_inventory.columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "upikotyhPDwr",
        "outputId": "a9212012-43f3-470f-e4f3-3479ee431005"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['Product Name', 'Warehouse Order Fulfillment (days)'], dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ],
      "source": [
        "df_fulfillment.columns"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_orders.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "eSBwtK8iFWWi",
        "outputId": "558c3d3d-212c-4ff9-915e-311db5f87ce5"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['Order ID', 'Order Item ID', 'Order YearMonth', 'Order Year',\n",
              "       'Order Month', 'Order Day', 'Order Time', 'Order Quantity',\n",
              "       'Product Department', 'Product Category', 'Product Name', 'Customer ID',\n",
              "       'Customer Market', 'Customer Region', 'Customer Country',\n",
              "       'Warehouse Country', 'Shipment Year', 'Shipment Month', 'Shipment Day',\n",
              "       'Shipment Mode', 'Shipment Days - Scheduled', 'Gross Sales',\n",
              "       'Discount %', 'Profit'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_orders['Discount %'].sample(30)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CDsRQ3bbFbmv",
        "outputId": "d3854f26-c554-4e74-de0f-03f7460f8c05"
      },
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "27686     0.2\n",
              "25619    0.17\n",
              "6227     0.16\n",
              "5019      0.1\n",
              "28764     0.1\n",
              "3798     0.25\n",
              "25096    0.17\n",
              "9450     0.18\n",
              "24169    0.06\n",
              "25979    0.16\n",
              "26315    0.07\n",
              "22004    0.13\n",
              "8804     0.09\n",
              "19228    0.09\n",
              "166       0.2\n",
              "29943    0.17\n",
              "13697    0.15\n",
              "23000    0.15\n",
              "13312     0.2\n",
              "13135    0.09\n",
              "8079     0.03\n",
              "12472    0.07\n",
              "3553     0.17\n",
              "14721    0.03\n",
              "7444     0.01\n",
              "4713     0.16\n",
              "18829     0.1\n",
              "6001     0.01\n",
              "556      0.06\n",
              "15576    0.06\n",
              "Name: Discount %, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Based on above data, there are some rows with - values. Need to replace this - values with 0. In odrer to read this data properly."
      ],
      "metadata": {
        "id": "TdTfQswn3dVp"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "id": "sD82QbdlnLxK"
      },
      "outputs": [],
      "source": [
        "# Convert the '-' values to 0 in the 'Discount %' column and then change the data type from object to float\n",
        "df_orders['Discount %'] = df_orders['Discount %'].replace('  -  ', 0).astype(float)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_orders.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hEquwTd44fJq",
        "outputId": "5151a342-f3c0-4a40-c5da-c43680edfa27"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['Order ID', 'Order Item ID', 'Order YearMonth', 'Order Year',\n",
              "       'Order Month', 'Order Day', 'Order Time', 'Order Quantity',\n",
              "       'Product Department', 'Product Category', 'Product Name', 'Customer ID',\n",
              "       'Customer Market', 'Customer Region', 'Customer Country',\n",
              "       'Warehouse Country', 'Shipment Year', 'Shipment Month', 'Shipment Day',\n",
              "       'Shipment Mode', 'Shipment Days - Scheduled', 'Gross Sales',\n",
              "       'Discount %', 'Profit'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tfs-hGuCzr2w"
      },
      "source": [
        "## Feature Engineering :\n",
        "Order Datetime & Shipment Datetime"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fyTxiUIzQxH9"
      },
      "source": [
        "Due to the data being separated into multiple columns for year, month, day, and time, resulting in a large number of columns, I have decided to combine these columns into a single column that includes all the necessary date and time information. This will help simplify the data and make it more manageable for further analysis in Tableau."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "ATaHLaf2gSKs",
        "outputId": "cd9ec855-efa8-406f-8d79-0439025cbc42"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       Order Datetime Shipment Datetime\n",
              "0 2015-02-21 14:07:00        2015-02-27\n",
              "1 2015-03-02 07:37:00        2015-03-06\n",
              "2 2015-04-18 22:47:00        2015-04-20\n",
              "3 2015-06-10 22:32:00        2015-06-12\n",
              "4 2015-06-10 22:32:00        2015-06-12"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-424dddd1-eb73-4f25-8df3-eddaf25a06fc\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Order Datetime</th>\n",
              "      <th>Shipment Datetime</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2015-02-21 14:07:00</td>\n",
              "      <td>2015-02-27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2015-03-02 07:37:00</td>\n",
              "      <td>2015-03-06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2015-04-18 22:47:00</td>\n",
              "      <td>2015-04-20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2015-06-10 22:32:00</td>\n",
              "      <td>2015-06-12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2015-06-10 22:32:00</td>\n",
              "      <td>2015-06-12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-424dddd1-eb73-4f25-8df3-eddaf25a06fc')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-424dddd1-eb73-4f25-8df3-eddaf25a06fc button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-424dddd1-eb73-4f25-8df3-eddaf25a06fc');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-d7fbbf74-c79d-45d9-b667-7d5a022e7e56\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d7fbbf74-c79d-45d9-b667-7d5a022e7e56')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-d7fbbf74-c79d-45d9-b667-7d5a022e7e56 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ],
      "source": [
        "# Make new columns: Order Datetime and Shipment Datetime\n",
        "df_orders['Order Datetime'] = pd.to_datetime(df_orders['Order Year'].astype(str) + '-' + df_orders['Order Month'].astype(str) + '-' + df_orders['Order Day'].astype(str) + ' ' + df_orders['Order Time'])\n",
        "df_orders['Shipment Datetime'] = pd.to_datetime(df_orders['Shipment Year'].astype(str) + '-' + df_orders['Shipment Month'].astype(str) + '-' + df_orders['Shipment Day'].astype(str))\n",
        "\n",
        "# Displaying the result\n",
        "df_orders[['Order Datetime', 'Shipment Datetime']].head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "id": "QjrKvA0_wWp-"
      },
      "outputs": [],
      "source": [
        "# Drop unnecessary columns\n",
        "df_orders.drop(columns=['Order Year', 'Order Month', 'Order Day', 'Order Time',\n",
        "                        'Shipment Year', 'Shipment Month', 'Shipment Day'], inplace=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Remving unwanted characters in country name"
      ],
      "metadata": {
        "id": "RneI8jotrwI_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_orders['Customer Country'].unique()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fYyxYc_x4MIF",
        "outputId": "a8cdca06-1d73-4b01-8e90-ae646c4122b1"
      },
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Mexico', 'Brazil', 'Denmark', 'Netherlands', 'Germany', 'China',\n",
              "       'Indonesia', 'Pakistan', 'India', 'USA', 'Hungary', 'Sudan',\n",
              "       'Democratic Republic of Congo', 'Poland', 'Togo', 'Guatemala',\n",
              "       'Panama', 'Chile', 'France', 'Sweden', 'Dominican\\xa0Republic',\n",
              "       'Venezuela', 'South Korea', 'Madagascar', 'Iran', 'Cuba',\n",
              "       'Nicaragua', 'United Kingdom', 'Afghanistan', 'Singapore',\n",
              "       'Morocco', 'Spain', 'Niger', 'Turkey', 'South Africa', 'Iraq',\n",
              "       'Honduras', 'Italy', 'Australia', 'Cote d\\x92Ivoire', 'Croatia',\n",
              "       'Ecuador', 'Syria', 'Haiti', 'Bangladesh', 'Argentina', 'Romania',\n",
              "       'El Salvador', 'Vietnam', 'Japan', 'Nigeria', 'Belarus',\n",
              "       'Uzbekistan', 'Egypt', 'Albania', 'Georgia', 'Cameroon',\n",
              "       'Colombia', 'New zealand', 'Canada', 'Thailand', 'Senegal',\n",
              "       'Russia', 'Perú', 'Algeria\\xa0', 'Ukraine', 'Belgium',\n",
              "       'Philippines', 'Austria', 'Uruguay', 'Malaysia', 'Hong Kong',\n",
              "       'Saudi Arabia', 'Switzerland', 'Ireland', 'Bulgaria', 'Zambia',\n",
              "       'Jamaica', 'Ghana', 'Yemen', 'Norway', 'Tanzania', 'Kazakhstan',\n",
              "       'Libya', 'Trinidad and Tobago', 'Finland', 'Portugal', 'Kenya',\n",
              "       'Jordania', 'Bolivia', 'Gabon', 'Angola', 'Myanmar', 'Mali', 'UAE',\n",
              "       'Bosnia and Herzegovina', 'Guinea', 'Cambodia', 'Papua New Guinea',\n",
              "       'Rwanda', 'Israel\\xa0', 'Guyana', 'Somalia', 'Barbados',\n",
              "       'Guadalupe', 'Kyrgyzstan', 'Benín', 'Tunisia', 'Lithuania',\n",
              "       'Montenegro', 'Costa Rica', 'Mozambique', 'Sri Lanka', 'Taiwan',\n",
              "       'Czech Republic (Czechia)', 'Lesotho', 'Mongolia', 'Macedonia',\n",
              "       'Zimbabwe', 'Liberia', 'Liban', 'Guinea-Bissau', 'Estonia',\n",
              "       'Azerbaijan', 'Moldova', 'Republic of Congo', 'Gambia',\n",
              "       'Mauritania', 'Belize', 'Qatar', 'Sierra Leona', 'Slovakia',\n",
              "       'Martinique', 'Uganda', 'Namibia', 'Paraguay', 'Oman',\n",
              "       'French Guiana', 'Nepal'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#replace the special characters in the Customer Country column\n",
        "df_orders['Customer Country'] = df_orders['Customer Country'].replace({\n",
        "    'Dominican\\xa0Republic': 'Dominican Republic',\n",
        "    'Cote d\\x92Ivoire': 'Cote d Ivoire', # Added a comma at the end of this line\n",
        "    'Perú': 'Peru',\n",
        "    'Algeria\\xa0': 'Algeria',\n",
        "    'Israel\\xa0':'Israel',\n",
        "    'Benín': 'Benin'\n",
        "})\n",
        "df_orders['Customer Country'].unique()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mCUPxlQe5P61",
        "outputId": "396da81c-cc14-41f7-c0e6-d27533438d40"
      },
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Mexico', 'Brazil', 'Denmark', 'Netherlands', 'Germany', 'China',\n",
              "       'Indonesia', 'Pakistan', 'India', 'USA', 'Hungary', 'Sudan',\n",
              "       'Democratic Republic of Congo', 'Poland', 'Togo', 'Guatemala',\n",
              "       'Panama', 'Chile', 'France', 'Sweden', 'Dominican Republic',\n",
              "       'Venezuela', 'South Korea', 'Madagascar', 'Iran', 'Cuba',\n",
              "       'Nicaragua', 'United Kingdom', 'Afghanistan', 'Singapore',\n",
              "       'Morocco', 'Spain', 'Niger', 'Turkey', 'South Africa', 'Iraq',\n",
              "       'Honduras', 'Italy', 'Australia', 'Cote d Ivoire', 'Croatia',\n",
              "       'Ecuador', 'Syria', 'Haiti', 'Bangladesh', 'Argentina', 'Romania',\n",
              "       'El Salvador', 'Vietnam', 'Japan', 'Nigeria', 'Belarus',\n",
              "       'Uzbekistan', 'Egypt', 'Albania', 'Georgia', 'Cameroon',\n",
              "       'Colombia', 'New zealand', 'Canada', 'Thailand', 'Senegal',\n",
              "       'Russia', 'Peru', 'Algeria', 'Ukraine', 'Belgium', 'Philippines',\n",
              "       'Austria', 'Uruguay', 'Malaysia', 'Hong Kong', 'Saudi Arabia',\n",
              "       'Switzerland', 'Ireland', 'Bulgaria', 'Zambia', 'Jamaica', 'Ghana',\n",
              "       'Yemen', 'Norway', 'Tanzania', 'Kazakhstan', 'Libya',\n",
              "       'Trinidad and Tobago', 'Finland', 'Portugal', 'Kenya', 'Jordania',\n",
              "       'Bolivia', 'Gabon', 'Angola', 'Myanmar', 'Mali', 'UAE',\n",
              "       'Bosnia and Herzegovina', 'Guinea', 'Cambodia', 'Papua New Guinea',\n",
              "       'Rwanda', 'Israel', 'Guyana', 'Somalia', 'Barbados', 'Guadalupe',\n",
              "       'Kyrgyzstan', 'Benin', 'Tunisia', 'Lithuania', 'Montenegro',\n",
              "       'Costa Rica', 'Mozambique', 'Sri Lanka', 'Taiwan',\n",
              "       'Czech Republic (Czechia)', 'Lesotho', 'Mongolia', 'Macedonia',\n",
              "       'Zimbabwe', 'Liberia', 'Liban', 'Guinea-Bissau', 'Estonia',\n",
              "       'Azerbaijan', 'Moldova', 'Republic of Congo', 'Gambia',\n",
              "       'Mauritania', 'Belize', 'Qatar', 'Sierra Leona', 'Slovakia',\n",
              "       'Martinique', 'Uganda', 'Namibia', 'Paraguay', 'Oman',\n",
              "       'French Guiana', 'Nepal'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "glDUdGmv5K3A"
      },
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yNB5-AE47aH1"
      },
      "source": [
        "# Data Manipulation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y8BkcrKoUUgr"
      },
      "source": [
        "## Order Processing Time"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z_EXFFcLUW-S"
      },
      "source": [
        "The \"`Order Processing Time`\" is obtained by subtracting the \"`Order Datetime`\" from the \"`Shipment Datetime`\".\n",
        "\n",
        "It measures the time taken for the order to move through various stages, including **processing, packing, and delivery preparation**, until it is ready for shipment.\n",
        "\n",
        "This metric helps us understand how quickly we can fulfill customer orders and deliver products to their destination."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 591
        },
        "id": "VGJlkQVoUZFl",
        "outputId": "b804d17a-bb2a-4c6c-d41f-f6403f4b899d"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       Order ID  Order Item ID  Order YearMonth  Order Quantity  \\\n",
              "22230     51668         129103           201701               4   \n",
              "11442     31548          78832           201605               2   \n",
              "19007      8323          20794           201506               1   \n",
              "2144      31051          77601           201506               2   \n",
              "20725     21393          53495           201512               1   \n",
              "\n",
              "      Product Department      Product Category  \\\n",
              "22230           Fan Shop  Indoor/Outdoor Games   \n",
              "11442               Golf         Shop By Sport   \n",
              "19007           Fan Shop  Indoor/Outdoor Games   \n",
              "2144            Outdoors           Electronics   \n",
              "20725            Apparel        Men's Footwear   \n",
              "\n",
              "                                        Product Name  Customer ID  \\\n",
              "22230               O'Brien Men's Neoprene Life Vest          982   \n",
              "11442  Under Armour Girls' Toddler Spine Surge Runni         1114   \n",
              "19007               O'Brien Men's Neoprene Life Vest         1839   \n",
              "2144   Bridgestone e6 Straight Distance NFL Carolina         5041   \n",
              "20725        Nike Men's CJ Elite 2 TD Football Cleat        10055   \n",
              "\n",
              "      Customer Market  Customer Region Customer Country Warehouse Country  \\\n",
              "22230           LATAM  Central America           Mexico               USA   \n",
              "11442   North America      West of USA              USA               USA   \n",
              "19007           LATAM    South America           Brazil               USA   \n",
              "2144     Pacific Asia          Oceania      New zealand               USA   \n",
              "20725    Pacific Asia   Southeast Asia        Indonesia               USA   \n",
              "\n",
              "        Shipment Mode  Shipment Days - Scheduled  Gross Sales  Discount %  \\\n",
              "22230    Second Class                          2          200        0.07   \n",
              "11442  Standard Class                          4           80        0.05   \n",
              "19007  Standard Class                          4           50        0.06   \n",
              "2144         Same Day                          3           64        0.03   \n",
              "20725  Standard Class                          4          130        0.17   \n",
              "\n",
              "       Profit      Order Datetime Shipment Datetime  Order Processing Time  \n",
              "22230     125 2017-01-24 05:15:00        2017-01-28                      3  \n",
              "11442      97 2016-05-06 01:56:00        2016-05-09                      2  \n",
              "19007     125 2015-06-04 20:27:00        2016-05-08                    338  \n",
              "2144       73 2015-06-12 03:37:00        2015-06-17                      4  \n",
              "20725      65 2015-12-05 11:05:00        2015-12-08                      2  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-4e5a84fa-50c3-4b8c-9fe4-2be3fb48a599\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Order ID</th>\n",
              "      <th>Order Item ID</th>\n",
              "      <th>Order YearMonth</th>\n",
              "      <th>Order Quantity</th>\n",
              "      <th>Product Department</th>\n",
              "      <th>Product Category</th>\n",
              "      <th>Product Name</th>\n",
              "      <th>Customer ID</th>\n",
              "      <th>Customer Market</th>\n",
              "      <th>Customer Region</th>\n",
              "      <th>Customer Country</th>\n",
              "      <th>Warehouse Country</th>\n",
              "      <th>Shipment Mode</th>\n",
              "      <th>Shipment Days - Scheduled</th>\n",
              "      <th>Gross Sales</th>\n",
              "      <th>Discount %</th>\n",
              "      <th>Profit</th>\n",
              "      <th>Order Datetime</th>\n",
              "      <th>Shipment Datetime</th>\n",
              "      <th>Order Processing Time</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>22230</th>\n",
              "      <td>51668</td>\n",
              "      <td>129103</td>\n",
              "      <td>201701</td>\n",
              "      <td>4</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Indoor/Outdoor Games</td>\n",
              "      <td>O'Brien Men's Neoprene Life Vest</td>\n",
              "      <td>982</td>\n",
              "      <td>LATAM</td>\n",
              "      <td>Central America</td>\n",
              "      <td>Mexico</td>\n",
              "      <td>USA</td>\n",
              "      <td>Second Class</td>\n",
              "      <td>2</td>\n",
              "      <td>200</td>\n",
              "      <td>0.07</td>\n",
              "      <td>125</td>\n",
              "      <td>2017-01-24 05:15:00</td>\n",
              "      <td>2017-01-28</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11442</th>\n",
              "      <td>31548</td>\n",
              "      <td>78832</td>\n",
              "      <td>201605</td>\n",
              "      <td>2</td>\n",
              "      <td>Golf</td>\n",
              "      <td>Shop By Sport</td>\n",
              "      <td>Under Armour Girls' Toddler Spine Surge Runni</td>\n",
              "      <td>1114</td>\n",
              "      <td>North America</td>\n",
              "      <td>West of USA</td>\n",
              "      <td>USA</td>\n",
              "      <td>USA</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>80</td>\n",
              "      <td>0.05</td>\n",
              "      <td>97</td>\n",
              "      <td>2016-05-06 01:56:00</td>\n",
              "      <td>2016-05-09</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19007</th>\n",
              "      <td>8323</td>\n",
              "      <td>20794</td>\n",
              "      <td>201506</td>\n",
              "      <td>1</td>\n",
              "      <td>Fan Shop</td>\n",
              "      <td>Indoor/Outdoor Games</td>\n",
              "      <td>O'Brien Men's Neoprene Life Vest</td>\n",
              "      <td>1839</td>\n",
              "      <td>LATAM</td>\n",
              "      <td>South America</td>\n",
              "      <td>Brazil</td>\n",
              "      <td>USA</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>50</td>\n",
              "      <td>0.06</td>\n",
              "      <td>125</td>\n",
              "      <td>2015-06-04 20:27:00</td>\n",
              "      <td>2016-05-08</td>\n",
              "      <td>338</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2144</th>\n",
              "      <td>31051</td>\n",
              "      <td>77601</td>\n",
              "      <td>201506</td>\n",
              "      <td>2</td>\n",
              "      <td>Outdoors</td>\n",
              "      <td>Electronics</td>\n",
              "      <td>Bridgestone e6 Straight Distance NFL Carolina</td>\n",
              "      <td>5041</td>\n",
              "      <td>Pacific Asia</td>\n",
              "      <td>Oceania</td>\n",
              "      <td>New zealand</td>\n",
              "      <td>USA</td>\n",
              "      <td>Same Day</td>\n",
              "      <td>3</td>\n",
              "      <td>64</td>\n",
              "      <td>0.03</td>\n",
              "      <td>73</td>\n",
              "      <td>2015-06-12 03:37:00</td>\n",
              "      <td>2015-06-17</td>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20725</th>\n",
              "      <td>21393</td>\n",
              "      <td>53495</td>\n",
              "      <td>201512</td>\n",
              "      <td>1</td>\n",
              "      <td>Apparel</td>\n",
              "      <td>Men's Footwear</td>\n",
              "      <td>Nike Men's CJ Elite 2 TD Football Cleat</td>\n",
              "      <td>10055</td>\n",
              "      <td>Pacific Asia</td>\n",
              "      <td>Southeast Asia</td>\n",
              "      <td>Indonesia</td>\n",
              "      <td>USA</td>\n",
              "      <td>Standard Class</td>\n",
              "      <td>4</td>\n",
              "      <td>130</td>\n",
              "      <td>0.17</td>\n",
              "      <td>65</td>\n",
              "      <td>2015-12-05 11:05:00</td>\n",
              "      <td>2015-12-08</td>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4e5a84fa-50c3-4b8c-9fe4-2be3fb48a599')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-4e5a84fa-50c3-4b8c-9fe4-2be3fb48a599 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-4e5a84fa-50c3-4b8c-9fe4-2be3fb48a599');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-3a3b7716-cef3-43d2-99f1-1cc551138f95\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-3a3b7716-cef3-43d2-99f1-1cc551138f95')\"\n",
              "            title=\"Suggest charts.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-3a3b7716-cef3-43d2-99f1-1cc551138f95 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ],
      "source": [
        "df_orders['Order Processing Time'] = (df_orders['Shipment Datetime'] - df_orders['Order Datetime']).dt.days\n",
        "df_orders['Order Processing Time'] = df_orders['Order Processing Time'].apply(lambda x: 0 if x == -1 else x)\n",
        "\n",
        "df_orders.sample(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "euTLZwEgUbKL"
      },
      "source": [
        "The presence of timestamps in the order of dates provides more accurate and detailed information about the time intervals between events. However, when calculating \"`Order Processing Time`\" using a timestamp, it can result in a negative value of 1 on same-day shipping as the order, because the shipping date does not have a timestamp, which is why the default timestamp of 00:00:00 is used.\n",
        "\n",
        "To address this issue, we implemented a `lambda` function to set a negative value of 1 to 0, ensuring the calculated duration is not negative and meaningful for analysis. This approach allows us to gain valuable insights into the efficiency of the shipping process and delivery time, taking into account the time-sensitive nature of the data."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Feature Metrics\n",
        "The feature metrics that planning to create in tableau which helps to further analyze inventory management, Shipement delay and many more."
      ],
      "metadata": {
        "id": "5ezj5FwLjSYD"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hn9rYugrm-WM"
      },
      "source": [
        "### Total Inventory Storage Cost"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YDeednbU55A6"
      },
      "source": [
        "Total Cost of Inventory is a significant financial metric in supply chain management that calculates the overall cost associated with holding and managing inventory.\n",
        "\n",
        "The formula for calculating Total Inventory Cost is: `Warehouse Inventory` times `Inventory Cost per Unit`."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Storage Cost\n",
        "Storage_Cost = [Inventory Cost Per Unit]*[Warehouse Inventory]"
      ],
      "metadata": {
        "id": "hSwKZMWNj2wL"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sVCg1S36sBdy"
      },
      "source": [
        "### Shipment Delay"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f_NaVtiQ6SOd"
      },
      "source": [
        "Shipment Delay is a metric that measures the time difference between the expected shipment date and the actual date that the order is delivered to the customer. This helps identify and measure the efficiency and reliability of the shipping processes.\n",
        "\n",
        "The calculation of Shipment Delay involves comparing the `Shipment Days - Actual` (actual date the order is shipped) with the `Shipment Days - Scheduled` (the expected or planned date of shipment)."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Shipment Delay in Days\n",
        "Shipment Delay  = [Shipment Days - Actual] - ['Shipment Days - Scheduled']"
      ],
      "metadata": {
        "id": "Wa3ROJ6uj93g"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Profit Margin"
      ],
      "metadata": {
        "id": "vYh_rJSlk3bV"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Profit Margin helps to assessing the profitability of the supply chain operations. It provides insights into the effectiveness of cost control and pricing strategies, enabling organizations to make adjustments to enhance overall profitability."
      ],
      "metadata": {
        "id": "b3SmibnYk8or"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Profit Margin : Total Profit / Total Gross Sales *100"
      ],
      "metadata": {
        "id": "9qJJg2DklGiR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Inventory to Sales Delta"
      ],
      "metadata": {
        "id": "tXT4pXajlhEj"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This is metric which indicating how efficiently inventory is managed and whether there may be overstocking or understocking issues, allowing for more informed decisions to optimize inventory levels."
      ],
      "metadata": {
        "id": "SeXT1Zw_lpGL"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Inventory to Sales Delta = Total Warehouse Inventory - Total Order Quantity"
      ],
      "metadata": {
        "id": "7KpCavDOmdo5"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Under or Overstock\n",
        "\n",
        "Under or Overstock helps to find which products are Overstock and which are understock.\n",
        "And it helps to the balance of inventory."
      ],
      "metadata": {
        "id": "lAEYNcPLlpN3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Under or Overstock = Inventory to Sales Delta >0 THEN 'Overstock'\n",
        "ELSE 'Understock'"
      ],
      "metadata": {
        "id": "2AwoqVKslUqb"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AS_tFP1ZfsQ8"
      },
      "source": [
        "# Data Exporting & Conclusion"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "N9-2S0_YXRKl",
        "outputId": "6a24f436-23d6-4535-f961-3d53cef4b7ad"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 30871 entries, 0 to 30870\n",
            "Data columns (total 20 columns):\n",
            " #   Column                     Non-Null Count  Dtype         \n",
            "---  ------                     --------------  -----         \n",
            " 0   Order ID                   30871 non-null  int64         \n",
            " 1   Order Item ID              30871 non-null  int64         \n",
            " 2   Order YearMonth            30871 non-null  int64         \n",
            " 3   Order Quantity             30871 non-null  int64         \n",
            " 4   Product Department         30871 non-null  object        \n",
            " 5   Product Category           30871 non-null  object        \n",
            " 6   Product Name               30871 non-null  object        \n",
            " 7   Customer ID                30871 non-null  int64         \n",
            " 8   Customer Market            30871 non-null  object        \n",
            " 9   Customer Region            30871 non-null  object        \n",
            " 10  Customer Country           30871 non-null  object        \n",
            " 11  Warehouse Country          30871 non-null  object        \n",
            " 12  Shipment Mode              30871 non-null  object        \n",
            " 13  Shipment Days - Scheduled  30871 non-null  int64         \n",
            " 14  Gross Sales                30871 non-null  int64         \n",
            " 15  Discount %                 30871 non-null  float64       \n",
            " 16  Profit                     30871 non-null  int64         \n",
            " 17  Order Datetime             30871 non-null  datetime64[ns]\n",
            " 18  Shipment Datetime          30871 non-null  datetime64[ns]\n",
            " 19  Order Processing Time      30871 non-null  int64         \n",
            "dtypes: datetime64[ns](2), float64(1), int64(9), object(8)\n",
            "memory usage: 4.7+ MB\n"
          ]
        }
      ],
      "source": [
        "# Final Check\n",
        "df_orders.info()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7pdjD6f8jeoB",
        "outputId": "33fb2152-e446-4972-f7a4-ff1bede644eb"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 4200 entries, 0 to 4199\n",
            "Data columns (total 4 columns):\n",
            " #   Column                   Non-Null Count  Dtype  \n",
            "---  ------                   --------------  -----  \n",
            " 0   Product Name             4200 non-null   object \n",
            " 1   Year Month               4200 non-null   int64  \n",
            " 2   Warehouse Inventory      4200 non-null   int64  \n",
            " 3   Inventory Cost Per Unit  4200 non-null   float64\n",
            "dtypes: float64(1), int64(2), object(1)\n",
            "memory usage: 131.4+ KB\n"
          ]
        }
      ],
      "source": [
        "df_inventory.info()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_fulfillment.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "374lXBTtlOhD",
        "outputId": "24b1b8d6-8aad-4646-80b0-f48bd8bf4ade"
      },
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 118 entries, 0 to 117\n",
            "Data columns (total 2 columns):\n",
            " #   Column                              Non-Null Count  Dtype  \n",
            "---  ------                              --------------  -----  \n",
            " 0   Product Name                        118 non-null    object \n",
            " 1   Warehouse Order Fulfillment (days)  118 non-null    float64\n",
            "dtypes: float64(1), object(1)\n",
            "memory usage: 2.0+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v7hZMiP4gZff"
      },
      "source": [
        "## Exporting Cleaned Data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tEW-ylTagwud"
      },
      "source": [
        "Save the cleaned and processed DataFrames to a csv file for further visualization and analysis in tableau."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "metadata": {
        "id": "dQiTclOuhO3n"
      },
      "outputs": [],
      "source": [
        "# Export DataFrames to CSV\n",
        "df_orders.to_csv('orders_and_shipment.csv', index=False)\n",
        "df_inventory.to_csv('inventory.csv', index=False)\n",
        "df_fulfillment.to_csv('fulfillment.csv', index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GeiUwOAxhs2N"
      },
      "source": [
        "## Conclusion"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dgcYSQsjhuvj"
      },
      "source": [
        "Through this analysis, we successfully explored and performed data cleaning on the `df_orders` and `df_inventory` DataFrames. Important metrics such as `Profit Margin`, `Inventory to Sales Delta`, `Storage Cost`, and `Shipment Delay` will be calculated in tableau to understand inventory performance and supply chain efficiency for the company.\n",
        "\n",
        "Additionally, we removed some anomalous data points that could impact the analysis results. The cleaned and processed data will be exported to a csv file for further analysis and visualization in Tableau.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QN2W_LLMnPbZ"
      },
      "source": [
        "# Closing"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "blJBMihMnTeg"
      },
      "source": [
        "As an aspiring data engineer/data analyst, I will consistently look for opportunities to improve my skills and insights.Thank you so much.\n",
        "\n",
        "This analysis was conducted as part of a competition organized by DataCamp, with the aim of demonstrating data analytics expertise and providing actionable insights to address real-world business challenges. Throughout this process, I strive to use effective data processing, data cleaning, and advanced analytics techniques to generate meaningful conclusions and support decision making."
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "ujoJiD3I6-yH"
      },
      "execution_count": 31,
      "outputs": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}