Pandas Data Exploration and Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9cc38e73",
   "metadata": {},
   "source": [
    "# Creating The Artificial Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f5985f37",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Name Age  Address Qualification Score        Date\n",
      "0        Amit  44  Aligarh           Phd    40  2022-07-12\n",
      "1        Ravi  50  Kannauj           NaN    63  2022-10-21\n",
      "2      Manish  38  Aligarh           Msc    27  2021-11-26\n",
      "3        Anuj  43   Bhopal           Msc    64  2021-07-07\n",
      "4        None  30  Aligarh          None    38  2022-03-20\n",
      "...       ...  ..      ...           ...   ...         ...\n",
      "2495    Sunil  30   Kanpur          None    78  2020-02-23\n",
      "2496    Vijay  35  Jaunpur        B.Tech    26  2022-06-03\n",
      "2497  Sandeep  37     None           B.A    70  2020-06-15\n",
      "2498  Sandeep  42   Kanpur          None    24  2023-01-23\n",
      "2499   Princi  42   Nagpur           NaN    70  2022-01-06\n",
      "\n",
      "[2500 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings \n",
    "warnings.filterwarnings('ignore')\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "# Add The Columns\n",
    "names = ['Jai', 'Anuj', 'Princi', 'Gaurav', 'Abhi', 'Ravi', 'Amit',\n",
    "         'Rahul', 'Kamal', 'Vikram', 'Sachin', 'Ankit', 'Mukesh', 'Saurabh', \n",
    "         'Rajesh', 'Suresh', 'Praveen', 'Vijay', 'Sandeep', 'Sunil', 'Deepak',\n",
    "         'Vinod', 'Manish', 'Tarun',None,None]\n",
    "addresses = ['Nagpur', 'Kanpur', 'Allahabad', 'Kannauj', 'Jaunpur', 'Aligarh',\n",
    "             'Lucknow', 'Bhopal',None,None]\n",
    "qualifications = ['Msc', 'MA', 'MCA', 'Phd', 'B.Tech', 'B.com', 'B.A',\n",
    "                  'Diploma',None,None]\n",
    "data1 = {'Name': [], 'Age': [], 'Address': [], 'Qualification': [], 'Score': []}\n",
    "\n",
    "for i in range(2000):\n",
    "    data1['Name'].append(random.choice(names))\n",
    "    data1['Age'].append(random.randint(22, 60))\n",
    "    data1['Address'].append(random.choice(addresses))\n",
    "    data1['Qualification'].append(random.choice(qualifications))\n",
    "    data1['Score'].append(random.randint(20, 100))\n",
    "\n",
    "    \n",
    "       \n",
    "df = pd.DataFrame(data1)\n",
    "   \n",
    "# Adding A Date column\n",
    "start_date = datetime(2020, 1, 1)\n",
    "end_date = datetime(2023, 2, 4)\n",
    "time_between_dates = end_date - start_date\n",
    "days_between_dates = time_between_dates.days\n",
    "df['Date'] = [start_date + timedelta(days=random.randint(0, days_between_dates)) for i in range(df.shape[0])]\n",
    "\n",
    "# Changing the Data Type\n",
    "df['Date'] = df['Date'].astype(str)\n",
    "df['Age'] = df['Age'].astype(str)\n",
    "df['Score'] = df['Score'].astype(str)\n",
    "\n",
    "# Add The Duplicated Data\n",
    "duplicated_rows = df.loc[random.sample(list(range(2000)), 500)].copy()\n",
    "df = df.append(duplicated_rows, ignore_index=True)\n",
    "rows = list(range(df.shape[0]))\n",
    "\n",
    "\n",
    "# Add The Null Data\n",
    "rows = random.sample(rows, int(df.shape[0] * 0.05))\n",
    "\n",
    "columns = random.sample(list(df.columns), int(df.shape[1] * 0.2))\n",
    "\n",
    "for column in columns:\n",
    "    df.loc[rows, column] = np.nan\n",
    "    \n",
    "    \n",
    "    \n",
    "# Shuffle The Data\n",
    "df = df.sample(frac=1, random_state=0).reset_index(drop=True)\n",
    "\n",
    "\n",
    "# Final Data\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e76e237c",
   "metadata": {},
   "source": [
    "We have generated this dataset containing duplicate and missing values for the purpose of demonstrating the significance of data cleaning and improving our understanding of the process."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93124b77",
   "metadata": {},
   "source": [
    "# Data Exploration and Data Cleaning\n",
    "\n",
    "Data Exploration is the process of analyzing and summarizing the characteristics and patterns of a dataset in order to gain a better understanding of the data and identify any potential issues that may need to be addressed before using the data for further analysis or modeling\n",
    "\n",
    "Data Cleaning is the process of correcting or removing errors, inconsistencies, and inaccuracies in a dataset in order to improve the quality and reliability of the data for analysis and modeling"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a5d9430",
   "metadata": {},
   "source": [
    "### Checking Anomalies in Data Statistics and Types"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b905fc4d",
   "metadata": {},
   "source": [
    "The **describe method** can be useful for quickly getting an overview of the distribution of your data and detecting any outliers or anomalies. It can also help identify any missing or non-numeric values in your data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5a51873f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Address</th>\n",
       "      <th>Qualification</th>\n",
       "      <th>Score</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2296</td>\n",
       "      <td>2500</td>\n",
       "      <td>2016</td>\n",
       "      <td>1887</td>\n",
       "      <td>2500</td>\n",
       "      <td>2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>24</td>\n",
       "      <td>39</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>81</td>\n",
       "      <td>938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Princi</td>\n",
       "      <td>53</td>\n",
       "      <td>Lucknow</td>\n",
       "      <td>B.Tech</td>\n",
       "      <td>79</td>\n",
       "      <td>2022-01-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>117</td>\n",
       "      <td>84</td>\n",
       "      <td>264</td>\n",
       "      <td>259</td>\n",
       "      <td>45</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Name   Age  Address Qualification Score        Date\n",
       "count     2296  2500     2016          1887  2500        2500\n",
       "unique      24    39        8             8    81         938\n",
       "top     Princi    53  Lucknow        B.Tech    79  2022-01-16\n",
       "freq       117    84      264           259    45          10"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7e60b1a",
   "metadata": {},
   "source": [
    "The info method in pandas is used to obtain a concise summary of a dataframe, including the number of non-missing values in each column, the data type of each column, and the memory usage of the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d530b63a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2500 entries, 0 to 2499\n",
      "Data columns (total 6 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   Name           2296 non-null   object\n",
      " 1   Age            2500 non-null   object\n",
      " 2   Address        2016 non-null   object\n",
      " 3   Qualification  1887 non-null   object\n",
      " 4   Score          2500 non-null   object\n",
      " 5   Date           2500 non-null   object\n",
      "dtypes: object(6)\n",
      "memory usage: 117.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6fb5af6",
   "metadata": {},
   "source": [
    "It appears that integer and datetime data are stored as string data types. To ensure proper data analysis and manipulation, it is necessary to perform data type conversion and convert these values into their appropriate integer and datetime data types."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "188e0d9e",
   "metadata": {},
   "source": [
    "### Data Type Conversion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2d1de203",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Score'] = df['Score'].astype(int)\n",
    "df['Age'] = df['Age'].astype(float)\n",
    "df['Date'] = pd.to_datetime(df['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5a66d6e5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2500 entries, 0 to 2499\n",
      "Data columns (total 6 columns):\n",
      " #   Column         Non-Null Count  Dtype         \n",
      "---  ------         --------------  -----         \n",
      " 0   Name           2296 non-null   object        \n",
      " 1   Age            2500 non-null   float64       \n",
      " 2   Address        2016 non-null   object        \n",
      " 3   Qualification  1887 non-null   object        \n",
      " 4   Score          2500 non-null   int32         \n",
      " 5   Date           2500 non-null   datetime64[ns]\n",
      "dtypes: datetime64[ns](1), float64(1), int32(1), object(3)\n",
      "memory usage: 107.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "903080f9",
   "metadata": {},
   "source": [
    "### Checking For Duplicated Rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89e4d284",
   "metadata": {},
   "source": [
    "The duplicated method in pandas is used to check for duplicate rows in a dataframe. The method returns a boolean series with True values for the rows that are duplicates and False values for the unique rows.\n",
    "\n",
    "The value_counts method can then be applied to the resulting series to get a count of the unique values, i.e., the number of duplicate and unique rows in the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f0011daf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    2038\n",
       "True      462\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1d6b042",
   "metadata": {},
   "source": [
    "Keep or drop duplicates depends on the purpose, sometimes keep for accuracy and sometimes drop to avoid mistakes, based on the goal of the analysis.\n",
    "In this Case We are dropping the Duplicated Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d08f0cae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    2038\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop_duplicates(inplace=True)\n",
    "df.duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e716ba0",
   "metadata": {},
   "source": [
    "### Checking for Null Values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7ceddde",
   "metadata": {},
   "source": [
    "The **isnull** method in pandas is used to check for missing values in a dataframe. The method returns a dataframe of the same shape as the original dataframe, with True values in the cells where the corresponding value in the original dataframe is missing (i.e., NaN or None), and False values in the cells where the corresponding value is not missing.\n",
    "\n",
    "The **sum** method can then be applied to the resulting dataframe to calculate the number of missing values in each column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc78fe09",
   "metadata": {},
   "source": [
    "Adding Null Values In the Data For Null Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "62cc1864",
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = np.random.choice(df.index, size=250, replace=False)\n",
    "df.loc[rows, 'Age'] = np.nan\n",
    "df.loc[rows, 'Score'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "abde3fa4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name             170\n",
       "Age              250\n",
       "Address          395\n",
       "Qualification    512\n",
       "Score            250\n",
       "Date               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99491abb",
   "metadata": {},
   "source": [
    "### Handling missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b77c4ee",
   "metadata": {},
   "source": [
    "1.For The Categorical Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4021ad1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name               0\n",
       "Age              250\n",
       "Address            0\n",
       "Qualification      0\n",
       "Score            250\n",
       "Date               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Name'].fillna('Unknown',inplace=True)\n",
    "df['Address'].fillna('Unknown',inplace=True)\n",
    "df['Qualification'].fillna('Unknown',inplace=True)\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c33842d",
   "metadata": {},
   "source": [
    "2.For The Numerical Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "274f3257",
   "metadata": {},
   "source": [
    "(a) Age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2319550a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unknown    77\n",
       "MCA        31\n",
       "Phd        25\n",
       "Msc        22\n",
       "MA         22\n",
       "B.A        21\n",
       "B.com      20\n",
       "B.Tech     19\n",
       "Diploma    13\n",
       "Name: Qualification, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'Qualification'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c43d281e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Princi     15\n",
       "Unknown    14\n",
       "Deepak     14\n",
       "Sandeep    13\n",
       "Vinod      13\n",
       "Sunil      13\n",
       "Anuj       12\n",
       "Jai        12\n",
       "Ankit      12\n",
       "Rajesh     11\n",
       "Vijay      10\n",
       "Amit       10\n",
       "Abhi        9\n",
       "Suresh      9\n",
       "Manish      9\n",
       "Mukesh      9\n",
       "Gaurav      9\n",
       "Kamal       8\n",
       "Ravi        8\n",
       "Rahul       8\n",
       "Tarun       7\n",
       "Vikram      7\n",
       "Sachin      7\n",
       "Praveen     6\n",
       "Saurabh     5\n",
       "Name: Name, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'Name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6ce5bb84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unknown      52\n",
       "Jaunpur      32\n",
       "Kanpur       28\n",
       "Bhopal       27\n",
       "Nagpur       26\n",
       "Allahabad    24\n",
       "Lucknow      21\n",
       "Aligarh      20\n",
       "Kannauj      20\n",
       "Name: Address, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'Address'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f793eea5",
   "metadata": {},
   "source": [
    "Creating Separate Date Column to Check for Variation in Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8a1ce4d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['start_year'] = df.Date.dt.year\n",
    "df['start_month'] = df.Date.dt.month_name()\n",
    "df['start_date'] = df.Date.dt.day\n",
    "df['week_day']  = df.Date.dt.day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b22c765d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020    90\n",
       "2021    82\n",
       "2022    69\n",
       "2023     9\n",
       "Name: start_year, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'start_year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a09d4257",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "January      27\n",
       "September    27\n",
       "April        27\n",
       "June         24\n",
       "December     23\n",
       "October      22\n",
       "July         21\n",
       "May          17\n",
       "February     16\n",
       "March        16\n",
       "November     16\n",
       "August       14\n",
       "Name: start_month, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'start_month'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e9788993",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14    17\n",
       "6     14\n",
       "24    13\n",
       "17    12\n",
       "20    11\n",
       "15    11\n",
       "8     11\n",
       "26    10\n",
       "19    10\n",
       "3      9\n",
       "12     8\n",
       "5      8\n",
       "28     8\n",
       "18     8\n",
       "10     8\n",
       "30     8\n",
       "27     7\n",
       "16     7\n",
       "29     7\n",
       "22     7\n",
       "7      7\n",
       "1      6\n",
       "9      6\n",
       "11     6\n",
       "21     6\n",
       "2      5\n",
       "25     5\n",
       "23     5\n",
       "31     4\n",
       "4      4\n",
       "13     2\n",
       "Name: start_date, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'start_date'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9ec16545",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Monday       44\n",
       "Wednesday    41\n",
       "Thursday     36\n",
       "Tuesday      36\n",
       "Friday       36\n",
       "Sunday       31\n",
       "Saturday     26\n",
       "Name: week_day, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Age'].isnull() ==True,'week_day'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d02e5fc",
   "metadata": {},
   "source": [
    "Since the missing values don't seem to be connected to any of the other columns, we can fill them by by taking the mean of the two closest non-null values in the same column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2fc7536e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "def fill_na_mean_closest_two(col):\n",
    "    \"\"\"\n",
    "    Fills missing values in the column by taking the mean of the two closest non-null values.\n",
    "    \"\"\"\n",
    "    not_null = col.notnull()\n",
    "    idx = col.index[not_null]\n",
    "    val = col.loc[not_null].values\n",
    "    \n",
    "    filled = col.copy()\n",
    "    for i, row in col[~not_null].iteritems():\n",
    "        j = np.searchsorted(idx, i, side='left')\n",
    "        if j > 0 and (j == len(idx) or math.fabs(i - idx[j-1]) < math.fabs(i - idx[j])):\n",
    "            j -= 1\n",
    "        filled.loc[i] = (val[j-1] + val[j]) / 2\n",
    "    return filled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "35960fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Age'] = fill_na_mean_closest_two(df['Age'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e675999c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name               0\n",
       "Age                0\n",
       "Address            0\n",
       "Qualification      0\n",
       "Score            250\n",
       "Date               0\n",
       "start_year         0\n",
       "start_month        0\n",
       "start_date         0\n",
       "week_day           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b9e36852",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name               0\n",
       "Age                0\n",
       "Address            0\n",
       "Qualification      0\n",
       "Score            250\n",
       "Date               0\n",
       "start_year         0\n",
       "start_month        0\n",
       "start_date         0\n",
       "week_day           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1763ec0",
   "metadata": {},
   "source": [
    "(b) Score\n",
    "\n",
    "Lets Repeat the Same method for the Score Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "26a22953",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unknown    77\n",
       "MCA        31\n",
       "Phd        25\n",
       "Msc        22\n",
       "MA         22\n",
       "B.A        21\n",
       "B.com      20\n",
       "B.Tech     19\n",
       "Diploma    13\n",
       "Name: Qualification, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'Qualification'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c60a2599",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Princi     15\n",
       "Unknown    14\n",
       "Deepak     14\n",
       "Sandeep    13\n",
       "Vinod      13\n",
       "Sunil      13\n",
       "Anuj       12\n",
       "Jai        12\n",
       "Ankit      12\n",
       "Rajesh     11\n",
       "Vijay      10\n",
       "Amit       10\n",
       "Abhi        9\n",
       "Suresh      9\n",
       "Manish      9\n",
       "Mukesh      9\n",
       "Gaurav      9\n",
       "Kamal       8\n",
       "Ravi        8\n",
       "Rahul       8\n",
       "Tarun       7\n",
       "Vikram      7\n",
       "Sachin      7\n",
       "Praveen     6\n",
       "Saurabh     5\n",
       "Name: Name, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'Name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "caca17c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unknown      52\n",
       "Jaunpur      32\n",
       "Kanpur       28\n",
       "Bhopal       27\n",
       "Nagpur       26\n",
       "Allahabad    24\n",
       "Lucknow      21\n",
       "Aligarh      20\n",
       "Kannauj      20\n",
       "Name: Address, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'Address'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "cf4e1e13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020    90\n",
       "2021    82\n",
       "2022    69\n",
       "2023     9\n",
       "Name: start_year, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'start_year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "83450cc1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "January      27\n",
       "September    27\n",
       "April        27\n",
       "June         24\n",
       "December     23\n",
       "October      22\n",
       "July         21\n",
       "May          17\n",
       "February     16\n",
       "March        16\n",
       "November     16\n",
       "August       14\n",
       "Name: start_month, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'start_month'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "154a62f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14    17\n",
       "6     14\n",
       "24    13\n",
       "17    12\n",
       "20    11\n",
       "15    11\n",
       "8     11\n",
       "26    10\n",
       "19    10\n",
       "3      9\n",
       "12     8\n",
       "5      8\n",
       "28     8\n",
       "18     8\n",
       "10     8\n",
       "30     8\n",
       "27     7\n",
       "16     7\n",
       "29     7\n",
       "22     7\n",
       "7      7\n",
       "1      6\n",
       "9      6\n",
       "11     6\n",
       "21     6\n",
       "2      5\n",
       "25     5\n",
       "23     5\n",
       "31     4\n",
       "4      4\n",
       "13     2\n",
       "Name: start_date, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'start_date'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9d14382b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Monday       44\n",
       "Wednesday    41\n",
       "Thursday     36\n",
       "Tuesday      36\n",
       "Friday       36\n",
       "Sunday       31\n",
       "Saturday     26\n",
       "Name: week_day, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['Score'].isnull() ==True,'week_day'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d255207a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Score'] = fill_na_mean_closest_two(df['Score'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "321029f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name             0\n",
       "Age              0\n",
       "Address          0\n",
       "Qualification    0\n",
       "Score            0\n",
       "Date             0\n",
       "start_year       0\n",
       "start_month      0\n",
       "start_date       0\n",
       "week_day         0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3090d2ea",
   "metadata": {},
   "source": [
    "It appears that the date columns contain some missing or null values. This can impact the accuracy of the analysis and should be handled appropriately, such as by imputing missing values with a suitable substitute or by removing records with missing date values. The best approach will depend on the specific use case and goals of the analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fcdd346",
   "metadata": {},
   "source": [
    "### Forward-filling The Dates\n",
    "3. For the Date Data\n",
    "\n",
    "The interpolate method with the argument method='ffill' is used to fill missing values in multiple columns of a pandas DataFrame. The method is being called on each of the columns 'Date', 'start_year', 'start_month', 'start_date', and 'week_day' and the inplace=True argument is being used to modify the DataFrame in place, meaning that the original DataFrame is being modified and no new DataFrame is being created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "d5736ed4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2038 entries, 0 to 2499\n",
      "Data columns (total 10 columns):\n",
      " #   Column         Non-Null Count  Dtype         \n",
      "---  ------         --------------  -----         \n",
      " 0   Name           2038 non-null   object        \n",
      " 1   Age            2038 non-null   float64       \n",
      " 2   Address        2038 non-null   object        \n",
      " 3   Qualification  2038 non-null   object        \n",
      " 4   Score          2038 non-null   float64       \n",
      " 5   Date           2038 non-null   datetime64[ns]\n",
      " 6   start_year     2038 non-null   int64         \n",
      " 7   start_month    2038 non-null   object        \n",
      " 8   start_date     2038 non-null   int64         \n",
      " 9   week_day       2038 non-null   object        \n",
      "dtypes: datetime64[ns](1), float64(2), int64(2), object(5)\n",
      "memory usage: 239.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df['Date'].interpolate(method='ffill', inplace=True)\n",
    "df['start_year'].interpolate(method='ffill', inplace=True)\n",
    "df['start_month'].interpolate(method='ffill', inplace=True)\n",
    "df['start_date'].interpolate(method='ffill', inplace=True)\n",
    "df['week_day'].interpolate(method='ffill', inplace=True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1104717",
   "metadata": {},
   "source": [
    "### Outlier detection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "948659a6",
   "metadata": {},
   "source": [
    "This Method performs outlier detection on a specific column in a pandas dataframe. The method used to detect outliers is based on the Z-score, which is a standardized measure of how many standard deviations a value is from the mean.\n",
    "\n",
    "\n",
    "Outlier detection is an important step in data cleaning and preparation. Outliers can significantly affect the results of statistical analysis, so it's important to identify and handle them appropriately."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "fe194a52",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [Name, Age, Address, Qualification, Score, Date, start_year, start_month, start_date, week_day]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "mean = df['Score'].mean()\n",
    "std = df['Score'].std()\n",
    "\n",
    "z_scores = (df['Score'] - mean) / std\n",
    "outliers = df[np.abs(z_scores) > 3]\n",
    "\n",
    "print(outliers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "156287fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [Name, Age, Address, Qualification, Score, Date, start_year, start_month, start_date, week_day]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "mean = df['Age'].mean()\n",
    "std = df['Age'].std()\n",
    "\n",
    "z_scores = (df['Age'] - mean) / std\n",
    "outliers = df[np.abs(z_scores) > 3]\n",
    "\n",
    "print(outliers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f63a8ac",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "After thoroughly reviewing the data, we discovered that there were duplicates, missing values, and incorrect data types present and finally It Had Some Empty Date Columns. In order to enhance the quality and reliability of the data, we conducted both Data Exploration and Data Cleaning. The outcome of these processes has resulted in a cleaned dataset, which can now be used for further analysis or modeling. Here is the cleaned data ready for the next steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "17474988",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Address</th>\n",
       "      <th>Qualification</th>\n",
       "      <th>Score</th>\n",
       "      <th>Date</th>\n",
       "      <th>start_year</th>\n",
       "      <th>start_month</th>\n",
       "      <th>start_date</th>\n",
       "      <th>week_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Amit</td>\n",
       "      <td>44.0</td>\n",
       "      <td>Aligarh</td>\n",
       "      <td>Phd</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2022-07-12</td>\n",
       "      <td>2022</td>\n",
       "      <td>July</td>\n",
       "      <td>12</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ravi</td>\n",
       "      <td>50.0</td>\n",
       "      <td>Kannauj</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>63.0</td>\n",
       "      <td>2022-10-21</td>\n",
       "      <td>2022</td>\n",
       "      <td>October</td>\n",
       "      <td>21</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Manish</td>\n",
       "      <td>38.0</td>\n",
       "      <td>Aligarh</td>\n",
       "      <td>Msc</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2021-11-26</td>\n",
       "      <td>2021</td>\n",
       "      <td>November</td>\n",
       "      <td>26</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anuj</td>\n",
       "      <td>43.0</td>\n",
       "      <td>Bhopal</td>\n",
       "      <td>Msc</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2021-07-07</td>\n",
       "      <td>2021</td>\n",
       "      <td>July</td>\n",
       "      <td>7</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Aligarh</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>38.0</td>\n",
       "      <td>2022-03-20</td>\n",
       "      <td>2022</td>\n",
       "      <td>March</td>\n",
       "      <td>20</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Name   Age  Address Qualification  Score       Date  start_year  \\\n",
       "0     Amit  44.0  Aligarh           Phd   40.0 2022-07-12        2022   \n",
       "1     Ravi  50.0  Kannauj       Unknown   63.0 2022-10-21        2022   \n",
       "2   Manish  38.0  Aligarh           Msc   27.0 2021-11-26        2021   \n",
       "3     Anuj  43.0   Bhopal           Msc   64.0 2021-07-07        2021   \n",
       "4  Unknown  30.0  Aligarh       Unknown   38.0 2022-03-20        2022   \n",
       "\n",
       "  start_month  start_date   week_day  \n",
       "0        July          12    Tuesday  \n",
       "1     October          21     Friday  \n",
       "2    November          26     Friday  \n",
       "3        July           7  Wednesday  \n",
       "4       March          20     Sunday  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "889bd8ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>Age</th>\n",
       "      <th>Score</th>\n",
       "      <th>start_year</th>\n",
       "      <th>start_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2038.000000</td>\n",
       "      <td>2038.000000</td>\n",
       "      <td>2038.000000</td>\n",
       "      <td>2038.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>40.927134</td>\n",
       "      <td>60.268646</td>\n",
       "      <td>2021.065260</td>\n",
       "      <td>15.464671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>10.886581</td>\n",
       "      <td>22.371283</td>\n",
       "      <td>0.885247</td>\n",
       "      <td>8.817936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>22.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>2020.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>32.000000</td>\n",
       "      <td>42.000000</td>\n",
       "      <td>2020.000000</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>41.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>2021.000000</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>2022.000000</td>\n",
       "      <td>23.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>60.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>2023.000000</td>\n",
       "      <td>31.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Age        Score   start_year   start_date\n",
       "count  2038.000000  2038.000000  2038.000000  2038.000000\n",
       "mean     40.927134    60.268646  2021.065260    15.464671\n",
       "std      10.886581    22.371283     0.885247     8.817936\n",
       "min      22.000000    20.000000  2020.000000     1.000000\n",
       "25%      32.000000    42.000000  2020.000000     8.000000\n",
       "50%      41.000000    60.000000  2021.000000    15.000000\n",
       "75%      50.000000    79.000000  2022.000000    23.000000\n",
       "max      60.000000   100.000000  2023.000000    31.000000"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}