What is dirty data?

Earlier, we discussed that dirty data is data that is incomplete, incorrect, or irrelevant to
the problem you are trying to solve.  This reading summarizes:

 Types of dirty data you may encounter

 What may have caused the data to become dirty
 How dirty data is harmful to businesses
Types of dirty data

Duplicate data
Description Possible causes Potential harm to businesses

Any data record that Manual data entry, Skewed metrics or analyses, inflated or
shows up more than batch data imports, or inaccurate counts or predictions, or
once data migration confusion during data retrieval

Outdated data
Potential harm to
Description Possible causes
businesses

Any data that is old which should People changing roles or Inaccurate insights,
 Potential harm to
Description Possible causes
businesses
be replaced with newer and companies, or software and decision-making, and
more accurate information systems becoming obsolete analytics

Incomplete data
Description Possible causes Potential harm to businesses
Any data that is Improper data Decreased productivity, inaccurate
missing important collection or incorrect insights, or inability to complete
fields data entry essential services

Incorrect/inaccurate data
Description Possible causes Potential harm to businesses
Any data that is Human error inserted during Inaccurate insights or decision-
complete but data input, fake information, making based on bad information
inaccurate or mock data resulting in revenue loss

Inconsistent data
Description Possible causes Potential harm to businesses
Any data that uses different Data stored incorrectly or Contradictory data points leading to
formats to represent the errors inserted during confusion or inability to classify or
same thing data transfer segment customers

Business impact of dirty data

For further reading on the business impact of dirty data, enter the term “dirty data” into
your preferred browser’s search bar to bring up numerous articles on the topic. Here are
a few impacts cited for certain industries from a previous search:

 Banking: Inaccuracies cost companies between 15% and 25% of revenue

(source).
 Digital commerce: Up to 25% of B2B database contacts contain inaccuracies
(source).
 Marketing and sales: 8 out of 10 companies have said that dirty data hinders
sales campaigns (source).
 Healthcare: Duplicate records can be 10% and even up to 20% of a hospital’s
electronic health records (source).

Data integrity principle

Common data-cleaning pitfalls
In this reading, you will learn the importance of data cleaning and how to identify common mistakes. Some
of the errors you might come across while cleaning your data could include:

Common mistakes to avoid

 Not checking for spelling errors: Misspellings can be as simple as typing or input errors. Most of
the time the wrong spelling or common grammatical errors can be detected, but it gets harder
with things like names or addresses. For example, if you are working with a spreadsheet table of
customer data, you might come across a customer named “John” whose name has been input
incorrectly as “Jon” in some places. The spreadsheet’s spellcheck probably won’t flag this, so if
you don’t double-check for spelling errors and catch this, your analysis will have mistakes in it. 
 Forgetting to document errors: Documenting your errors can be a big time saver, as it helps you
avoid those errors in the future by showing you how you resolved them. For example, you might
find an error in a formula in your spreadsheet. You discover that some of the dates in one of your
columns haven’t been formatted correctly. If you make a note of this fix, you can reference it the
next time your formula is broken, and get a head start on troubleshooting. Documenting your
errors also helps you keep track of changes in your work, so that you can backtrack if a fix didn’t
work. 
 Not checking for misfielded values: A misfielded value happens when the values are entered into
the wrong field. These values might still be formatted correctly, which makes them harder to catch
if you aren’t careful. For example, you might have a dataset with columns for cities and countries.
These are the same type of data, so they are easy to mix up. But if you were trying to find all of the
instances of Spain in the country column, and Spain had mistakenly been entered into the city
column, you would miss key data points. Making sure your data has been entered correctly is key
to accurate, complete analysis. 
 Overlooking missing values: Missing values in your dataset can create errors and give you
inaccurate conclusions. For example, if you were trying to get the total number of sales from the
last three months, but a week of transactions were missing, your calculations would be
inaccurate.  As a best practice, try to keep your data as clean as possible by maintaining
completeness and consistency.
 Only looking at a subset of the data: It is important to think about all of the relevant data when
you are cleaning. This helps make sure you understand the whole story the data is telling, and that
you are paying attention to all possible errors. For example, if you are working with data about bird
migration patterns from different sources, but you only clean one source, you might not realize
that some of the data is being repeated. This will cause problems in your analysis later on. If you
want to avoid common errors like duplicates, each field of your data requires equal attention.
 Losing track of business objectives: When you are cleaning data, you might make new and
