Notebook 4: Data Cleaning and Preparation

Module: Data Analytics Level: Intermediate Prerequisites: Notebook 3 (High-Performance Data Manipulation) Estimated Time: 4 hours

---

Table of Contents#

1. Why Cleaning Is the Foundation of Every Analysis
2. Detecting and Handling Missing Data
3. Detecting and Handling Duplicates
4. Outlier Detection
5. Type Conversion
6. String and Text Cleaning
7. Datetime Parsing and Cleaning
8. Validating Cleaned Data
9. Hands-On: Cleaning a Realistically Messy Dataset
10. Case Studies
11. Common Misconceptions and Pitfalls
12. Glossary
13. Exercises
14. Quiz
15. Summary
16. Further Reading

---

Learning Objectives#

By the end of this notebook, you will be able to:

• Detect missing data and choose an appropriate handling strategy based on why it is missing
• Identify and correctly remove or resolve duplicate records, including near-duplicates
• Detect outliers using both statistical (z-score, IQR) and visual methods, and decide when to investigate versus remove them
• Convert columns to appropriate, memory-efficient dtypes
• Clean and standardize text data using vectorized string operations
• Parse and validate datetime data, including handling multiple formats and time zones
• Apply a systematic validation pass to confirm a dataset is genuinely analysis-ready

---

1. Why Cleaning Is the Foundation of Every Analysis#

Notebook 0, Section 4.1 noted that data cleaning typically consumes the majority of time in a real analytics project. This notebook makes that claim concrete: every technique here addresses a specific, common way that real-world data arrives broken, inconsistent, or misleading, and every one of these problems, if left unaddressed, silently corrupts every downstream statistic, visualization, and conclusion in Notebooks 6 through 10. Unlike a bug in code, a data quality problem often produces no error at all, only a wrong answer that looks perfectly plausible, which is precisely what makes systematic cleaning discipline essential rather than optional.

---

2. Detecting and Handling Missing Data#

2.1 Detecting Missing Data#

Python
CopyAsk AI
import pandas as pd
import numpy as np

df = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "age": [25, np.nan, 35, 40, np.nan],
    "email": ["a@x.com", "b@x.com", None, "d@x.com", "e@x.com"]
})

print(df.isna().sum())               # count of missing values per column
print(df.isna().mean().round(3))     # proportion missing per column

Expected output:

code
Ask AICopy
customer_id    0
age            2
email          1
dtype: int64

customer_id    0.0
age            0.4
email          0.2
dtype: float64

2.2 Why Data Is Missing: A Direct Extension From Notebook 0#

Notebook 0, Section 3.1 introduced the MCAR, MAR, and MNAR framework for classifying why data is missing. This distinction is not academic: it directly determines whether a simple imputation strategy is safe or whether it will introduce systematic bias into every downstream statistic.

• MCAR (Missing Completely at Random): a sensor randomly drops a small fraction of readings due to unrelated technical glitches. Simple imputation is generally safe here, since missingness carries no information about the true value or about any other variable.
• MAR (Missing at Random): older survey respondents are less likely to answer a digital-only follow-up question, but conditional on age, missingness is unrelated to the actual answer. Imputation using related observed variables (e.g., predicting the missing value from age and other fields) can be reasonably safe.
• MNAR (Missing Not at Random): customers with very low satisfaction are less likely to complete a satisfaction survey at all. Simple imputation here is dangerous, since the very fact of being missing is correlated with the (unobserved) true value, and imputing based on observed respondents alone will systematically overestimate satisfaction.

2.3 Deletion Strategies#

Python
CopyAsk AI
# Drop any row with at least one missing value
df_dropped_rows = df.dropna()

# Drop only rows missing a specific critical column
df_dropped_critical = df.dropna(subset=["email"])

# Drop a column entirely if it is mostly missing
threshold = 0.5  # drop columns missing more than 50 percent of values
df_dropped_cols = df.dropna(axis=1, thresh=int(len(df) * (1 - threshold)))

Deletion is appropriate primarily under MCAR with a small proportion of missing rows; deleting under MAR or MNAR risks introducing exactly the systematic bias described in Section 2.2, since the rows being dropped are not a random sample of the whole.

2.4 Imputation Strategies#

Python
CopyAsk AI
# Numeric: fill with median (robust to outliers, per Section 4)
df["age"] = df["age"].fillna(df["age"].median())

# Categorical: fill with mode (most frequent value)
df["email_domain"] = df["email"].str.split("@").str[1]
df["email_domain"] = df["email_domain"].fillna(df["email_domain"].mode()[0])

# Forward fill / backward fill: appropriate for ordered/time-series data,
# where a missing value likely equals the most recent known value
df_sorted = df.sort_values("customer_id")
df_sorted["age"] = df_sorted["age"].ffill()

2.5 Preserving the Missingness Signal#

Exactly as introduced in Notebook 0, Section 3.4, adding an explicit indicator column preserves the potentially informative fact that a value was missing, even after imputation fills in a placeholder value:

Python
CopyAsk AI
df["age_was_missing"] = df["age"].isna().astype(int)
df["age"] = df["age"].fillna(df["age"].median())

This is particularly important to consider whenever there is any suspicion the data might be MNAR (Section 2.2), since in that case the missingness itself may carry real analytical signal that a plain imputed value would otherwise erase.

---

3. Detecting and Handling Duplicates#

3.1 Detecting Exact Duplicates#

Python
CopyAsk AI
df = pd.DataFrame({
    "customer_id": [1, 2, 2, 3, 4, 4],
    "name": ["Alice", "Bob", "Bob", "Carol", "David", "David"],
    "purchase": [50, 30, 30, 70, 20, 20]
})

print(df.duplicated().sum())        # count of fully duplicated rows
print(df[df.duplicated()])           # the duplicate rows themselves (excluding first occurrence)
print(df[df.duplicated(keep=False)]) # ALL rows involved in a duplicate, including the first occurrence

Expected output:

code
Ask AICopy
2
   customer_id   name  purchase
2            2    Bob        30
5            4  David        20
   customer_id   name  purchase
1            2    Bob        30
2            2    Bob        30
4            4  David        20
5            4  David        20

3.2 Duplicates Based on a Subset of Columns#

Real-world duplicates often are not identical across every column (for example, a customer record entered twice with a slightly different timestamp). Checking duplication based on a meaningful subset of columns catches these:

Python
CopyAsk AI
duplicates_by_id = df[df.duplicated(subset=["customer_id"], keep=False)]
print(duplicates_by_id)

3.3 Removing Duplicates#

Python
CopyAsk AI
df_deduped = df.drop_duplicates()                     # remove exact duplicate rows
df_deduped_by_id = df.drop_duplicates(subset=["customer_id"], keep="first")

The keep parameter controls which occurrence survives: "first" (default), "last", or False (drop all occurrences of anything duplicated, keeping none). The correct choice depends on context — for example, keep="last" is often appropriate when later records represent corrected or updated information.

3.4 Near-Duplicates: A Harder Problem#

Exact duplicate detection misses records that are logically the same but differ due to formatting (e.g., "John Smith" versus "john smith " with trailing whitespace, or "123 Main St." versus "123 Main Street"). A common first step is standardizing text (Section 6) before duplicate detection:

Python
CopyAsk AI
df["name_normalized"] = df["name"].str.strip().str.lower()
near_duplicates = df[df.duplicated(subset=["name_normalized"], keep=False)]

More sophisticated fuzzy-matching techniques (comparing string similarity scores) exist for harder near-duplicate problems but are outside the scope of this course; the key takeaway is that duplicate detection should always be considered at multiple levels of strictness, not just exact row equality.

---

4. Outlier Detection#

4.1 The Z-Score Method#

Directly extending the z-score technique introduced in Notebook 1, Section 11, an outlier is flagged when its distance from the mean, measured in standard deviations, exceeds a threshold (commonly 3):

