Data Cleaning 101: A Beginner's Guide

What is data cleaning?

Data cleaning (also called data munging or data wrangling) is the process of detecting and correcting corrupt, inaccurate, or inconsistent records in a dataset. It is the single most time-consuming task in any data workflow, typically consuming 60-80% of an analyst's working hours.

Raw data almost never arrives in a usable format. Dates come in five different formats (05/12/89, 1989-05-12, May 12, 1989, 12-May-89, 12/05/1989). Names are inconsistently cased. Currency columns contain dollar signs that prevent calculations. Without cleaning, your analysis will produce wrong results or fail entirely.

Consider a simple example: if one system records a date as 05/12/89, does that mean May 12th or December 5th? Is the year 1989 or 2089? Without standardization, downstream reports, joins, and filters will silently produce incorrect results. This is why data cleaning matters.

The standard workflow

Professional data analysts follow a consistent sequence when cleaning any dataset. Order matters because some steps depend on the output of previous ones.

1. Trim whitespace from all text columns
2. Standardize text casing (title case, UPPER, lower)
3. Standardize date formats to ISO 8601 (YYYY-MM-DD)
4. Clean numeric fields (remove currency symbols, thousand separators)
5. Split combined fields into atomic columns
6. Handle null/missing values (fill, flag, or remove)
7. Deduplicate rows
8. Fix domain-specific codes (diagnosis codes, postal codes, etc.)
9. Validate and export

Step-by-step guide using the sample CSV

Below we walk through each common data problem using a typical messy CSV file. For each issue, we explain what it looks like, why it matters, and exactly how to fix it, using the same transforms you'll find in Mungr's step builder.

1. Trim whitespace

What it looks like: Invisible spaces before or after values. A cell might contain " John Smith " instead of "John Smith". These are invisible in most spreadsheet views but cause joins, lookups, and deduplication to fail silently.

Why analysts do this first: Whitespace corrupts every downstream operation. A VLOOKUP on "Smith" will not match " Smith". Deduplication will treat them as different records. Always trim before any other transformation.

How to fix it: Add a Trim step. Select the columns you want to trim (or select all). Mungr removes leading and trailing whitespace and collapses multiple internal spaces into a single space.

2. Standardize text casing

What it looks like: The same name appears as john smith, JOHN SMITH, and John Smith. Department names alternate between "marketing", "Marketing", and "MARKETING".

Why it matters: Inconsistent casing creates false duplicates in reports and breaks group-by operations. A pivot table on "Department" will show three separate rows for what should be one category.

How to fix it: Add a Change Case step. Choose "Title Case" for name columns and department names. Choose "UPPER" for state abbreviations or codes. Choose "lower" for email addresses.

3. Standardize dates

What it looks like: A single date column contains values in multiple formats: 05/12/89, 1985-11-23, Jun 3 2026, 3-Jun-2026.

Why it matters: Mixed date formats make sorting impossible and cause incorrect date arithmetic. The international standard ISO 8601 (YYYY-MM-DD) is unambiguous, sorts correctly as text, and is understood by every database system.

How to fix it: Add a Standardize Date step. Select your date column and choose the target format YYYY-MM-DD. Mungr auto-detects the input format for each cell and converts it. Ambiguous dates (like 05/12/89) are flagged for your review.

4. Clean numbers

What it looks like: An "Amount" column has values like $1,500.00, 1500, $2,100, and 150.00. The dollar signs and commas prevent the column from being treated as numeric.

Why it matters: You cannot SUM, AVERAGE, or sort a column that contains text characters. Most databases will reject the import, or worse, silently cast these values to NULL, losing your data.

How to fix it: Add a Regex Replace step. Use the pattern [$,] and replace with nothing. This strips dollar signs and commas, leaving clean decimal numbers. Alternatively, use the built-in "Clean Currency" preset which handles multiple currency symbols.

5. Split combined fields

What it looks like: A "Full Name" column contains John A Smith, Maria Garcia, and Bob Lee Jr.. First name, middle initial, last name, and suffixes are all in one field.

