Update: I have created a playlist of suggested solutions to the dirty datasets provided in this article. I used Microsoft Power Query on Excel. To Learn more about Power Query, get this excellent book by the best Power Query resource in the world Power Query for Power BI and Excel.
The only guaranteed way to become better at cleaning dirty data is to avoid getting it. No, that is the biggest lie of the millennium. The opposite is the case. The only way to get better at preparing and cleaning dirty data is to clean a variety of them.
The problem, however, is to find a guaranteed source with lots of different dirty data cases for practice.
The objective of this article is to create a bank of different dirty data types, mostly simulated with real life scenarios of what I have encountered. I always say, data can be dirty in several millions of ways and you will never see it all.
I generally classify dirty data into 2 categories: Structure Dirty and Content Dirty. You should know there can be a third one, which is both dirty in structure and in content. A while ago, I wrote about how to clean dirty data, you should check it out to catch some tips before diving into this dirty ocean. The compilation I have here can be downloaded as Excel Files. Each workbook contains a worksheet for the raw dirty data and a second worksheet for a sample of the target solution.
Here you go:
1. Badly Structured Sales Data 1
Try re-arranging this data into the correct four columns. There has been a mix of rows and columns everywhere. Also, watch out for Grand Totals and Sub Totals, you do not need those in clean data.
Badly Structured Sales Data 1
2. Badly Structured Sales Data 2
This is pretty like number 1 above, with a different flavor. It has a date column and does not include totals.
Badly Structured Sales Data 2
3. Badly Structured Sales Data 3
Try re-arranging this data into the correct five columns. Again, you should watch out for Totals.
Badly Structured Sales Data 3
4. Badly Structured Sales Data 4
Very similar to number 3 above and with a little different flavor as well.
Badly Structured Sales Data 4
5. Jumbled Customer Details
We see this one often when you download or copy something from the web. You should separate the different data categories into separate columns.
Jumbled Customer Details
6. Medicine Data With Combined Quantity and Measure
Going by clean data rules, you should have every field/column represent unique things. So split the combined Quantity and Measure on this data into separate columns/fields. When you are done, your Quantity column should sum up to 17,600.00. You will find this total on the clean worksheet once you download.
Medicine Data With Combined Quantity and Measure
7. Hospital Data With Mixed Numbers and Characters
This data was collected by non-data-centric professionals. They have sometimes used letters in place of some numbers. Like using letter S in place of Number 5. When you are done with this, your numbers should sum up to that shown on the clean data from the download.
Hospital Data With Mixed Numbers and Characters
8. Invoices With Merged Categories and Merged Amounts
Because a single transaction (identified with an order id ) has multiple items purchased, who ever captured this data decided to create a single row for each order, thereby lumping the different items purchased and the amounts together into 2 fields respectively.
The better thing to do is to let each item purchased be on a single row with the amount. It is better to repeat the Order IDs on different rows than lumping up amounts in a single cell. We would be analyzing items bought and amounts a lot, we need them separated into rows.
Invoices With Merged Categories and Merged Amounts
If you have some samples, please do well to send them in to Info@foresightbi.com.ng and we’ll add them to the bank. Just send in a brief description of the data like I have done above. You can also include your social media links if you do not mind a proper mention.
Up your Data Hygiene Skills. Cheers.