Python
CopyAsk AI
import numpy as np

data = pd.Series([45, 48, 52, 47, 300, 50, 49, 46, -150, 51])

z_scores = (data - data.mean()) / data.std()
outliers = data[np.abs(z_scores) > 3]
print(outliers)

The z-score method assumes the data is roughly normally distributed; it becomes unreliable on strongly skewed data, since a skewed distribution's own mean and standard deviation are themselves distorted by the same extreme values you are trying to detect.

4.2 The Interquartile Range (IQR) Method#

A more robust alternative that does not assume normality, based on percentiles rather than mean and standard deviation:

$\text{IQR} = Q_3 - Q_1$ $\text{Lower bound} = Q_1 - 1.5 \times \text{IQR}, \quad \text{Upper bound} = Q_3 + 1.5 \times \text{IQR}$

where $Q_1$ and $Q_3$ are the 25th and 75th percentiles. Values outside these bounds are flagged as outliers. This is the same rule used to determine whisker length in a box plot (Notebook 8).

Python
CopyAsk AI
Q1 = data.quantile(0.25)
Q3 = data.quantile(0.75)
IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

outliers_iqr = data[(data < lower_bound) | (data > upper_bound)]
print(outliers_iqr)

The multiplier 1.5 is a widely used convention, not a universal law; a multiplier of 3 is sometimes used to flag only more "extreme" outliers specifically, with 1.5 catching a broader set of "mild" outliers.

4.3 Choosing Between Z-Score and IQR#

4.4 Deciding What to Do With Detected Outliers#

Detecting an outlier is not the same as deciding what to do about it. Four common responses, in rough order of how much they alter the data:

1. Investigate first, always: is this a data entry error (e.g., a negative age, a decimal point error), a legitimate but rare extreme value (e.g., a genuine high-net-worth customer), or a sign of a data pipeline bug (Section 10.1)?
2. Correct: if the outlier is a known, identifiable error (e.g., a unit conversion mistake), correct it if the true value can be determined.
3. Remove: if the outlier cannot be explained and is likely erroneous, and its presence would distort downstream analysis, removal may be appropriate, but should always be documented.
4. Keep and note: if the outlier is a legitimate, if extreme, real value, it often should be kept, since removing genuine data specifically because it is inconvenient can bias the analysis in the opposite, equally problematic direction.

Note: outlier detection should never be a fully automated, silent step in a pipeline without human review, since the "correct" action depends entirely on domain context that a statistical rule alone cannot determine.

---

5. Type Conversion#

5.1 Why Correct Dtypes Matter#

Notebook 1, Section 2.2 established that fixed, correct dtypes are essential for both correctness and vectorized performance. Data loaded from CSV files, in particular, frequently arrives with incorrect dtypes: numbers stored as text (due to stray formatting like currency symbols), dates stored as generic strings, and low-cardinality categorical columns stored as generic, memory-heavy object columns.

5.2 Converting Numeric Types#

Python
CopyAsk AI
df = pd.DataFrame({"price": ["$19.99", "$24.50", "$9.99", "invalid"]})

# Remove non-numeric formatting first
df["price_clean"] = df["price"].str.replace("$", "", regex=False)

# Convert to numeric, turning unparseable values into NaN rather than raising an error
df["price_numeric"] = pd.to_numeric(df["price_clean"], errors="coerce")
print(df)

Expected output:

code
Ask AICopy
     price price_clean  price_numeric
0    $19.99      19.99          19.99
1    $24.50      24.50          24.50
2     $9.99       9.99           9.99
3   invalid     invalid            NaN

