NB1_IdentifyProblem+DataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Using Statistical Methods To Find Pay Gap Within The Organization   \r\n",
    "\r\n",
    "By Niladri Ghosh\r\n",
    "\r\n",
    "## 1. Identify Problem\r\n",
    "\r\n",
    "Pay Gap has became one of the main concerns in recent times within the working environment. We are living in 21st century and the rights and freedom are equal for every being, irrespective of their gender, race, age or whatnot. People should be accomodated with the quality work they do or the revenuw they bring in. So with this motivation we need to find if pay gap on any basis exists within an organization, if yes what are the reasons behind it and report it.\r\n",
    "\r\n",
    "### 1.1 Expected Outcome\r\n",
    "\r\n",
    "The given data provided by Spark Foundation (imaginary) provides various features of employees like Name, Age, Gender, Country, Ethnicity,etc. Our final outcome is whether there is a gap gap or not.\r\n",
    "\r\n",
    "### 1.2 Objective\r\n",
    "\r\n",
    "Since many features in our data is datetype value, we need to find appropriate techniques to convert them into date type format, also we need to rectify and clean the data. This statistical method is refered to as Hypothesis Test.\r\n",
    "\r\n",
    "> Since this is a __hypothesis test__, our __final goal would be to prove that our given hypothesis is wrong, which suggests the alternative hypothesis is correct and hence pay gap exists__.\r\n",
    "\r\n",
    "### 1.3 Identify Data Sources\r\n",
    "\r\n",
    "The dataset contains 174 rows of data and 10 columns (including salary).\r\n",
    "\r\n",
    "* The 10th column provides the employee salary.\r\n",
    "* Columns 1-9 provides various details for the employee."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "__Getting Started : Load libraries and set options__"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "# import necessary libraries\r\n",
    "import numpy as np\r\n",
    "import pandas as pd\r\n",
    "import math\r\n",
    "from scipy.stats import sem\r\n",
    "from scipy.stats import t\r\n",
    "\r\n",
    "# default='warn'\r\n",
    "pd.options.mode.chained_assignment = None  \r\n",
    "pd.set_option('display.max_columns', None)\r\n",
    "\r\n",
    "import warnings\r\n",
    "warnings.filterwarnings('ignore')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "__Load Dataset__\r\n",
    "\r\n",
    "First load the supplied CSV file using Pandas read_csv function."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "# read data\r\n",
    "df_primary = pd.read_csv('data/evidence.csv',delimiter=',')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# create copy of dataframe\r\n",
    "df = df_primary.copy()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "__Inspecting the data__\r\n",
    "\r\n",
    "The first step is to visually inspect the new dataset. There are multiple ways to acheive this:\r\n",
    "* The easiest way is to fetch first 5 rows is using DataFrame.head(), here df.head().\r\n",
    "* Alternatively we can fetch the last 5 rows using DataFrame.tail(), here df.tail().\r\n",
    "\r\n",
    "__NOTE:__ \r\n",
    "\r\n",
    "For both the above methods we can add a parameter inside the parenthesis '()' to specify how many rows we want to display, thus we can inspect the data."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "df.head(10)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      Surname     Name  Age  Gender        Country Ethnicity  Start_date  \\\n",
       "0  Sweetwater     Alex   51    Male  United States     White  15-08-2011   \n",
       "1    Carabbio   Judith   30  Female  United States     White  11-11-2013   \n",
       "2       Saada    Adell   31  Female  United States     White  05-11-2012   \n",
       "3       Szabo   Andrew   34    Male  United States     White  07-07-2014   \n",
       "4    Andreola    Colby   38  Female  United States     White  10-11-2014   \n",
       "5    Daneault     Lynn   27  Female  United States     White  05-05-2014   \n",
       "6    Houlihan    Debra   51  Female  United States     White  05-05-2014   \n",
       "7       Onque  Jasmine   27  Female  United States     White  30-09-2013   \n",
       "8      Jeremy    Peter   43    Male  United States     White  12-05-2014   \n",
       "9    Gonzales  Ricardo   63    Male  United States     White  12-05-2014   \n",
       "\n",
       "                  Department                      Position        Salary  \n",
       "0  Software Engineering       Software Engineering Manager    $56,160.00  \n",
       "1       Software Engineering             Software Engineer  $1,16,480.00  \n",
       "2       Software Engineering             Software Engineer  $1,02,440.00  \n",
       "3       Software Engineering             Software Engineer    $99,840.00  \n",
       "4       Software Engineering             Software Engineer    $99,008.00  \n",
       "5                      Sales                 Sales Manager  $1,12,320.00  \n",
       "6                      Sales             Director of Sales  $1,24,800.00  \n",
       "7                      Sales            Area Sales Manager  $1,18,560.00  \n",
       "8                      Sales            Area Sales Manager  $1,16,480.00  \n",
       "9                      Sales            Area Sales Manager  $1,15,440.00  "
      ],
      "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>Surname</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Country</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>Start_date</th>\n",
       "      <th>Department</th>\n",
       "      <th>Position</th>\n",
       "      <th>Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>Alex</td>\n",
       "      <td>51</td>\n",
       "      <td>Male</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>15-08-2011</td>\n",
       "      <td>Software Engineering</td>\n",
       "      <td>Software Engineering Manager</td>\n",
       "      <td>$56,160.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Carabbio</td>\n",
       "      <td>Judith</td>\n",
       "      <td>30</td>\n",
       "      <td>Female</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>11-11-2013</td>\n",
       "      <td>Software Engineering</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>$1,16,480.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Saada</td>\n",
       "      <td>Adell</td>\n",
       "      <td>31</td>\n",
       "      <td>Female</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>05-11-2012</td>\n",
       "      <td>Software Engineering</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>$1,02,440.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Szabo</td>\n",
       "      <td>Andrew</td>\n",
       "      <td>34</td>\n",
       "      <td>Male</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>07-07-2014</td>\n",
       "      <td>Software Engineering</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>$99,840.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andreola</td>\n",
       "      <td>Colby</td>\n",
       "      <td>38</td>\n",
       "      <td>Female</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>10-11-2014</td>\n",
       "      <td>Software Engineering</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>$99,008.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Daneault</td>\n",
       "      <td>Lynn</td>\n",
       "      <td>27</td>\n",
       "      <td>Female</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>05-05-2014</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Sales Manager</td>\n",
       "      <td>$1,12,320.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Houlihan</td>\n",
       "      <td>Debra</td>\n",
       "      <td>51</td>\n",
       "      <td>Female</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>05-05-2014</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Director of Sales</td>\n",
       "      <td>$1,24,800.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Onque</td>\n",
       "      <td>Jasmine</td>\n",
       "      <td>27</td>\n",
       "      <td>Female</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>30-09-2013</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Area Sales Manager</td>\n",
       "      <td>$1,18,560.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Jeremy</td>\n",
       "      <td>Peter</td>\n",
       "      <td>43</td>\n",
       "      <td>Male</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>12-05-2014</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Area Sales Manager</td>\n",
       "      <td>$1,16,480.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Gonzales</td>\n",
       "      <td>Ricardo</td>\n",
       "      <td>63</td>\n",
       "      <td>Male</td>\n",
       "      <td>United States</td>\n",
       "      <td>White</td>\n",
       "      <td>12-05-2014</td>\n",
       "      <td>Sales</td>\n",
       "      <td>Area Sales Manager</td>\n",
       "      <td>$1,15,440.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "# check shape of the given data\r\n",
    "df.shape"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(174, 10)"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "We can observe here the number of 174 rows, each with 10 columns.\r\n",
    "\r\n",
    "On contrary we can use use info() method provided by pandas to generate a consise summary of the data. It provides the detail about each column, number of rows, null values, the data type and the memory usage."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "df.info()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 174 entries, 0 to 173\n",
      "Data columns (total 10 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Surname     174 non-null    object\n",
      " 1   Name        174 non-null    object\n",
      " 2   Age         174 non-null    int64 \n",
      " 3   Gender      174 non-null    object\n",
      " 4   Country     174 non-null    object\n",
      " 5   Ethnicity   174 non-null    object\n",
      " 6   Start_date  174 non-null    object\n",
      " 7   Department  174 non-null    object\n",
      " 8   Position    174 non-null    object\n",
      " 9   Salary      174 non-null    object\n",
      "dtypes: int64(1), object(9)\n",
      "memory usage: 13.7+ KB\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "There are some wrong data types. The postcode columns is float, it's a categorical value. And the following columns :\r\n",
    "\r\n",
    "* Start_date column needs to be set as datetime.\r\n",
    "* Salary to be set as float."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Change data type of Start_date to datetime and salary to float, since salary has \"&\" and \",\" we need to remove it."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "# rectify data types \r\n",
    "\r\n",
    "df['Start_date'] = pd.to_datetime(df['Start_date'])\r\n",
    "df['Salary'] = df['Salary'].str.replace('$','')\r\n",
    "df['Salary'] = df['Salary'].str.replace(',','')\r\n",
    "df['Salary'] = df['Salary'].astype(float)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Check for Null and Duplicate values."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "# check duplicates\r\n",
    "df.duplicated().all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "No duplicated values present in the dataset."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "# check null values \r\n",
    "df.isna().all()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Surname       False\n",
       "Name          False\n",
       "Age           False\n",
       "Gender        False\n",
       "Country       False\n",
       "Ethnicity     False\n",
       "Start_date    False\n",
       "Department    False\n",
       "Position      False\n",
       "Salary        False\n",
       "dtype: bool"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "No Null values present in the dataset."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Check all the columns."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "df.columns"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Index(['Surname', 'Name', 'Age', 'Gender', 'Country', 'Ethnicity',\n",
       "       'Start_date', 'Department', 'Position', 'Salary'],\n",
       "      dtype='object')"
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Remove unnecessary columns."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "df.drop(['Surname', 'Name','Country','Department'], axis = 1, inplace=True) "
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Save the clean dataset."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "df.to_csv('data/evidence_clean.csv', index=False)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "> Next notebook involves satastical methods to calculate wage gap. In notebook title : NB2_Conclusion."
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python",
   "version": "3.9.6",
   "mimetype": "text/x-python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "pygments_lexer": "ipython3",
   "nbconvert_exporter": "python",
   "file_extension": ".py"
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "interpreter": {
   "hash": "f9c37c0c0ec68cab32f2d3a83771b1acca5832c232a19ed61fc7a5f670446100"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}