Basic data cleaning strategies

All data requires some level of cleaning to resolve typos or to convert into formats suitable for calculations or analysis.For our first steps in a data cleaning adventure, we will use Excel.

The Find/Replace function is a key beginning concept in data cleaning. See this article from Microsoft in case this is a new concept for you.

  Common ways to use Find/Replace
  --To normalize spelling of a specific name. For example: IBM and I.B.M would be separate 
  entries. Use Find for I.B.M and Replace with IBM. I do this constantly.
  --To remove stray characters such as asterix, exclamation points, brackets
  

Splitting text to columns is a technique to extract structured data into its own column for visualization or analysis. This is very useful for mapping. See this article about how to use text to columns

TRIM function is a super handy way to strip out hidden spaces and control characters from your data. Learn more about =TRIM

Paste Special function allows you to paste the results of a formula instead of the actual formula or paste the contents of a cell but not its formatting. It is accessed by Cntl+Alt+V. Details are here

Cleaning Data in Excel: BEA Data

Here is a video tutorial on how to clean Bureau of Economic Research data, a real-world example. In this video, we cleaned up a government published spreadsheet so it will act well with Tableau or any statistical software.

Techniques in this video:   

--Data dictionary to document your work   
--Copying data to a tab, working with a copy   
--Unmerging cells     
--Deleting and aligning rows to allow clean import into Tableau, R etc   
--Text to columns to break out a state field into its own column    

Follow along with this data

Data Cleaning and Joining: Map School COVID Data

This a common real-world data problem: adding zip codes or addresses to a dataset in order to map the data. It’s complex.

We want to map the school districts and COVID-19 data.

To do so, we need addresses for the districts.

So a Google search of “arkansas department of education school districts addresses zip code” yielded a U.S. Department of Education spreadsheet with all Arkansas school districts

It’s your turn now.

1. Examine the two public schools combo and the ar.xls district address spreadsheets. What common column can you join these two?

2. What data cleaning needs to be performed to allow a join?

–Remember to duplicate any source data before altering it.

3. Perform an inner join of the two spreadsheets in Tableau.

4. Map the districts. Visualize by Cumulative student cases.

5. Filter by Oct 14

6. Post your map on Tableau Online

Part 2:

Dual axis map with the school districts with more / less than 25% Hispanic enrollment as the base layer. Map the cumulative student COVID rates.

Census Data Cleaning Exercises

**Census Data: data.census.gov

Download the Race / Population Data

1. Advanced Search
2. Select: Topics | Race and Ethnicity | White–Note that the “White” filter displays below
3. Geography | County | Arkansas | All Counties in Arkansas–Note that the “All counties in Arkansas” filter displays
4. Search!
5. Select Table Named RACE |American Community Survey | Total Population | TableID: B02001
6. Switch to 2019: ACS 5-Year Estimates Detailed Tables
7. Customize Table. Download. Make Sure to Download;2019: ACS 5-Year Estimates Detailed Tables

Clean Census Data

1. Create Data Dictionary
2. Duplicate Sheet
3. Four corners select and copy
4. New Sheet. Paste Special | Transpose –the races are now the rows –Filter by Estimate: Contains Estimate, Delete
5. Edit Headers: White, Black, Hispanic
6. Check totals - do they add up?
7. Two races including Some other race. Two races excluding Some other race, and three or more races (delete)
8. Save and Load to Tableau
9. Build a Arkansas Population Map by Race

Build Arkansas Population Map By Race
–Clean the County Field “, Arkansas” –Create Calculations for Percentage Population by Race: Calculated Fields