The errors="coerce" parameter is essential for real-world data: it converts genuinely unparseable values to NaN (which can then be handled using Section 2's techniques) rather than halting the entire operation with an exception the moment a single bad value is encountered.

5.3 Converting to Categorical Type#

For columns with a limited number of repeated distinct values (e.g., region, product category, status), converting to the category dtype can dramatically reduce memory usage, since Pandas stores the underlying data as compact integer codes with a small lookup table of unique values, rather than repeating full string values for every row:

Python
CopyAsk AI
df["region"] = df["region"].astype("category")
print(df["region"].memory_usage(deep=True))

This is especially valuable for the large-dataset performance techniques covered later in this course, and directly complements the dtype parameter used at load time in Notebook 2, Section 8.2.

5.4 Boolean Conversion#

Python
CopyAsk AI
df = pd.DataFrame({"is_active": ["yes", "no", "yes", "TRUE", "False"]})
df["is_active_bool"] = df["is_active"].str.lower().map({
    "yes": True, "true": True, "no": False, "false": False
})
print(df)

Real data often encodes booleans inconsistently ("yes"/"no", "Y"/"N", "1"/"0", "True"/"False" in varying capitalizations), and standardizing text case (Section 6.1) before mapping is a common necessary first step.

---

6. String and Text Cleaning#

6.1 Basic Vectorized String Cleaning#

Directly building on the .str accessor introduced in Notebook 3, Section 2.4:

Python
CopyAsk AI
df = pd.DataFrame({"name": ["  Alice Smith ", "BOB JONES", "carol white  "]})

df["name_clean"] = (
    df["name"]
    .str.strip()       # remove leading/trailing whitespace
    .str.lower()        # standardize case
    .str.replace(r"\s+", " ", regex=True)  # collapse multiple internal spaces to one
)
print(df)

Expected output:

code
Ask AICopy
              name       name_clean
0    Alice Smith        alice smith
1        BOB JONES        bob jones
2    carol white          carol white

6.2 Extracting Structured Information From Text#

Python
CopyAsk AI
df = pd.DataFrame({"full_contact": ["Alice Smith <alice@example.com>", "Bob Jones <bob@example.com>"]})

df["email"] = df["full_contact"].str.extract(r"<(.+)>")
df["name"] = df["full_contact"].str.extract(r"^([^<]+)").apply(lambda s: s.str.strip())
print(df)

.str.extract() uses a regular expression with a capture group to pull structured substrings out of free-text fields, a common need when working with data exported from systems that combine multiple logical fields into a single text column.

6.3 Standardizing Categorical Text Values#

A frequent real-world problem: the same logical category is represented inconsistently across records (e.g., "USA", "U.S.A.", "United States", "us"):

Python
CopyAsk AI
df = pd.DataFrame({"country": ["USA", "U.S.A.", "United States", "us", "Canada"]})

country_mapping = {
    "usa": "United States", "u.s.a.": "United States",
    "united states": "United States", "us": "United States",
    "canada": "Canada"
}

df["country_standardized"] = df["country"].str.lower().str.strip().map(country_mapping)
print(df)

This mapping-based approach requires knowing the set of inconsistent variants in advance, typically discovered by inspecting .value_counts() (Notebook 2, Section 9) on the raw column before cleaning, to catch every variant that needs standardizing.

6.4 Removing or Handling Special Characters#

Python
CopyAsk AI
df = pd.DataFrame({"product_code": ["ABC-123", "DEF_456", "GHI#789"]})
df["product_code_clean"] = df["product_code"].str.replace(r"[^A-Za-z0-9]", "", regex=True)
print(df)

---

7. Datetime Parsing and Cleaning#

7.1 Parsing Dates#

Python
CopyAsk AI
df = pd.DataFrame({"date_str": ["2024-01-15", "01/20/2024", "Jan 25, 2024"]})

df["date_parsed"] = pd.to_datetime(df["date_str"], errors="coerce")
print(df)

Expected output:

code
Ask AICopy
       date_str date_parsed
0    2024-01-15  2024-01-15
1    01/20/2024  2024-01-20
2  Jan 25, 2024  2024-01-25

Pandas's to_datetime can automatically infer several common formats, but mixed, ambiguous, or unusual formats (particularly ambiguous month/day ordering, such as "01/02/2024," which could mean January 2nd or February 1st depending on locale) may require an explicit format parameter to parse correctly and unambiguously:

Python
CopyAsk AI
df["date_explicit"] = pd.to_datetime(df["date_str"], format="%m/%d/%Y", errors="coerce")

As with pd.to_numeric in Section 5.2, errors="coerce" converts unparseable date strings to NaT (Pandas's "Not a Time," the datetime equivalent of NaN) rather than halting on the first invalid entry.

7.2 Extracting Datetime Components#

Extending the vectorized .dt accessor introduced in Notebook 3, Section 2.4:

Python
CopyAsk AI
df["year"] = df["date_parsed"].dt.year
df["month"] = df["date_parsed"].dt.month
df["day_of_week"] = df["date_parsed"].dt.day_name()
df["is_weekend"] = df["date_parsed"].dt.dayofweek.isin([5, 6])

7.3 Handling Time Zones#

Python
CopyAsk AI
# Localize a naive (timezone-unaware) datetime to a specific timezone
df["date_localized"] = df["date_parsed"].dt.tz_localize("America/New_York")

# Convert an already-localized datetime to a different timezone
df["date_utc"] = df["date_localized"].dt.tz_convert("UTC")

Combining timestamps from multiple sources that use different, unlabeled time zone assumptions (some systems log in UTC, others in local server time) without addressing this explicitly is a common and hard-to-detect source of subtly incorrect time-based analysis, particularly for anything involving day boundaries or time-of-day patterns.

7.4 Validating Date Ranges#

Python
CopyAsk AI
# Flag dates outside a plausible range (e.g., future signup dates, or dates before the company existed)
invalid_dates = df[(df["date_parsed"] > pd.Timestamp.now()) | (df["date_parsed"] < pd.Timestamp("2015-01-01"))]
print(invalid_dates)

This is a domain-specific outlier check (Section 4) applied specifically to datetime data, since a "statistically extreme" date is not the relevant concept here; a definitionally impossible date (a signup date in the future) is.

---

8. Validating Cleaned Data#

8.1 A Systematic Post-Cleaning Checklist#

After applying cleaning steps, a systematic validation pass should confirm the dataset is genuinely ready for analysis, rather than assuming cleaning code worked as intended:

Python
CopyAsk AI
def validate_dataset(df):
    print("Missing values per column:")
    print(df.isna().sum())

    print("\nDuplicate rows:", df.duplicated().sum())

    print("\nDtypes:")
    print(df.dtypes)

    print("\nNumeric column ranges:")
    print(df.describe())

    for col in df.select_dtypes(include="object").columns:
        print(f"\nUnique values in '{col}' (first 10): {df[col].unique()[:10]}")

validate_dataset(df)

8.2 Asserting Expectations Explicitly#

For production analytics pipelines, explicit assertions catch violated assumptions immediately, rather than allowing corrupted data to propagate silently downstream, echoing the validate parameter philosophy from Notebook 3, Section 6.5:

Python
CopyAsk AI
assert df["age"].between(0, 120).all(), "Found ages outside a plausible human range"
assert df["email"].str.contains("@", na=False).all(), "Found malformed email addresses"
assert not df.duplicated(subset=["customer_id"]).any(), "Found duplicate customer IDs"

---

9. Hands-On: Cleaning a Realistically Messy Dataset#

This example combines every technique from this notebook into a single, realistic cleaning pipeline, using method chaining (Notebook 3, Section 8.2) to keep the pipeline readable end to end.

Python
CopyAsk AI
import pandas as pd
import numpy as np

raw = pd.DataFrame({
    "customer_id": [1, 2, 2, 3, 4, 5],
    "name": ["  Alice Smith", "BOB JONES ", "BOB JONES ", "carol white", "David Lee", "Eve Chen"],
    "age": [28, 34, 34, np.nan, 41, 999],
    "signup_date": ["2023-01-15", "01/20/2023", "01/20/2023", "2023-03-10", "not a date", "2023-02-05"],
    "revenue": ["$1,200.50", "$3,400", "$3,400", "$800", "$-50", "$2,100"]
})

cleaned = (
    raw
    .drop_duplicates(subset=["customer_id"], keep="first")
    .assign(
        name=lambda d: d["name"].str.strip().str.lower(),
        signup_date=lambda d: pd.to_datetime(d["signup_date"], errors="coerce"),
        revenue=lambda d: pd.to_numeric(
            d["revenue"].str.replace(r"[$,]", "", regex=True), errors="coerce"
        )
    )
    .assign(
        age=lambda d: d["age"].where(d["age"].between(0, 110))  # treat 999 as invalid -> NaN
    )
    .assign(
        age=lambda d: d["age"].fillna(d["age"].median())
    )
)

print(cleaned)
print("\nRemaining missing values:")
print(cleaned.isna().sum())

Expected output (approximate):

code
Ask AICopy
   customer_id           name   age signup_date  revenue
0            1    alice smith  28.0  2023-01-15   1200.5
1            2      bob jones  34.0  2023-01-20   3400.0
3            3    carol white  34.0  2023-03-10    800.0
4            4      david lee  41.0         NaT    -50.0
5            5       eve chen  34.0  2023-02-05   2100.0

Remaining missing values:
customer_id    0
name           0
age            0
signup_date    1
revenue        0
dtype: int64

Interpretation: Notice several deliberate issues were resolved: the exact duplicate customer (ID 2) was removed, names were standardized in case and whitespace, currency-formatted revenue strings were converted to proper numeric values (including correctly parsing a negative value that itself warrants further business investigation, per Section 4.4's "always investigate first" principle), the implausible age of 999 was treated as invalid and imputed with the median, and one signup date remains as a NaT because "not a date" cannot be parsed, an issue explicitly surfaced by the final validation check rather than silently hidden.

---

10. Case Studies#

10.1 Case Study: An Outlier That Was Actually a Pipeline Bug#

An analytics team notices a small number of transactions with revenue values in the billions of dollars, far beyond any plausible individual transaction, in an otherwise normal-looking retail dataset. An initial instinct is to simply remove these as outliers using the IQR method (Section 4.2).

Diagnosis: Further investigation (following the "investigate first" principle in Section 4.4) reveals the actual cause: a small number of records had a currency field ambiguously double-converted (once during data entry, once during an ETL processing step), effectively multiplying the true value by 100 in a way that happened to look statistically implausible but was actually a specific, traceable data pipeline bug rather than a naturally occurring extreme value.

Lesson: Removing these as generic "outliers" without investigation would have discarded valuable information about a genuine data pipeline defect (masking a bug the engineering team needed to know about and fix at its source) rather than simply cleaning noise, and would also have left an unknown number of similarly affected but less extreme (and therefore undetected) records silently corrupted elsewhere in the dataset.

10.2 Case Study: A Silent Timezone Mismatch Skewing "Peak Usage Hour" Analysis#

A team analyzing when users are most active on a platform combines event logs from two different servers, one recording timestamps in UTC and one recording timestamps in the server's local Pacific time, without realizing the discrepancy (Section 7.3). The resulting "peak usage hour" analysis shows two distinct, oddly separated activity peaks roughly 7-8 hours apart, initially interpreted as two genuinely distinct user segments in different time zones.

Diagnosis: After investigation, the two "peaks" are found to be the exact same underlying usage pattern, simply recorded under two different, unreconciled timezone assumptions; there is only one real peak, not two, and the timezone metadata for each data source needed to be explicitly identified and standardized (localized and converted, per Section 7.3) before combining the datasets.

Lesson: Any time-based analysis that combines data from multiple systems should explicitly confirm and standardize timezone handling as an early cleaning step, since a timezone mismatch produces a plausible-looking but entirely incorrect pattern rather than an obvious error.

10.3 Case Study: MNAR Missingness Producing an Overly Optimistic Customer Satisfaction Score#

A company's average customer satisfaction score, computed only from customers who completed an optional post-purchase survey, looks consistently strong. A separate analysis using a mandatory, universally-collected proxy metric (return/refund rate) reveals meaningfully worse underlying customer sentiment than the survey score suggests.

Diagnosis: This is a direct real-world instance of the MNAR scenario described in Section 2.2 and previewed conceptually in Notebook 0's credit application case study: customers with a negative experience are less likely to voluntarily complete a satisfaction survey at all, meaning the survey's missingness is correlated with the very quantity being measured. Any imputation or simple average based only on observed (completed) responses inherits this systematic bias.

Lesson: Whenever a metric is derived from optional or voluntary data collection, the possibility of MNAR missingness (Section 2.2) should be explicitly considered before treating the observed average as representative of the full population, and triangulating against an independent, more universally collected proxy metric is a valuable sanity check.

---

11. Common Misconceptions and Pitfalls#

---

12. Glossary#

---

13. Exercises#

Exercise 1 (Conceptual)#

A dataset of loan applications has a "reported_income" field that is missing for 15 percent of applicants, and further investigation shows the missing rate is much higher among applicants who were ultimately denied a loan. Classify this missingness mechanism and explain the risk of simply imputing the missing values with the overall median income.

<details> <summary>Solution</summary>

This is most consistent with MNAR (Missing Not at Random), since missingness in reported income appears correlated with an outcome (loan denial) that is itself likely correlated with the true, unreported income value (e.g., applicants with lower or harder-to-verify income may be both more likely to leave the field blank and more likely to be denied). Imputing with the overall median would implicitly assume these applicants' true incomes resemble the typical applicant, which is very likely to be wrong precisely because they are missing for a reason connected to the unobserved value itself, echoing the satisfaction-survey case study in Section 10.3. A more careful approach might add a missingness indicator (Section 2.5) and treat the pattern of missingness itself as a potentially informative feature, or investigate the actual cause with the lending team before choosing an imputation strategy.

</details>

---

Exercise 2 (Conceptual)#

Explain why the IQR method for outlier detection is generally considered more robust than the z-score method when applied to a strongly right-skewed distribution, such as household income.

<details> <summary>Solution</summary>

The z-score method depends directly on the mean and standard deviation (Section 4.1), both of which are themselves highly sensitive to extreme values in a skewed distribution; a few very large incomes can inflate the mean and standard deviation substantially, which can cause genuinely extreme values to appear less extreme in z-score terms (masking real outliers) or cause moderately high values to appear more extreme than they really are, depending on the specific distortion. The IQR method (Section 4.2) instead relies on the 25th and 75th percentiles, which are far less sensitive to a small number of extreme values, since percentiles are determined by rank/position in the sorted data rather than by the magnitude of extreme values themselves, making the resulting outlier boundaries considerably more stable and reliable for skewed data.

</details>

---

Exercise 3 (Coding)#

Given a DataFrame df with a column phone containing values like "(555) 123-4567", "555.123.4567", and "5551234567", write code that standardizes every value to a plain 10-digit numeric string with no formatting characters.

<details> <summary>Solution</summary>

Python
CopyAsk AI
import pandas as pd

df = pd.DataFrame({"phone": ["(555) 123-4567", "555.123.4567", "5551234567"]})

df["phone_clean"] = df["phone"].str.replace(r"[^0-9]", "", regex=True)
print(df)

This applies the special-character removal pattern from Section 6.4, using a regular expression that matches any character that is not a digit ([^0-9]) and replaces it with an empty string, leaving only the raw 10-digit sequence regardless of the original formatting style, which then allows reliable duplicate detection (Section 3) or joining against other systems that store phone numbers differently.

</details>

---

Exercise 4 (Applied Thinking)#

You discover that a "purchase_amount" column contains a small number of negative values in a dataset that should logically only contain positive purchase amounts. Using Section 4.4's framework, describe the investigation steps you would take before deciding how to handle these values.

<details> <summary>Solution</summary>

Following the "investigate first" principle from Section 4.4, before taking any action: first, check whether negative values might be legitimate in this business context (for example, they could correctly represent refunds or returns rather than being erroneous, in which case they may need to be handled as a distinct category rather than removed or treated as an error, directly paralleling the pipeline-bug case study in Section 10.1). Second, check whether negative values cluster around specific dates, sources, or systems (which might point to a specific upstream data entry or integration bug rather than a business-legitimate pattern). Third, only after ruling out both a legitimate business meaning and a specific identifiable bug, consider whether to correct (if the true value can be inferred, e.g., a sign error), exclude, or flag the values, and document whichever decision is made so the reasoning is auditable later.

</details>

---

14. Quiz#

Q1. Which missingness mechanism poses the greatest risk when using simple imputation based only on observed values? a) MCAR b) MAR c) MNAR d) All three pose identical risk

