M2DataWrangling-lab.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-SkillsNetworkCoursesIBMDA0321ENSkillsNetwork928-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": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the dataset into a dataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DA0321EN-SkillsNetwork/LargeData/m1_survey_data.csv\")"
   ]
  },
  {
   "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": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "154\n"
     ]
    }
   ],
   "source": [
    "# your code goes here\n",
    "print(df.duplicated().sum())"
   ]
  },
  {
   "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(inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify if duplicates were actually dropped.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# your code goes here\n",
    "print(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/plain": [
       "Index(['OpenSource', 'Student', 'EdLevel', 'UndergradMajor', 'EduOther',\n",
       "       'OrgSize', 'DevType', 'YearsCode', 'Age1stCode', 'YearsCodePro',\n",
       "       'JobSat', 'MgrIdiot', 'MgrMoney', 'MgrWant', 'LastInt', 'FizzBuzz',\n",
       "       'JobFactors', 'ResumeUpdate', 'CompTotal', 'CompFreq', 'ConvertedComp',\n",
       "       'WorkWeekHrs', 'WorkPlan', 'WorkChallenge', 'WorkRemote', 'WorkLoc',\n",
       "       'ImpSyn', 'CodeRev', 'CodeRevHrs', 'UnitTests', 'PurchaseHow',\n",
       "       'PurchaseWhat', 'LanguageWorkedWith', 'LanguageDesireNextYear',\n",
       "       'DatabaseWorkedWith', 'DatabaseDesireNextYear', 'PlatformWorkedWith',\n",
       "       'PlatformDesireNextYear', 'WebFrameWorkedWith',\n",
       "       'WebFrameDesireNextYear', 'MiscTechWorkedWith',\n",
       "       'MiscTechDesireNextYear', 'DevEnviron', 'OpSys', 'Containers',\n",
       "       'BlockchainOrg', 'BlockchainIs', 'BetterLife', 'ITperson', 'OffOn',\n",
       "       'SocialMedia', 'Extraversion', 'ScreenName', 'SOVisit1st',\n",
       "       'SOVisitFreq', 'SOVisitTo', 'SOFindAnswer', 'SOTimeSaved',\n",
       "       'SOHowMuchTime', 'SOAccount', 'SOPartFreq', 'SOJobs', 'EntTeams',\n",
       "       'WelcomeChange', 'SONewContent', 'Age', 'Gender', 'Trans', 'Sexuality',\n",
       "       'Ethnicity', 'Dependents', 'SurveyLength', 'SurveyEase'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df.columns[df.isnull().any()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find out how many rows are missing in the column 'WorkLoc'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df['Country'].isna().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": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Computer science, computer engineering, or software engineering          6953\n",
       "Information systems, information technology, or system administration     794\n",
       "Another engineering discipline (ex. civil, electrical, mechanical)        759\n",
       "Web development or web design                                             410\n",
       "A natural science (ex. biology, chemistry, physics)                       403\n",
       "Mathematics or statistics                                                 372\n",
       "A business discipline (ex. accounting, finance, marketing)                244\n",
       "A social science (ex. anthropology, psychology, political science)        210\n",
       "A humanities discipline (ex. literature, history, philosophy)             207\n",
       "Fine arts or performing arts (ex. graphic design, music, studio art)      161\n",
       "I never declared a major                                                  124\n",
       "A health science (ex. nursing, pharmacy, radiology)                        24\n",
       "Name: UndergradMajor, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df['UndergradMajor'].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": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Office\n"
     ]
    }
   ],
   "source": [
    "#make a note of the majority value here, for future reference\n",
    "majority = df['WorkLoc'].value_counts().idxmax()\n",
    "print(majority)"
   ]
  },
  {
   "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": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code goes here\n",
    "df['WorkLoc'].fillna('Office', inplace=True)"
   ]
  },
  {
   "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": [
    "Verify if imputing was successful.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df['WorkLoc'].isna().sum()"
   ]
  },
  {
   "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": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Yearly', 'Monthly', 'Weekly'], dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "df.CompFreq.value_counts().index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Yearly     6073\n",
       "Monthly    4788\n",
       "Weekly      331\n",
       "Name: CompFreq, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['CompFreq'].value_counts()"
   ]
  },
  {
   "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": 16,
   "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>NormalizedAnnualCompensation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>138000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>348000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11547</th>\n",
       "      <td>130000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11548</th>\n",
       "      <td>74400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11549</th>\n",
       "      <td>105000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11550</th>\n",
       "      <td>80000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11551</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11398 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       NormalizedAnnualCompensation\n",
       "0                           61000.0\n",
       "1                          138000.0\n",
       "2                           90000.0\n",
       "3                          348000.0\n",
       "4                           90000.0\n",
       "...                             ...\n",
       "11547                      130000.0\n",
       "11548                       74400.0\n",
       "11549                      105000.0\n",
       "11550                       80000.0\n",
       "11551                           NaN\n",
       "\n",
       "[11398 rows x 1 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# your code goes here\n",
    "anncomp=[]\n",
    "def NAC():\n",
    "    for x,y in zip(df['CompFreq'], df['CompTotal']):\n",
    "        if x=='Monthly':\n",
    "            anncomp.append(y*12)\n",
    "        elif x=='Weekly':\n",
    "            anncomp.append(y*52)\n",
    "        else:\n",
    "            anncomp.append(y)\n",
    "NAC()\n",
    "          \n",
    "df['NormalizedAnnualCompensation']=anncomp\n",
    "df[['NormalizedAnnualCompensation']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100000.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['NormalizedAnnualCompensation'].median()"
   ]
  },
  {
   "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-SkillsNetworkCoursesIBMDA0321ENSkillsNetwork928-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
}