Standardizing Strings, Dates, and Categorical Values

Why Standardization Matters

Real-world data rarely arrives clean. When a city collects permit records, 311 requests, and zoning decisions from three separate departments, each system may store the same city name as "St. Louis", "ST LOUIS", or "saint louis". Dates might appear as 04/15/2023, 2023-04-15, or "April 15, 2023" depending on which form a clerk used. Neighborhood zone codes might be "R-1", "residential_1", or simply "1" across sources. Before any analysis is meaningful, all columns must conform to a single agreed-upon schema. This lab gives you hands-on practice making that happen with Python tools you will use throughout your career.

The Reference Schema

A reference schema is the authoritative definition of what every column must look like after cleaning — the data types, allowed values, and formats. In this lab a schema sheet is provided. Always consult it first; writing code before understanding the target format wastes time and produces wrong results. Check three things for every column you clean:

1. Type — should the column be a string, integer, float, or datetime?

2. Format — for strings, is it title case, upper, or lower? For dates, is it ISO 8601 (`YYYY-MM-DD`)?

3. Allowed values — for categoricals, what is the exact list of valid codes?

Cleaning Strings with pandas

pandas string accessor (`.str`) lets you apply string operations to an entire Series at once without writing a loop. Common methods you will use:

• `.str.strip()` — removes leading and trailing whitespace, a nearly universal first step.
• `.str.lower()` / `.str.upper()` / `.str.title()` — normalizes case.
• `.str.replace(pattern, replacement, regex=True)` — substitutes text using a literal string or a regular expression.
• `.str.contains(pattern, regex=True)` — returns a boolean Series useful for filtering or validating.

A typical city-name cleaning sequence looks like:

```python

df['city'] = df['city'].str.strip().str.title()

```

That single line removes stray spaces and converts "ST LOUIS" and "st. louis" toward a consistent form. If spelling variants remain ("Saint Louis" vs. "St. Louis"), you will need a mapping dictionary combined with `.replace()` or `.map()`.

Regular Expressions for Validation and Extraction

A regular expression (regex) is a pattern string that describes a set of text. Python's `re` module and pandas `.str` methods both accept regex patterns. Five patterns worth memorizing:

| Target | Example pattern |

|---|---|

| US phone (basic) | `r'\(?\d{3}\)?[\s\-.]?\d{3}[\s\-.]?\d{4}'` |

| US ZIP code | `r'\b\d{5}(?:-\d{4})?\b'` |

| Email address | `r'[\w.+-]+@[\w-]+\.[a-zA-Z]{2,}'` |

| Currency string | `r'\$?\d{1,3}(?:,\d{3})*(?:\.\d{2})?'` |

| Date fragment (MM/DD/YYYY) | `r'\d{1,2}/\d{1,2}/\d{4}'` |

When you apply a regex in pandas, `regex=True` must be set explicitly in `.str.replace()`. Always test patterns on a small sample before applying them to the full column.

Parsing and Standardizing Dates

Python's `datetime` module and `pandas.to_datetime()` handle mixed date formats. The key function is:

```python

df['date'] = pd.to_datetime(df['date'], infer_datetime_format=False)

```

When formats are mixed across rows, pass `format='mixed'` (pandas 2.0+) or use `errors='coerce'` to convert unparseable values to `NaT` so you can inspect them separately. After parsing, output a consistent string with:

```python

df['date'] = df['date'].dt.strftime('%Y-%m-%d')

```

ISO 8601 (`YYYY-MM-DD`) is the standard format for analytical pipelines because it sorts correctly as a plain string and is unambiguous across locales.

Standardizing Categorical Codes

Categorical columns require mapping every raw value to its canonical code. Build a dictionary from the schema sheet, then apply it:

```python

zone_map = {

'R-1': 'RES_LOW', 'residential_1': 'RES_LOW', '1': 'RES_LOW',

'C-1': 'COM_GEN', 'commercial': 'COM_GEN'

}

df['zone'] = df['zone'].str.strip().str.upper().map(zone_map)

```

Values not present in the map become `NaN`. Check for those with `df['zone'].isna().sum()` and investigate before proceeding.

Validating with assert Statements and pytest

Cleaning code without validation is risky. Use assert statements inside your script to catch errors immediately:

```python

assert df['city'].str.strip().eq(df['city']).all(), "City column has leading/trailing whitespace"

assert df['date'].str.match(r'\d{4}-\d{2}-\d{2}').all(), "Date column not in YYYY-MM-DD format"

```

The provided pytest file contains 12 such assertions covering all three columns. Your cleaning script must produce a DataFrame that passes all 12 before you submit. If a test fails, read the assertion message, locate the offending rows, and fix the root cause — do not hard-code row-level overrides that only mask the problem. The code review will flag hard-coded workarounds as a non-pass.

Data Safety and Professional Practice

This dataset contains municipal records that may include personally identifiable information (PII) such as addresses and contact details. Follow your program's data handling policy:

• Work only on the provided sample file; do not upload it to public cloud services or share it outside class.
• Do not copy PII into comments or print statements that could appear in logs.
• When cleaning scripts are submitted, ensure no credentials or file paths specific to your machine are hard-coded.

These habits reflect industry expectations and align with data governance principles tested on credentials such as CompTIA Data+ and the Google Data Analytics Certificate.