Private-projects /
IBM-Data-Analyst-Capstone-Project /
2 Data wrangling /
Lab 9 Impute Missing Values.ipynb
Lab 9 Impute Missing Values.ipynb
Impute Missing Values
Estimated time needed: 30 minutes
In this lab, you will practice essential data wrangling techniques using the Stack Overflow survey dataset. The primary focus is on handling missing data and ensuring data quality. You will:
-
Load the Data: Import the dataset into a DataFrame using the pandas library.
-
Clean the Data: Identify and remove duplicate entries to maintain data integrity.
-
Handle Missing Values: Detect missing values, impute them with appropriate strategies, and verify the imputation to create a complete and reliable dataset for analysis.
This lab equips you with the skills to effectively preprocess and clean real-world datasets, a crucial step in any data analysis project.
Objectives
In this lab, you will perform the following:
-
Identify missing values in the dataset.
-
Apply techniques to impute missing values in the dataset.
-
Use suitable techniques to normalize data in the dataset.
Install needed library
!pip install pandas
Collecting pandas
Downloading pandas-3.0.0-cp312-cp312-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl.metadata (79 kB)
Collecting numpy>=1.26.0 (from pandas)
Downloading numpy-2.4.1-cp312-cp312-manylinux_2_27_x86_64.manylinux_2_28_x86_64.whl.metadata (6.6 kB)
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)
Downloading pandas-3.0.0-cp312-cp312-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl (10.9 MB)
[2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m10.9/10.9 MB[0m [31m165.1 MB/s[0m eta [36m0:00:00[0m
[?25hDownloading numpy-2.4.1-cp312-cp312-manylinux_2_27_x86_64.manylinux_2_28_x86_64.whl (16.4 MB)
[2K [90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[0m [32m16.4/16.4 MB[0m [31m170.2 MB/s[0m eta [36m0:00:00[0m
Installing collected packages: numpy, pandas
Successfully installed numpy-2.4.1 pandas-3.0.0
Step 1: Import Required Libraries
import pandas as pd
Step 2: Load the Dataset Into a Dataframe
Read Data
The functions below will download the dataset into your browser:
file_path = "https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/n01PQ9pSmiRX6520flujwQ/survey-data.csv"
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)
Step 3. Finding and Removing Duplicates
Task 1: Identify duplicate rows in the dataset.
## Write your code here
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
Task 2: Remove the duplicate rows from the dataframe.
## 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])
df_d = df.drop_duplicates(subset=subset_cols)
print('Final dataframe:', df_d.shape)
Number of duplicate rows: 487
Final dataframe: (64950, 114)
Step 4: Finding Missing Values
Task 3: Find the missing values for all columns.
## Write your code here
frnan = df_d.isna().mean().sort_values(ascending=False)
print('Fraction of missing values in each column')
print(frnan.round(decimals=4).head())
print('RemoteWork:', frnan.RemoteWork.round(decimals=4))
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
dtype: float64
RemoteWork: 0.1624
Task 4: Find out how many rows are missing in the column RemoteWork.
## Write your code here
print(df_d.RemoteWork.isna().sum())
10546
Step 5. Imputing Missing Values
Task 5: Find the value counts for the column RemoteWork.
## Write your code here
print(df_d.RemoteWork.value_counts(dropna=False))
RemoteWork
Hybrid (some remote, some in-person) 22850
Remote 20732
In-person 10822
NaN 10546
Name: count, dtype: int64
Task 6: Identify the most frequent (majority) value in the RemoteWork column.
## Write your code here
print(df_d.RemoteWork.describe().top)
Hybrid (some remote, some in-person)
Task 7: Impute (replace) all the empty rows in the column RemoteWork with the majority value.
## Write your code here
df_dm = df_d.copy()
common_RemoteWork = df_dm['RemoteWork'].value_counts().idxmax()
df_dm['RemoteWork'] = df_dm['RemoteWork'].fillna(common_RemoteWork)
print(df_dm['RemoteWork'].describe())
count 64950
unique 3
top Hybrid (some remote, some in-person)
freq 33396
Name: RemoteWork, dtype: object
Task 8: Check for any compensation-related columns and describe their distribution.
## Write your code here
print(df_dm[['CompTotal', 'ConvertedCompYearly']].info())
print(df_dm[['CompTotal', 'ConvertedCompYearly']].describe())
<class 'pandas.DataFrame'>
Index: 64950 entries, 0 to 65436
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 CompTotal 33740 non-null float64
1 ConvertedCompYearly 23435 non-null float64
dtypes: float64(2)
memory usage: 1.5 MB
None
CompTotal ConvertedCompYearly
count 3.374000e+04 2.343500e+04
mean 2.963841e+145 8.615529e+04
std 5.444117e+147 1.867570e+05
min 0.000000e+00 1.000000e+00
25% 6.000000e+04 3.271200e+04
50% 1.100000e+05 6.500000e+04
75% 2.500000e+05 1.079715e+05
max 1.000000e+150 1.625660e+07
Summary
In this lab, you focused on imputing missing values in the dataset.
-
Use the
pandas.read_csv()function to load a dataset from a CSV file into a DataFrame. -
Download the dataset if it's not available online and specify the correct file path.