Data Integrity

Accessibility Notice

This document is also available in HTML format at:

Link: https://aholdengouveia.name/IntroData/labs/dataintegrity.html

The HTML version provides enhanced accessibility features including keyboard navigation, screen reader support, responsive design, dark mode support, and high contrast options.

Learning Outcomes

By the end of this lab, you will be able to:

• Identify data integrity issues such as outliers, inconsistent formatting, extra whitespace, and duplicates
• Use data cleanup tools in Google Sheets to fix issues in a data set
• Explain what likely went wrong when data was collected
• Use a machine learning tool to predict values, and describe the results

Lab Instructions

To do this lab you need:

• The HalloweenCandy.csv data set, linked here Link: https://www.aholdengouveia.name/IntroData/labs/HalloweenCandy.csv
• The data you collected for the Data Collection lab in Week 2
• Google Sheets with the Simple ML for Sheets extension. If you do not have a Google account, you can get one for free before doing this lab.

Before attempting this lab, watch this video on Simple ML for Sheets to get an idea of what can be done with Machine Learning Link: https://www.youtube.com/watch?v=GYCORMNr_IU

The documentation for Google Sheets is linked under Resources.

Screenshots

Several questions ask for a screenshot. Each screenshot should have your name, term, and year. One of the easiest ways to do that is make a text document with your name and term/year on your computer and save it to use all term. Any screenshots that don't include this information won't be counted.

Given Data set

Using the given data set HalloweenCandy.csv, we're going to actively explore the dataset and look for potential issues, including missing data, inconsistent formatting, duplicates, or outliers. The tools we'll be using are going to be under Data and Extensions in Google Sheets.

Open the data set in Google Sheets, try the data tools, and answer the following questions.

Note: the duplicate tool removes the duplicates it finds. For question 3, write down how many duplicates it found, then undo it before you do question 4.

1. Age and country (the Q3: AGE and Q4: COUNTRY columns)
   1. Looking at Q3: AGE, do you see any outliers or things that look different than you expected? For example, did everyone enter a number for their age? If not, what did they enter?
   2. What do you think went wrong in the collection of that data?
   3. Looking at Q4: COUNTRY, what did you notice? What do you think went wrong?
2. Whitespace
   1. Trim the whitespace. How much whitespace did you get rid of?
   2. Visually, what changed in your data?
3. Using the duplicate check on the whole sheet, how many duplicates are there? Take a screenshot.
4. Using the duplicate check on the whole sheet except the Internal ID column, how many duplicates are there? Take a screenshot.
5. Cleanup suggestions
   1. What are the suggested actions to take?
   2. What happened after you did them?
6. Column stats. After doing the cleanup suggestions, review the column stats and pick one column to look at the suggested graph.
   1. What column did you pick? Why?
   2. What's going on in the graph?
   3. Take a screenshot of the graph.
7. Simple ML for Sheets. Try predicting values on two columns: the Q3: AGE column and one column of your choice.
   1. Which other column did you pick? Why?
   2. How did it work? What happened?
   3. How long did it take?
   4. Take a screenshot of your results.

Your data set

Using the data you collected for the Data Collection lab in Week 2, we're going to explore how we can check over that data for potential issues. You can choose to do more data validation than what's listed here, but make sure to note that in your documentation.

Open your data set in Google Sheets, try the data tools, and answer the following questions.

1. Outliers
   1. Pick two of the columns. Which columns did you pick to check, and why?
   2. Do you see any outliers or things that look different than you expected?
2. Whitespace
   1. Trim the whitespace. How much whitespace did you get rid of?
   2. Visually, what changed in your data?
3. Using the duplicate check on the whole sheet, how many duplicates are there? Take a screenshot.
4. Cleanup suggestions
   1. What are the suggested actions to take?
   2. What happened after you did them?
5. Column stats. After doing the cleanup suggestions, review the column stats and pick one column to look at the suggested graph.
   1. What column did you pick? Why?
   2. What's going on in the graph?
   3. Take a screenshot of the graph.
6. Simple ML for Sheets. Try predicting values on two of the columns.
   1. Which two columns did you pick? Why?
   2. How did it work? What happened?
   3. How long did it take? Were you expecting it to take more or less time than it did?
   4. Take a screenshot of your results.

Deliverables

1. A text document with the answers to the questions for the given data on Halloween Candy, labeled with their question numbers.
2. A text document with the answers to the questions for your data set, labeled with their question numbers.
3. All screenshots, clearly labeled: 4 for the Halloween Candy data (questions 3, 4, 6, and 7) and 3 for your data (questions 3, 5, and 6).
4. Your data as a CSV after you have finished the data cleanup tools and any changes.

Resources

References, a video, a PowerPoint and some notes are available at my website Link: https://www.aholdengouveia.name/IntroData/dataintegrity.html

Google Sheets documentation Link: https://support.google.com/docs/topic/1361472