M2DataWrangling-lab (2).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align:center\">\n",
    "    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDA0321ENSkillsNetwork21426264-2022-01-01\" target=\"_blank\">\n",
    "    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n",
    "    </a>\n",
    "</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Data Wrangling Lab**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estimated time needed: **45 to 60** minutes\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this assignment you will be performing data wrangling.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objectives\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this lab you will perform the following:\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*   Identify duplicate values in the dataset.\n",
    "\n",
    "*   Remove duplicate values from the dataset.\n",
    "\n",
    "*   Identify missing values in the dataset.\n",
    "\n",
    "*   Impute the missing values in the dataset.\n",
    "\n",
    "*   Normalize data in the dataset.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hands on Lab\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import pandas module.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the dataset into a dataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Respondent</th>\n",
       "      <th>MainBranch</th>\n",
       "      <th>Hobbyist</th>\n",
       "      <th>OpenSourcer</th>\n",
       "      <th>OpenSource</th>\n",
       "      <th>Employment</th>\n",
       "      <th>Country</th>\n",
       "      <th>Student</th>\n",
       "      <th>EdLevel</th>\n",
       "      <th>UndergradMajor</th>\n",
       "      <th>...</th>\n",
       "      <th>WelcomeChange</th>\n",
       "      <th>SONewContent</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Trans</th>\n",
       "      <th>Sexuality</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>Dependents</th>\n",
       "      <th>SurveyLength</th>\n",
       "      <th>SurveyEase</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>No</td>\n",
       "      <td>Never</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>United States</td>\n",
       "      <td>No</td>\n",
       "      <td>Bachelor’s degree (BA, BS, B.Eng., etc.)</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Just as welcome now as I felt last year</td>\n",
       "      <td>Tech articles written by other developers;Indu...</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Easy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Once a month or more often</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>No</td>\n",
       "      <td>Some college/university study without earning ...</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Just as welcome now as I felt last year</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Bisexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Neither easy nor difficult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Less than once a month but more than once per ...</td>\n",
       "      <td>OSS is, on average, of HIGHER quality than pro...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>United States</td>\n",
       "      <td>No</td>\n",
       "      <td>Master’s degree (MA, MS, M.Eng., MBA, etc.)</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Somewhat more welcome now than last year</td>\n",
       "      <td>Tech articles written by other developers;Cour...</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Easy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Never</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>No</td>\n",
       "      <td>Master’s degree (MA, MS, M.Eng., MBA, etc.)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Just as welcome now as I felt last year</td>\n",
       "      <td>Tech articles written by other developers;Indu...</td>\n",
       "      <td>26.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Neither easy nor difficult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Less than once a month but more than once per ...</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>Australia</td>\n",
       "      <td>No</td>\n",
       "      <td>Bachelor’s degree (BA, BS, B.Eng., etc.)</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Just as welcome now as I felt last year</td>\n",
       "      <td>Tech articles written by other developers;Indu...</td>\n",
       "      <td>29.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>Hispanic or Latino/Latina;Multiracial</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Easy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 85 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Respondent                      MainBranch Hobbyist  \\\n",
       "0           4  I am a developer by profession       No   \n",
       "1           9  I am a developer by profession      Yes   \n",
       "2          13  I am a developer by profession      Yes   \n",
       "3          16  I am a developer by profession      Yes   \n",
       "4          17  I am a developer by profession      Yes   \n",
       "\n",
       "                                         OpenSourcer  \\\n",
       "0                                              Never   \n",
       "1                         Once a month or more often   \n",
       "2  Less than once a month but more than once per ...   \n",
       "3                                              Never   \n",
       "4  Less than once a month but more than once per ...   \n",
       "\n",
       "                                          OpenSource          Employment  \\\n",
       "0  The quality of OSS and closed source software ...  Employed full-time   \n",
       "1  The quality of OSS and closed source software ...  Employed full-time   \n",
       "2  OSS is, on average, of HIGHER quality than pro...  Employed full-time   \n",
       "3  The quality of OSS and closed source software ...  Employed full-time   \n",
       "4  The quality of OSS and closed source software ...  Employed full-time   \n",
       "\n",
       "          Country Student                                            EdLevel  \\\n",
       "0   United States      No           Bachelor’s degree (BA, BS, B.Eng., etc.)   \n",
       "1     New Zealand      No  Some college/university study without earning ...   \n",
       "2   United States      No        Master’s degree (MA, MS, M.Eng., MBA, etc.)   \n",
       "3  United Kingdom      No        Master’s degree (MA, MS, M.Eng., MBA, etc.)   \n",
       "4       Australia      No           Bachelor’s degree (BA, BS, B.Eng., etc.)   \n",
       "\n",
       "                                      UndergradMajor  ...  \\\n",
       "0  Computer science, computer engineering, or sof...  ...   \n",
       "1  Computer science, computer engineering, or sof...  ...   \n",
       "2  Computer science, computer engineering, or sof...  ...   \n",
       "3                                                NaN  ...   \n",
       "4  Computer science, computer engineering, or sof...  ...   \n",
       "\n",
       "                              WelcomeChange  \\\n",
       "0   Just as welcome now as I felt last year   \n",
       "1   Just as welcome now as I felt last year   \n",
       "2  Somewhat more welcome now than last year   \n",
       "3   Just as welcome now as I felt last year   \n",
       "4   Just as welcome now as I felt last year   \n",
       "\n",
       "                                        SONewContent   Age Gender Trans  \\\n",
       "0  Tech articles written by other developers;Indu...  22.0    Man    No   \n",
       "1                                                NaN  23.0    Man    No   \n",
       "2  Tech articles written by other developers;Cour...  28.0    Man    No   \n",
       "3  Tech articles written by other developers;Indu...  26.0    Man    No   \n",
       "4  Tech articles written by other developers;Indu...  29.0    Man    No   \n",
       "\n",
       "                 Sexuality                              Ethnicity Dependents  \\\n",
       "0  Straight / Heterosexual           White or of European descent         No   \n",
       "1                 Bisexual           White or of European descent         No   \n",
       "2  Straight / Heterosexual           White or of European descent        Yes   \n",
       "3  Straight / Heterosexual           White or of European descent         No   \n",
       "4  Straight / Heterosexual  Hispanic or Latino/Latina;Multiracial         No   \n",
       "\n",
       "            SurveyLength                  SurveyEase  \n",
       "0  Appropriate in length                        Easy  \n",
       "1  Appropriate in length  Neither easy nor difficult  \n",
       "2  Appropriate in length                        Easy  \n",
       "3  Appropriate in length  Neither easy nor difficult  \n",
       "4  Appropriate in length                        Easy  \n",
       "\n",
       "[5 rows x 85 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DA0321EN-SkillsNetwork/LargeData/m1_survey_data.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding duplicates\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section you will identify duplicate values in the dataset.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find how many duplicate rows exist in the dataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "154"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.duplicated().sum()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing duplicates\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove the duplicate rows from the dataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n",
    "df.drop_duplicates(subset=None,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify if duplicates were actually dropped.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding Missing values\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the missing values for all columns.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Respondent</th>\n",
       "      <th>MainBranch</th>\n",
       "      <th>Hobbyist</th>\n",
       "      <th>OpenSourcer</th>\n",
       "      <th>OpenSource</th>\n",
       "      <th>Employment</th>\n",
       "      <th>Country</th>\n",
       "      <th>Student</th>\n",
       "      <th>EdLevel</th>\n",
       "      <th>UndergradMajor</th>\n",
       "      <th>...</th>\n",
       "      <th>WelcomeChange</th>\n",
       "      <th>SONewContent</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Trans</th>\n",
       "      <th>Sexuality</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>Dependents</th>\n",
       "      <th>SurveyLength</th>\n",
       "      <th>SurveyEase</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11547</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11548</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11549</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11550</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11551</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11398 rows × 85 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Respondent  MainBranch  Hobbyist  OpenSourcer  OpenSource  Employment  \\\n",
       "0           False       False     False        False       False       False   \n",
       "1           False       False     False        False       False       False   \n",
       "2           False       False     False        False       False       False   \n",
       "3           False       False     False        False       False       False   \n",
       "4           False       False     False        False       False       False   \n",
       "...           ...         ...       ...          ...         ...         ...   \n",
       "11547       False       False     False        False       False       False   \n",
       "11548       False       False     False        False       False       False   \n",
       "11549       False       False     False        False       False       False   \n",
       "11550       False       False     False        False       False       False   \n",
       "11551       False       False     False        False       False       False   \n",
       "\n",
       "       Country  Student  EdLevel  UndergradMajor  ...  WelcomeChange  \\\n",
       "0        False    False    False           False  ...          False   \n",
       "1        False    False    False           False  ...          False   \n",
       "2        False    False    False           False  ...          False   \n",
       "3        False    False    False            True  ...          False   \n",
       "4        False    False    False           False  ...          False   \n",
       "...        ...      ...      ...             ...  ...            ...   \n",
       "11547    False    False    False           False  ...          False   \n",
       "11548    False    False    False           False  ...          False   \n",
       "11549    False    False    False           False  ...          False   \n",
       "11550    False    False    False            True  ...          False   \n",
       "11551    False    False    False           False  ...          False   \n",
       "\n",
       "       SONewContent    Age  Gender  Trans  Sexuality  Ethnicity  Dependents  \\\n",
       "0             False  False   False  False      False      False       False   \n",
       "1              True  False   False  False      False      False       False   \n",
       "2             False  False   False  False      False      False       False   \n",
       "3             False  False   False  False      False      False       False   \n",
       "4             False  False   False  False      False      False       False   \n",
       "...             ...    ...     ...    ...        ...        ...         ...   \n",
       "11547         False  False   False  False      False      False       False   \n",
       "11548         False  False   False  False      False      False       False   \n",
       "11549         False  False   False  False      False      False       False   \n",
       "11550          True  False   False  False      False      False       False   \n",
       "11551         False  False   False  False      False      False       False   \n",
       "\n",
       "       SurveyLength  SurveyEase  \n",
       "0             False       False  \n",
       "1             False       False  \n",
       "2             False       False  \n",
       "3             False       False  \n",
       "4             False       False  \n",
       "...             ...         ...  \n",
       "11547         False       False  \n",
       "11548         False       False  \n",
       "11549         False       False  \n",
       "11550         False       False  \n",
       "11551         False       False  \n",
       "\n",
       "[11398 rows x 85 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df.isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find out how many rows are missing in the column 'WorkLoc'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df[ 'WorkLoc'].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imputing missing values\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the  value counts for the column WorkLoc.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Office                                            6806\n",
       "Home                                              3589\n",
       "Other place, such as a coworking space or cafe     971\n",
       "Name: WorkLoc, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df[ 'WorkLoc'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Identify the value that is most frequent (majority) in the WorkLoc column.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#make a note of the majority value here, for future reference\n",
    "#office has value that is most frequent majority\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After imputation there should ideally not be any empty rows in the WorkLoc column.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Impute (replace) all the empty rows in the column WorkLoc with the value that you have identified as majority.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['WorkLoc'].dropna(inplace=True)\n",
    "df['WorkLoc'].replace('',df['WorkLoc'][1], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify if imputing was successful.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Office                                            6806\n",
      "Home                                              3589\n",
      "Other place, such as a coworking space or cafe     971\n",
      "Name: WorkLoc, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# your code goes here\n",
    "print(df[\"WorkLoc\"].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Normalizing data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are two columns in the dataset that talk about compensation.\n",
    "\n",
    "One is \"CompFreq\". This column shows how often a developer is paid (Yearly, Monthly, Weekly).\n",
    "\n",
    "The other is \"CompTotal\". This column talks about how much the developer is paid per Year, Month, or Week depending upon his/her \"CompFreq\".\n",
    "\n",
    "This makes it difficult to compare the total compensation of the developers.\n",
    "\n",
    "In this section you will create a new column called 'NormalizedAnnualCompensation' which contains the 'Annual Compensation' irrespective of the 'CompFreq'.\n",
    "\n",
    "Once this column is ready, it makes comparison of salaries easy.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List out the various categories in the column 'CompFreq'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         Yearly\n",
       "1         Yearly\n",
       "2         Yearly\n",
       "3        Monthly\n",
       "4         Yearly\n",
       "          ...   \n",
       "11547     Yearly\n",
       "11548     Yearly\n",
       "11549     Yearly\n",
       "11550     Yearly\n",
       "11551        NaN\n",
       "Name: CompFreq, Length: 11398, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df['CompFreq']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new column named 'NormalizedAnnualCompensation'. Use the hint given below if needed.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Double click to see the **Hint**.\n",
    "\n",
    "<!--\n",
    "\n",
    "Use the below logic to arrive at the values for the column NormalizedAnnualCompensation.\n",
    "\n",
    "If the CompFreq is Yearly then use the exising value in CompTotal\n",
    "If the CompFreq is Monthly then multiply the value in CompTotal with 12 (months in an year)\n",
    "If the CompFreq is Weekly then multiply the value in CompTotal with 52 (weeks in an year)\n",
    "\n",
    "-->\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Respondent</th>\n",
       "      <th>MainBranch</th>\n",
       "      <th>Hobbyist</th>\n",
       "      <th>OpenSourcer</th>\n",
       "      <th>OpenSource</th>\n",
       "      <th>Employment</th>\n",
       "      <th>Country</th>\n",
       "      <th>Student</th>\n",
       "      <th>EdLevel</th>\n",
       "      <th>UndergradMajor</th>\n",
       "      <th>...</th>\n",
       "      <th>SONewContent</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Trans</th>\n",
       "      <th>Sexuality</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>Dependents</th>\n",
       "      <th>SurveyLength</th>\n",
       "      <th>SurveyEase</th>\n",
       "      <th>NormalizedAnnualCompensation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>No</td>\n",
       "      <td>Never</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>United States</td>\n",
       "      <td>No</td>\n",
       "      <td>Bachelor’s degree (BA, BS, B.Eng., etc.)</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Tech articles written by other developers;Indu...</td>\n",
       "      <td>22.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Easy</td>\n",
       "      <td>61000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Once a month or more often</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>No</td>\n",
       "      <td>Some college/university study without earning ...</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Bisexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Neither easy nor difficult</td>\n",
       "      <td>138000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Less than once a month but more than once per ...</td>\n",
       "      <td>OSS is, on average, of HIGHER quality than pro...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>United States</td>\n",
       "      <td>No</td>\n",
       "      <td>Master’s degree (MA, MS, M.Eng., MBA, etc.)</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Tech articles written by other developers;Cour...</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Easy</td>\n",
       "      <td>90000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Never</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>No</td>\n",
       "      <td>Master’s degree (MA, MS, M.Eng., MBA, etc.)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Tech articles written by other developers;Indu...</td>\n",
       "      <td>26.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>White or of European descent</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Neither easy nor difficult</td>\n",
       "      <td>29000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>I am a developer by profession</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Less than once a month but more than once per ...</td>\n",
       "      <td>The quality of OSS and closed source software ...</td>\n",
       "      <td>Employed full-time</td>\n",
       "      <td>Australia</td>\n",
       "      <td>No</td>\n",
       "      <td>Bachelor’s degree (BA, BS, B.Eng., etc.)</td>\n",
       "      <td>Computer science, computer engineering, or sof...</td>\n",
       "      <td>...</td>\n",
       "      <td>Tech articles written by other developers;Indu...</td>\n",
       "      <td>29.0</td>\n",
       "      <td>Man</td>\n",
       "      <td>No</td>\n",
       "      <td>Straight / Heterosexual</td>\n",
       "      <td>Hispanic or Latino/Latina;Multiracial</td>\n",
       "      <td>No</td>\n",
       "      <td>Appropriate in length</td>\n",
       "      <td>Easy</td>\n",
       "      <td>90000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 86 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Respondent                      MainBranch Hobbyist  \\\n",
       "0           4  I am a developer by profession       No   \n",
       "1           9  I am a developer by profession      Yes   \n",
       "2          13  I am a developer by profession      Yes   \n",
       "3          16  I am a developer by profession      Yes   \n",
       "4          17  I am a developer by profession      Yes   \n",
       "\n",
       "                                         OpenSourcer  \\\n",
       "0                                              Never   \n",
       "1                         Once a month or more often   \n",
       "2  Less than once a month but more than once per ...   \n",
       "3                                              Never   \n",
       "4  Less than once a month but more than once per ...   \n",
       "\n",
       "                                          OpenSource          Employment  \\\n",
       "0  The quality of OSS and closed source software ...  Employed full-time   \n",
       "1  The quality of OSS and closed source software ...  Employed full-time   \n",
       "2  OSS is, on average, of HIGHER quality than pro...  Employed full-time   \n",
       "3  The quality of OSS and closed source software ...  Employed full-time   \n",
       "4  The quality of OSS and closed source software ...  Employed full-time   \n",
       "\n",
       "          Country Student                                            EdLevel  \\\n",
       "0   United States      No           Bachelor’s degree (BA, BS, B.Eng., etc.)   \n",
       "1     New Zealand      No  Some college/university study without earning ...   \n",
       "2   United States      No        Master’s degree (MA, MS, M.Eng., MBA, etc.)   \n",
       "3  United Kingdom      No        Master’s degree (MA, MS, M.Eng., MBA, etc.)   \n",
       "4       Australia      No           Bachelor’s degree (BA, BS, B.Eng., etc.)   \n",
       "\n",
       "                                      UndergradMajor  ...  \\\n",
       "0  Computer science, computer engineering, or sof...  ...   \n",
       "1  Computer science, computer engineering, or sof...  ...   \n",
       "2  Computer science, computer engineering, or sof...  ...   \n",
       "3                                                NaN  ...   \n",
       "4  Computer science, computer engineering, or sof...  ...   \n",
       "\n",
       "                                        SONewContent   Age Gender Trans  \\\n",
       "0  Tech articles written by other developers;Indu...  22.0    Man    No   \n",
       "1                                                NaN  23.0    Man    No   \n",
       "2  Tech articles written by other developers;Cour...  28.0    Man    No   \n",
       "3  Tech articles written by other developers;Indu...  26.0    Man    No   \n",
       "4  Tech articles written by other developers;Indu...  29.0    Man    No   \n",
       "\n",
       "                 Sexuality                              Ethnicity Dependents  \\\n",
       "0  Straight / Heterosexual           White or of European descent         No   \n",
       "1                 Bisexual           White or of European descent         No   \n",
       "2  Straight / Heterosexual           White or of European descent        Yes   \n",
       "3  Straight / Heterosexual           White or of European descent         No   \n",
       "4  Straight / Heterosexual  Hispanic or Latino/Latina;Multiracial         No   \n",
       "\n",
       "            SurveyLength                  SurveyEase  \\\n",
       "0  Appropriate in length                        Easy   \n",
       "1  Appropriate in length  Neither easy nor difficult   \n",
       "2  Appropriate in length                        Easy   \n",
       "3  Appropriate in length  Neither easy nor difficult   \n",
       "4  Appropriate in length                        Easy   \n",
       "\n",
       "  NormalizedAnnualCompensation  \n",
       "0                      61000.0  \n",
       "1                     138000.0  \n",
       "2                      90000.0  \n",
       "3                      29000.0  \n",
       "4                      90000.0  \n",
       "\n",
       "[5 rows x 86 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "import numpy as np\n",
    "\n",
    "df['NormalizedAnnualCompensation']=np.where(\n",
    "    df['CompFreq'] == 'Weekly',df['CompTotal']*52,np.where(\n",
    "    df['CompFreq'] == 'Montly',df['CompTotal']*12,df['CompTotal']))\n",
    "df.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Authors\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ramesh Sannareddy\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Other Contributors\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rav Ahuja\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change Log\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Date (YYYY-MM-DD) | Version | Changed By        | Change Description                 |\n",
    "| ----------------- | ------- | ----------------- | ---------------------------------- |\n",
    "| 2020-10-17        | 0.1     | Ramesh Sannareddy | Created initial version of the lab |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright © 2020 IBM Corporation. This notebook and its source code are released under the terms of the [MIT License](https://cognitiveclass.ai/mit-license?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDA0321ENSkillsNetwork21426264-2022-01-01&cm_mmc=Email_Newsletter-\\_-Developer_Ed%2BTech-\\_-WW_WW-\\_-SkillsNetwork-Courses-IBM-DA0321EN-SkillsNetwork-21426264&cm_mmca1=000026UJ&cm_mmca2=10006555&cm_mmca3=M12345678&cvosrc=email.Newsletter.M12345678&cvo_campaign=000026UJ).\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "language": "python",
   "name": "conda-env-python-py"
  },
  "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}