interesting discoveries about your dataset-- but you don’t want those discoveries to distract you
from the task at hand. For example, if you were working with weather data to find the average
number of rainy days in your city, you might notice some interesting patterns about snowfall, too.
That is really interesting, but it isn’t related to the question you are trying to answer right now.
Being curious is great! But try not to let it distract you from the task at hand.  
 Not fixing the source of the error: Fixing the error itself is important. But if that error is actually
part of a bigger problem, you need to find the source of the issue. Otherwise, you will have to keep
fixing that same error over and over again. For example, imagine you have a team spreadsheet that
tracks everyone’s progress. The table keeps breaking because different people are entering
different values. You can keep fixing all of these problems one by one, or you can set up your table
to streamline data entry so everyone is on the same page. Addressing the source of the errors in
your data will save you a lot of time in the long run. 
 Not analyzing the system prior to data cleaning: If we want to clean our data and avoid future
errors, we need to understand the root cause of your dirty data. Imagine you are an auto
mechanic. You would find the cause of the problem before you started fixing the car, right? The
same goes for data. First, you figure out where the errors come from. Maybe it is from a data entry
 error, not setting up a spell check, lack of formats, or from duplicates. Then, once you understand
where bad data comes from, you can control it and keep your data clean.
 Not backing up your data prior to data cleaning: It is always good to be proactive and create your
data backup before you start your data clean-up. If your program crashes, or if your changes cause
a problem in your dataset, you can always go back to the saved version and restore it. The simple
procedure of backing up your data can save you hours of work-- and most importantly, a
headache. 
 Not accounting for data cleaning in your deadlines/process: All good things take time, and that
includes data cleaning. It is important to keep that in mind when going through your process and
looking at your deadlines. When you set aside time for data cleaning, it helps you get a more
accurate estimate for ETAs for stakeholders, and can help you know when to request an adjusted
ETA. 

Additional resources
Refer to these "top ten" lists for data cleaning in Microsoft Excel and Google Sheets to help you avoid the
most common mistakes:

 Top ten ways to clean your data: Review an orderly guide to data cleaning in Microsoft Excel.
 10 Google Workspace tips to clean up data: Learn best practices for data cleaning in Google
Sheets.

Activity overview

You’ve learned about cleaning data and its importance in meeting good data science
standards. In this activity, you’ll do some data cleaning with spreadsheets, then transpose
the data.

By the time you complete this activity, you will be able to perform some basic cleaning
methods in spreadsheets. This will enable you to clean and transpose data, which is
important for making data more specific and accurate in your career as a data analyst.

What you will need

To get started, first access the data spreadsheet.

To use the spreadsheet for this course item, click the link below and select “Use Template.” 

Link to data spreadsheet: Cleaning with spreadsheets

OR

If you don’t have a Google account, you can download the template directly from the
attachment below.

Data Spreadsheet for Cleaning with Spreadsheets

XLSX File

Select and remove blank cells

The first technique we’ll use is to select and eliminate rows containing blank cells by using
filters. To eliminate rows with blank cells:

1. Highlight all cells in the spreadsheet. You can highlight Columns A-H by clicking on the
header of Column A, holding Shift, and clicking on the header of Column H.

2. Click on the Data tab and pick the Create a filter option. In Microsoft Excel, this is called
Filter.
Excel:

3. Every column now shows a green triangle in the first row next to the column title. Click the
green triangle in Column B to access a new menu.

4. On that new menu, click Filter by condition and open the dropdown menu to select Is
empty. Click OK.
In Excel, click the dropdown, then Filter... then make sure only (Blanks) is checked. Click OK.

Excel:
You can then review a list of all the rows with blank cells in that column. 

5. Select all these cells and delete the rows except the row of column headers.

6. Return to the Filter by condition and return it to None. In Excel, click Clear Filter from
‘Column’. 
  Note: You will now notice that any row that had an empty cell in Column A will be
removed (including the extra empty rows after the data).
7. Repeat this for Columns B-H.

All the rows that had blank cells are now removed from the spreadsheet.

