Data Cleaning and Organization: Assessment Strategies

1. What to Look For: Common Types of "Dirty" Data

Before you can fix a messy dataset, you need to develop an eye for spotting common problems. Raw data can be "dirty" in many ways, from simple typos to structural errors that can make analysis impossible.

The following video provides an excellent overview of the most frequent issues you'll encounter. As you watch, take note of the different categories of errors the presenter discusses.

Now that you have a general idea of what to look for, let's see these issues in a real-world context. The next video shows a presenter's initial reaction when looking at a new dataset for the first time.

2. A Systematic Checklist for Data Assessment

Professional analysts don't just scan data randomly; they follow a structured process to ensure nothing is missed. We can adapt this professional approach into a simple checklist that will serve as your guide for this project. The goal is to investigate, not to fix—that comes next.

Here is a 5-step checklist for assessing any new dataset.

Step 1: Check the Structure (The "Shape")

First, get a sense of the dataset's size and layout.

• Rows and Columns: How many rows (records) and columns (fields) are there? Does this match what you expect?
• Unique Identifier (UID): Is there a column that should uniquely identify each row, like an OrderID or CustomerID? If so, this will be critical for finding duplicates later.

Step 2: Look for Duplicates

Duplicate rows can inflate your numbers and lead to incorrect conclusions. It's one of the first and most important things to check for. In the previous module, you used the "Remove duplicates" tool to fix this, but for now, your job is just to see if they exist. You can quickly spot potential duplicates by sorting the data and looking for identical rows.

Step 3: Find the Blanks (Missing Data)

Data is often incomplete. Are there entire rows or individual cells that are empty?

• Use the Filter function on each column header and look for a (Blanks) option in the filter menu.
• If you find blanks, ask yourself: Does a blank cell here make sense? Or does it represent missing information that needs to be addressed?

Step 4: Check for Inconsistency

This is often where the majority of "dirtiness" lies.

• Categorical Data: For columns with a limited set of options (e.g., Country, Status, Product Category), use the filter to see all unique values. Look for variations like "USA" vs. "United States," misspellings, or inconsistent capitalization.
• Text Data: Scan columns with free text (like Name or Address) for extra leading/trailing spaces or inconsistent casing (e.g., "john smith" vs. "John Smith").
• Numeric & Date Data: Look at columns that should contain numbers or dates. Are there any text values mixed in? Are dates in a consistent format (e.g., MM/DD/YYYY vs. DD-Mon-YY)?

This messy spreadsheet is a great example of inconsistent data. Notice the #DIV/0! errors, mixed number formats, and varied text entries.

Step 5: Spot Potential Outliers

Outliers are data points that are significantly different from all the others. They could be typos (e.g., a price of $1,000,000 instead of $10.00) or legitimate but extreme values.

• A quick way to spot them is to sort a numeric column from largest to smallest and smallest to largest. Do the values at the top and bottom look reasonable?

Your Task: Assess the Project Dataset

For this module's project, you have been given a messy sales dataset from a fictional e-commerce company. Your first task is to assess its quality.

Imagine you have just received the Excel file. Your goal is not to clean it yet, but to create a report of the issues you find.

Open a blank Google Doc or text file and, using the 5-step checklist, write down every problem you can identify from the description and visual examples below.

Dataset Description:
The file raw_sales_data.xlsx contains sales records. It is supposed to have the columns: OrderID, OrderDate, CustomerID, CustomerName, Email, Country, Product, Price, and Quantity.

Here are some snapshots from the file:

• A filter on the Country column shows: USA, U.S.A., United States, Canada, CAN.
• The CustomerName column has entries like " jane doe ", " John Smith", and "PETER JONES".
• The Price column is formatted as currency ($49.99) for some rows, but as plain numbers (25.50) for others. One entry is N/A.
• Sorting the Price column reveals a value of $99999.00 for a t-shirt, while most other prices are below $100.
• Some rows are completely empty. Other rows have a CustomerID but are missing an Email.
• Sorting by OrderID shows that Order ID 1054A appears twice with identical information.

Based on this information, create your "Cleaning Plan" by listing all the problems you've identified, categorized by the 5 steps in our checklist.

Conclusion

In this lesson, you've learned the critical first step of any data cleaning project: how to systematically assess a raw dataset for errors. This process of investigation is what separates a novice from a professional.

Your key takeaways are:

• Data assessment precedes data cleaning. You must diagnose the problems before you can treat them.
• A systematic checklist is your best tool. By checking for structure, duplicates, blanks, inconsistencies, and outliers, you can be confident you haven't missed anything.
• Use filters and sorting as your primary investigative tools to quickly reveal patterns and problems within the data.

Your list of identified issues is now your official "cleaning plan." In the next lesson, we will begin executing this plan, tackling each problem one by one to transform the messy dataset into a clean, reliable, and usable resource.