1. Cross field validation
Hi and welcome to the second lesson of this chapter! In this lesson we'll talk about cross field validation for diagnosing dirty data.
2. Motivation
Let's take a look at the following dataset. It contains flight statistics on the total number of passengers in economy, business and first class as well as the total passengers for each flight.
We know that these columns have been collected and merged from different data sources, and a common challenge when merging data from different sources is data integrity, or more broadly making sure that our data is correct.
3. Cross field validation
This is where cross field validation comes in. Cross field validation is the use of multiple fields in your dataset to sanity check the integrity of your data.
For example in our flights dataset, this could be summing economy, business and first class values and making sure they are equal to the total passengers on the plane.
This could be easily done in Pandas, by first subsetting on the columns to sum, then using the sum method with the axis argument set to 1 to indicate row wise summing.
We then find instances where the total passengers column is equal to the sum of the classes.
And find and filter out instances of inconsistent passenger amounts by subsetting on the equality we created with brackets and the tilde symbol.
4. Cross field validation
Here's another example containing user IDs, birthdays and age values for a set of users. We can for example make sure that the age and birthday columns are correct by subtracting the number of years between today's date and each birthday.
5. Cross field validation
We can do this by first making sure the Birthday column is converted to datetime with the pandas to datetime function. We then create an object storing today's date using the datetime package's date dot today function.
We then calculate the difference in years between today's date's year, and the year of each birthday by using the dot dt dot year attribute of the user's Birthday column.
We then find instances where the calculated ages are equal to the actual age column in the users DataFrame.
We then find and filter out the instances where we have inconsistencies using subsetting with brackets and the tilde symbol on the equality we created.
6. What to do when we catch inconsistencies?
So what should be the course of action in case we spot inconsistencies with cross-field validation? Just like other data cleaning problems, there is no one size fits all solution, as often the best solution requires an in depth understanding of our dataset.
We can decide to either drop inconsistent data, set it to missing and impute it, or apply some rules due to domain knowledge.
All these routes and assumptions can be decided upon only when you have a good understanding of where your dataset comes from and the different sources feeding into it.
7. Let's practice!
Now that you know about cross field validation, let's get to practice!