{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import the libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "%matplotlib inline\n",
    "from scipy import stats\n",
    "\n",
    "#seaborn, scipy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read Sales.Dirty.xls\n",
    "salesdf = pd.read_excel('Sales.Dirty.xls')\n",
    "\n",
    "# Lets get a sense of sample in hand\n",
    "print(\"Rows=%d, Columns=%d\" % (salesdf.shape[0], salesdf.shape[1]))\n",
    "print(salesdf.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.tail(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Incorrect Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will first replace incorrect values with null values.\n",
    "Once this is done, we will deal with missing values in the next stage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dealing with incorrect values\n",
    "\n",
    "# Order Date seems to have some incorrect values, lets start with that\n",
    "\n",
    "# Lets see how many rows will be affected if we replace incorrect values with NaT\n",
    "affected_date_records = pd.to_datetime(salesdf['Order Date'], errors='coerce').isnull().sum() - salesdf['Order Date'].isnull().sum()\n",
    "print(\"Affected Order date records=%d\" % affected_date_records)\n",
    "\n",
    "# Same for Ship Date\n",
    "affected_ship_date_records = pd.to_datetime(salesdf['Ship Date'], errors='coerce').isnull().sum() - salesdf['Ship Date'].isnull().sum()\n",
    "print(\"Affected Ship date records=%d\" % affected_ship_date_records)\n",
    "\n",
    "# Order Quantity also seems to have incorrect values. Lets check how many rows will be affected if they are replaced with NaN\n",
    "affected_qty_records = pd.to_numeric(salesdf['Order Quantity'], errors='coerce').isnull().sum() - salesdf['Order Quantity'].isnull().sum()\n",
    "print(\"Affected Order Quantity records=%d\" % affected_qty_records)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Very few rows are being affected, so safe to replace\n",
    "salesdf['Order Date'] = pd.to_datetime(salesdf['Order Date'], errors='coerce') # Replace incorrect values with NaT\n",
    "\n",
    "salesdf['Ship Date'] = pd.to_datetime(salesdf['Ship Date'], errors='coerce') # Replace incorrect values with NaT\n",
    "\n",
    "# safe to replace incorrect Order Quantity with NaN\n",
    "salesdf['Order Quantity'] = pd.to_numeric(salesdf['Order Quantity'], downcast='integer', errors='coerce')\n",
    "\n",
    "# Lets deal with incorrect Sales value in the same way for now\n",
    "salesdf['Sales'] = pd.to_numeric(salesdf['Sales'], errors='coerce')\n",
    "\n",
    "# Lets deal with incorrect profit value in the same way for now\n",
    "salesdf['Profit'] = pd.to_numeric(salesdf['Profit'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf['Order Quantity'].head(45)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# regular expression to eliminate numeric values in string columns and replace them with null (NaT in python)\n",
    "# we are writing this to eliminate numeric values in customer name and product name\n",
    "import re\n",
    "def eliminate_numeric_values(item):\n",
    "    if isinstance(item, str):\n",
    "        return item if not re.match(r\"^[0-9]+$\", item) else pd.NaT\n",
    "    else:\n",
    "        return pd.NaT\n",
    "\n",
    "# Customer Name values seems to be incorrect in some cases, they are just numbers, \n",
    "#lets replace incorrect values with NaN\n",
    "salesdf['Customer Name'] = salesdf['Customer Name'].map(eliminate_numeric_values)\n",
    "\n",
    "# Product Name also seems to be incorrect in some cases, \n",
    "#lets replace entries with NaN where its just a number\n",
    "salesdf['Product Name'] = salesdf['Product Name'].map(eliminate_numeric_values)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have data in unified format and we can move to next step of dealing with missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Missing values and  removing unnecessary columns and rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let check the current count of missing values in our dataset\n",
    "salesdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deleting unnecessary rows and columns while we are dealing with missing values would certainly save us from some extra work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Order ID seems to have alot of null values\n",
    "#and it cannot be used as unique identifier for our records\n",
    "print(salesdf['Order ID'].is_unique)\n",
    "\n",
    "# whereas Row ID can be used as unique identifier\n",
    "print(salesdf['Row ID'].is_unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# so there is no real significance of keeping Order ID,\n",
    "# Lets drop this column\n",
    "salesdf = salesdf.drop('Order ID', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extracting year from shipping date\n",
    "ship_date_year = salesdf['Ship Date'].apply(lambda x: x.year)\n",
    "ship_date_year.hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a separate column for the year and see boxplot\n",
    "salesdf['ship_date_year'] = salesdf['Ship Date'].apply(lambda x: x.year)\n",
    "salesdf.boxplot(column='ship_date_year', sym='o', return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1900 value seems to be a data entry error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#first drop the extra column\n",
    "salesdf = salesdf.drop('ship_date_year', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# records with null order date and ship date in year 1900 are clearly outliers\n",
    "# Lets remove such records\n",
    "salesdf = salesdf[((salesdf['Ship Date'].apply(lambda x: x.year) <= 1900) & salesdf['Order Date'].isna()) == False]\n",
    "salesdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a separate column for the year and see boxplot\n",
    "salesdf['ship_date_year'] = salesdf['Ship Date'].apply(lambda x: x.year)\n",
    "salesdf.boxplot(column='ship_date_year', sym='o', return_type='axes')\n",
    "salesdf = salesdf.drop('ship_date_year', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Three records where both Ship Date and Order Date are null, lets delete these rows to be on safe side\n",
    "salesdf = salesdf.drop(salesdf[(salesdf['Ship Date'].isnull()) & (salesdf['Order Date'].isnull())].index)\n",
    "\n",
    "# For remaining records missing Ship Date (there are 10 such records), \n",
    "#lets fill them with Order Date. Its a reasonable assumption and shouldn't affect any pattern\n",
    "salesdf['Ship Date'].fillna(salesdf['Order Date'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Order Prioirity, we can replace null values with Not Specified\n",
    "salesdf['Order Priority'].fillna('Not Specified', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf['Ship Mode'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace missing values in Ship Mode with 'Regular Air' i.e. most occuring value\n",
    "salesdf['Ship Mode'].fillna('Regular Air', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Handling missing Regions\n",
    "\n",
    "# Lets first check Province value for records that are missing Region\n",
    "print(salesdf[salesdf['Region'].isnull()]['Province'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# so missing Regions belong to Saskachewan and Quebec Province\n",
    "\n",
    "# Lets check what are the general values for Region where Province = Saskachewan\n",
    "print(salesdf[salesdf['Province'] == 'Saskachewan']['Region'].value_counts())\n",
    "\n",
    "# Lets check what are the general values for Region where Province = Quebec\n",
    "print(salesdf[salesdf['Province'] == 'Quebec']['Region'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All records with Province=Saskachewan belongs to Prarie Region and\n",
    "# all records with Province=Quebec belongs to Quebec Region\n",
    "\n",
    "# Lets fill missing values accordingly\n",
    "cond = (salesdf['Province'] == 'Saskachewan') & (salesdf['Region'].isnull())\n",
    "salesdf.loc[cond, 'Region'] = 'Prarie'\n",
    "\n",
    "cond2 = (salesdf['Province'] == 'Quebec') & (salesdf['Region'].isnull())\n",
    "salesdf.loc[cond2, 'Region'] = 'Quebec'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dealing with missing Product Category\n",
    "# Lets try to find all Product Sub-Categories for missing product categories\n",
    "sub_cat_list = salesdf[salesdf['Product Category'].isnull()]['Product Sub-Category'].value_counts().index.to_list()\n",
    "print(sub_cat_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_missing_product_category(item):\n",
    "    # Lets try to find a non null product category for same sub category \n",
    "    #in our dataframe \n",
    "    cond = (salesdf['Product Sub-Category'] == item) & (salesdf['Product Category'].isnull() == False)\n",
    "    res_category = salesdf[cond].iloc[1]['Product Category']\n",
    "    \n",
    "    cond_to_replace = (salesdf['Product Category'].isnull() == True) & (salesdf['Product Sub-Category'] == item)\n",
    "    salesdf.loc[cond_to_replace, 'Product Category'] = res_category\n",
    "\n",
    "for item in sub_cat_list:\n",
    "    fill_missing_product_category(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dealing with missing Product Container values\n",
    "print(salesdf['Product Container'].value_counts())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets check Product Category for these entries\n",
    "salesdf[salesdf['Product Container'].isnull()]['Product Category']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets check Product Category for these entries\n",
    "salesdf[salesdf['Product Category']=='Office Supplies']['Product Container'].mode()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# From available data we can see that Office Supplies and Technology \n",
    "#category generally has 'Small Box' as their container\n",
    "# while furniture items has 'Jumbo Box' as their container\n",
    "# we can follow same trend to fill missing values\n",
    "cond = ((salesdf['Product Category'] == \"Office Supplies\") | \\\n",
    "        (salesdf['Product Category'] == \"Technology\")) & \\\n",
    "        (salesdf['Product Container'].isnull() == True)\n",
    "salesdf.loc[cond, 'Product Container'] = 'Small Box'\n",
    "\n",
    "cond2 = ((salesdf['Product Category'] == \"Furniture\")) & \\\n",
    "        (salesdf['Product Container'].isnull() == True)\n",
    "salesdf.loc[cond2, 'Product Container'] = 'Jumbo Box'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dealing with product name\n",
    "# lets check how many unique product names do we have in our sample, this would give an idea of whether this feature is useful or not\n",
    "prodcut_name_cardinality = len(salesdf['Product Name'].value_counts().index)\n",
    "print (prodcut_name_cardinality)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#salesdf.groupby(['Customer Segment', 'Product Name']).size()\n",
    "#salesdf.groupby(['Product Sub-Category', 'Product Name']).size()\n",
    "#salesdf['Product Category']['Product Container'].mode()\n",
    "#salesdf.groupby(['Product Sub-Category','Product Name']).agg({'Sales': ['mean', 'min', 'max']})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Product Name column has too much variance (too many product names), so this wouldn't go in our Machine Learning model.\n",
    "# So its not worth filling missing values for this column\n",
    "\n",
    "# Lets drop this column\n",
    "salesdf = salesdf.drop('Product Name', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Same goes for Customer Name column, we are not going to pass this feature to our ML model.\n",
    "# so lets drop Customer Name column as well\n",
    "salesdf = salesdf.drop('Customer Name', axis=1)\n",
    "\n",
    "salesdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets calcuate % of records that are missing sales value\n",
    "precent_missing = ((salesdf['Sales'].isnull().sum())/salesdf.shape[0])*100\n",
    "print(precent_missing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# So only 0.38% records are missing sales value\n",
    "# Lets delete these records for now\n",
    "salesdf = salesdf.drop(salesdf[salesdf['Sales'].isnull()].index)\n",
    "\n",
    "# Lets do the same for Order Quantity\n",
    "salesdf = salesdf.drop(salesdf[salesdf['Order Quantity'].isnull()].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We don't really need any unique identifier for ML, lets drop Row ID as well\n",
    "salesdf = salesdf.drop('Row ID', axis=1)\n",
    "\n",
    "salesdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with remaining categorical columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have already removed categorical columns with too many different values like Product Name and Customer Name after checking value_counts, Lets run same analysis over other categorical variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Order Priority\n",
    "print(salesdf['Order Priority'].value_counts())\n",
    "salesdf['Order Priority'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So distribution looks uniform, and it doesn't have too many unqiue values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Ship Mode\n",
    "print(salesdf['Ship Mode'].value_counts())\n",
    "salesdf['Ship Mode'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So most of the transactions in our sample (74.67% to be exact) were shipped via `Regular Air`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Province\n",
    "print(salesdf['Province'].value_counts())\n",
    "salesdf['Province'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Region\n",
    "print(salesdf['Region'].value_counts())\n",
    "salesdf['Region'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both Province and Region are following same distribution. So we can keep Region as it has less unique values and its equally meaningful."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf = salesdf.drop('Province', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Customer Segment \n",
    "print(salesdf['Customer Segment'].value_counts())\n",
    "salesdf['Customer Segment'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More transactions are from Corporate customers. This is a valuable information, could be useful for other departments as well like for targeted marketing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Product Category\n",
    "print(salesdf['Product Category'].value_counts())\n",
    "salesdf['Product Category'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the trend in above plot, Most sold category is `Office Supplies`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Product Sub-Category\n",
    "print(salesdf['Product Sub-Category'].value_counts())\n",
    "salesdf['Product Sub-Category'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For Product Container\n",
    "print(salesdf['Product Container'].value_counts())\n",
    "salesdf['Product Container'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For boxplots, lets first convert all columns holding categorical data to pandas category type\n",
    "\n",
    "# Lets pick categorical columns in a separate dataframe to be on safe side\n",
    "salesdf_cat = salesdf[['Order Priority', 'Ship Mode', 'Region',\n",
    "                       'Customer Segment', 'Product Category', 'Product Sub-Category',\n",
    "                       'Product Container']]\n",
    "\n",
    "mapper = {}\n",
    "\n",
    "for i in salesdf_cat.columns:\n",
    "    salesdf_cat[i] = salesdf_cat[i].astype('category')\n",
    "    k = i.lower().replace(' ', '_')\n",
    "    \n",
    "    # Save map to use later for better understanding\n",
    "    d = dict(enumerate(salesdf_cat[i].cat.categories))\n",
    "    mapper[k] = d\n",
    "    salesdf_cat[k] = salesdf_cat[i].cat.codes\n",
    "    salesdf_cat = salesdf_cat.drop(i, axis=1)\n",
    "\n",
    "import matplotlib.pyplot as plt    \n",
    "#print(mapper)\n",
    "\n",
    "for i in salesdf_cat.columns:\n",
    "    text_to_display = \"\"\n",
    "    count = 0\n",
    "    for k,v in mapper[i].items():\n",
    "        count += 1\n",
    "        text_to_display += \"%s = %s\"%(str(k),v)\n",
    "        if count%2 == 0:\n",
    "            text_to_display += \"\\n\"\n",
    "        else:\n",
    "            text_to_display += \", \"\n",
    "    fig = plt.figure()\n",
    "    plt.text(2, 1, text_to_display)\n",
    "    salesdf_cat.boxplot(column=i, sym='o', return_type='axes')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf[['Sales','Profit']].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t_val, p_val = stats.ttest_ind(salesdf['Sales'], salesdf['Profit'])\n",
    "print(\"p-value=%s\" % str(p_val))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# T-Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# T-Test\n",
    "types_map = salesdf.dtypes.to_dict()\n",
    "num_columns = []\n",
    "for k,v in types_map.items():\n",
    "    if np.issubdtype(np.int64, v) or np.issubdtype(np.float64, v):\n",
    "        num_columns.append(k)\n",
    "\n",
    "print(num_columns)\n",
    "\n",
    "for i in range(len(num_columns)-1):\n",
    "    for j in range(i+1,len(num_columns)):\n",
    "        col1 = num_columns[i]\n",
    "        col2 = num_columns[j]\n",
    "        t_val, p_val = stats.ttest_ind(salesdf[col1], salesdf[col2])\n",
    "        print(\"(%s,%s) => t-value=%s, p-value=%s\" % (num_columns[i], num_columns[j], str(t_val), str(p_val)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Conducted t-test for between all numeric columns in our dataset(i.e. `'Order Quantity', 'Sales', 'Discount', 'Profit', 'Unit Price', 'Shipping Cost'`) and as shown in the results above all p-value for every pair turns out to be less than 0.05 (p-val < 0.05), infact its very small for every pair.\n",
    "Thus, there is a statistically significant difference between all above mentioned numeric columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1-way Anova"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "categorical = list((set(salesdf.columns) - set(num_columns)) - set(['Order Date', 'Ship Date']))\n",
    "print(categorical)\n",
    "print(num_columns)\n",
    "\n",
    "import scipy.stats as stats\n",
    "import statsmodels.api as sm\n",
    "from statsmodels.formula.api import ols\n",
    "\n",
    "# Taking 1-way anova of Sales with all categorical variables\n",
    "for i in categorical:\n",
    "    model = ols('Sales ~ C(Q(\"%s\"))'% i, data=salesdf).fit()\n",
    "    anova_table = sm.stats.anova_lm(model, typ=2)\n",
    "    print (\"\\nAnova => Sales - %s\" % i)\n",
    "    display(anova_table)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With our null hypothesis being Ho = no difference between population means of Sales and groups(categorical columns).\n",
    "Above results of 1-way Anova indicates that for Ship Mode, Product Category, Product Container and Product Sub-Category p-value turns out to be less than 0.05, so we can reject our null hypothesis thus we can state with 95% confidence that difference between means exist for sales and groups in above mentioned columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets perform the same 1-way anova test between profit and categorical colums we used above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in categorical:\n",
    "    model = ols('Profit ~ C(Q(\"%s\"))'% i, data=salesdf).fit()\n",
    "    anova_table = sm.stats.anova_lm(model, typ=2)\n",
    "    print (\"\\n Anova => Profit - %s\" % i)\n",
    "    display(anova_table)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, we performed 1-way Anova of Profit with Categorical columns and results indicates that for Order Priority, Product Category, Product Container and Product Sub-Category, p-value is less than 0.05, so we can state with 95% confidence that difference between means exist for profit and groups in above mentioned columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2-way Anova"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Making a list of tuples holding 2 categorical columns\n",
    "lst = []\n",
    "for i in range(len(categorical)-1):\n",
    "    for j in range(i+1, len(categorical)):\n",
    "        lst.append((categorical[i], categorical[j]))\n",
    "        \n",
    "# Lets perform 2-way anova with Sales and tuples we populated in above list\n",
    "for i in lst:\n",
    "    model = ols('Sales ~ C(Q(\"%s\")) + C(Q(\"%s\")) + C(Q(\"%s\")):C(Q(\"%s\"))' % (i[0],i[1],i[0],i[1]), data=salesdf).fit()\n",
    "    anova_table = sm.stats.anova_lm(model, typ=2)\n",
    "    display(anova_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets perform 2-way anova with Profit and tuples we populated in above list\n",
    "for i in lst:\n",
    "    model = ols('Profit ~ C(Q(\"%s\")) + C(Q(\"%s\")) + C(Q(\"%s\")):C(Q(\"%s\"))' % (i[0],i[1],i[0],i[1]), data=salesdf).fit()\n",
    "    anova_table = sm.stats.anova_lm(model, typ=2)\n",
    "    display(anova_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Plotting histograms of numerical columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salesdf.hist(column=['Order Quantity', 'Sales', 'Discount', 'Profit', 'Unit Price', 'Shipping Cost'],\n",
    "            bins=25, grid=False, figsize=(15,12), color='#86bf91', zorder=2, rwidth=0.9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- So there are right skews in `Shpping Cost, Sales, Unit Price and Profit`\n",
    "- No skews observed in `Order Quantity and Discount`\n",
    "- Right skews in above graphs are showing that most of the data appears at the left of graph(smaller values) but there are few larger values in our sample that makes mean/average greater than median(middle value).\n",
    "So the mean of Shpping Cost, Sales, Unit Price and Profit is clearly affected by outliers (i.e. few larger values) and it cannot represent data in its true essense."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets check mean and median of all right skewed histograms\n",
    "for i in ['Shipping Cost', 'Unit Price', 'Sales', 'Profit']:\n",
    "    print(\"%s => Median=%s, Mean=%s\" % (i, str(salesdf[i].median()), str(salesdf[i].mean())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Heat Map for numerical KPIs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "sns.set(color_codes=True, font_scale=1.2)\n",
    "\n",
    "salesnum_df = salesdf[num_columns]\n",
    "corr = salesnum_df.corr()\n",
    "\n",
    "#corr.style.background_gradient(cmap='coolwarm')\n",
    "\n",
    "ax = sns.heatmap(\n",
    "    corr, \n",
    "    vmin=-1, vmax=1, center=0,\n",
    "    cmap=sns.diverging_palette(20, 220, n=200),\n",
    "    square=True\n",
    ")\n",
    "ax.set_xticklabels(\n",
    "    ax.get_xticklabels(),\n",
    "    rotation=45,\n",
    "    horizontalalignment='right'\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From above heatmap, we can draw following conclusions\n",
    "- `Strong positive correlation` between `Sales and Profit`\n",
    "- `Strong positive correlation` between `Sales and Unit Price`\n",
    "- `Moderate positive correlation` between `Shpping Cost and Sales`\n",
    "- `Weak positive correlation` between `Order Quantity and Sales`\n",
    "- `Weak positive correlation` between `Order Quantity and Profit`\n",
    "- `Weak negative correlation` between `Unit Price and Order Quantity`\n",
    "\n",
    "These correlations represent the association between different features in our dataset and ML is all about correlations. This sets the base for Machine Learning modelling techinques. Correlation helps in predicting the value of an attribute (e.g. sales) from other attributes(e.g. Profit, Unit Price, Shipping Cost, Order Qunatity)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking which deliveries are taking more time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is just to turn off warning\n",
    "pd.set_option('mode.chained_assignment', None)\n",
    "\n",
    "# Lets pick features of our interest and store them as a separate dataframe\n",
    "salesdf_new = salesdf[['Order Date', 'Order Priority', 'Ship Mode', 'Region', 'Customer Segment', 'Product Category', 'Product Container', 'Ship Date']]\n",
    "\n",
    "# Store difference between Ship Date and Order Date as a separate column \n",
    "salesdf_new['time_diff'] = salesdf_new['Ship Date'] - salesdf_new['Order Date']\n",
    "\n",
    "# Sort them in descending order according to time_diff. This would bring deliveries that took more time to the top\n",
    "salesdf_new = salesdf_new.sort_values(by='time_diff', ascending=False)\n",
    "\n",
    "print(\"average time=\" + str(salesdf_new['time_diff'].mean()))\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# now lets see the stats for top 100 records in this new df\n",
    "salesdf_new = salesdf_new.head(100)\n",
    "print (\"average time for top 100 most delayed deliveries =\" + str(salesdf_new['time_diff'].mean()))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lets get a sense of whats common among these records\n",
    "salesdf_new.sample(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It seems that most of these records are the orders placed with `Low` Order Priority.\n",
    "Lets validate this theory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find % of records with Low Order Priority in full dataset\n",
    "percent_low = ((salesdf[salesdf['Order Priority'] == \"Low\"].shape[0])/salesdf.shape[0])*100\n",
    "\n",
    "# Now lets find % of records with Low Order Priority in top 100 most delayed deliveries\n",
    "percent_low_delayed = ((salesdf_new[salesdf_new['Order Priority'] == \"Low\"].shape[0])/salesdf_new.shape[0])*100\n",
    "\n",
    "print(\"percentage of records with Low Order Priority in full dataset = %s\" % str(percent_low))\n",
    "print(\"percentage of records with Low Order Priority in top 100 most delayed deliveries = %s\" % str(percent_low_delayed))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we can clearly see that % of records with Low Order Priority in top 100 most delayed deliveries is actually much higher than its percentage in full dataset. So we can conclude that records with `Low` Order Priority are taking more time in comparison with others"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
