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