Skip to main content
Loading Explore guides…
Dirty Data vs Clean Data · Acedra
Home
Explore
Computer Science
Dirty Data vs Clean Data
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
155 segments
Copy
Hide video
0:04
SPEAKER: Can you guess what inaccurate or bad data costs businesses every year? Thousands of dollars, millions, billions? Well, according to IBM, the yearly cost of poor quality data is $3.1 trillion in the US alone. That's a lot of zeros. Now can you guess the number one cause of poor quality data? It's not a new system implementation or a computer technical glitch. The most common factor is actually human error. Here's a spreadsheet from a law office.
0:41
It shows customers, the legal services they bought, the service order number, how much they paid, and the payment method. Dirty data can be the result of someone typing in a piece of data incorrectly, inconsistent formatting, blank fields, or the same piece of data being entered more than once, which causes duplicates.
1:15
Dirty data is data that's incomplete, incorrect, or irrelevant to the problem you're trying to solve. When you work with dirty data, you can't be sure that your results are correct. In fact, you can pretty much bet they won't be. Earlier, you learned that data integrity is critical to reliable data analytics results. And clean data helps you achieve data integrity. Clean data is data that's complete, correct, and relevant to the problem you're trying to solve.
1:45
When you work with clean data, you'll find that your projects go much more smoothly. I remember the first time I witnessed first hand how important clean data really is. I had just started using SQL. And I thought it worked like magic. I could have the computer sum up millions of numbers, saving me tons of time and effort. But I quickly discovered that only works when the data is clean. If there was even one accidental letter in a column that should only have numbers,
2:12
the computer wouldn't know what to do so it would throw an error and suddenly I was stuck. And there's no way I can add up millions of numbers by myself so I had to clean up that data to make it work. The good news is that there's plenty of effective processes and tools to help you do that. Coming up, you'll gain the skills and knowledge you need to make sure the data you work with is always clean. Along the way, we'll dig deeper into the difference
2:39
between clean and dirty data and why clean data is so important. We'll also talk about different ways to clean your data and common problems to look for during the process. Ready to start? Let's do it.
2:57
Clean data is incredibly important for effective analysis. If a piece of data is entered into a spreadsheet or database incorrectly, or if it's repeated, or if a field is left blank, or if data formats are inconsistent, the result is dirty data. Small mistakes can lead to big consequences in the long run. Now I'll be completely honest with you. Data cleaning is like brushing your teeth. It's something you should do and do properly, because otherwise it can cause serious problems.
3:29
For teeth, that might be cavities or gum disease. For data, that might be costing your company money or an angry boss. But here's the good news. If you keep brushing twice a day every day, it becomes a habit. Soon you don't even have to think about it. And it's the same with data. Trust me. It'll make you look great when you take the time to clean up that dirty data. As a quick refresher, dirty data is incomplete, incorrect, or irrelevant to the problem you're trying to solve.
4:01
It can't be used in a meaningful way, which makes analysis very difficult, if not impossible. On the other hand, clean data is complete, correct, and relevant to the problem you're trying to solve. This allows you to understand and analyze the information and identify important patterns, connect related information, and draw useful conclusions. Then you can apply what you learned to make effective decisions. In some cases, you won't have to do a lot of work to clean data.
4:33
For example, when you use internal data that's been verified and cared for by your company's data engineers and data warehouse team, it's more likely to be clean. Let's talk about some people you'll work with as a data analyst. Data engineers transform data into a useful format for analysis and give it a reliable infrastructure. This means they develop, maintain, and test databases, data processors, and related systems. Data warehousing specialists develop
5:06
processes and procedures to effectively store and organize data. They make sure that data is available, secure, and backed up to prevent loss. When you become a data analyst, you can learn a lot by working with the person who maintains your databases to learn about their systems. If data passes through the hands of a data engineer or a data warehousing specialist first, you know you're off to a good start on your project. There's a lot of great career opportunities
5:35
as a data engineer or a data warehousing specialist. If this kind of work sounds interesting to you, maybe your career path will involve helping organizations save lots of time, effort, and money by making sure their data is sparkling clean. But even if you go in a different direction with your data analytics career and have the advantage of working with data engineers and warehousing specialists, you're still likely to have to clean your own data.
6:00
And it's important to remember no data set is perfect. It's always a good idea to examine and clean data before beginning analysis. Here's an example. Let's say you're working on a project where you need to figure out how many people use your company's software program. You have a spreadsheet that was created internally and verified by a data engineer and a data warehousing specialist. Check out the column labeled username. Now it might seem logical that you can just
6:30
scroll down and count the rows to figure out how many users you have.
6:37
But that won't work because one person sometimes has more than one username.
6:46
Maybe they registered from different email addresses or maybe they have a work and personal account. In situations like this, you would need to clean the data by eliminating any rows that are duplicates.
7:03
Once you've done that, there won't be any more duplicate entries. Then your spreadsheet's ready to be put to work. OK. So far we've discussed working with internal data, but data cleaning becomes even more important when working with external data, especially if it comes from multiple sources. Let's say the software company from our example surveyed its customers to learn how satisfied they are with its software product. But when you review the survey data, you find that you have several nulls.
7:37
A null is an indication that a value does not exist in a data set. Note that it's not the same as zero. In the case of our survey, a null would mean the customer skipped that question. A zero would mean they provided zero as their response. To do your analysis, you would first need to clean this data step one would be to decide what to do with those nulls. You could either filter them out and communicate that you now have a smaller sample size or you can keep them in and learn from the fact
8:08
that the customers did not provide responses. There's lots of reasons why this could have happened. Maybe your survey questions weren't written as well as they could be. Maybe they were confusing or biased, something we learned about earlier. OK. We've touched on the basics of cleaning internal and external data, but there's lots more to come. Soon we'll learn about the common errors to be aware of to ensure your data is complete, correct, and relevant. See you soon.
8:42
In this video, we'll focus on common issues associated with dirty data. These include spelling and other text errors; inconsistent labels, formats, and field length; missing data; and duplicates. This will help you recognize problems quicker and give you the information you need to fix them when you encounter something similar during your own analysis. This is incredibly important in data analytics. OK. Let's go back to our law office spreadsheet. As a quick refresher, we'll start
9:11
by checking out the different types of dirty data it shows. Sometimes someone might key in a piece of data incorrectly. Other times they might not keep data formats consistent.
9:27
It's also common to leave a field blank.
9:32
That's also called null, which we learned about earlier. And if someone adds the same piece of data more than once, that creates a duplicate.
9:45
So let's break that down, then we'll learn about a few other types of dirty data and strategies for cleaning it. Misspellings, spelling variations, mixed up letters, inconsistent punctuation, and typos in general happen when someone types in a piece of data incorrectly. As a data analyst, you'll also deal with different currencies. For example, one data set could be in US dollars and another in euros. And you don't want to get them mixed up.
10:15
We want to find these types of errors and fix them like this.
10:22
You'll learn more about this soon. Clean data depends largely on the data integrity rules that an organization follows, such as spelling and punctuation guidelines. For example, a beverage company might ask everyone working in its database to enter data about volume in fluid ounces instead of cups. It's great when an organization has rules like this in place. It really helps minimize the amount of data cleaning required, but it can't eliminate it completely.
10:53
Like we discussed earlier, there's always the possibility of human error. The next type of dirty data our spreadsheet shows is inconsistent formatting. In this example, something that should be formatted as currency is shown as a percentage. Until this error is fixed like this, the law office will have no idea how much money this customer paid for its services. We'll learn about different ways to solve this and many other problems soon. We discussed nulls previously.
11:27
But as a reminder, nulls are empty fields.
11:33
This kind of dirty data requires a little more work than just fixing a spelling error or changing a format. In this example, the data analysts would need to research which customer had a consultation on July 4, 2020. Then when they find the correct information, they'd have to add it to the spreadsheet.
12:00
Another common type of dirty data is duplicate.
12:08
Maybe two different people added this appointment on August 13, not realizing that someone else had already done it, or maybe the person entering the data hit Copy and Paste by accident. Whatever the reason, it's the data analyst's job to identify this error and correct it by deleting one of the duplicates.
12:32
OK. Now let's continue on to some other types of dirty data. The first has to do with labeling. To understand labeling, imagine trying to get a computer to correctly identify panda bears among images of all different kinds of animals. You need to show the computer thousands of images of panda bears. They're all labeled as panda bears. Any incorrectly labeled picture like the one here that's just bear will cause a problem. The next type of dirty data is having an inconsistent field length.
13:05
You learned earlier that a field is a single piece of information from a row or column of a spreadsheet. Field length is a tool for determining how many characters can be keyed into a field. Assigning a certain length to the fields in your spreadsheet is a great way to avoid errors. For instance, if you have a column for someone's birth year, you know the field length is four because all years are four digits long. Some spreadsheet applications have a simple way to specify field lengths and make
13:35
sure users can only enter a certain number of characters into a field. This is part of data validation. Data validation is a tool for checking the accuracy and quality of data before adding or importing it. Data validation is a form of data cleansing, which you'll learn more about soon. But first, you'll get familiar with more techniques for cleaning data. This is a very important part of the data analyst job and I look forward to sharing these data cleaning strategies with you.
14:08
Now that you're familiar with some of the most common types of dirty data, it's time to clean them up. As you've learned, clean data is essential to data integrity and reliable solutions and decisions. The good news is that spreadsheets have all kinds of tools you can use to get your data ready for analysis. The techniques for data cleaning will be different depending on the specific data set you're working with so we won't cover everything you might run into.
14:35
But this will give you a great starting point for fixing the types of dirty data analysts find most often. Think of everything that's coming up as a teaser trailer of data cleaning tools. I'm going to give you a basic overview of some common tools and techniques and then we'll practice them again later on. Here we'll discuss how to remove unwanted data, clean up text to remove extra spaces and blanks, fix typos, and make formatting consistent. However, before removing unwanted data,
15:07
it's always a good practice to make a copy of the data set. That way if you remove something that you end up needing in the future, you can easily access it and put it back in the data set. Once that's done, then you can move on to getting rid of the duplicates or data that isn't relevant to the problem you're trying to solve. Typically, duplicates appear when you're combining data sets from more than one source or using data from multiple departments within the same business.
15:37
You've already learned a bit about duplicates, but let's practice removing them once more now using this spreadsheet, which lists members of a professional logistics association. Duplicates can be a big problem for data analysts so it's really important that you can find and remove them before any analysis starts. Here's an example of what I'm talking about.
16:07
Let's say this association has duplicates of one person's $500 membership in its database.
16:15
When the data is summarized, the analysts would think there was $1,000 being paid by this member and would make decisions based on that incorrect data. But in reality, this member only paid $500. These problems can be fixed manually but most spreadsheet applications also offer lots of tools to help you find and remove duplicates.
16:42
Now irrelevant data, which is data that doesn't fit the specific problem that you're trying to solve, also needs to be removed. Going back to our association membership list example, let's say a data analyst was working on a project that focused only on current members. They wouldn't want to include information on people who are no longer members or who never joined in the first place.
17:18
Removing irrelevant data takes a little more time and effort because you have to figure out the difference between the data you need and the data you don't. But believe me, making those decisions will save you a ton of effort down the road. The next step is removing extra spaces and blanks. Extra spaces can cause unexpected results when you sort, filter, or search through your data. And because these characters are easy to miss, they can lead to unexpected and confusing results.
17:47
For example, if there's an extra space in a member ID number, when you sort the column from lowest to highest this row will be out of place.
18:03
To remove these unwanted spaces or blank cells you can delete them yourself or again you can rely on your spreadsheets, which offer lots of great functions for removing spaces and blanks automatically. The next data cleaning step involves fixing misspellings, inconsistent capitalization, incorrect punctuation, and other typos. These types of errors can lead to some big problems. Let's say you have a database of emails that you use to keep in touch with your customers.
18:39
If some emails have misspellings, a period in the wrong place, or any other kind of typo, not only do you run the risk of sending an email to the wrong people, you also run the risk of spamming random people. Think about our association membership example again. Misspelling might cause the data analyst to miscount the number of professional members if they sorted this membership type and then counted the number of rows.
19:13
Like the other problems we've come across, you can also fix these problems manually or you can use spreadsheet tools such as Spell Check, Autocorrect, and conditional formatting to make your life easier. There's also easy ways to convert text to lowercase, uppercase, or proper case, which is one of the things we'll check out again later. All right. We're getting there. The next step is removing formatting. This is particularly important when you get data from lots of different sources.
19:47
Every database has its own formatting, which can cause the data to seem inconsistent. Creating a clean and consistent visual appearance for your spreadsheets will help make it a valuable tool for you and your team when making key decisions. Most spreadsheet applications also have a Clear Formats tool, which is a great time saver. Cleaning data is an essential step in increasing the quality of your data. Now you know lots of different ways to do that.
20:16
In the next video, you'll take that knowledge even further and learn how to clean up data that's come from more than one source.
20:27
So far, you learned a lot about dirty data and how to clean up the most common errors in the data set. Now we're going to take that a step further and talk about cleaning up multiple data sets. Cleaning data that comes from two or more sources is very common for data analysts but it does come with some interesting challenges. A good example is a merger, which is an agreement that unites two organizations into a single new one. In the logistics field, there's been
20:56
lots of big changes recently, mostly because of the ecommerce boom. With so many people shopping online, it makes sense that the companies responsible for delivering these products to their homes are in the middle of a big shakeup. When big things happen in an industry, it's common for two organizations to team up and become stronger through a merger. Let's talk about how that'll affect our logistics association. As a quick reminder, this spreadsheet
21:24
lists association member ID numbers, first and last names, addresses, how much each member pays in dues, when the membership expires, and the membership types. Now let's think about what would happen if the International Logistics Association decided to get together with the Global Logistics Association in order to help their members handle the incredible demands of ecommerce. First, all the data from each organization would need to be combined using data merging.
21:59
Data merging is the process of combining two or more data sets into a single data set. This presents a unique challenge because, when two totally different data sets are combined, the information is almost guaranteed to be inconsistent and misaligned. For example, the Global Logistics Association spreadsheet has a separate column for a person's suite, apartment, or unit number. But the International Logistics Association combines that information with their street address.
22:33
This needs to be corrected to make the number of address columns consistent. Next, check out how the Global Logistics Association uses people's email addresses as their member ID while the International Logistics Association uses numbers. This is a big problem because people in a certain industry such as logistics typically join multiple professional associations. So there's a very good chance that these data sets include membership information on the exact same person just in different ways.
23:12
It's super important to remove those duplicates. Also, the Global Logistics Association has many more member types than the other organization.
23:25
On top of that, it uses the term young professional instead of student associate.
23:35
But both describe members who are still in school or just starting their careers. If you were merging these two data sets, you need to work with your team to fix the fact that the two associations describe memberships very differently. Now you understand why the merging of organizations also requires the merging of data. And that can be tricky. But there's lots of other reasons why data analysts merge data sets. For example, in one of my past jobs, I merged a lot of data
24:05
from multiple sources to get insights about our customers' purchases. The kinds of insights I gained helped me identify customer buying patterns. When merging data sets, I always begin by asking myself some key questions to help me avoid redundancy and to confirm that the data sets are compatible. In data analytics, compatibility describes how well two or more data sets are able to work together. So the first question I would ask is, do I have all the data I need?
24:36
To gather customer purchase insights, I wanted to make sure I had data on customers, their purchases, and where they shopped. Next, I would ask, does the data I need exist within these data sets? As you learned earlier in this program, this involves considering the entire data set analytically. Looking through the data before I start using it lets me get a feel for what it's all about, what the schema looks like, if it's relevant to my customer purchase insights, and if it's clean data.
25:07
That brings me to the next question. Do the data sets need to be cleaned or are they ready for me to use? And because I'm working with more than one source, I'll also ask myself, are the data sets cleaned to the same standard? For example, what fields are regularly repeated? How are missing values handled? How recently was the data updated? Finding the answers to these questions and understanding if I need to fix any problems at the start of a project is a very important step in data merging.
25:38
In both the examples we explored here, a data analyst could use either the spreadsheet tools or SQL queries to clean up, merge, and prepare the data sets for analysis. Depending on the tool you decide to use, the cleanup process can be simple or very complex. Soon you'll learn how to make the best choice for your situation. And as a final note, programming languages like R are also very useful for cleaning data. You'll learn more about how to use R and the other concepts we covered soon.
26:14
As you learned earlier, there's a lot of different ways to clean up data. I've shown you some examples of how you can clean data manually, such as searching for and fixing misspellings, or removing empty spaces and duplicates. We also learned that lots of spreadsheet applications have tools that help simplify and speed up the data cleaning process. There's a lot of great efficiency tools that data analysts use all the time, such as conditional formatting, removing duplicates, formatting
26:42
dates, fixing text strings and substrings, and splitting text to columns. We'll explore those in more detail now. The first is something called conditional formatting. Conditional formatting is a spreadsheet tool that changes how cells appear when values meet specific conditions. Likewise, it can let you know when a cell does not meet the conditions you've set. Visual cues like this are very useful for data analysts, especially when we're working in a large spreadsheet with lots of data.
27:13
Making certain data points stand out makes the information easier to understand and analyze. And for cleaning data, knowing when the data doesn't follow the condition is very helpful. Let's return to the Logistics Association spreadsheet to check out conditional formatting in action. We'll use conditional formatting to highlight blank cells. That way we know where there's missing information so we can add it to the spreadsheet. To do this, we'll start by selecting the range we want to search.
27:42
For this example, we're not focused on address three and address five so the fields will include all the columns in our spreadsheets except for F and H. Next, we'll go to Format and choose Conditional Formatting.
28:15
Great. Our range is automatically indicated in the field. The format rule will be to format cells if the cell is empty.
28:28
Finally, we'll choose the formatting style. I'm going to pick a shade of bright pink so my blanks really stand out.
28:39
Then click Done. And the blank cells are instantly highlighted. OK. The next spreadsheet tool removes duplicates. As you've learned before, it's always smart to make a copy of the data set before removing anything so let's do that now.
29:01
Great. Now we can continue. You might remember that our example spreadsheet has one association member listed twice.
29:15
To fix that, go to Data and select Remove Duplicates. Remove Duplicates is a tool that automatically searches for and eliminates duplicate entries from a spreadsheet. Choose data has a header row because our spreadsheet has a row at the very top that describes the contents of each column. Next, select all because we want to inspect our entire spreadsheet. Finally, remove duplicates.
29:50
You'll notice the duplicate robe was found and immediately removed.
29:58
Another useful spreadsheet tool enables you to make formats consistent. For example, some of the dates in the spreadsheet aren't in a standard date format.
30:13
This could be confusing if you wanted to analyze when association members joined, how often they renewed their memberships, or how long they've been with the association. To make all of our dates consistent, first select column J. Then go to Format, Select Number, then Date. Now all of our dates have a consistent format. Before we go over the next tool, I want to explain what a text string is. In data analytics a texturing is a group of characters within a cell most often composed of letters.
30:52
An important characteristic of a text string is its length, which is the number of characters in it. You'll learn more about that soon. For now, it's also useful to know that a substring is a smaller subset of a text string. OK. Now let's talk about Split. Split is a tool that divides a text string around a specified character and puts each fragment into a new and separate cell. Split is helpful when you have more than one piece of data in a cell and you want to separate them out.
31:24
This might be a person's first and last name listed together or it could be a cell that contains someone city, state, country, and zip code but you actually want each of those in its own column. So let's say this association wanted to analyze all of the different professional certifications its members have earned. To do this, you want each certification separated out into its own column. Right now, the certifications are separated by a comma.
31:57
That's the specified text separating each item, also called the delimiter. Let's get them separated. Highlight the column, then select Data, and Split Texts to Columns.
32:15
This spreadsheet application automatically knew that the comma was the delimiter and separated each certification, but sometimes you might need to specify what the delimiter should be. You can do that here.
32:31
Split Text to Columns is also helpful for fixing instances of numbers stored as text. Sometimes values in your spreadsheet will seem like numbers, but they're formatted as text. This can happen when copy and pasting from one place to another or if the formatting is wrong. For this example, let's check out a new spreadsheet from a cosmetics maker. If a data analyst wanted to determine total profits, they could add up everything in column F, but there's a problem. One of the cells has an error.
33:10
If you check into it, you'll learn that the 707 in this cell is text and can't be changed into a number. So when the spreadsheet tries to multiply the cost of the product by the number of units sold, it's unable to make the calculation. But if we select the Orders column and choose Split Text to Columns, the error is resolved because now it can be treated as a number. Coming up, you'll learn about a tool that does just the opposite. Concatenate is a function that joins multiple text strings
33:52
into a single string. Spreadsheets are a very important part of data analytics. They save data analysts time and effort and help us eliminate errors each and every day. Here you learned about some of the most common tools that we use. But there's a lot more to come. Next, we'll learn even more about data cleaning with spreadsheet tools. Bye for now.
34:20
You've learned about some very useful data cleaning tools that are built right into spreadsheet applications. Now we'll explore how functions can optimize your efforts to ensure data integrity. As a reminder, a function is a set of instructions that performs a specific calculation using the data in a spreadsheet. The first function we'll discuss is called COUNTIF. COUNTIF is a function that returns the number of cells that match a specified value.
34:49
Basically, it counts the number of times a value appears in a range of cells. Let's go back to our professional association spreadsheet. In this example, we want to make sure the association membership prices are listed accurately. We'll use COUNTIF to check for some common problems, like negative numbers or a value that's much less or much greater than expected. To start, let's find the least expensive membership, $100 for student associates. So that will be the lowest number
35:25
that exists in this column. If any cell has a value that's less than 100, COUNTIF will alert us. We'll add a few more rows at the bottom of our spreadsheet.
35:41
Then beneath column H, type member dues less than $100. Next, type the function in the cell next to it. Every function has a certain syntax that needs to be followed for it to work. Syntax is a predetermined structure that includes all required information and its proper placement. The syntax of a COUNTIF function should be like this, equals COUNTIF open parentheses range comma the specified value in quotation marks and a close parentheses. So it will show up like this where I2 through I72
36:30
is the range and the value is less than 100. This tells the function to go through column I and return a count of all cells that contain a number less than 100. Turns out there is one. Scrolling through our data, we find that one piece of data was mistakenly keyed in as a negative number. Let's fix that now.
37:01
Now we'll use COUNTIF to search for any values that are more than we would expect. The most expensive membership type is $500 for corporate members. Type the function in the cell.
37:24
This time it'll appear like this. I2 through I72 is still the range. But the value is greater than 500. There's one here, too. Check it out.
37:47
This entry has an extra zero. It should be $100. OK. The next function we'll discuss is called LEN. LEN is a function that tells you the length of a text string by counting the number of characters it contains. This is useful when cleaning data if you have a certain piece of information in your spreadsheet that you know must contain a certain length. For example, this association uses six digit member ID codes. So if we just import this data and wanted
38:23
to be sure our codes are all the correct number of digits, we'd use LEN. The syntax of LEN is equals LEN open parentheses, the range, and the closed parentheses. So we'll insert a new column after member ID, then type an equal sign and LEN. Add an open parentheses. The range is the first member ID number in A2. Finish the function by closing the parentheses. It tells us that there are six characters in cell A2. Let's continue the function through the entire column
39:11
and find out if any results are not six. But instead of manually going through our spreadsheet to search for these instances, we'll use conditional formatting. We talked about conditional formatting earlier. It's a spreadsheet tool that changes how cells appear when values meet specific conditions. Let's practice that now. Select all of column B except for the header. Then go to Format and choose Conditional Formatting. The format rule is to Format Cells if not equal to 6.
39:56
Click Done. And the cell with the seven inside is highlighted.
40:05
All right. Now we're going to talk about LEFT and RIGHT. LEFT is a function that gives you a set number of characters from the left side of a text string. RIGHT is a function that gives you a set number of characters from the right side of the text string. As a quick reminder, a text string is a group of characters within a cell commonly composed of letters, numbers, or both. To see these functions in action, let's go back to the spreadsheet from the cosmetics maker from earlier.
40:36
This spreadsheet contains product codes.
40:42
Each has a five digit numeric code and then a four character text identifier.
40:53
But let's say we only want to work with one side or the other. You can use LEFT or RIGHT to give you the specific set of characters or numbers you need. We'll practice cleaning up our data using the LEFT function first. The syntax of LEFT is equals LEFT, open parentheses, the range, a comma, and the number of characters from the left side of the text string we want. Then we finish it with a closed parentheses. Here our project requires just the five digit numeric codes.
41:27
So in a separate column, type equals LEFT, open parentheses, then add the range. Our range is A2. Then add a comma and the number 5 for our five digit product code. Finally, finish the function with a closed parentheses. Our function should show up like this. Press Enter. And now we have a substring, which is the number part of the product code only.
42:09
Click and drag this function through the entire column to separate out the rest of the product codes by number only.
42:20
OK. Now let's say our project only needs the four character text identifier.
42:28
For that, we'll use the RIGHT function. In the next column, we'll begin the function.
42:35
The syntax is equals RIGHT, open parentheses, the range, a comma, and the number of characters we want, then we finish with a close parentheses. Let's key that in now. Equals right, open parentheses, and the range is still A2.
42:56
Add a comma. And this time we'll tell it that we want the first four characters from the right.
43:04
Close up the parentheses and press Enter. Then drag the function throughout the entire column.
43:16
Now we can analyze the products in our spreadsheet based on either substring, the five digit numeric code or the four character text identifier. So hopefully, that makes it clear how you can use LEFT and RIGHT to extract substrings from the left and right sides of a string. Now let's learn how you can extract something in between. Here's where we'll use something called MID. MID is a function that gives you a segment from the middle of a text string.
43:49
This cosmetics company lists all of its client using a client code. It's composed of the first three letters of the city where the client's located, its state abbreviation, and then a three digit identifier. But let's say a data analyst needs to work with just the states in the middle. The syntax for MID is equals MID, open parentheses, the range, then a comma. When using MID, you always need to supply a reference point. In other words, you need to set where the function should start.
44:25
After that, place another comma and how many middle characters you want. In this case, our range is D2. Let's start the function in a new column.
44:39
Type equals MID, open parentheses, D2, then the first three characters represent a city name so that means the starting point is the fourth. Add a comma and 4. We also need to tell the function how many middle characters we want. Add one more comma and two, because the state abbreviations are two characters long. Press Enter and bam. We just get the state abbreviation. Continue the MID function through the rest of the column.
45:20
So we've learned about a few functions that help separate out specific text strings. But what if we want to combine them instead? For that, we'll use CONCATENATE, which is a function that joins together two or more text strings. The syntax is equals CONCATENATE, then an open parentheses. Inside indicate each text string you want to join separate by commas. Then finish the function with the closed parentheses. So just for practice, let's say we
45:53
needed to rejoin the left and right text strings back into complete product codes. In a new column, let's begin our function.
46:06
Type equals CONCATENATE, then an open parentheses. The first test string we want to join is in H2. Then add a comma. The second part is in I2. Add a closed parentheses and press Enter. Drag it down through the entire column.
46:35
And just like that, all of our product codes are back together.
46:41
OK. The last function we'll learn about here is TRIM. TRIM is a function that removes leading, trailing, and repeated spaces in data. Sometimes when you import data, your cells have extra spaces which can get in the way of your analysis.
47:03
For example, if this cosmetics maker wanted to look up a specific client name, it won't show up in the search if it has extra spaces. You can use TRIM to fix that problem. The syntax for TRIM is equals TRIM, open parentheses, your range, and closed parentheses. So in a separate column, type equals TRIM and an open parentheses. The range is C2 as you want to check out the client names. Close the parentheses and press Enter. Finally, continue the function down the column.
47:50
Trim fixed the extra spaces.
48:00
So now we know some very useful functions that can make your data cleaning even more successful. This was a lot of information so as always, feel free to go back and review the video and then practice on your own. We'll continue building all these tools soon. And you'll also have a chance to practice. Pretty soon these data clean steps will become second nature, like brushing your teeth.
48:27
Let's get into it. Motivational speaker Wayne Dyer once said, if you change the way you look at things, the things you look at change. This is so true in data analytics. No two analytics projects are ever exactly the same so it only makes sense that different projects require us to focus on different information, differently. In this video, we'll explore different methods that data analysts use to look at data differently and how that leads to more efficient and effective data cleaning.
49:01
Some of these methods include sorting and filtering, pivot tables, a function called VLOOKUP, and plotting to find outliers. Let's start with sorting and filtering. As you learned earlier, sorting and filtering data helps data analysts customize and organize the information the way they need for a particular project. But these tools are also very useful for data cleaning. You might remember that sorting involves arranging data into a meaningful order to make it easier to understand,
49:36
analyze, and visualize. For data cleaning, you can use sorting to put things in alphabetical or numerical order so you can easily find a piece of data. Sorting can also bring duplicate entries closer together for faster identification. Filters, on the other hand, are very useful in data cleaning when you want to find a particular piece of information. You learned earlier that filtering means showing only the data that meets a specific criteria while hiding the rest.
50:10
This lets you view only the information you need. When cleaning data, you might use a filter to only find values above a certain number or just even or odd values. Again, this helps you find what you need quickly and separates out the information you want from the rest. That way you can be more efficient when cleaning your data. Another way to change the way you view data is by using pivot tables. You've learned that a pivot table is a data summarization tool that is used in data processing.
50:47
Pivot tables sort, reorganize, group, count, total, or average data stored in a database. In data cleaning, pivot tables are used to give you a quick, clutter free view of your data. You can choose to look at the specific parts of a data set you need and get a visual in the form of a pivot table. Let's create one now using our cosmetic makers spreadsheet again. To start, select the data we want to use. Here we'll choose the entire spreadsheet.
51:24
Select data, then pivot table.
51:31
Choose New Sheet and Create.
51:37
Let's say we're working on a project that requires us to look at only the most profitable products, items that earn the cosmetics maker at least $10,000 in orders. So the row we'll include is total for total profits.
51:57
We'll sort in descending order to put the most profitable items at the top.
52:04
And we'll show totals. Next, we'll add another row for products so that we know what those numbers are about. We can clearly determine that the most profitable products have the product codes 15143exfo and 32729masc. We can ignore the rest for this particular project because they fall below 10,000 in orders. Now we might be able to use context clues that assume we're talking about exfoliants and mascaras, but we don't know which ones or if that assumption is
52:54
even correct so we need to confirm what the product codes correspond to. And this brings us to the next tool. It's called VLOOKUP. VLOOKUP stands for vertical lookup. It's a function that searches for a certain value in a column to return a corresponding piece of information. When data analysts look up information for a project, it's rare for all of the data they need to be in the same place. Usually, you'll have to search across multiple sheets or even different databases.
53:31
The syntax of VLOOKUP is equals VLOOKUP, open parentheses, then the data you want to look up. Next is a comma and where you want to look for that data. In our example, this will be the name of a spreadsheet followed by exclamation point. The exclamation point indicates that we're referencing a cell in a different sheet from the one we're currently working in. Again, that's very common in data analytics. OK. Next is the range in the place where you're looking for data, indicated
54:07
using the first and last cell separated by a colon. After one more comma is the column in the range containing the value to return. Next, another comma and the word false, which means that an exact match is what we're looking for. Finally, complete your function by closing the parentheses. To put it simply, VLOOKUP searches for the value in the first argument in the left most column of the specified location. Then the value of the third argument tells VLOOKUP to return the value in the same row
54:43
from the specified column. The false tells VLOOKUP that we want an exact match. Soon you'll learn the difference between exact and approximate matches. But for now, just know that VLOOKUP takes the value in one cell and searches for a match in another place. Let's begin. We'll type equals VLOOKUP.
55:15
Then add the data we want to look for, which is the product code.
55:22
The dollar signs make sure that the corresponding part of the reference remains unchanged or locked. You can lock just the column, just the row, or both at the same time.
55:45
Next, we'll tell it to look at sheet 2 in both columns.
55:59
We added 2 to represent the second column. The last term false says we want an exact match.
56:13
With this information, we can now analyze the data for only the most profitable products.
56:27
Going back to the two most profitable products, we can search for 15143exfo and 32729masc. Go to Edit, and then Find. Type in the product codes and search for them.
56:57
Now we can learn which products we'll be using for this particular project. The final tool we'll talk about is something called plotting. When you plot data, you put it in a graph, chart, table, or other visual to help you quickly find what it looks like. Plotting is very useful when trying to identify any skewed data or outliers. For example, if we wanted to make sure the price of each product is correct, we could create a chart. This would give us a visual aid that
57:31
helps us quickly figure out if anything looks like an error. So let's select the column with our prices.
57:41
Then we'll go to Insert and choose chart.
57:50
Pick a column chart as the type. One of these prices looks extremely low.
57:57
If we look into it, we discover that this item has a decimal point in the wrong place.
58:07
It should be $7.30, not $0.73. That would have a big impact on our total profits so it's a good thing we caught that doing data cleaning. Looking at data in new and creative ways helps data analysts identify all kinds of dirty data. Coming up, you'll continue practicing these new concepts so you can get more comfortable with them. You'll also learn additional strategies for ensuring your data is clean. And we'll provide you with effective insights. Great work so far.
58:49
So far, you've learned about a lot of different tools and functions that analysts use to clean up data for analysis. Now we'll take a step back and talk about some of the really big picture aspects of clean data. Knowing how to fix specific problems, either manually with spreadsheet tools or with functions, is extremely valuable. But it's also important to think about how your data has moved between systems and how it's evolved along its journey to your data analysis project.
59:19
To do this, data analysts use something called data mapping. Data mapping is the process of matching fields from one database to another. This is very important to the success of data migration, data integration, and lots of other data management activities. As you learned earlier, different systems store data in different ways. For example, the state field in one spreadsheet might show Maryland spelled out but another spreadsheet might store it as MD.
59:55
Data mapping helps us note these kinds of differences so we know when data is moved and combined, it will be compatible. As a quick reminder, compatibility describes how well two or more data sets are able to work together. So the first step to data mapping is identifying what data needs to be moved. This includes the tables and the fields within them. We also need to define the desired format for the data once it reaches its destination. To figure out how this works, let's go back
1:00:29
to the merger between our two logistics associations.
1:00:36
Starting with the first data field, we'll identify that we need to move both sets of member IDs. And to define the desired format, we'll choose whether to use numbers, like this spreadsheet, or email addresses, like the other spreadsheet. Next comes mapping the data. Depending on the schema and number of primary and foreign keys in a data source, data mapping can be simple or very complex. As a reminder, a schema is a way of describing how something is organized.
1:01:12
A primary key references a column in which each value is unique. And a foreign key is a field within a table that is a primary key in another table. For more challenging projects, there's all kinds of data mapping software programs you can use. These data mapping tools will analyze field by field how to move data from one place to another. Then they automatically clean, match, inspect, and validate the data. They also create consistent naming conventions,
1:01:46
ensuring compatibility when the data is transferred from one source to another. When selecting a software program to map your data, you want to be sure that it supports the file types you're working with such as Excel, SQL, Tableau, and others. Later on, you'll learn more about selecting the right tool for a particular task. For now, let's practice mapping data manually. First, we need to determine the content of each section to make sure the data ends up in the right place.
1:02:20
For example, the data on when memberships expire would be consolidated into a single column. This step makes sure that each piece of information ends up in the most appropriate place in the merged data source. Now you might remember that some of the data was inconsistent between the two organizations, like the fact that one uses a separate column for suite, apartment, or unit number but the other doesn't.
1:02:59
This brings us to the next step, transforming the data into a consistent format. This is a great time to use CONCATENATE. As you learned before, CONCATENATE is a function that joins together two or more text strings, which is what we did earlier with our cosmetics company example. So we'll insert a new column and then type equals CONCATENATE, then the two text strings we want to make one.
1:03:45
Drag that through the entire column.
1:03:56
And now we have the consistency in the new merged association list of member addresses.
1:04:07
OK. Now that everything's compatible, it's time to transfer the data to its destination. There's a lot of different ways to move data from one place to another, including querying, import wizards, and even simple drag and drop. Here you'll learn more about choosing the right method for different situations. All right. Here's our merged spreadsheet.
1:04:35
It looks good, but we still want to make sure everything was transferred properly so we'll go into the testing phase of data mapping. For this, you inspect a sample piece of data to confirm that it's clean and properly formatted.
1:04:53
It's also a smart practice to do spot checks on things such as the number of nulls. For the test, you can use a lot of the data cleaning tools we discussed previously, such as data validation, conditional formatting, COUNTIF, sorting, and filtering. Finally, once you've determined that the data is clean and compatible, you can start using it for analysis. Data mapping is so important because even one mistake when merging data can ripple throughout an organization,
1:05:26
causing the same error to appear again and again. This leads to poor results. On the other hand, data mapping can save the day by giving you a clear roadmap you can follow to make sure your data arrives safely at its destination. And that's why you learn how to do it. TONY: Congratulations on finishing this video from the Google Data Analytics certificate. Access the full experience, including job search help, and start to earn the official certificate
1:05:54
by clicking the icon or the link in the description. Watch the next video in the course by clicking here and subscribe to our channel for more from upcoming
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