Q2. Which outlier detection method is generally more robust to skewed, non-normal data? a) Z-score method b) IQR method c) Both are equally robust d) Neither method works on numeric data

Q3. What does errors="coerce" do when passed to pd.to_numeric() or pd.to_datetime()? a) Raises an exception immediately on the first unparseable value b) Converts unparseable values to NaN or NaT instead of raising an exception c) Automatically corrects malformed values to the nearest valid value d) Has no effect on how errors are handled

Q4. Why might combining timestamp data from two different systems produce a misleading pattern even if both use the same date format? a) Date formats always guarantee identical timezone handling b) The two systems could be using different, unreconciled time zones c) Pandas cannot parse dates from more than one source at a time d) This scenario cannot occur if both timestamps are valid

Q5. True or False: Detecting an outlier statistically is sufficient justification to remove it from a dataset without further investigation.

<details> <summary>Answers</summary>

1. c) MNAR, since missingness is correlated with the unobserved true value itself
2. b) IQR method, since it is based on percentiles rather than the mean and standard deviation
3. b) Converts unparseable values to NaN or NaT instead of raising an exception
4. b) The two systems could be using different, unreconciled time zones
5. False — per Section 4.4 and the case study in Section 10.1, outliers should always be investigated to understand their cause before deciding how to handle them