Why it matters: Atomic columns (one piece of information per column) are a foundational principle of database normalization. You cannot sort by last name, filter by first name, or match records across systems when names are combined.

How to fix it: Add a Split step. Select the name column, choose space as the delimiter, and specify how many output columns you need (e.g., first_name, last_name). For complex names with suffixes or middle initials, use the "Split from left" or "Split from right" options to control which parts go where.

6. Handle nulls

What it looks like: Empty cells, cells containing "N/A", "NULL", "n/a", "-", or just whitespace. Each represents missing data but in an inconsistent way.

Why it matters: Missing data skews calculations (averages, counts), breaks joins, and creates misleading reports. The correct action depends on the column type and business context.

How to fix it: Add a Fill Nulls step. Choose the target column, then select a strategy: fill with a constant value, zero, the column mean or median, the most common value, or forward/backward fill (use the previous or next row's value).

7. Deduplicate

What it looks like: The same record appears multiple times, either as exact duplicates or as near-duplicates with slightly different formatting (e.g., "John Smith" and "john smith" both with the same DOB and amount).

Why it matters: Duplicate records inflate counts, double-count revenue, and create incorrect aggregations. In healthcare, duplicate claims mean double billing. In finance, duplicate transactions mean incorrect balances.

How to fix it: Add a Deduplicate step. Select the columns that together form a unique identifier (e.g., first_name + last_name + dob). Choose whether to keep the first occurrence, the last occurrence, or flag duplicates for manual review. Always run deduplication after trimming and case standardization to catch near-duplicates.

8. Fix domain-specific codes

What it looks like: Diagnosis codes appear as E11.9, E119, e11.9, and E11.90. These all refer to the same ICD-10 code but with inconsistent formatting.

Why it matters: Domain codes must match a specific format for regulatory reporting, insurance claims processing, and system interoperability. A code that is off by one character can mean a rejected claim or incorrect diagnosis.

How to fix it: Use a combination of steps. First, apply Change Case to UPPER. Then use Regex Replace to insert the decimal point at the correct position if missing (pattern: ^([A-Z]\d{2})(\d+)$, replacement: $1.$2). Finally, use Validate to flag any codes that don't match the expected ICD-10 pattern.

Complete recipe for a messy CSV

Here is the recommended sequence of 12 steps for cleaning a typical messy CSV file. Save this as a Mungr recipe and apply it to similar files with one click.

Key principles for data analysts

• Always preserve the original file. Never modify source data directly. Work on a copy or use a tool like Mungr that applies non-destructive transforms.
• Order matters. Trim before casing. Case before deduplication. Split before validation. Following the standard sequence prevents rework.
• Document every transformation. Six months from now, you (or your replacement) need to understand why a column was modified. Mungr recipes serve as living documentation.
• Validate after cleaning, not just before. Run a final validation pass to confirm your transforms produced the expected formats. Check row counts against the original.
• Nulls are not zeros. A missing value and a zero are fundamentally different. A null salary means "we don't know." A zero salary means "unpaid/volunteer." Treat them accordingly.
• Test on a sample first. Before applying a recipe to a 2-million-row file, run it on the first 100 rows. Inspect the output. Fix edge cases. Then apply to the full dataset.
• Reproducibility over heroics. A saved recipe that anyone can run is worth more than a one-time manual cleanup that only you understand. Invest time in building reusable recipes.

What's next

Once your data is clean, the real analysis begins. Here are the natural next steps in a data analyst's workflow:

• Learn SQL. Most cleaned data ends up in a database. SQL is the universal language for querying structured data. Start with SELECT, WHERE, GROUP BY, and ORDER BY.
• Master joins. Combining data from multiple tables (or multiple CSV files) is where analysis gets powerful. Understand INNER JOIN, LEFT JOIN, and when to use each.
• Aggregate and summarize. COUNT, SUM, AVG, MIN, MAX with GROUP BY let you turn millions of rows into actionable insights.
• Automate with recipes. Save your cleaning steps as a recipe in Mungr. Share it with your team so everyone produces consistent output.