2.7.3: Hands-On Lab Exercise
- Page ID
- 58289
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\ket}[1]{\left| #1 \right>}\)
\(\newcommand{\bra}[1]{\left< #1 \right|}\)
\(\newcommand{\braket}[2]{\left< #1 \vphantom{#2} \right| \left. #2 \vphantom{#1} \right>}\)
\(\newcommand{\braopket}[3]{\left< #1 \vphantom{#2}\vphantom{#3} \right| #2 \vphantom{#1}\vphantom{#3} \left| #3 \vphantom{#1}\vphantom{#2} \right>}\)
\(\newcommand{\qmvec}[1]{\mathbf{\vec{#1}}}\)
\(\newcommand{\op}[1]{\hat{\mathbf{#1}}}\)
\(\newcommand{\expect}[1]{\langle #1 \rangle}\)
\(\newcommand{\dfn}[1]{\emph{\textbf{#1}}}\)
Lab: Data Quality Assessment and Improvement Plan
Objective: Assess the quality of a provided dataset and develop a remediation plan.
Scenario: You are a data analyst at UniTrack, a university enrollment management office. You've received a dataset of 5,000 student records that will be used to analyze enrollment trends and predict student retention. Upon initial inspection, you notice several quality issues.
The dataset contains these fields: Student_ID, First_Name, Last_Name, Email, Date_of_Birth, Gender, Major, GPA, Enrollment_Date, Credits_Completed, Graduation_Status, Phone_Number.
Known issues in the dataset:
- 200 records have NULL values in GPA (some are freshmen with no grades yet, others are data entry errors)
- Date_of_Birth has mixed formats: "MM/DD/YYYY", "YYYY-MM-DD", and "DD-Mon-YY"
- Gender field contains: "M", "F", "Male", "Female", "male", "Other", "Prefer not to say", NULL
- 47 duplicate Student_IDs found
- 12 records show GPA values above 4.0 (university uses 4.0 scale)
- Some Email fields contain "N/A" or "none" instead of NULL
- Phone_Number field has inconsistent formats: "(555) 123-4567", "5551234567", "555-123-4567"
Instructions:
-
Data Profiling Report (20 min): For each of the issues above, classify it by data quality dimension (accuracy, completeness, consistency, validity, uniqueness). Create a findings table: Issue | Affected Field | Quality Dimension | Severity (High/Med/Low) | Recommended Action.
-
Missing Data Analysis (15 min): For the 200 missing GPA values, propose different handling strategies depending on the reason for missingness. What would you do for freshmen (legitimate NULL) vs. data entry errors? How would you distinguish between MCAR, MAR, and MNAR in this context?
-
Standardization Plan (20 min): Write pseudocode or SQL queries to:
- Standardize Date_of_Birth to a single format (YYYY-MM-DD)
- Normalize Gender values to a consistent set of categories
- Clean Phone_Number to a standard format
- Identify and flag records with GPA > 4.0
-
Duplicate Resolution (15 min): The 47 duplicate Student_IDs need investigation. Describe your approach to identifying whether these are exact duplicates, near-duplicates, or data entry errors. What criteria would you use to decide which record to keep?
-
Metadata Documentation (20 min): Create a data dictionary for this dataset. For each field, document: field name, data type, description, allowed values/format, required vs. optional, and any business rules.
Deliverables:
- Data quality findings table with severity ratings
- Missing data handling strategy document
- Standardization pseudocode/SQL for 4 cleaning tasks
- Duplicate resolution procedure
- Complete data dictionary