{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Data and Basic Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Press Ctrl + Enter in each cell below to show the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing the basic libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read the CSV file\n",
    "missingdf = pd.read_csv(\"Missing.Value.Data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#list all columns\n",
    "missingdf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#see data types\n",
    "missingdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we have 28 rows and 8 columns\n",
    "missingdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#See the NaN values in some of the columns - these are the missing values\n",
    "missingdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we need to do some type casting here, but with missing values we cannot cast (the following will give an error)\n",
    "missingdf['Customer.ID'] = missingdf['Customer.ID'].astype(int) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we remove missings first. Let's see how many missings we have\n",
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dealing first with Customer.ID (let's see the distribution)\n",
    "missingdf['Customer.ID'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling by Mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let us replace the one missing value by the mode, i.e., 102\n",
    "missingdf['Customer.ID'].fillna(102,inplace=True)\n",
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now, we can cast the customer ID to string (more logical)\n",
    "missingdf['Customer.ID'] = missingdf['Customer.ID'].astype(str)\n",
    "missingdf.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#now, let's tackle Customer Name, which has 2 missing values\n",
    "missingdf['Customer.Name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Logical Replacement of Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Customer.Names are associated with an ID. If ID is there and name is missing, \n",
    "#we can get the name from the other row\n",
    "#this is because ID 102 is repeated in 3 rows; if one of these rows contains missing name, we can get it from others\n",
    "\n",
    "#find out the IDs where names are missing (119, 102)\n",
    "print(missingdf[missingdf['Customer.Name'].isna()]['Customer.ID'])\n",
    "\n",
    "#find out customer names associated with these IDs\n",
    "IDs = ['102.0','119.0']\n",
    "missingdf[missingdf['Customer.ID'].isin(IDs)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# we can replace the missing customer name with 102 Customer ID with Saim.\n",
    "#ID 119 is a different person but as Customer Name is less imp than ID, so we will go with Saim here\n",
    "\n",
    "missingdf['Customer.Name'].fillna('Saim',inplace=True)\n",
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf['Customer.Name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#let's see the distribution of age\n",
    "missingdf['Customer.Age'].hist()\n",
    "print(missingdf['Customer.Age'].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imputation with Mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#its safe to approximate the age as mean\n",
    "missingdf['Age.Modified'] = missingdf['Customer.Age'].fillna(missingdf['Customer.Age'].mean())\n",
    "missingdf['Age.Modified'].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Interpolation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we can also use interpolation technique (forward linear)\n",
    "missingdf['Age.Interpolate'] = missingdf['Customer.Age'].interpolate(method ='linear', limit_direction ='forward')\n",
    "missingdf['Age.Interpolate'].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we can also use interpolation technique (backward linear)\n",
    "missingdf['Age.Interpolate.2'] = missingdf['Customer.Age'].interpolate(method ='linear', limit_direction ='backward')\n",
    "missingdf['Age.Interpolate.2'].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#tackling product bought\n",
    "print(missingdf['Product.Bought'].value_counts())\n",
    "\n",
    "#lets check the rows where values are missing\n",
    "missingdf[missingdf['Product.Bought'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#if we look at the sales, then we cannot make any assumptions regarding the product bought. \n",
    "#Definitely, they can't be water bottles. Most probable seems Fruit, so replacing with that\n",
    "#we can also choose to drop the rows altogether if you are not able to make a decision\n",
    "\n",
    "missingdf['Product.Bought'].fillna('Clothes',inplace=True)\n",
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#whoops. Fillna does inplace so no need to create a new column.. lets' drop it\n",
    "missingdf = missingdf.drop('Customer.Age',axis=1)\n",
    "print(missingdf.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#also drop the age columns not needed\n",
    "missingdf = missingdf.drop(['Age.Interpolate.2','Age.Modified'],axis=1)\n",
    "print(missingdf.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for quantity and sales, we will adopt the same approach as age\n",
    "#these are critical indicators and we cannot delete rows based on their missings\n",
    "#let's see the distribution of age\n",
    "missingdf['Quantity'].hist()\n",
    "print(missingdf['Quantity'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf['Quantity.Interpolate'] = missingdf['Quantity'].interpolate(method ='linear', limit_direction ='backward')\n",
    "missingdf['Quantity.Interpolate'].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf['Sales'].hist()\n",
    "print(missingdf['Sales'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf['Sales.Interpolate'] = missingdf['Sales'].interpolate(method ='linear', limit_direction ='backward')\n",
    "missingdf['Sales.Interpolate'].hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missingdf.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#removing the original columns (in case you want everything clean)\n",
    "missingdf = missingdf.drop(['Quantity','Sales'],axis=1)\n",
    "missingdf.isnull().sum()"
   ]
  }
 ],
 "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
}
