Data Cleaning, Profiling, and Preparation

Data cleaning, profiling, and preparation are the three phases you go through before you can actually analyse data. Think of them as the 'wash, sort, and chop' of data work. Let's break down each one from the ground up.

Data profiling is the discovery phase. Before you clean anything, you need to understand what you're working with. Profiling means examining a dataset to find its structure, content, and quality. You look at things like: what columns exist, what data types are in each column (numbers, text, dates), how many rows there are, and whether values are missing. For example, if you have a spreadsheet of customer orders, profiling would tell you that the 'Order Date' column contains dates, but some cells have text like 'last week' instead of a proper date. Profiling also reveals patterns, like a column that should have male or female values but contains 'M', 'Male', 'F', 'Female', and even 'f' — this is called inconsistent formatting. The goal of profiling is to create a 'profile report' that lists every issue you need to fix.

Data cleaning is the repair phase. Once you know what's wrong, you fix it. Cleaning involves correcting errors, removing duplicates, handling missing data, and standardising formats. Common cleaning tasks include: - Handling missing values: You might remove rows with missing data (called deletion), fill them with an average value (imputation), or mark them as 'unknown'. - Removing duplicates: If the same customer appears twice, you delete one copy. - Fixing typos and inconsistencies: Changing 'N/A' to 'Null' or 'Male' to 'M' so everything matches. - Outlier treatment: Identifying values that are far outside the normal range, like an age of 450 in a column of human ages, and deciding whether to delete them or investigate. - Validating data: Checking that dates are real dates, phone numbers have the right number of digits, and email addresses contain an '@' symbol.

Data preparation is the arrangement phase. After cleaning, the data is clean but often not in the right shape for analysis. Preparation means transforming the data into a format that analysis tools can use. This includes: - Reshaping data: Changing data from a 'wide' format (many columns) to a 'long' format (few columns but more rows) or vice versa. - Merging datasets: Combining data from two or more sources, like joining a customer list with a sales list. - Creating new calculated fields: For example, adding a 'Profit' column that subtracts Cost from Revenue. - Sorting and filtering: Organising data so you can focus on the relevant parts. - Splitting columns: If a column contains full names, you might split it into 'First Name' and 'Last Name'.

Why does this whole process exist? Before computers, data was recorded on paper forms, and errors were corrected by hand. Today, data comes from many automated sources like website forms, sensors, and databases, and each source can introduce errors: humans mistype their names, sensors glitch, and systems store dates in different formats. If you skip cleaning and profiling, your analysis will be 'garbage in, garbage out' — no amount of fancy charts can fix bad underlying data.

What does it replace? In the past, a single data entry clerk might have checked data manually. Now, with millions of rows, we use software like Excel, OpenRefine, or Python libraries (like pandas) to automate much of the profiling and cleaning. The exam focuses on understanding the concepts and knowing which technique to apply in which situation, not on writing code.

Let's walk through a realistic scenario at a mid-sized retail company called 'ShopSmart'. The marketing team wants to send a promotional email to all customers who have made a purchase in the last six months. They ask you, the data analyst, to prepare the list. Here is what you actually do step by step.

First, you receive a raw export from the company's sales database. The file is a CSV (comma-separated values) with columns like CustomerName, Email, PurchaseDate, TotalAmount, and City. You open it in a spreadsheet tool. This is where profiling begins.

You start by checking the total number of rows. There are 12,000 rows, but you notice some rows are completely blank. That's a red flag. You use a pivot table or a profiling function to count how many emails are missing. The result: 450 rows have no email address. You also spot that the Email column contains duplicates: some customers appear twice because they bought items on separate days, and the system created a new record each time.

Now you move to cleaning. You have to decide what to do with the 450 missing emails. Because you need an email to send the promotion, you decide to delete those rows entirely (deletion by listwise). Next, you handle duplicates. You use a 'Remove Duplicates' tool, which keeps only the most recent purchase for each customer. After deletion, you have 10,200 unique customers. But then you check the City column and find that 'New York' is written as 'NY', 'New York City', and 'NYC'. You standardise all of them to 'New York' using a find-and-replace function.

Finally, preparation. The marketing team wants the list sorted alphabetically by last name. Your data has a 'CustomerName' column with full names like 'John Smith'. You split this into two new columns: 'FirstName' and 'LastName'. Then you sort the entire table by LastName. You also create a new column called 'Segment' that categorises customers based on their TotalAmount: 'High Spender' if over £500, 'Medium' if between £100 and £500, and 'Low' if under £100. This is a calculated field.

The final step is to save the cleaned and prepared data as a new CSV file, which you send to the marketing team. They import it into their email tool. Because you cleaned and prepared it, every email address is valid, nobody gets duplicate emails, and the team can easily target high spenders. If you had skipped these steps, the marketing team might have sent 450 emails to blank addresses (bouncing), annoyed customers with duplicate messages, and been unable to segment their audience.