</details>

---

15. Summary#

• Missing data should be diagnosed using the MCAR/MAR/MNAR framework before choosing a handling strategy, since the appropriate response depends entirely on why the data is missing, not just how much is missing.
• Duplicate detection should consider both exact duplicates and near-duplicates arising from inconsistent formatting, and the choice of which duplicate record to keep should be a deliberate decision.
• Outlier detection (z-score for roughly normal data, IQR for skewed or unknown distributions) identifies candidates for investigation, not automatic removal; the correct response depends on the outlier's actual cause.
• Correct, memory-efficient dtypes (numeric, categorical, boolean, datetime) are essential for both correctness and the vectorized performance established in earlier notebooks.
• Vectorized string and datetime operations (the .str and .dt accessors) provide fast, consistent tools for cleaning text and temporal data at scale.
• Combining data across systems requires explicit attention to consistent units, formats, and time zones, since mismatches in any of these can produce plausible-looking but incorrect results.
• A systematic post-cleaning validation pass, including explicit assertions about expected data properties, should confirm a dataset is genuinely analysis-ready rather than assuming cleaning code worked as intended.

---

16. Further Reading#

• Pandas official documentation on working with missing data: https://pandas.pydata.org/docs/user_guide/missing_data.html
• Pandas official documentation on string methods: https://pandas.pydata.org/docs/user_guide/text.html
• Pandas official documentation on time series and date functionality: https://pandas.pydata.org/docs/user_guide/timeseries.html
• Hadley Wickham, "Tidy Data" (Journal of Statistical Software, 2014) — a foundational paper on structuring clean, analysis-ready datasets

---

Next Up#

Notebook 5: Data Transformation and Reshaping — pivot tables, melt/stack/unstack, concatenation revisited in the context of reshaping, and wide versus long data formats, applying the now-cleaned dataset from this notebook into the various shapes needed for different types of analysis and visualization.

---

Course: Data Analytics | Notebook 4 of 13