Lets start cleaning! All about Data Cleaning for Data Analysis.
Picture from: https://medium.com/
What do you do when you are asked to do some data analysis?
Do you start thinking about what to do?
Then, this is the right place where we talk all about starting Data Analysis!
Well, we like to find some answers like, what do you want to know from this data set? specifically? conclusively?
What is this data about ? (know what's given to us and what is not given in the data)
What kind of data is given to us? (numbers, words, the business related information described)
I like to talk about how to start with a data set and
and gain clarity to thinking about beginning it well.
I like to get inspired from the famous phrase and rephrase it.
The original title from William Shakespeare's play: "All's well that ends well".
I like to rephrase and apply it to Data Analysis as "All that begins well, ends well!". So, let's start understanding
how to begin Data Analysis!
First, review the given data file.
Is there any details given, dates, numbers, yes/no?
Think about what we can answer seeing the given data.(imagine some goal about the data in your mind).
Look for some common structure and formatting, put on your technical hat and check the number of columns, rows, column names, are there any empty spaces, any special characters or symbols given in the data?
Best way to understand is that, one row of data is one record about some occurrence we can figure out from the given data set.
One column is a data field, mentioned about some particular data.
Ex. We can preview data using the Data Source tab in Tableau, we can also rename columns. Power BI uses Power Query Editor to rename columns.
Right! Let's go further.
Next, see if the data is clean, check for any redundant information, see it the data was copied more than once, as we need to do some aggregation calculations on the data. Duplicate data can create errors while aggregating sums, averages and counts. So, keep consistency in mind.
Identify and remove duplicate values.
Ex. Filter duplicates in Power BI, use COUNTD() to check for unique values in Tableau.
Next, see the raw data if there are any missing values or see if any values need to be determined as unusable
and remove rows, understand if there is any chance of having null values.
See if you can replace a number value like zero "0" in place of an unknown or "not available" values.
Ex. replace values in Power BI Power Query, Filter nulls, use calculated field in Tableau: IFNULL([Field],0)
Notice how the data is grouped, do you see any inconsistent values?
Notice mixed case words, "New Jersey", NJ", capital letters used for "NY", New York"? We need to come
up with a fix for this problem, create a rule to use in place, this is called as "Mapping Rule".
Ex. IF LOWER([state])= nj" then "New Jersey"
ELSE [state]
END
Next, check for numeric values and text values, is it stored as a numeric or is it stored as a string value? make sure any Yes/NO values are misrepresented, these are called "Boolean values".
Ex. you can change such values in the Data Pane in Tableau or Transform Data in Power BI.
Then, look further in the raw data, Dates, Time, any First Name, Last Name.
Ex. Tableau has Split and Calculated Fields. Power BI uses Split Column and Custom Columns.
Check for any data entry errors and extreme values in the expected range of values. Mark or flag any values
that don't fit well. We can remove any invalid data but review with the anyone who can help you, check and understand the reason before deleting any data!
Finally, do the basics!
See if the cleaned data counts same number of rows before and after cleaning. Check with the source data
or check some random rows of data.
Identify the source system, what are the permanent ways to fix and clean the data?
What transformations can be done ?
Note: Always make a copy of your raw data before starting to work on it.
Make a note of all the steps followed in the cleaning process.
Main Point to understand is that, Data Analysis begins with understanding the context where the data is collected, understand the business strategy that's put in place to use the current data for analysis purposes.
Some of the steps we followed:
1. Clearly understand the given data and its structure.
2.Priority number one is to Standardize the binary or Boolean values.
3.Rename any columns after reviewing with the business stakeholders.
4.Validate the date field format and values mentioned.
5.Validate the address and postal region and geographic mapping.
6.Any recommendations to convert numeric/Boolean for Dashboard representation?
7.Create aggregations for ease of use.
8.Check for any data conflicts.
9.Maintain a copy of raw data.
10.Follow a checklist for the cleaning order.
I will describe the exact steps we followed while working on a sample data file in my upcoming blog.
Continue reading another post about Data Analysis where I talked about different types of Data Analysis, how the study evolved and used and The Future of Data Analytics.
