Data Science Fundamentals: Data Cleaning in Python

Key Takeaways

hello everyone can hear back with the third installment of my data science fundamentals series in this video I'm talking about data cleaning why it's important as well as some of the main techniques or curating your data set so it can be run into a model now in data science you hear a lot about garbage in garbage out if you put poor quality data into a model you'll get a poor result of that model so it's really important to set the foundation to understand what makes a good quality data set how do you take a raw data set and make it into something that is clean enough to get a good output out of in the first two videos I talked about data exploration and data manipulation you can see links to those in the description below you don't need any of the information there to follow along with this video video but it wouldn't hurt to go back through those just so you're familiar with how I teach and some of the concepts that I use continually now as usual if you enjoy this content please hit that like button and if you'd like to see similar videos to this please subscribe and turn on notifications so you can know when I publish new material alright so we'll be using the same data set as my previous two videos this is a car pricing data set from Craigslist comm there's a link to that in the description below I got this data set off of kegels so you can go in there and make an account and download it I'm gonna set my working directory to the folder that has this information and the first thing that I'm gonna do is also create a new file so I'm gonna save this into this same location we're going to call it data clinging to and now we're ready to get going so as usual the first thing that I'm going to do is I'm going to import and us an alias it as PD so that we can go forward using that next I'm going to read in the data so we do PD read speed and I like to just copy this name saves us a little bit of time great so when you see me highlight it I press ctrl enter on Windows and that actually runs the command that's a question I've gotten asked a reasonable amount in some of my past videos so I believe it's command enter on Mac to do that that lets you just run one portion of your code rather than pressing this button and running the whole file so now that we've read this in we can see the data frame in our in our variable viewer here so a good practice for me is just to look at the columns so we've done this before we can see what all of the features are in our dataset and then we can also do data describe where we can see all of the characteristics of the numeric fields in our dataset now this dataset I've already looked it does not have any duplicates but if I wanted to remove any duplicate data I would do this so we're gonna remove duplicates and then we do beta a bra duplicates and we're gonna do in place equals true so that means it's going to use our data set here and drop all the duplicate rows from this one so if I didn't have the in place equals true this would initiate a new data set and I could set it equal to like data set too but it wouldn't affect the existing data frame that we have when we do in place it affects the existing data friends that we have so I don't expect again there to be any people gets there now the next thing we do is we check for null values a little bit of this is review from our data exploration but this really builds on itself here so and we're also gonna look at the percent of null values that we have so we can do data is null and so if we just were to run this we would get in each column each row if the value is null or not and so here we can include the any method and then we can see for each column if there are any null values at all and so you can see URL I believe URL is the the primary key of this data set so there shouldn't be any nulls of that and then we have city city or L and price so it looks like for a Craigslist car ad you always have to include a price that's a feature that they have all the other ones appear to be optional so we can also look at the percent of each of these columns that is not deleted data same thing is no and since these are trues and falses when we just did is null proportion true is equal to one in Python and false is equal to zero so we can do some and we can get for each each column how many of the characteristics are not known right so basically all of the characteristics in URL or not and all actually it sums up how many are null I apologize so if we divide this by the total number in each column so we can do data a dot shape so if we look at just data dot shape it gives us how many rows and how many columns right so we would want to take the first one which is the number of rows and that gives us the percent of each one that is known so very few no one's in here but there's a lot of nulls and the size of the car maybe that's a bit more of an ambiguous field now the first thing I'm going to show you related to nulls is how to remove columns or rows that have over a certain threshold that you wouldn't want so let's say a column has like size over 65% and all we might not want to include that in our data set unless we were saying hey you know this is a meaningful feature if they include size or not where you change it into a binary or a small categorical variable a lot of the times if there's a really high percentage in all you're looking at like 90 95 percent you might just not want to include it all and this is how we would handle that so we're gonna in this one remove no columns over a threshold all right so we're gonna make a threshold and we're gonna say we want less than 60% to be no right so we can take the length of the data frame which is going to be the same thing as as that shape number it's just different way to do it I I like to mix it up just so you guys have a bit more variety and how you can issue the mass and we're going to multiply that by point six right so this threshold looks like that's around 260,000 of these of these rows should be not known and so we can now do data dot draw n/a and we're gonna set the thrush equal to our thrush variable that we had above and it's important here that we have set the axis equal to one so my previous videos you know that the axis is if we're looking at the columns or we're looking at the rows so in this one we want to drop the columns that have a threshold lower than this and so again here we did not do we did not use the in place so you'll see the data above is not affected at all right and we have this data frame that we output so we can do on this let's do shape and see if it's different than the one above oops sorry so as you can see we dropped four columns because four columns were above that null threshold we can also drop rows with you know less than a certain threshold as well so we do data drop an a fresh and then since we have 22 columns up here let's say we want a data frame where all the rows have at least 21 and 21 values that are not gone so we can set the threshold only one axis is equal to zero so let's give this a go as you can see it'll output let's do the same thing with shape and we'd expect to see significantly less in the roads value exactly so this cut our data set by almost three quarters doing that we have a couple options we can either drop nulls or we can impute them so we can fill the mas with a different values so sometimes it would make sense to fill it with zero so let's say that in a categorical variable we have a lot of nulls we can actually make it that we can use that as a feature to say if they didn't respond to this question that can be meaningful we can also replace that with the average of all the things in the column or the medium of all the things in the column and this is part of future engineering that could be fun that can help you improve your model so the decisions you make here can have an impact on your model I usually go through a couple of different versions of this so I'll look at the mean the median will look at quartiles different type of quantiles etc so I'm just going to show you here how to replace a numeric variable with the median or average if there's a null on that call alright so what we're gonna do here is we're gonna look at let's see here let's rerun this for Sentinel here so I know odometer is a numeric variable and we can see that from when we ran describe so if we're looking at that we can say okay I want to replace all of the null values in the domitor with with something-something numeric so we can run that through a model so the way I would go this no values is we just data odometer I spell that right find out a little book I get great and then we just use film at fill in a so it just will fill all the null values in that column with theta dot odometer media so I like median it's been normalized if we have like really crazy values which we might we'll find out a little bit later how to evaluate under that so here now we have if we did this in place is equal to true it would fill in the actual data set we can do now is null and then any and we shouldn't have any null values exactly so it's perfect with false on the null values there so we can do the same thing we can put a function in here as median this meeting is just the built-in pandas function we can also do mean and we'll get the average imputed in there instead so that's a great way to replace that data it's a very common approach to handling null values so moving on from null values I think it's always interesting to manage working with some text in your data set so in this specific data set we have like a description field and the description field has a mix of capital letters lowercase numeric etc and if we want to be able to evaluate that as effectively as possible we should probably make all of the letters lowercase or uppercase that means if we want to use a bag of word if we want to use some of these other techniques everything is normalized you can't really get rid of spelling problems but this is a great way to make your data as similar as possible so we're gonna convert here - all lower or upper so let's just take a look at data a dot description so it's the bestie and we're gonna look at the head of that right and so that's just gonna give us the first five so you can see selling BMW all of the other a good example is we have classic board it needs a home that's all capital that might look different in some of these different algorithms so we want to just turn all of this into a lower case set so what we can do is you can be data descending sorry we're gonna apply lambda function which I've talked about in my previous videos this just basically lets us write a really quick function and I'll fly it to the variable so do lambda X now we do X dot lower so that just converts all of the text in X which is our row to lowercase so let's just do the head of this here are to the wall no well ahead and we can see the same thing it's just now converted float it looks like so there are some numeric values in here that's another data pre-processing thing that we'll have to do I know that there aren't any lower cases and in the head of that data frame so let's just look at the head and apply the same function good so we can see this eliminated all of the upper cases that we were seeing before we can do the same thing the other option where it's gonna be lower case but where you can just upper there we go and it'll change everything again to upper case now you saw that we had a kind of a type error before that's something that now we can go into and talk about okay how do you manage the type errors so we had some float it looks like that means that I knew there was a numeric value adjust a numeric value here going from float to string is pretty easy going from I mean yeah going from string to float and provide some more complications so let's just handle a quick from like floater into string so to frame so we can just do data description as type string and it'll return everything is the strange so we shouldn't have the same problem that we did before it's the lower and that should work again unlike before where we had some of those issues it now works because it is classified this all as a string we can see this by doing gotta d type and it'll show oops types there we go so down at D types and I'll show the type of all these things if it is object it can be a mix of string and float an int or some of the different types so we want to make sure that in a circumstance like this we set a hard rule about what it is and what it should be going the other way this is a pretty like almost textbook picture perfect example of how you might want to go from string to int or float so we'll do to float so we're gonna look at data dot cylinders so it looks like we have four cylinder 608 cylinders you know six cylinders again etc and we also have some nons in there if we want to understand this differently a lot of people would say like why don't we just use four six eight why don't we actually keep them numeric and we're gonna go through some code about how to actually clean this up and make it just a numeric type so do that now we're gonna go to data dot cylinders you took just for a little background so we know that it is an object so that means it can have like a mixed type we're also gonna do data cylinders value count just to see how many are of each different type so we have other we have you know three four six eight five ten and twelve cylinders so no 16 cylinder cars in this data set here I guess they're not selling any Mac trucks or anything but the next step here is that we're going to replace cylinders with basically a blank space so we're gonna remove cylinders from all of these variables we're going to data cylinders dot apply so I like using lambda functions here so lambda and and we're gonna duel into X and then we're gonna take we're going to make sure X is a string I'm going to take that we're going to replace cylinders so these are all cylinders they should probably do change this to lower right knowing that at the string and then we're gonna replace cylinders just we're doing lower just to make sure that cylinders are all the same and we're gonna replace that with just a blank right I'm sorry there we go and then we want to strip this of of any extra so strip just removes any extra spaces on the outside so they should clean this up quite a bit and we want to set this actually equal to this so this will actually change data cylinders in our data frame here so that it has cylinders removed and it has it is stripped of any any extra spaces so it was actually booked now let's let the data dot cylinders value counts and see if it worked for us nice so we still have this other that we have to deal with and we have a lot of null values so there's a couple different things that we have to do here so we can transform other into noms I believe by doing this one quick step here so we can do data dot cylinders and then now we want to use pandas to numeric and we're going to data cylinders obviously and they're gonna be Ariston so yaarrr us and they equal we want to coerce them so that should make them into the correct data type so let's try this one more time we're going to do value counts again and now we have a good clean data set of all cylinders so the next thing we would do again would be to fill the null values so what's the data cylinders is null penny and let's compare that to the null values that we had before oops we want to sum I'm sorry oh so we're looking at a hundred and seventy eight thousand which seems to be good because that would include the nons there as well as the other here so we can now compute them with david cylinders dot fill na and david cylinders and then we'll do in place that will fill all those data frames there all of that data frame so now we should see there's no null values so when you're actually cleaning data one of the most valuable things you can use is histograms and box box so they tell you if there's something basically wrong with your data so let's look at a box plot first we'll do data box plot and then we'll look at odhh ometer right so let's see here we should see okay so there's a ton of outliers that go above here and this is at least this is to the seventh so we're looking at you know 100,000 fish models might be more not but usually you want to see a big box because that's where the the main bulk of your data is and then a couple outliers on the top if we look at price I think price is extremely skewed as well we can see that you know the box is very small here and we see a lot of upwards deviation you know this is like millions billions of miles which is probably unrealistic someone probably fat-fingered some things there so let's focus on you know understanding what this tells us about it the data that means that there's some error and we probably want to clean this data set maybe take you know a z-score within a certain range rather than using all of the data points because this error is likely related to someone miss typing something not something that is actually within the realm of reset the other thing that we can use is the histogram and let's do this for odometer and so this tells you how many very how many data points are within certain ranges so again we have very few that are super high and that's doing everything to this one bucket here which is not what we want we want a bunch of different we want to be able to see the actual distribution so let's clean this data up let's remove some of the noise by trimming a certain amount of unneeded variables and then let's do the histogram again and see what that looks like so we're gonna do this only on the numeric variables so where did Americans equal to data this is a kind of a hidden command so it's get numeric and it'll just return all of the all of the numeric variables sorry the data which is a fun kind of little little trick so you get all six of the numeric variables that we've created and now that includes cylinders correct so this time if we want to do this for all them we're going to import a couple different modules so we're gonna import four modules we're gonna do from Sify and pork fats and from numpy we're just gonna put on by pork thank you alright so the best way I found to do this is let's say we wanted you probably want to do these individually so you can do data we'll just make a new data frame outliers and that's going to equal to data we're gonna take data price and we want data got price quantile to be you know and then ninety-nine point fifth percentile right so it's less than whatever this value is so anything over this on the high end is going to be too high for our use case here we also want on the other end data dot price to the greater than zero dot dot price dot quantile then anything and the point zero zero five Rach and if we run this we should get a date outlier set so we shaved off you know 41 ish thousand rows here and so that should really reduce the amount of huge outliers that we have so let's take a look we just did price so let's do date it up box lock holder the price again and see how this looks so that oh sorry it looks the exact same width as we did data not eight outliers look a new or a second there you go so we still have a lot of upended outliers but this is the the mean right here is around $10,000 we have I believe the high end is the you know this is basically a certain subsection I believe it's like the 25th percentile to the 75th percentile these up ends of the 90 95th percentile like of the outliers and then these are anything above that area so let's also look at the histogram of the price again as well just to see what that would look like good before this is what it looks like before right so everything was right around the left side of the screen because we had such huge outliers now when we run it it should look more like a normal distribution or a half of a normal distribution because they're starting at zero so that's also something to consider I'm not gonna get too far into it in this video but on Craigslist it looks like a lot of people just put the prices 0 and did like a what's your best offer type of thing that's something that we might want to remove all those in the data set I get something that's more normally distributed or we want to you know change how that's done and see what if they in the text they had some sort of pricing information but it's something to watch out for something to think about when you're evaluating this data set let's do the same thing for for the outliers with not just price with odometer so we're gonna change the odometer here we're going to change the odometer here we're only need to change it over I guess and if you're going to be repeating this whenever you have to do something more than once I recommend writing a function but for this use case for essence of time not butcher so same thing data outliers hist let's do box and then we're gonna do odometer so we should see something similar again you have this box plot which is pretty normal you know no one's selling a negative car value so we wouldn't expect to have too much downside deviation but there is a good amount of outliers that are above that certain threshold up here so again this is a great way to visualize and understand the data cleaning process and this is how we can go about removing certain thresholds so we could remove an even larger threshold we could remove on the top side threshold budgeting something just this but I think for this use case this is a the most effective process right now so the last thing I want to talk about related to data cleaning is scaling your data so for some algorithms especially the ones that use Euclidean distance like K nearest neighbors it's important to make all of your data on the same scale so again for something that uses distance larger values can have larger distances when and that means that some features will be accounted for more than others in the algorithm so when we scale something we put everything in the same numeric range so a lot of the times you'll see like like z-score scaling where you make everything a z-score or you'll have like I guess like some sort of mean differencing which can give you a score between negative 1 and 1 and then you have min max scaler and some other vector scaling techniques that will give you a value between 0 & 1 I don't like that when you're on one there they're great for different types of analysis or again K nearest neighbors or doing some PCA stuff so let's go through and show you how to do this you don't generally need to scale your data with linear regression or with true based models or you know naive Bayes but there are some specific types of models even in deep learning where actually scaling your data can improve speed and quality so to get started with min max killing we're gonna use a different library than we have and we're gonna use SP learn so we have to import that so from SK learn pre-processing and or in max scaler and we're gonna do so when you do this you have to create a scalar object and that is all right so we fit the scalar object and then we use the scalar object on our data set so in order to fit this we need scaler fit and we put in the data that we want to fit to it so it's data cylinders and then so you have to do some to get one frame fit you have to do some idiosyncrasies to get it in the four out that it's looking for so we can either use numpy to do this or we can do kind of a walk around in pandas I'm going to do that since this video is mostly focused on pandas with these values which creates an array so data Java cylinders is a series if we do data cylinders dot values it's and it becomes an array and we have to reshape this array so it's an array of arrays so we get negative 101 and let's look how this looks now so it seems like a lot of Hoops to go through but now you can see this is an array of each of these individual rights that we have that's just the way that this feature scaling mod will accept the data in so we have an outfit this and we do scaler transform and then we do we copy this same information so as you can see previously we're looking for six and then this is the average number of cylinders we should be getting things all now for cleaning at zero and one so same thing now we have ones and these are all scaled individually so we can also skip the fit and transform set and we can just do scaler fit underscore transform and include the same stuff and we'll get the exact same results but generally how SK learn works is you fit a model and then when you want to find the output from a new input you use this transform feature and that's how they have built out all of their regression all of their machine learning any of these other algorithms that they use there so after you scaled this you can scale all of your numeric data or just some of it again depending on the type of analysis that you're hoping to run I think that's it for data clean today again thank you so much for watching if you have any questions about this please leave them in the comment section below good luck on your data science journey and please look forward to more videos like this from me