How can you prepare a meal without the necessary materials and ingredients? How can you move from Point A to Point B without actually taking a step?
The place of Data Preparation is not as pronounced as all other things data. Yet most analysts who work with data agree that up to 80% of their time is spent cleaning data and only the remaining 20% on the actual objective – Analyzing Data for Decision Making. Before the beautiful end products coming out from that Power BI, Tableau, SQL, Qlik, R, Python, whatever, lies a demon called dirty data.
Dirty data can come in different types, forms and shapes. Even colors. Missing Values, Duplicates, Data Entry Blunders, Data Type Inconsistencies. You will never see it all. Some of the most notorious ones I have seen include:
Imagine having to check through millions of records for such notorious issues. If you have such issues, I envy you (Because I love cleaning dirty data)
Through all of these, it takes a very experienced and highly skilled analyst to use a tool of choice to clean dirty data. I will talk about my tool of choice in a bit.
The best tool for cleaning data is the mind. Yes, the Mind. According to Wikipedia (Not a good source to quote but what I saw there made sense), “The mind (not to be confused with the brain) is a set of cognitive faculties including consciousness, imagination, perception, thinking, judgement, language and memory. It is usually defined as the faculty of an entity’s thoughts and consciousness”
This is all one needs to clean any data. Not all actually, but the bulk of it.
The whole of your thinking faculty continues to work through put the process. The way you clean some bad data will sometimes make you think you are a criminal.
1. Put on your Mind and activate your cognitive faculties (Consciousness, Imagination, Perception, Thinking, Judgement, Memory and may be your sense of smell too, you want to be able to smell trouble)
2. Access your data to identify what makes it dirty. You should can use these guides to carry out your general assessments:
3. Use your tool of choice to carry out data cleansing.
Never confuse a report for data. A report can come in any format, but data must align to a structure as stated on the guide number 2 on the list above.
Fortunately or unfortunately for some people, the data they require for their analysis is someone else’s report. You must be able to reshape it from a report structure to data structure.
One tool that does an amazing job (understatement) at cleaning data is the Microsoft Power Query. It is my tool of choice. All you’ll be doing is pointing and clicking away till your data is clean. Power Query is a Microsoft ETL (Extract, Transform and Load)tool available on both Power BI and Excel. It runs on a powerful language – M. I will write a fully dedicated post about Power Query in my next article.
Until then, remember, Cleanliness is next to Godliness. Clean data is essential to Data Analysis.