Transpose the data

The second technique you will practice will help you convert the data from the current long
format (more rows than columns) to the wide format (more columns than rows). This action
is called transposing. To transpose your data:

1. Highlight and copy the data that you want to transpose including the column labels. You
can do this by highlighting Columns A-H. In Excel, highlight only the relevant cells (A1-H45)
instead of the headers.

2. Right-click on cell I1. This is where you want the transposed data to start.

3. Hover over Paste Special from the right-click menu. Select the Transposed option. In Excel,
select the Transpose icon under the paste options.
Excel:
You should now find the data transformed into the new wide format. At this point, you should
remove the original long data from the spreadsheet.

4. Delete the previous long data. The easiest way to do this is to click on Column A, so the
entire column is highlighted. Then, hold down the Shift key and click on Column H. You
should find these columns highlighted. Right-click on the highlighted area and select Delete
Columns A - H.
Your screen should now appear like this:

Get rid of extra spaces in cells with string data

Now that you have transposed the data, eliminate the extra spaces in the values of the cells.

1. Highlight the data in the spreadsheet.

2. Click on the Data tab, then hover over Data cleanup and select Trim whitespace.
In Excel, you can use the TRIM command to get rid of white spaces. In any space beneath your
data (such as cell A10), type =TRIM(A1). Then, drag the bottom right corner of the cell to the
bottom right to call the data without the white spaces.

Now all the extra spaces in the cells have been removed.

Change Text Lower/Uppercase/Proper Case 

Next, you’ll process string data. The easiest way to clean up string data will depend on the
spreadsheet program you are using. If you are using Excel, you’ll use a simple formula. If you
are using Google Sheets, you can use an Add-On to do this with a few clicks. Follow the steps
in the relevant section below.
Microsoft Excel

If you are using Microsoft Excel, this documentation explains how to use a formula to change
the case of a text string. Follow these instructions to clean the string text and then move on to
the confirmation and reflection section of this activity. 

Google sheets

If you’re completing this exercise using Google Sheets, you’ll need to install an add-in that
will give you the functionality needed to easily clean string data and change cases. 

Google Sheets Add-on Instructions:

1. Click on the Add-Ons option at the top of Google Sheets.

2. Click on Get add-ons.
3. Search for ChangeCase. It should appear like this:
4. Click on Install to install the add-on. It may ask you to login or verify the installation
permissions. 

Once you have installed the add-on successfully, you can access it by clicking on the Add-ons
menu again. 

Now, you can change the case of text data that shows up. To change the text in Column C to
all uppercase:

