Skip to main content
Loading Explore guides…
Data Cleaning Framework · Acedra
Home
Explore
Computer Science
Data Cleaning Framework
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
58 segments
Copy
Hide video
0:00
Welcome back to our Portfolio Playbook series, where I'm guiding you step-by-step through creating a job-ready portfolio project. Today's video is episode three, and it's going to be all about data cleaning. If you're an aspiring data analyst and you've ever wondered, how do I make sure that my data is actually clean enough? How do real data analysts actually clean data on the job? And how do I deal with things like missing values or nonsensical
0:19
values or know when I should actually impute values? Then this video is going to be very eye-opening for you. We're going to be talking about the five-step framework that I've used as a data director and a lead data analyst to clean data the way that we do on the job. And yes, we will be using a business relevant data set, which you can download below to follow along in this series. If you're new here, I'm Christine. I'm a former data director and a hiring manager,
0:40
and now a data mentor who takes people on the most direct path to working in data. And on this channel, I share the strategies, the frameworks, and the mindset tips to help you stand out as a data analyst. Let's dive in.
0:53
So my five-step framework for the cleaning process is called clean. Should be easy enough It stands for conceptualize the data, locate solvable issues, evaluate unsolvable issues, augment the data, and then note and document. Now, a lot of early career data analysts will ask, how do I know when my data is perfect? And the answer is, you don't. The data cleaning philosophy is that you're not aiming for perfect data.
1:13
You're aiming to get to a place where your data is good enough to analyze, share, and iterate on. You can think of data cleaning like peeling an onion. The first layer is just getting rid of the obvious dirty stuff. The second layer is then where you start to polish it and standardize it. And then the third layer is where you refine and revisit the underlying data after you've done some initial analysis.
1:34
For this series, we'll be working with a dataset from a sample company called GameZone. And yes, it is a fictional company, but the dataset is very realistic given its business relevance, its metrics, dimensions, and its messiness, which we're going to see in a sec. For some background, the company was founded in 2018, and they sell new and refurbished gaming products all around the world.
1:51
They mostly sell through their online website, but they do also have a mobile app and they use a variety of different marketing channels to find their customers. I've provided a sample data set below, which you can access by signing up for the newsletter, and it is 20k records. Now, remember, ideally you would find a data set that is 50k records, which I talk about more in episode two of this series.
2:10
But if you are not able to find a data set that is larger or you just want to make sure that you can go ahead and get started, then feel free to download this one to use it as practice as we follow along in this playbook. If you're applying to jobs in tech, SaaS, product, operations, logistics, honestly, any company that sells a product, then this data set is going to be gold for you because it's going
2:29
to be very representative of the kind of data you'll be using in those jobs. But even if you're not, then just pay a little bit less attention to the column names and pay more attention to the actual thought process because the thinking that we're going to be using applies to every industry.
2:44
Before you actually dive into the data cleaning process, thoughtful data analysts take a step back and ask themselves, what am I actually looking at here? And what story can this data help me tell? In order to understand and conceptualize the data, you first wanna identify three main things. First, what is the grain of the table or what does each row actually represent? The second is what are the key metrics? And then the third is one of the key dimensions.
3:05
So in our case, each row is a unique order. I can see that the identifier is the order ID. And if I wanted to tell the story of one example record, I would say that user TC0 placed an order on December 24th, 2020 that got shipped out two days later. It was a Nintendo Switch that was bought for almost $170 and it was purchased on the website. And the user came to GameZone through affiliate marketing and we don't actually know how their account was created,
3:31
but they're located in the United States. The key metric here is the US dollar price. The key dimensions here are going to be time. So when it was purchased, when it was shipped, the product name, and then also these other attributes over here. So the purchase platform, the marketing channel, the account creation method, and the country code. This is the first step to identifying what you actually need to care about and prioritize in your data cleaning process.
3:52
Also, even better if you know the actual business questions being asked. So in this case, let's say I had a stakeholder question that was trying to understand overall sales trends across a variety of different regions. Then I would know that my key metric is USD price and my key dimension is going to be anything having to do with geographic location.
4:14
Now, the next step in the data cleaning process is to start identifying and addressing solvable issues. Solvable issues usually fall into a few main categories that could be inconsistent data formats, inconsistent spellings or categorizations. Nulls are sometimes solvable, sometimes not. We're going to talk about that in a sec. And duplicates. Solvable issues are problems with the data set that you usually can solve on your own without needing to go to a
4:36
stakeholder for more business context. So in our data set, I'm going to do a few things for this first passed. First, I'm going to just eyeball the data just to make sure that there's nothing glaringly obvious. Then I'm going to use filters to start doing some initial checks on the distinct values for every single column. As I go through, I'm going to be documenting all the issues that I come across and I'm going to start forming an issues log, which is going to be very helpful for
4:57
us in tracking all the issues that we resolve or we don't. Now I'm noticing here that there are dates that are in some weird format so i'm actually just going to filter down to those dates and start identifying the magnitude of each of these issues
6:09
so Now, something interesting that I'm noticing here is that in the USD price column, if I filter and look at the distinct values, I'm seeing that there are some $0 transactions and there are some values where there are blanks. According to my business logic and my business intuition, there shouldn't really be any transactions that are $0. So I'm going to flag this one and note it in our issues log for later.
6:45
in our marketing channel column it looks like we have a bunch of blanks but we do also have another category called unknown so that might be a hint for how we want to resolve this issue later on
7:07
i find a little bit strange the marketing channel and the account creation method have the same exact number of blanks and it seems like the rows that have blanks in one also have blanks in another so these two might actually be related to each other now again i don't really have the business context to be able to solve this right now so i just noted it in the issue log and it might be something that I investigate later on. So I also have a regional
7:37
lookup table here which is just going to help me map my country code to the corresponding region. Let's take a look at what this table has. Because there aren't a lot of records in this table this one looks pretty manageable so I'm just going to group this together as one main data issue just saying inconsistent or nonsensical values.
8:01
Notice how in this first pass I'm just going through each of the columns and just doing some initial sanity checks. I'm not yet getting really fancy with any business logic or thinking about all the what-if situations. Now I also have an issues log that documents not only what are all the things that I need to address but also the overall magnitude and that is what helps me prioritize which one of these I actually need to focus on. Let's get on the list and see what we
8:23
can solve. So purchase timestamp, inconsistent date formats. Now this should be something that we should be able to solve because we have the actual data here. We just need to reformat it. So I'm going to say yes, it's solvable and we're going to address it in a second. Now for the missing dates, if the date is null, I don't really have a way of inferring it because I don't really have another source of truth here. So I'm going to say no for right now and come back to this in a
8:44
second. Inconsistent spelling between the two different kinds of products. Again, this should that we should be able to fix so i'm going to mark it down as a yes and then usd price missing or zero dollar transactions now right now off the top of my head i don't have another way to figure out what the zero dollar transaction should have been now missing marketing channel and missing account creation method if you guys remember even though there are blanks there's also another category
9:08
here called unknown and so yes it is soluble on the data layer because we can impute it with the right category which is just that we don't know what the category should actually be so we're we're going to mark it as unknown. And then for missing country code, this is not something that I have enough information on to impute right now. So again, I'm also going to say no and address this later. Lastly, for the inconsistent or nonsensical values in the regional lookup table,
9:30
if you think about it, these are solvable because this has a source of truth. It's just whatever region the country actually belongs in. And so that's something that we can actually look up. So I'm going to say, yes, this is solvable. So now I'm going to actually work through the process of cleaning these columns. And you'll notice a few best practices that I do. Number one, I create a new tab where I replicate the original data so that I don't overwrite anything and I
9:51
always have the original data to go back to. Because if a stakeholder ever asks you to explain more of the background process, you always want to be able to have a paper trail. The second is that instead of replacing a column in line, I'm going to add another column to the right of it with underscore cleaned. And that shows me that it's the new version of that column. And I'm not going to replace the original column with the clean version until I'm super confident that it's
10:13
actually doing what I want it to.
11:28
So for the purchase timestamp, the clean version uses a date function to parse the year, the month and the day and put together a new version of the date so that it gets formatted correctly. When I check this, I can see that everything has been grouped correctly now. So Excel is actually recognizing them all as the same date format. Now the blank, I'm just going to leave it in there because I don't have a correct way of imputing it for now. And it's only one record,
11:49
so it doesn't really do much harm. Then when I'm looking at product name, there's a few different ways of doing this. Of course, I could have manually just replaced and overwritten the
11:58
category here, the 27 inches 4K gaming monitor, and just renamed it to this label over here. But I went a slightly more formulaic way using an if statement. This is just like a case win statement in sql if the value to the right is spelled this way then just replace it with this spelling otherwise keep the original i actually did the same thing for the marketing channel and the account creation method as well where i said if it's blank then replace it with unknown for both
12:25
of them now one thing i also want to check on is duplicates there's a few ways to do this but i'm going to use a pivot table to actually count the number of orders that have maybe more than one of of the same order ID. I can already see in my pivot table that there are a bunch of orders that actually have more than one user ID. So what I'm going to do is actually filter down to all the places where there are more than two user IDs per order ID. And then I'm going to start doing some
12:56
spot checks in the original data to see what these duplicates actually look like.
13:18
So according to this spot check, there are two different customers assigned to this one order ID that does look to be an exact duplicate because everything else is the same. I'm going to do a few more spot checks just to make sure it's the same pattern across the board. So because there's only 145 duplicates and that's out of 20,000 records, the impact is probably going to be so small that it's not really going to affect the analysis. So in this case, I'm actually going to leave the
13:54
duplicates in and just make sure that I document this because I want to make sure that before I delete them, I actually have full business context and clarify that these genuinely are records that we can delete. I also almost forgot to clean the regions table. So I'm going to go to the region table and actually replace the nonsensical values with the sensical values by looking up what the country and the region should be.
14:39
So now I have my clean regions table and I can actually use that lookup when I then go to add the region to the orders table. So this first pass is all about restoring the values where you can, identifying and solving the low risk issues. This is usually things like data formats, inconsistent spellings and categorizations, and in some cases, nulls where you know what the underlying value should be. Unsolvable issues usually belong to a few main categories.
15:04
The first is having missing data where you actually cannot infer what the value should be. The second is having outliers or anomalous values that may or may not represent real events, but you're not really sure. And the third is having business logic violations. For example, having a ship date after the purchase timestamp. Now on the job, you might need to actually refer to a stakeholder team who has more business context or a technical team like engineering who can help you
15:27
understand where these data issues are coming from. In your portfolio projects, when you bump into these challenges, most of the time you want to document and evaluate the magnitude of the issues and just make sure you're surfacing it in your insights. In my issues log, I'm seeing that I noted the missing date of purchase timestamp to be unsolvable. But then again, because the magnitude is so small, I'm just going to leave it as is. Usually if the magnitude is like less than 20%,
15:50
it's a good rule of thumb that you can just document it and leave it as is and continue along with your analysis. Let's see, what else do I have? Missing or $0 transactions in USD price. Now, this is unsolvable right now because I don't have another reference table to actually go and find what the right value should be. You could do something like imputation where you actually fill in the missing values with something like the average price of the product that was purchased.
16:14
However, I will say as data analysts, we very suddenly actually employ imputation on the job. If you think about it, our job is a little bit different from data scientists who rely a little bit more on data completeness. For us, our job is to service the truth of what the data is actually saying. If you impute the data, you run the risk of actually introducing bias. You only want to impute data if you have a reliable other source of truth, if you have
16:38
clear business logic that would actually give you confidence in inferring what that value should be, or if for whatever reason you really care more about data completeness than data accuracy, then you might also be able to fill in with a representative sample of what those values should be. Now, the other category of unsolvable issues of outliers, where you don't actually know whether or not this is a true event or a data issue, usually outliers or anomalous values
17:00
will become clear in the actual analysis where you actually start looking at the data, visualizing data, and you'll very naturally see, okay, there's one value that's completely out of bounds. So outliers is typically not something I really look for in the data cleaning process, but we could do a quick sanity check where we just sort by descending order, look at the top values, look at the lowest values, and see if those line up with our sanity checks.
17:22
Now, if we did find outliers in analysis, what do you think would be the right side to take here? This is where we actually have a similar best practice to imputation, where you don't necessarily need to delete the outliers. I mean, if it's a real event, you obviously would want to see that value, even if it skews the results or skews the overall graph. In terms of business logic checks, this is where I would actually want to do some setting checks
17:42
on my dataset to make sure that the relationships between the columns also make sense. This requires a little bit of domain knowledge. So for example, let's look at a purchase timestamp and the ship timestamp. Now, my common sense says that the ship timestamp should always be after the purchase timestamp. So that might be something that we would want to check. So now all I'm going to do is implement a logical check to see the number of ship timestamps that might be after a purchase timestamp.
18:37
before the purchase timestamp. So if I filter to that, just do a few spot checks. So some of them, for some reason, there's like a few months before the purchase timestamp. Some of them are just a few days before the purchase timestamp. So there's definitely some wonkiness here. Let's actually look at the magnitude of this issue. Okay. So this is where we are getting our first chunk of actually a pretty high magnitude issue. It's actually 2000 records out of our 20,000 or so record data set.
19:03
so that's like a little bit less than 10 now it's the same thought process before i don't really have a good way to actually update these nonsensical values if this is all the data that i have so the best case is for me to document it and just surface it i'm not going to delete the records because you don't want to just get rid of 20 of your actual data set however keep in mind that the ship timestamp isn't really one of our key dimensions we're probably much more concerned
19:29
with the actual product, the geographic region, and like the customer channels than the day that it was shipped. If, however, a business stakeholder was asking us specifically about the time to ship, for example, then we would need to pay a little bit more attention to this column and investigate what we can do. So to summarize how to take care of unsolvable issues, most of the time you just want to document them, note down the magnitude, and also note down if there are any other teams
19:51
that you need to talk to to investigate this further. In your portfolio projects, the main point is that you've documented this and that it's somewhere in your project write-up for transparency.
20:04
see. So now we go from cleanup to enhancement and this is where you start thinking how can I make my data set more flexible and more robust. This is normally so that you can slice and dice by different time grains or that you can segment by more dimensions or maybe even calculate more nuanced metrics using the existing metrics in the data set. So in this case for example I could slice my purchase timestamp into the weeks, into the months, into the years. I could do the same for
20:26
ship timestamp but again it's not really one of our key dimensions so i'm not going to focus on it um i could also calculate something like the time to ship now time to ship is an operational metric that a lot of different companies that sell physical products actually uses so maybe i'll do that where i calculate the difference in days between the ship timestamp and the purchase timestamp knowing that for some of them the values will actually be negative because the ship timestamp
20:48
is before the purchase timestamp let's see what else i could do i could also bring in the actual region from my lookup table, bring in the clean region so that I can see for every single purchase what region it was actually purchased in. And of course if I actually had a user or customer table to reference then maybe I could pull in more demographic information like their street address, their state, or any other information that was collected when they sign up for an account. So
21:12
I'm going to go ahead and add in new time greens, calculate the purchase to ship time, and also bring bring in the region. Now note that all these values look funky that's because Excel is registering them as dates so I'm just gonna use number forming to correct them and turn them into values. And then one of my favorite tricks if I double-click on this little thing right here then it's going to drag the formula through all the way to the bottom. I'm then just gonna do some setting checks
21:46
and see what values I've ended up with. So the purchase years, there's one that's probably the blank, 1900. That means that the blank is just being recorded as a nonsensical value. So I'm gonna go ahead and delete that. Again, we don't know what the purchase month is, so I'm gonna do ahead and delete that. That's again, because Excel is just interpreting the blank as a certain value. And then for the time to ship, we also know that this doesn't actually have a time to ship. So remove that.
22:20
In this step, I've thought about adding categorical variables, more nuanced and calculated metrics, and also additional time grains to make my dataset more robust and dimensional.
22:32
So now that I've also built the first draft of my issues log along the process, I'm now to go back and finalize it by adding in some notes about the resolution and my final decisions about each of those columns as well as carrying the percent magnitude that was impacted for the data issues that i found this not only serves as a paper trail for you but also demonstrates to hiring managers and other people that you work with your thoughtfulness as a data analyst
23:46
so overall you can see that our data issues were relatively low in magnitude except for this one at the bottom but in general the thought process applies even if the magnitude was higher once you get to a point where a column has more than 70 percent of messed up data then that means that column is probably actually unusable so ultimately the full data cleaning process looks like this we understand the data set by identifying the grain metrics and dimensions and making sure that
24:10
we can tell the story of an example record in the data we locate solvable issues by conducting a first pass of cleaning and deal with what we can in that process and then we evaluate the unsolvable issues where we're documenting what we're finding and referring to another team if possible otherwise just leaving it as is and making those issues transparent then we're augmenting the data by about what other information is available to us that we could use to make our dataset even more interesting.
24:35
And then finally, we're noting and documenting everything and tying it all up with an issues log. I'm very excited for the next video in this series where we're gonna be diving into some exploratory analysis and the true analytics lifecycle to start looking at some stakeholder relevant questions. This week's interactive comment is, what is it about the exploratory data analysis process that you find the most challenging? I'm gonna take your notes into account
24:57
as I film the next video in the series. And if you found this helpful and you want to take your projects to the next level with me, check out my mentorship program below and I'll see you in the next one.
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat