{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "

\n", " \n", " \"Skills\n", " \n", "

\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# **Removing Duplicates**\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Estimated time needed: **30** minutes\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Introduction\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In this lab, you will focus on data wrangling, an important step in preparing data for analysis. Data wrangling involves cleaning and organizing data to make it suitable for analysis. One key task in this process is removing duplicate entries, which are repeated entries that can distort analysis and lead to inaccurate conclusions. \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": [ "1. Identify duplicate rows in the dataset.\n", "2. Use suitable techniques to remove duplicate rows and verify the removal.\n", "3. Summarize how to handle missing values appropriately.\n", "4. Use ConvertedCompYearly to normalize compensation data.\n", " \n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Install the Required Libraries\n" ] }, { "cell_type": "code", "execution_count": 114, "metadata": { "collapsed": true, "jupyter": { "outputs_hidden": true } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: pandas in /opt/conda/lib/python3.12/site-packages (3.0.0)\n", "Requirement already satisfied: numpy>=1.26.0 in /opt/conda/lib/python3.12/site-packages (from pandas) (2.4.1)\n", "Requirement already satisfied: python-dateutil>=2.8.2 in /opt/conda/lib/python3.12/site-packages (from pandas) (2.9.0.post0)\n", "Requirement already satisfied: six>=1.5 in /opt/conda/lib/python3.12/site-packages (from python-dateutil>=2.8.2->pandas) (1.17.0)\n" ] } ], "source": [ "!pip install pandas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 1: Import Required Libraries\n" ] }, { "cell_type": "code", "execution_count": 115, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 2: Load the Dataset into a DataFrame\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "load the dataset using pd.read_csv()\n" ] }, { "cell_type": "code", "execution_count": 116, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " ResponseId MainBranch Age \\\n", "0 1 I am a developer by profession Under 18 years old \n", "1 2 I am a developer by profession 35-44 years old \n", "2 3 I am a developer by profession 45-54 years old \n", "3 4 I am learning to code 18-24 years old \n", "4 5 I am a developer by profession 18-24 years old \n", "\n", " Employment RemoteWork Check \\\n", "0 Employed, full-time Remote Apples \n", "1 Employed, full-time Remote Apples \n", "2 Employed, full-time Remote Apples \n", "3 Student, full-time NaN Apples \n", "4 Student, full-time NaN Apples \n", "\n", " CodingActivities \\\n", "0 Hobby \n", "1 Hobby;Contribute to open-source projects;Other... \n", "2 Hobby;Contribute to open-source projects;Other... \n", "3 NaN \n", "4 NaN \n", "\n", " EdLevel \\\n", "0 Primary/elementary school \n", "1 Bachelor’s degree (B.A., B.S., B.Eng., etc.) \n", "2 Master’s degree (M.A., M.S., M.Eng., MBA, etc.) \n", "3 Some college/university study without earning ... \n", "4 Secondary school (e.g. American high school, G... \n", "\n", " LearnCode \\\n", "0 Books / Physical media \n", "1 Books / Physical media;Colleague;On the job tr... \n", "2 Books / Physical media;Colleague;On the job tr... \n", "3 Other online resources (e.g., videos, blogs, f... \n", "4 Other online resources (e.g., videos, blogs, f... \n", "\n", " LearnCodeOnline ... JobSatPoints_6 \\\n", "0 NaN ... NaN \n", "1 Technical documentation;Blogs;Books;Written Tu... ... 0.0 \n", "2 Technical documentation;Blogs;Books;Written Tu... ... NaN \n", "3 Stack Overflow;How-to videos;Interactive tutorial ... NaN \n", "4 Technical documentation;Blogs;Written Tutorial... ... NaN \n", "\n", " JobSatPoints_7 JobSatPoints_8 JobSatPoints_9 JobSatPoints_10 \\\n", "0 NaN NaN NaN NaN \n", "1 0.0 0.0 0.0 0.0 \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " JobSatPoints_11 SurveyLength SurveyEase ConvertedCompYearly JobSat \n", "0 NaN NaN NaN NaN NaN \n", "1 0.0 NaN NaN NaN NaN \n", "2 NaN Appropriate in length Easy NaN NaN \n", "3 NaN Too long Easy NaN NaN \n", "4 NaN Too short Easy NaN NaN \n", "\n", "[5 rows x 114 columns]\n", "Initial dataframe: (65437, 114)\n" ] } ], "source": [ "# Define the URL of the dataset\n", "file_path = \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/n01PQ9pSmiRX6520flujwQ/survey-data.csv\"\n", "\n", "# Load the dataset into a DataFrame\n", "df = pd.read_csv(file_path)\n", "\n", "# Display the first few rows to ensure it loaded correctly\n", "print(df.head())\n", "\n", "print('Initial dataframe:', df.shape)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Note: If you are working on a local Jupyter environment, you can use the URL directly in the pandas.read_csv() function as shown below:**\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#df = pd.read_csv(\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/n01PQ9pSmiRX6520flujwQ/survey-data.csv\")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 3: Identifying Duplicate Rows\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Task 1: Identify Duplicate Rows**\n", " 1. Count the number of duplicate rows in the dataset.\n", " 2. Display the first few duplicate rows to understand their structure.\n" ] }, { "cell_type": "code", "execution_count": 117, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Fraction of duplicates in each column\n", "ResponseId: 0.0\n", "LanguageHaveWorkedWith: 0.6353\n", "LanguageWantToWorkWith: 0.652\n", "ToolsTechHaveWorkedWith: 0.6961\n", "ToolsTechWantToWorkWith: 0.7471\n" ] } ], "source": [ "frdupl = []\n", "for col in df.columns:\n", " nunique = df[col].nunique(dropna=False) # number of unique values for this column\n", " frdupl.append((col, round(((df[col].size - nunique) / df[col].size), 4)))\n", "frdupl = sorted(frdupl, key=lambda n:n[1])\n", "print('Fraction of duplicates in each column')\n", "for i in range(5):\n", " print(*frdupl[i], sep=': ')" ] }, { "cell_type": "code", "execution_count": 118, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Number of duplicate rows: 487\n", " ResponseId MainBranch \\\n", "46264 46265 I am a developer by profession \n", "46774 46775 I am a developer by profession \n", "46921 46922 I am a developer by profession \n", "47073 47074 I am not primarily a developer, but I write co... \n", "47340 47341 I code primarily as a hobby \n", "\n", " Age Employment \\\n", "46264 25-34 years old Employed, full-time \n", "46774 18-24 years old Employed, full-time \n", "46921 18-24 years old Employed, full-time \n", "47073 25-34 years old Employed, full-time \n", "47340 Under 18 years old Student, full-time \n", "\n", " RemoteWork Check \\\n", "46264 Remote Apples \n", "46774 Hybrid (some remote, some in-person) Apples \n", "46921 In-person Apples \n", "47073 In-person Apples \n", "47340 NaN Apples \n", "\n", " CodingActivities EdLevel LearnCode \\\n", "46264 NaN NaN NaN \n", "46774 Hobby;Professional development or self-paced l... NaN NaN \n", "46921 NaN NaN NaN \n", "47073 NaN NaN NaN \n", "47340 NaN NaN NaN \n", "\n", " LearnCodeOnline ... JobSatPoints_6 JobSatPoints_7 JobSatPoints_8 \\\n", "46264 NaN ... NaN NaN NaN \n", "46774 NaN ... NaN NaN NaN \n", "46921 NaN ... NaN NaN NaN \n", "47073 NaN ... NaN NaN NaN \n", "47340 NaN ... NaN NaN NaN \n", "\n", " JobSatPoints_9 JobSatPoints_10 JobSatPoints_11 SurveyLength SurveyEase \\\n", "46264 NaN NaN NaN NaN NaN \n", "46774 NaN NaN NaN NaN NaN \n", "46921 NaN NaN NaN NaN NaN \n", "47073 NaN NaN NaN NaN NaN \n", "47340 NaN NaN NaN NaN NaN \n", "\n", " ConvertedCompYearly JobSat \n", "46264 NaN NaN \n", "46774 NaN NaN \n", "46921 NaN NaN \n", "47073 NaN NaN \n", "47340 NaN NaN \n", "\n", "[5 rows x 114 columns]\n" ] } ], "source": [ "## Write your code here\n", "subset_cols = list(df.drop(['ResponseId'], axis=1).columns)\n", "dup_rows = df[df.duplicated(subset=subset_cols)]\n", "print('Number of duplicate rows:', dup_rows.shape[0])\n", "print(dup_rows.head())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 4: Removing Duplicate Rows\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Task 2: Remove Duplicates**\n", " 1. Remove duplicate rows from the dataset using the drop_duplicates() function.\n", "2. Verify the removal by counting the number of duplicate rows after removal .\n" ] }, { "cell_type": "code", "execution_count": 119, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Final dataframe: (64950, 114)\n", "Final number of duplicate rows: 0\n" ] } ], "source": [ "## Write your code here\n", "df_d = df.drop_duplicates(subset=subset_cols)\n", "print('Final dataframe:', df_d.shape)\n", "dup_rows = df_d[df_d.duplicated(subset=subset_cols)]\n", "print('Final number of duplicate rows:', dup_rows.shape[0])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 5: Handling Missing Values\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Task 3: Identify and Handle Missing Values**\n", " 1. Identify missing values for all columns in the dataset.\n", " 2. Choose a column with significant missing values (e.g., EdLevel) and impute with the most frequent value.\n" ] }, { "cell_type": "code", "execution_count": 120, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Fraction of missing values in each column\n" ] }, { "data": { "text/plain": [ "AINextMuch less integrated 0.9823\n", "AINextLess integrated 0.9637\n", "AINextNo change 0.8076\n", "AINextMuch more integrated 0.7931\n", "EmbeddedAdmired 0.7424\n", "EmbeddedWantToWorkWith 0.7290\n", "EmbeddedHaveWorkedWith 0.6580\n", "ConvertedCompYearly 0.6392\n", "AIToolNot interested in Using 0.6241\n", "AINextMore integrated 0.6239\n", "dtype: float64" ] }, "execution_count": 120, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Write your code here\n", "frnan = df_d.isna().mean().sort_values(ascending=False)\n", "print('Fraction of missing values in each column')\n", "frnan.round(decimals=4).head(10)" ] }, { "cell_type": "code", "execution_count": 121, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "count 60784\n", "unique 8\n", "top Bachelor’s degree (B.A., B.S., B.Eng., etc.)\n", "freq 24942\n", "Name: EdLevel, dtype: object" ] }, "execution_count": 121, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_d['EdLevel'].describe()" ] }, { "cell_type": "code", "execution_count": 122, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "count 64950\n", "unique 8\n", "top Bachelor’s degree (B.A., B.S., B.Eng., etc.)\n", "freq 29108\n", "Name: EdLevel, dtype: object" ] }, "execution_count": 122, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_dm = df_d.copy()\n", "common_EdLevel = df_dm['EdLevel'].value_counts().idxmax()\n", "df_dm['EdLevel'] = df_dm['EdLevel'].fillna(common_EdLevel)\n", "df_dm['EdLevel'].describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 6: Normalizing Compensation Data\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Task 4: Normalize Compensation Data Using ConvertedCompYearly**\n", " 1. Use the ConvertedCompYearly column for compensation analysis as the normalized annual compensation is already provided.\n", " 2. Check for missing values in ConvertedCompYearly and handle them if necessary.\n" ] }, { "cell_type": "code", "execution_count": 125, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Index: 64950 entries, 0 to 65436\n", "Series name: ConvertedCompYearly\n", "Non-Null Count Dtype \n", "-------------- ----- \n", "23435 non-null float64\n", "dtypes: float64(1)\n", "memory usage: 1014.8 KB\n", "None\n", "count 23435\n", "mean 86155\n", "std 186756\n", "min 1\n", "25% 32712\n", "50% 65000\n", "75% 107971\n", "max 16256603\n", "Name: ConvertedCompYearly, dtype: int64\n" ] } ], "source": [ "## Write your code here\n", "print(df_dm['ConvertedCompYearly'].info())\n", "print(df_dm['ConvertedCompYearly'].describe().astype(int))" ] }, { "cell_type": "code", "execution_count": 126, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Index: 64950 entries, 0 to 65436\n", "Series name: ConvertedCompYearly\n", "Non-Null Count Dtype \n", "-------------- ----- \n", "64950 non-null float64\n", "dtypes: float64(1)\n", "memory usage: 1014.8 KB\n", "None\n", "count 64950\n", "mean 86155\n", "std 112179\n", "min 1\n", "25% 86155\n", "50% 86155\n", "75% 86155\n", "max 16256603\n", "Name: ConvertedCompYearly, dtype: int64\n" ] } ], "source": [ "mean_CCY = df_dm['ConvertedCompYearly'].mean(axis=0)\n", "df_dm['ConvertedCompYearly'] = df_dm['ConvertedCompYearly'].fillna(mean_CCY)\n", "print(df_dm['ConvertedCompYearly'].info())\n", "print(df_dm['ConvertedCompYearly'].describe().astype(int))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Step 7: Summary and Next Steps\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**In this lab, you focused on identifying and removing duplicate rows.**\n", "\n", "- You handled missing values by imputing the most frequent value in a chosen column.\n", "\n", "- You used ConvertedCompYearly for compensation normalization and handled missing values.\n", "\n", "- For further analysis, consider exploring other columns or visualizing the cleaned dataset.\n" ] }, { "cell_type": "code", "execution_count": 133, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Index: 64950 entries, 0 to 65436\n", "Series name: EdLevel\n", "Non-Null Count Dtype\n", "-------------- -----\n", "64950 non-null str \n", "dtypes: str(1)\n", "memory usage: 1014.8 KB\n", "None\n", "count 64950\n", "unique 8\n", "top Bachelor’s degree (B.A., B.S., B.Eng., etc.)\n", "freq 29108\n", "Name: EdLevel, dtype: object\n", "\n", "\n", "Index: 64950 entries, 0 to 65436\n", "Series name: ConvertedCompYearly\n", "Non-Null Count Dtype \n", "-------------- ----- \n", "64950 non-null float64\n", "dtypes: float64(1)\n", "memory usage: 1014.8 KB\n", "None\n", "count 6.495000e+04\n", "mean 8.615529e+04\n", "std 1.121796e+05\n", "min 1.000000e+00\n", "25% 8.615529e+04\n", "50% 8.615529e+04\n", "75% 8.615529e+04\n", "max 1.625660e+07\n", "Name: ConvertedCompYearly, dtype: float64\n", "\n", " ResponseId MainBranch Age \\\n", "0 1 I am a developer by profession Under 18 years old \n", "1 2 I am a developer by profession 35-44 years old \n", "2 3 I am a developer by profession 45-54 years old \n", "3 4 I am learning to code 18-24 years old \n", "4 5 I am a developer by profession 18-24 years old \n", "\n", " Employment RemoteWork Check \\\n", "0 Employed, full-time Remote Apples \n", "1 Employed, full-time Remote Apples \n", "2 Employed, full-time Remote Apples \n", "3 Student, full-time NaN Apples \n", "4 Student, full-time NaN Apples \n", "\n", " CodingActivities \\\n", "0 Hobby \n", "1 Hobby;Contribute to open-source projects;Other... \n", "2 Hobby;Contribute to open-source projects;Other... \n", "3 NaN \n", "4 NaN \n", "\n", " EdLevel \\\n", "0 Primary/elementary school \n", "1 Bachelor’s degree (B.A., B.S., B.Eng., etc.) \n", "2 Master’s degree (M.A., M.S., M.Eng., MBA, etc.) \n", "3 Some college/university study without earning ... \n", "4 Secondary school (e.g. American high school, G... \n", "\n", " LearnCode \\\n", "0 Books / Physical media \n", "1 Books / Physical media;Colleague;On the job tr... \n", "2 Books / Physical media;Colleague;On the job tr... \n", "3 Other online resources (e.g., videos, blogs, f... \n", "4 Other online resources (e.g., videos, blogs, f... \n", "\n", " LearnCodeOnline ... JobSatPoints_6 \\\n", "0 NaN ... NaN \n", "1 Technical documentation;Blogs;Books;Written Tu... ... 0.0 \n", "2 Technical documentation;Blogs;Books;Written Tu... ... NaN \n", "3 Stack Overflow;How-to videos;Interactive tutorial ... NaN \n", "4 Technical documentation;Blogs;Written Tutorial... ... NaN \n", "\n", " JobSatPoints_7 JobSatPoints_8 JobSatPoints_9 JobSatPoints_10 \\\n", "0 NaN NaN NaN NaN \n", "1 0.0 0.0 0.0 0.0 \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " JobSatPoints_11 SurveyLength SurveyEase ConvertedCompYearly JobSat \n", "0 NaN NaN NaN 86155.287263 NaN \n", "1 0.0 NaN NaN 86155.287263 NaN \n", "2 NaN Appropriate in length Easy 86155.287263 NaN \n", "3 NaN Too long Easy 86155.287263 NaN \n", "4 NaN Too short Easy 86155.287263 NaN \n", "\n", "[5 rows x 114 columns]\n" ] } ], "source": [ "## Write your code here\n", "for col in ['EdLevel', 'ConvertedCompYearly']:\n", " print(df_dm[col].info())\n", " print(df_dm[col].describe())\n", " print()\n", "print(df_dm.head())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Copyright © IBM Corporation. All rights reserved.\n" ] } ], "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.13.5" }, "prev_pub_hash": "2116052544ce403759eef2159eb3d21f1d38e895d652bcaffa36a5791482361d" }, "nbformat": 4, "nbformat_minor": 4 }