1. Click on Column C. Be sure to deselect the column header, unless you want to change the
case of that as well (which you don't).

2. Click on the Add-Ons tab and select ChangeCase. Select the option All uppercase. Notice
the other options that you could have chosen if needed.

Delete all formatting

If you want to clear the formatting for any or all cells, you can find the command in the
Format tab. To clear formatting:

1. Select the data for which you want to delete the formatting. In this case, highlight all the
data in the spreadsheet by clicking and dragging over Rows 1-8.

2. Click the Format tab and select the Clear Formatting option.

In Excel, go to the Home tab, then hover over Clear and select Clear Formats.
You will notice that all the cells have had their formatting removed.

Activity overview

By now, you’ve been introduced to some useful techniques for cleaning spreadsheet data,
such as sorting and filtering. In this activity, you'll continue to develop your data-cleaning
skills by using spreadsheet functions.  

Imagine you are a data analyst working for a marketing agency based in San Francisco. The
marketing agency wants to contact local boba tea shops to inquire about a potential
collaboration for a new marketing campaign. The agency plans to visit the top-rated shops
within a 10-mile radius of the center of their target area. To assist with planning, the agency
asks your team to review external data related to ratings and locations of boba tea shops in
San Francisco. One of your teammates has created a spreadsheet from an online source.
However, the data is not in the greatest shape.

Your assignment is to identify the dirty elements in the dataset and clean them up. 

By the time you complete this activity, you will be able to identify dirty elements in a dataset,
remove duplicate data, and use the COUNTIF and SPLIT functions to help clean data.

What you will need

The dataset includes the following column headers:
To get started, access the spreadsheet that contains the data. Click the link and make a copy
of the spreadsheet. 

Or, if you don’t have a Google account, you may download the dataset directly from the
attachment below:

San Francisco Boba Tea Shop Location Info

CSV File

Identify the dirty elements in your data

As a data analyst, your job is to present data that is readable, accurate, and visually
appealing. Cleaning your data helps you achieve this goal. The first step is to identify the dirty
elements in your data.

1. Rename your spreadsheet. Click Untitled Spreadsheet and enter a new name. You can
use the name sf_boba_tea_shop_data or a similar name that describes the data your
spreadsheet contains. 
2. If you want to get a better view of your data, you can make the columns wider by
dragging the right boundary of the column heading. This may apply to the name (B),
address (D), and lat-long (F) columns. 
 3. Now, review your data and consider any problems you may need to address. The
following are examples of errors that you can quickly identify and fix. This is not a
comprehensive list of every potential problem, but is a great starting point for data
cleaning.
 First, there is at least one duplicate line (rows 20 and 21) in your dataset.

 Second, all Yelp ratings should fall between 0 and 5. However, at least one rating (in
cell C8) falls outside of that range.  

 Finally, the data for latitude and longitude is contained in a single column (F). In order
for someone to be able to use this data for analysis, the two values should be in
separate columns.

Now you know what issues to focus your attention on during the cleaning process.

Clean your data

Your goal is to fix these errors and help create a clean dataset for analysis. You can address
each issue in turn.

Remove duplicates
The first step is to eliminate any duplicate entries from your dataset. As a best practice,
duplicates should be removed even if they are not readily apparent.

1. To start, select columns A through F.

2. Then, in the menu bar, choose Data, then Data Cleanup and Remove duplicates.
3. In the pop-up window, click Data has header row. You want to remove duplicate boba
shop id's and boba shop names. In the Columns to analyze section, make sure the
relevant columns (id, name) are selected.

4. Once everything has been selected, click Remove duplicates.

5. If done correctly, 3 duplicate rows will be found and removed and 604 rows will remain.

Correct the ratings data

Next, clean up any data that does not make sense. Yelp ratings should be less than 5 and
greater than 0. Now, you will determine how many entries are inaccurate and correct them.
You can use the COUNTIF function to perform this task. 

1. The COUNTIF function quickly counts how many items in a range of cells meet a
given criterion. In cell I2, type =COUNTIF(C:C,">5"). The first entry (C:C) refers to
the range where you are counting the data. In this case, the range is the entire rating
column (C), which contains the Yelp ratings. The second entry refers to the criterion
(>5), and tells the function to count all the values greater than 5. 
2. Press Enter. You’ll notice that the function returns a value of 9. This tells you that your
dataset contains 9 entries that have a rating greater than 5.

As a data analyst, it's your job to decide what to do with incorrect values or to ask the dataset
owner for advice if you’re unsure. In this case, one effective approach would be to search on
Yelp for the actual ratings. For this activity, you can just replace the incorrect ratings with the
number 5. An efficient way to replace the ratings is to sort the data numerically from largest
to smallest rating.

3. Select columns A through F.

4. Then, from the menu bar, choose Data, then Sort range and Advanced range sorting
options.

5. In the pop-up window, check the box next to Data has header row. Sort by rating from Z
→A. This way, the highest ratings will be listed first.

6. Click Sort. Check out your spreadsheet. At the start of the rating column, you should now
find the 9 rows that have incorrect values (rating > 5).
7. Next, select the range of cells C2:C10. Press delete to delete the values that are greater
than 5.

8. Replace all the values with the number 5. In cell C2, type 5. Then, drag the fill handle down
to cell C10 to fill the remaining cells with 5.

9. After replacing the incorrect ratings with the number 5, you may notice that the new value
in cell I2 is 0. The output of the COUNTIF function now reflects the changes in your dataset.
This confirms that the rating column no longer contains any values greater than 5. 

10. FInally, delete the formula from cell I2 since you don’t need this information anymore.

Clean up the latitude and longitude data

Next, clean up the latitude and longitude data by placing each value in a separate column.
You can use the SPLIT function to accomplish this task.  

1. The SPLIT function divides text around a specified character or string, and puts each
fragment of text into a separate cell in the row. The SPLIT function will split the single lat-
long column into two separate columns, one for latitude and the other for longitude. In cell
G2, type =SPLIT(F2,"-"). The first entry (F2) refers to the cell where the text is located. The
second entry (“-”) refers to the fact that you are dividing the text based on the minus sign.

2. Press Enter. The result shows each fragment of text in a different cell.
3. Select cell G2 again. In cell G2, double-click on the fill handle to split all the remaining lat-
long entries.

4. Now add column headers to the two new columns (G and H). In cell G1, type lat. In cell H1,
type long.

5. Next, replace the original lat-long data in column F with the new split entries in columns G
and H. Select columns G and H, right-click, and choose Copy.

6. Then, select Column F, right-click, and choose Paste special and Paste values only.
7. Now the new lat column is column F, and the new long column is column G. Adjust the
width of the lat column (F) to fit the data by dragging the right boundary of the column
heading.

8. Next, select column H, right-click, and choose Delete column.

9. Finally, the longitude values should be negative so that they are accurate coordinates for
mapping. To make the values in the long column negative, multiply them by -1. In cell H2,
type =G2*-1. The asterisk is the operator for multiplication. Press Enter.

10. Still in cell H2, double-click on the fill handle to fill in the rest of the values.

11. Next, add a column header. In cell H1, type: long.

12. Now, replace the longitude data in column G with the new data in column H. Select
column H, right-click, and choose Copy.

13. Select Column G, right-click, and choose Paste special and Paste values only.

14. Then, select column H, right-click, and choose Delete column.

Columns F and G should look like this:

Now your data is cleaner, clearer, and easier to use.

Using SQL as a junior data analyst

In this reading, you will learn more about how to decide when to use SQL, or Structured
Query Language. As a data analyst, you will be tasked with handling a lot of data, and SQL is
one of the tools that can help make your work a lot easier. SQL is the primary way data
analysts extract data from databases. As a data analyst, you will work with databases all the
time, which is why SQL is such a key skill. Let’s follow along as a junior data analyst uses SQL
to solve a business task.  

The business task and context

The junior data analyst in this example works for a social media company. A new business
model was implemented on February 15, 2020 and the company wants to understand how
their user-growth compares to the previous year. Specifically, the data analyst was asked to
find out how many users have joined since February 15, 2020. 
Spreadsheets functions and formulas or SQL queries?
Before they can address this question, this data analyst needs to choose what tool to use.
First, they have to think about where the data lives. If it is stored in a database, then SQL is
the best tool for the job. But if it is stored in a spreadsheet, then they will have to perform
their analysis in that spreadsheet. In that scenario, they could create a pivot table of the data
and then apply specific formulas and filters to their data until they were given the number of
users that joined after February 15th. It isn’t a really complicated process, but it would involve
a lot of steps. 

In this case, the data is stored in a database, so they will have to work with SQL. And this data
analyst knows they could get the same results with a single SQL query:

SELECT COUNT(DISTINCT user_id) AS count_of_unique_users FROM table WHERE

join_date >= ‘2020-02-15’
Spreadsheets and SQL both have their advantages and disadvantages:

Features of Spreadsheets Features of SQL Databases 

Smaller data sets Larger datasets
Enter data manually Access tables across a database
Create graphs and visualizations in the Prepare data for further analysis in another
same program software
Built-in spell check and other useful
Fast and powerful functionality
functions
Great for collaborative work and tracking queries
Best when working solo on a project
run by all users
When it comes down to it, where the data lives will decide which tool you use. If you are
working with data that is already in a spreadsheet, that is most likely where you will perform
your analysis. And if you are working with data stored in a database, SQL will be the best tool
for you to use for your analysis. You will learn more about SQL coming up, so that you will be
ready to tackle any business problem with the best tool possible. 
Advanced functions for speedy data cleaning
In this reading, you will learn about some advanced functions that can help you speed up the data cleaning
process in spreadsheets. Below is a table summarizing three functions and what they do:

Keeping data clean and in sync with a source

The IMPORTRANGE function in Google Sheets and the Paste Link feature (a Paste Special option in
Microsoft Excel) both allow you to insert data from one sheet to another. Using these on a large amount of
data is more efficient than manual copying and pasting. They also reduce the chance of errors being
introduced by copying and pasting the wrong data. They are also helpful for data cleaning because you can
“cherry pick” the data you want to analyze and leave behind the data that isn’t relevant to your project.
Basically, it is like canceling noise from your data so you can focus on what is most important to solve your
problem. This functionality is also useful for day-to-day data monitoring; with it, you can build a tracking
spreadsheet to share the relevant data with others. The data is synced with the data source so when the
data is updated in the source file, the tracked data is also refreshed.

In Google Sheets, you can use the IMPORTRANGE function. It enables you to specify a range of cells in the
other spreadsheet to duplicate in the spreadsheet you are working in. You must allow access to the
spreadsheet containing the data the first time you import the data.

The URL shown below is for syntax purposes only. Don't enter it in your own spreadsheet. Replace it
with a URL to a spreadsheet you have created so you can control access to it by clicking the Allow access
button.
Refer to the Google support page for IMPORTRANGE for the sample usage and syntax.

Example of using IMPORTRANGE

An analyst monitoring a fundraiser needs to track and ensure that matching funds are distributed. They
use IMPORTRANGE to pull all the matching transactions into a spreadsheet containing all of the
individual donations. This enables them to determine which donations eligible for matching funds still
need to be processed. Because the total number of matching transactions increases daily, they simply
need to change the range used by the function to import the most up-to-date data. 

On Tuesday, they use the following to import the donor names and matched amounts:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10”, “Matched

Funds!A1:B4001”)

On Wednesday, another 500 transactions were processed. They increase the range used by 500 to easily
include the latest transactions when importing the data to the individual donor spreadsheet:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123”, “Matched Funds!

A1:B4501”)

Note: The above examples are for illustrative purposes only. Don't copy and paste them into your
spreadsheet. To try it out yourself, you will need to substitute your own URL (and sheet name if you
have multiple tabs) along with the range of cells in the spreadsheet that you have populated with data.

Pulling data from other data sources

The QUERY function is also useful when you want to pull data from another spreadsheet. The QUERY
function's SQL-like ability can extract specific data within a spreadsheet. For a large amount of data, using
the QUERY function is faster than filtering data manually. This is especially true when repeated filtering is
required. For example, you could generate a list of all customers who bought your company’s products in a
particular month using manual filtering. But if you also want to figure out customer growth month over
month, you have to copy the filtered data to a new spreadsheet, filter the data for sales during the
following month, and then copy those results for the analysis. With the QUERY function, you can get all
the data for both months without a need to change your original dataset or copy results.

The QUERY function syntax is similar to IMPORTRANGE. You enter the sheet by name and the range of
data that you want to query from, and then use the SQL SELECT command to select the specific columns.
You can also add specific criteria after the SELECT statement by including a WHERE statement. But
remember, all of the SQL code you use has to be placed between the quotes!
Google Sheets run the Google Visualization API Query Language across the data. Excel spreadsheets use a
query wizard to guide you through the steps to connect to a data source and select the tables. In either
case, you are able to be sure that the data imported is verified and clean based on the criteria in the query.

Examples of using QUERY

Check out the Google support page for the QUERY function with sample usage, syntax, and examples you
can download in a Google sheet.

Link to make a copy of the sheet: QUERY examples

Real life solution

Analysts can use SQL to pull a specific dataset into a spreadsheet. They can then use the QUERY function
to create multiple tabs (views) of that dataset. For example, one tab could contain all the sales data for a
particular month and another tab could contain all the sales data from a specific region. This solution
illustrates how SQL and spreadsheets are used well together.

Filtering data to get what you want

The FILTER function is fully internal to a spreadsheet and doesn’t require the use of a query language.
The FILTER function lets you view only the rows (or columns) in the source data that meet your specified
conditions. It makes it possible to pre-filter data before you analyze it.

The FILTER function might run faster than the QUERY function. But keep in mind, the QUERY function
can be combined with other functions for more complex calculations. For example, the QUERY function
can be used with other functions like SUM and COUNT to summarize data, but the FILTER function
can't.

Example of using FILTER

Check out the Google support page for the FILTER function with sample usage, syntax, and examples you
can download in a Google sheet.

Link to make a copy of the sheet: FILTER examples