Data Cleaning for Everyone | Don't Ignore This Crucial Data Task

I'm going to talk today about data cleaning and some of the things that I've learned in my time as a data scientist, but also that I see really commonly in people going through the certification process. So just a quick background, as Richie said, I manage the certification content team at Data Camp, so I'm working with our subject matter experts. So people who are working as data scientists, data analysts, data engineers, hiring for those roles, the whole range of experts on what it takes to actually get these jobs. And data cleaning comes up in all of these data roles every single time we talk to them. Before I was at Data Camp, I was a consultant for a data science firm here in the uk. So I also actually got my hands dirty cleaning data. So a lot of the things I will mention, I've actually had experience in myself, so hopefully share some of my experiences with you as well as some of what I've seen from that certification process. So, as Richie suggested, the reason that I'm here is really because of what I see day in, day out with our certification, and that is that not cleaning data correctly is the most common reason that people fail certification. And that is across all of our certifications. You can now get certified as a data analyst, a data scientist, a data engineer, or in SQL at multiple different levels. And across all of them, not properly cleaning data is the number one reason that people fail. I'm pretty passionate about this topic. I want people to get through and pass the certification. I want you all to be able to show that you have a huge range of skills and I want to make sure your, your cleaning is there and you're ready for this. So that's why I am here today. So today what my goal is is to really start you thinking about in the right mindset for data cleaning, how do you approach data cleaning and what are some of the most common things to look out for. I'm not going to tell you exactly what we're going to put into the certification exams, but I might give you a few hints and tips on the way that will help you out. But as Richard said, please do feel free to ask any questions as we go through about this, or if you have other questions around certification, I'm happy to answer those as well. It's pretty much obligatory, I think, to start with Wikipedia's definition of data cleaning. Data cleaning, according to Wikipedia, is the process of detecting and correcting corrupt or inaccurate records. And I kind of agree with this definition. It is a technically accurate definition. You're trying to find things that are wrong in your data and put them right so that you can carry on your analysis. But for me, it doesn't really capture why I should really care about data cleaning. Why is it the most important part of your process, really? For me, data cleaning is about getting to know what data you actually have. If someone gives you a data set, what does that data actually look and feel like? Is it exactly what you expect? Are things a bit different? How might you be able to work with this data? What questions might you be able to answer with this data? Data cleaning really delving into what do I actually have? What does it look like? Really getting into the nitty gritty of that is what data cleaning is about for me. And some of you might say, well, isn't that what exploratory analysis is for? But exploratory analysis for me is that next step. Once you've really got to know your data, it's then exploring it to start to drive insights. But data cleaning is really when you get into the detail of what is in my data set, if you take nothing else from what I say today, this is the one thing I would hope you take. And that's the biggest mistake you can make in data cleaning is not actually looking closely at your data. I see every single day people going through the certification and they'll do a quick summary count of how many missing values there are. A quick output of these are the output types of the data. We'll talk about some of these things later. They're perfectly valid steps, but you need to be looking in much more detail at your data. Summaries are not going to give you the insight into what data you really have, whether it is exactly as you expect or different. So really, please, please, please look at your data closely. By the time you finish your data cleaning, you should feel nobody can tell you any more about this data that you didn't already know. Although I will say from experience, I don't think that is ever 100% true. But you really should know your data well after you finish your cleaning. So how overall should we be thinking about data cleaning? What are some of the steps to take? So said that it's not just about creating summaries and there's really five steps that I think are really important to focus on as you go through data cleaning. I'm going to focus on four of these today and leave one out. I'll come back to the one I leave out at the end, but this is roughly what we're going to talk through today. The first most important question I think you have to ask when you get your data set, before even you open this data set is start to understand what is the backstory to that data set. That really starts by asking yourself where does this data come from? And how was the data generated? And there's a few different reasons. These are really important questions to ask. So where does the data come from? Has someone given you this data that you can trust? Has it come from somewhere on the Internet that maybe you're not sure of the the accuracy of that data, if it should really be used, is it trustworthy? Or has it come straight out of your company's database? You're really confident in where that data came from and you're happy to use it and trust it. So they're too much more extreme ends. But asking yourself where has this come from? Can also start to help you understand how you might go about cleaning it. So if you've been given this by a data engineer in your organization who has spent a lot of time making sure it's in the right formats and scrubbing that data for you, you're still going to need to do some cleaning. But it is probably in a good shape already. If someone has just pulled it together for you by putting information into a spreadsheet, you might need to do a little bit more there to that that starts to touch on then how was the data generated? Data generation is a really important question that can help you to think through some of the cleaning tasks you might end up facing. This can be as simple as was this column in my data created by someone picking a drop down menu in a form where the choices were fixed? Or was it created by people typing text in a box? That difference can totally change how you start to think about that particular column of your data and the things you might expect to to see in those particular values. I'm definitely going to mention this a few times, but as soon as a human has to type something, you're going to experience typos, spelling mistakes, differences that you're going to need to clean. Being aware of that from the second you get the data is really, really valuable to you. Where does the data come from? How was it generated? The next question, if I can get my slides to move forward, do you know what each row relates to? And the columns in the data as well. So actually starting to think about that data now. So what is each row? That may seem like a really obvious thing. Well, it's an observation, it's a row of data, but what does it relate to? Is it Every time a product has been sold in your organization, is it every time someone has clicked on a page on your website? Is it a summary statistic that you've been given? So what does each row actually represent? What are you being told with that information? And what about the columns? Sometimes you could get data and you think, yep, I know what all these columns are about. Straightawear, maybe you've worked with that data a lot before and you think you know, but it's always worth just starting to put together for yourself. Essentially a data dictionary, an overview of what are all the columns that I have? What do they mean? What should I expect to be in that particular column? What's the typical range of values? I would expect you may be very lucky and someone may hand you these sorts of descriptions straight away, but if not, which is far more likely, in my experience, you're going to need to find that out. You're going to need to start asking questions and figuring out all the different columns in your data. I want to think a little bit about an example to talk you through some of these things in practice. And the example I'm actually going to use is an example from a former certification examination. If you've taken one of our certifications in the past, you might recognize this. But before you get excited, it is no longer a live exam. So I'm not giving anything away by showing you this, but we're going to think about an example of a company called petmind. And I would just say petmind is an invented company. If it does exist, it's purely coincidental. But this is an invented company that sells products for pets. They sell a mix of luxury items like toys and everyday items like food, and they want to increase their sales by selling more everyday products repeatedly. So they want to sell food multiple times to the same customer. The data contains sales records for the last year. Now, from just this description alone, this is the sort of description we'll give you in certification exams to give you a bit of backstory to the scenario you're working on. You can start to get a sense of the things you might expect to see in your data. You're probably going to see categorization of types of product. You're probably going to see sales in there. Hopefully we're going to find out if something sells repeatedly or not. And the very last line there has told us it contains sales records for the last year. So this is the last year's worth of data is what we're expecting. So if I were to jump straight into my data set and start to look. There's probably not a lot that I can tell about this data. There are some of the things I was expecting here, but. But I don't necessarily know what all of these columns represent what I should expect to see here. The observant among you might spot a few issues in here already. But are they issues or is it intentional that they are the way they are? So it would be really helpful to start collecting, as I mentioned, that information on what each of these columns are. If we look at the rows, it said that the data was related to sales over the last year. So I can get a good sense. I've got a price column and a sales. So maybe this is the total sales for each product. But yeah, I can't tell a lot just by looking at this data. So I need to start asking more questions to the people who provided that to me. Now, in general, in the the certification exams, we're going to give you some of that information. And just to show you this is the type of thing we would give you in certification. And this is something that if you were doing your own analysis outside of certification, I'd really strongly encourage you to put together something similar yourself. Here is a really basic example. We've got each of the column, it's shortened to fit on the slides, but we've got column names on the left and a description here of what they are. I can see, for instance, I've got a product ID and this should uniquely identify the product. We put a little extra information in here. In this case, there shouldn't be any missing values in this particular column and that tells me immediately I want to go check are there missing values in here or not? Because if they are, something has probably gone wrong in this data and maybe I want to go back to it, but hopefully there are none. If I'm looking at the next column there category. So this should be the category of the product. And I've got some useful information here. One of six possible values. So it lists the values there immediately. I know if I have more than six values here, something might have gone wrong. Maybe I have less than six values. Is that okay as well? So lots of information. I'm starting to gather questions I can ask about this data set and I haven't even written any code yet. I haven't actually looked at the data in any depth. So really, really important to get that backstory and understanding of the data. Now, missing values was one of the next things on the list of things to check. So before I carry on. Just want you to take a moment to ask yourself the question. Think the answer for yourself. How would you check for missing values if I asked you to do that? Now, Now, the answer that you're going to give will probably depend on which tool you're using, whether you're programming or using something else. But I've just put a few examples up here. You might have thought of something similar to one of these three. We've got Python R and SQL as examples here. And these are all ways of finding for a data set, a column in a data set. Are there missing values in here? But there's a huge problem with this. And the problem is that all of these lines of code already assume that this tool knows what a missing value is in my particular data. This is probably the second thing I would say. Please take from this. Missing values don't always start in the default format that your tool or technology would recognize as a missing value. Most of the time you're working with missing values, they are going to be represented in different ways because different tools that may have generated that data may put them into a different format. Humans will put missing values in as different things. I know from my experience if I'm creating a table of summary statistics, maybe I'm creating a spreadsheet in Excel and I know that a value is missing, I'll put a dash in it because it will help everyone else to understand it's not a mistake, there isn't meant to be a value there. But imagine if I then try to work with that data later on. I've got a whole bunch of dashes and my tool isn't going to recognize that that's a missing value. It is really, really common that when you get a data set before anyone else has done anything to it, you might find that missing values are not already N is. This is one of the challenges that you have to work through is figuring out how are the missing values represented and what to do with them if they are there. And are they really missing values? Is of course an important one. I've just listed a few common ones here. So dashes, dots, spaces, excessive amounts of spaces or tabs. Really common. This may seem like a strange one to some of you, but there are some tools that will put large numbers. I'm not sure -999 is really a large number anymore, but historically tool would put something like this in in place of a missing value to stand out from other things. So there's all sorts of different things and ways people can represent missing values and it is not using your NA or null or standard missing value character. So do be on the lookout for that. Now, I talked about looking closely at your data to find problems. So what are some of the things you can do to to really start to see are these issues in my data? And the first one is just checking the data is the right type. I suggested at the start that sometimes people do this and think that's all there is to data cleaning, but actually looking at the type of your data is an important step, but it's what you do with that information that's important. So if you have a column in your data that should be a number, but your tool is telling you it's a character string, that could potentially indicate you have missing values that are a dash or a dot. So take a look at the actual values themselves. Are the categories what you expected? So I talked about the example in the pet stores of the different categories. Are they showing just what I expect or is there an extra category that is something else? A dot, a dash, an unknown value that could represent missing value. So someone might have even just written the word missing in there. That is also equally likely. Are the categories what you expect them to be or do you think one represents a missing value? Just to show you this on our pet example and what I didn't mention, we simulate all of the data used in our certification exams. I know we have put missing values into these and we don't just put them in as nas. I'm not going to tell you what we always use, but there will be a few different things in there. Here is an example from that pet store example where I've used Python here to find how many missing values are in each column. What you might think is a perfectly reasonable thing to do, and it tells me there's 150 missing values in the rating column, tells me there are no missing values in the category column. Now, I personally know that there are missing values in here, but you can see from this that it hasn't highlighted that just relying on is NA or similar is not necessarily going to get you to the answer that you're looking for. If, as I suggested, we take a look at the different categories within the category column. Apologies, that's very confusing, but I can see that one of those categories here is a dash somewhere. Dashes have been added into this data to represent missing values. I probably want to now go back to this data. Maybe I want to confirm that with the person who gave me the data first. But I want to go back and update this so that I can treat it as a missing value. There are more to missing values. I could talk about missing values for hours. You can take courses and courses on missing values. So I won't go into the whole extent, but if you're wanting to take this a little bit further, it's also worth looking at missing values and how they relate to other variables in your data to start to understand if there's some structure to that. In the example we had, maybe the missing values always relate to a particular type of animal. Maybe only one animal has been excluded. That would actually maybe tell us a little bit about how the data was collected. If there were issues in the data collection process. But it's worth taking a look. Are there only missing values for certain subsets of the data? Are values always missing for multiple columns? Maybe you've got missing values in 10 rows of data, but it's always the same 10 rows across all columns. So there are ways that missing values could have some structure to them that is worth considering while you're, you're doing that investigation. So there's a whole lot more that you can investigate here if you are interested. And it's probably one of the topics you will think about a lot as you start to do more and more analysis. All right, I mentioned data types already, but the next question I would be asking myself once I'm happy with missing values is are the data types correct? And we've seen obviously already that incorrect data types could potentially indicate missing values. But there's a whole lot more that that could be telling us. One thing I will say I'm not going to touch on dates. Converting dates to the right type is a huge, huge topic. We could probably have a whole webinar just on that. But I'm just going to focus on some of the more common data types that you'll experience every single day. Dates I will leave today. But one of the most common things that might go wrong is that your numbers may be stored as strings. I already mentioned this. In the case that someone has put a dash into your column, your whole column then might be stored as strings instead of numbers. Hopefully you're aware if you were then to try and do something with that. If you're trying to calculate a metric, a mean, or maybe create a graphic, it's not going to come out the way you expect if it's stored as the wrong data type. It's really important we get to the bottom of what's going on there. As I said, missing values can cause those to be incorrectly coded. But there are other things that can happen with numbers that can lead them to be stored as strings. So someone might have randomly written a word in for a number. So think of a situation where a person is manually entering data. Someone might just one day write one as a word instead of one as a numeral. So something like that could happen. Spaces can be added to the end of numbers in tools and that can convert them into being strings. Lots of different things that can go wrong there. Spaces is one that's always really, really hard to spot. But I think that's probably the cleaning task I hate the most is finding extra spaces. But it is reasonably common to come across. But more commonly you'll find extra strings on the start or end of numbers. So money, a really, really common one. Having a currency symbol somewhere in your number. Especially if you're importing data from something like Excel, something like that. Every tool treats it differently. But you might suddenly have a currency symbol in front of or behind your number that you need to deal with. You need to make sure that that disappears so you can treat it as a number. Dates in the wrong format. As I said, dates are a whole topic, but if they are in a non standard format, they can be treated incorrectly as well. So that is something to look out for. But there are a whole host of all different things putting words on to the end. So if your number represents a weight, for instance, maybe someone's added kilograms to the end of your number, the unit that is in. So there could be all sorts of different things people add to the end of numbers to look out for. The other one I want to mention, because I think this is my personal pet peeve, so maybe no one else is too worried about this. But categories as numbers, really, really common. It is really common that a number is actually representing a category. So think about anytime you've been asked to give a rating that is not a continuous number, you can't give any possible number on the number scale. So it should often be treated as a category. Especially if you're visualizing coming up with metrics, you want to treat that a little bit differently. So don't just assume because you have a number that it should be a number, should it be something else in your data? And the only way you're going to know that is by thinking about the context, all of those questions that you ask at the beginning, the backstory to your data. That's how you're going to find out if what you have is really a number. Or if it should be a category. My other tip, I keep talking about looking at the data. It's really, really important to look at the data. And I know data can, can get really, really big now, but it is very important to take a look at it. Use all of the methods and techniques you've learned to really delve into it before you start doing anything. But do take a look at the data before you just start converting the type. Don't just assume that, oh, this column should be a number, so I'm just going to convert it. Because you could end up doing strange things, you could end up losing information. Your tool might just convert it to a missing value, when in fact you had a perfectly reasonable data point that you could have just converted. Take the time to look what are the problem entries in your data? And can you yourself do something to that to make that data usable rather than just throwing it away? Just because your data doesn't look the way you think doesn't mean it's useless data. You can often make some quite straightforward assumptions to correct that and make it into usable data. Just because you've got millions of rows doesn't mean that you can just throw things away or that you need to throw things away. This is an example from the pet supplies. This on the left is something I've seen many, many times. People looking at the data types. Yes, this is fine, but what you can see, so the price column here is represented as an object. If you're not familiar with Python, that is essentially telling me it's not a number, even though it should be. And when I take a look at the values, we can actually see that the last value there I've highlighted, we've got this unlisted character string in our data. So if I convert those values to a missing value, I can then convert the rest of this to numbers and this would become a real usable column in my data rather than something that is challenging to work with. So worth taking a look figuring out what is going on. I will say what you haven't seen, you've seen the nice end output here. What you haven't seen is that even I, who have looked at this data many, many times, had to scan this data a few times to spot where the unlisted value was in the data. So don't worry if you don't always spot it straight away. It might take a little bit of looking to find the problem. The second thing that I mentioned, so the categories, this is an example of that. So we've got a rating column in this data. The rating from the description that we provide for this data should be a customer rating of a product from the values 1 to 10. So discrete values from 1 to 10. So it may be that it's okay to treat this as a number, leave it as a number in your data, but you definitely want to be thinking about what analysis are you doing with that, how do you treat that particular value, what visualization should you create with it? So definitely think carefully about what you're doing with these values. Don't just assume, oh, it's a number, I can take a mean or something like that. Think carefully about your data. So on to categorical data. So categorical data again can be quite problematic. The problem is people assume that categories are correct, that they should always be correct and they're never going to be incorrect. That people have this perception that, well, a category, it's been picked from a drop down menu. It's only a fixed set of values, of course it's going to be right. But this is again why I really promoted at the beginning the idea of getting to know how your data was created and where it came from. Because categories could have been entered by a human, even to create that drop down list. A human may have created that, but if not, if people have had to type into boxes, there could be inconsistencies. You could have one person writing a word in one way in lowercase one in uppercase one in title case. There could be spelling mistakes, there could be typos, there's all sorts of things that could go wrong if a human has to enter anything with that data. But knowing where your data has come from can also help. Imagine where you have different teams in different locations technically collecting the same data. But they may use different ways to encode a variable. Let's say the example of the size of different pets. And maybe one team uses the full word medium and another just uses the letter M as a short form for it. So suddenly you've got two different representations of the same value. The other thing, data collected over time. So categories might have changed, there might be historic categorizations that you could map to new categorizations. So again, just knowing what data do I have, what is it telling me? How can I get the most from this data? Do I need to change it in some way in a reasonable way that will actually add more value to me? My last thing I just want to mention really is don't forget to visualize your data. This is often overlooked. People jump into all of these Summary statistics. But visualizations can be massively, massively powerful. We do in certification grade you on visualizations as well. Not at the associate level, but in the more advanced certifications. We are looking to see that you have used visualizations to get to know your data. It's not just about the visualizations you use to present at the end. It's also about using visualizations to understand the variables in your data. So this is the sales variable from the PETS data that I've been using throughout. And by taking a quick look at this, I couldn't see this from the numbers that I had in front of me. But quick visualization, I can see that maybe there's a couple of peaks going on in this data. Maybe I want to investigate this a little bit more. Maybe there are underlying impacts from some of the other variables in my data. But visualization really helps to see that in your data. Please don't forget visualizations, even during the data cleaning stage. Just because you are not going to show them to your boss's boss or something like that doesn't mean it's not valuable to the analysis process. Creating quick visualizations really helpful. And the final thing that I want to talk about in relation to cleaning is documenting any assumptions you make or questions that you may have. This is particularly important in certification. In certification, you're not going to have the luxury of going backwards and forwards to ask someone any questions you might have about the data. But that is also true of real life as well. There have been many occasions when I've been working on a piece of of analysis where I haven't been able to just pick up a phone and ask someone a question or drop someone an email or a message on Slack. I've had to wait quite some time to get answers and I could have just stopped and not done anything or I could have said, hang on, right, let's make a document. What have I assumed about the data? So maybe I've assumed that M and medium are the same thing and I'm going to carry on and do my analysis with that assumption. Maybe I can't actually understand the variable properly. So maybe I've got some questions about that. So creating some documentation, a list of questions and assumptions that I've made, can help me to progress and get to know my data more without just stopping and waiting for someone to give me an answer. And in certification, that is how we expect you to do it. We're not going to be able to answer all of your questions. You are going to maybe have to make A few assumptions about some of the data that you're seeing. And just to reassure you they're not going to be crazy assumptions, they are reasonable assumptions we might ask you to make in line with things that you might see in the real world. So keep track of it. Make sure you know that you can tell someone later on. This is everything that I did, these are all the things I assumed and this is what we learned from that. Because then they can come back and correct you later and say, actually, this is not the same thing. You need to treat this differently. So keeping track of that is really, really helpful. I covered four of these. The one that I didn't cover is duplicate values, duplicate rows in your data. Duplicate rows is something it is really important to check for. For. I have to be honest, I don't experience this on an everyday basis. It's not as common as it seems to be in courses that I take for me personally, but it could be something you have to deal with. It's worth checking for, but that is definitely something you want to get to the bottom of. Why are there duplicate rows? Are they really duplicate? Can you get rid of them? Do you need to keep them? Lots and lots of questions that they throw up. So definitely look out for those things. But my tip is we're probably not going to give you them in your certification exam because they are quite a big topic that you need to ask a lot of questions if that sort of thing comes up. So be aware of it, definitely take a look, but know that you should get those questions answered before you continue. So with that, hopefully today what I have done is given you more of an overview of some of the things that might come up, why they might come up and how to approach broadly going about that. But if you want the specifics of how to deal with some of the issues that I've highlighted, I've picked out three courses here, Python R and PostgreSQL because these align with our various certifications. There. There are others that you can take on Data Camp, but these would be the three that I would pick out specifically for. Yeah, learning to, to actually go about doing each of these different things I have talked about. And I think with that, I, I, that's all I have for today. And now I'm happy to take any questions that anyone might have.

All right, thank you, Amy. That was brilliant. Because I sort of think that when you're cleaning data, data can be dirty and like all sorts of ways, like there's maybe infinite different ways things can go wrong. But it's nice to know that actually there are only like, maybe a few dozen ways where it's really common for there to be a problem data. So I like just this sort of strategy of. These are the specific things you have to look at.

Yeah, I think there's definitely like. Like the missing values. How are they encoded? There's like, so many ways it could be, but that will come up at almost every analysis you do. So, yeah, the strategy is the same, even if the specific detail might be different each time.

Okay, brilliant. So we've got plenty of time for questions. So for everyone in the audience, if you've got a question for Amy either about data cleaning or about certification more generally, please do ask in the chat. In the meantime, while you're thinking about, I've got a couple of questions for you. So I remember, like, back in the dark ages, when I first joined data camp, we had the data cleaning courses were one of the first courses we made people take, and we found that people were struggling with them. It's like, actually, data cleaning is more of a difficult task. So now that goes later in the curriculum and we sort of teach data manipulation, exploratory data analysis. First, I'm assuming whether you have any opinions on when people need to start worrying about cleaning data.

It's a really good question. I mean, the second someone gives you a data set that they haven't told you everything about, as soon as you go outside of any course that you might take, you have to worry about cleaning dirty. You have to assume that your data is not clean. It isn't the most glamorous task to do, which is why it definitely gets pushed down in terms of learning, because you want to get to the fun stuff first. You want to get the graphics and the insights and see what you can learn from data. But yeah, it needs to come really quickly. The actual elements of how to do it, I personally don't think are actually that challenging. But putting together that whole thinking process of how do I even approach this seemingly mammoth task? I think that's actually harder than the individual pieces themselves. So, yeah, as soon as anyone gives you a data set that is not in a training course, you absolutely have to worry about cleaning it.

Absolutely. Okay, so maybe you just need to learn the individual steps and then figure out how to put them together later on.

Yeah, yeah.

Okay. And you mentioned the data cleaning courses. Now, I know those are fairly advanced cleaning courses. And there were a lot of different things that you talked about as you talked about things like dealing with missing Data handling, duplicates, cleaning dubious strings, things like that. So I know there are a lot of different courses on datacamp for those specific tasks. Would you recommend that people who are taking certification go for some of these other courses as well?

Yeah, I mean, it depends how, how much you want to delve into the whole range of possible functions. I mean, hopefully from what you've seen today, you can get a sense that actually by the time you've gone through the core courses in our tracks, so the data scientist, data analyst, you've probably actually learned all of the pieces that you need. It is just about bringing them together in different ways. There's always more that you can learn. So I'm never going to say no, don't take those courses because they're not going to help. They absolutely are. But I would just say, like, be really, really confident in your basics, your style, standard skill set. And for, especially for our associate level certifications, that is going to be enough. But once you've done that, you should definitely go, go take more content, more, more learning, find all the different ways of dealing with things and, and especially if you're thinking about working in a particular industry, if you do work in a particular industry, thinking about the cleaning tasks specific to that industry, I deliberately didn't go down dates and times. But if you're working in the finance industry, you're going to have to, to get comfortable with it sometimes. So there's a lot more that you could delve into there and yeah, definitely suggest it eventually, but maybe not on week one.

Okay, so there's enough to get you started with just maybe a couple of courses involving data cleaning. But then if you want to learn more about it, I think we actually, we must have more than a dozen courses that touch on. All right, excellent. We have a load of questions now from the audience, so let's take a look at what people are asking. Okay, so this one comes from Jessner. So what are the steps have present in the moment? Cleaned it so. Oh, right. So I guess is there a sequence to this? Like you, you talked about a few different areas of cleaning data. Like where do you begin? Is there an order to it?

I mean, I, I would definitely, I. The reason I put the like, backstory stuff first is because I really do think that is the, the number one thing you should start with. Everything else you can often do in any order. I would tend to focus on what's the question I'm trying to answer and what is the stuff I'm really going to need with this data first but really knowing where did the data come from? How was it created? What have I got in the data? That absolutely has to come first. You have to get that good understanding first. But then after that I personally wouldn't worry too much. I guess I've put things in, in a way that I would probably logically do it, but if I looked at the data types first and then missing values probably isn't going to change too much about my analysis, but it's going to depend on the data because sometimes as I mentioned, like missing values could appear as a data type inconsistency. So you're probably going to be going a bit around in circles with all of the different pieces.

Okay, so it sounds like this is going to be some sort of iterative process where you try and guess what.

Yeah, try something and then move on and think, oh, maybe I need to go back to that bit. And yeah, but I, I probably take it column by column more in my analysis rather than I'm going to deal with missing data first.

So yeah, okay, that's an interesting approach. So start doing things column by column. All right, next, there's actually a few people asking very similar questions. So this one's from Yachima. There's another similar question from Julia saying do you need to worry about treating outliers as part of your data cleaning? So I know we have courses on anomaly detection and working with outliers.

Yeah, and outliers again, it's like a whole nother topic into itself. I deliberately didn't mention outliers today because I think outliers isn't necessarily a data cleaning task because it all depends on why that value is an outlier. So I, I, I should probably also I'm like a bit an old fashioned statistician person who doesn't want to throw away data because the data is quite small and, but I, I actually don't think that it's helpful to use rules of like oh, it's so many standard deviations, therefore it's an and I'm going to drop it because actually I think it's more interesting of why is that value an outlier? Is it really an outlying value that adds useful information to your analysis or did something go wrong in the data entry process? So yeah, taking a look at it, considering it, but don't necessarily in your data cleaning just get rid of that value in your data. Don't just kind of apply rules and go, that's it, I'm getting rid of these values. So but that's again where visualizations for me would, would be more valuable. It's sort of take a look at the data. Say, is there anything that stands out as being odd for, for some reason? But yeah, I, I definitely wouldn't clean them out of my data.

Yeah, I suppose if you got like data on weights of people and you've got someone who's 250 kilograms, it's like, well, it might be a typo which places a data cleaning issue or they might have entered their weight in pounds and that's another data cleaning issue. Or they might be really fat and it's like, well, it's real data. So lots of different possibilities there.

You pro. That's where like talking about, write down your list of questions for wherever your data came from. That would go on my list. So. Absolutely.

Nice. Okay, so next question comes from Sumanth saying when you work with data engineers, how much of the data cleaning is done by data scientists or how much by data engineers? That's really interesting. Yeah. What's the sort of split of this within an organization?

Yeah, I think this is a really good question because it's not, I think you should not. If you're a data scientist or data analyst, do not assume the data engineer is going to have perfectly cleaned your data for you because they may not know that something is an issue. So maybe it does need feedback to your data engineer of, hang on, this is something that needs handling, but I guess it's going to depend from company to company. But I'm not sure I should say this because it's being recorded and my colleagues might watch it back. But I'm not sure I would ever trust some data that someone has given me without looking at it first and making sure, do I know what's in this? Am I comfortable with, with this and what's been put into here? So yeah, just because you've got a data engineer, don't assume that they've done your job for you. I guess is the short version of that.

Yeah, so I suppose it depends. Like in general you want the data to be cleaned earlier in the process. So that's going to be a data engineering task. But sometimes what constitutes clean is going to depend on the analysis. So yeah, so yeah, data engineers aren't going to be able to clean everything because they can only clean stuff enough that it's suitable for like any possible further analysis.

Yeah, so yeah, definitely. Just, just check your data, like always check your data. Don't assume anyone's cleaned it for you.

All right, nice. So next question is From Billy. So Billy asks, how much of a data analyst job is data cleaning? So, yeah, maybe we take that first question first, we come back to the next one later.

I mean, the one everyone always says is the 80% of the job is data cleaning. Who knows how that number got invented? But I do think it does ring true. Certainly in my experience, I spent far more time cleaning data than doing fun modeling stuff, which is why everyone wants to be a data scientist or like you're going to do far more cleaning than anything else. So is it really 80% of my time? I don't think so, but especially once you really get to know the data. So the data I'm working with every day and now I know that data very well. I know the sorts of things that are in that data and that I need to look out for. So I probably spend less time time cleaning now, but largely because I've got pieces of reusable code to do it for me. But if I'm just getting used to a new data set, I'm going to spend an awful lot of time on the cleaning of that. I think it varies, but assume you're going to spend a lot of time cleaning data. I think, yeah.

I've certainly worked at companies in the past where there was very little quality control over the data and it's like way too much time spent clean cleaning it. But that. Amy, your point about how if you're working with similar data sets over and over, a lot of this stuff you can either automate so you can have automatic processing with the data to clean it, or you can give feedback to say, well, this column, like people's locations or whatever. It's always terrible. Can we have this as a drop down so people don't make typos and then that's fixed forever and you don't have to clean it anymore?

Absolutely. And that's something that I would be looking for in certification. They're the sort of things that I'm looking for in people's submissions. Like, have you thought about how you could solve this problem in the future? And feedback you could give and that sort of reason to document and ask questions is so that you're essentially giving feedback to the people who gave you the data of like, look, your data is not great, let's do something about it. So yeah, definitely multiple purposes to that, but it doesn't mean all the problems will go away.

Excellent. All right, so the second part of this was are there any conditions that should be met for data set to be cleaned and when can we rely on, oh, so, yeah, how clean is clean enough? And can you use synthetic data?

How clean is clean enough? That's a really good question. I think it probably when you're comfortable that you know what's in your data and that the thing that you're confident with, the things you're going to be using to answer the questions you have is probably the point that you could say it's clean enough. It's a really tricky question to answer because it's never going to be the same twice. You can't say like, oh, this is now clean enough for my analysis because the next time you do something it may not be. Having said that, if you ignore data that you're not going to use, there could be issues that are impacting the data you are using. So, yeah, it's definitely worth just being aware of your whole data set. But I'm not really giving a great answer to this because I don't feel like there's a. Like this is when the data is clean enough. I think you have to kind of look at your data and think, think through what you're seeing and feeling there.

Yeah, I suppose is if you can get the right answer in your analysis, it's probably clean enough.

Yeah, probably. I maybe should answer this from a certification perspective of when you found the problems we've put into the data, then it's clean enough. But in real life you don't have a known cleanliness number. So yeah, all right, super.

The other thing about synthetic data, actually I think that's a whole separate topic. We did do a code along that on synthetic data a few months back with Alexandra Ebert. Maybe if Rhys can locate the link for that, we'll pop that in the chat. But yes, synthetic data is big topic and yeah, too much for just now. All right, so Richard asks, do you have a preferred language for data cleaning? Python SQL. Are those are the three we covered today? And do you have any advice on libraries or packages for cleaning?

I'm going to get into the grain. I'm actually an R person. I learned R back when I was at university and it is still my tool of choice today. Even today when I'm doing analysis, I'm using Datacam workspace to get my data out of our database and, and get it into R as quickly as possible. So in terms of R tooling that I'm using, I'm using generally the tidyverse packages because I like how smooth they make my analysis. Python. If I am doing work in Python, then I'm Using Pandas and the standard things there. I don't tend to use packages that claim they will do cleaning for me or find. I choose in data because I really want to get my own sense for the data that I have. So essentially I don't trust tools that people have written that will tell me that it will automatically do my cleaning for me and I want to get that feel myself. So I'm using the Tidy verse in R, Pandas in Python and yeah, if I'm using SQL, just the standard postgres functionality there.

Yeah, I've say I. I do feel like the. The tools for data manipulation stuff in R is still slightly slicker than in Python. But in general, because most data analysis or data science projects are collaborative, involve multiple people, you should use the language that your colleagues are using because otherwise you're stuck maintaining that code forever and like no one else can, can help you out. So being able to work together is incredibly important. So if you're in a workplace, use whatever everyone else is using.

And yeah, I would just I default to R, but I do have to use Python as well. Now my preference would always be R, but Python is more common.

Excellent. So just a question, Patrick, can you pull up the slide with the five steps again?

Yes.

While you're doing that, I'll say we are going to run a bit over because we've got a few more great questions from the audience, so we'll carry them for five minutes more. And I just want to say we've got a few more sessions coming up, so click on the QR code for future events. Tomorrow we've got a leadership focus session on how business leads can work with data science. So this is about how you can actually get value out of using data science within an organization. On Tuesday, with a session on pair programming with GitHub Copilot. So if you're interested in the programming side of things, then that's well worth attending. Next Wednesday, there is a session on datacamp's Roadmap. So you find out what is coming next from datacamp. All right, Amy, do you want to talk through these five steps again?

Yeah. So, five steps starting out with the backstory. Where have you got your data from? What's in it? What are you expecting in the rows and columns? We didn't cover in detail the duplicates, but are there duplicates? What do you need to know about them? Where did they come from? Why are they there? Missing values, what's in your data are there as you expect them to be looking into the data Types and then your categorical data as well. Are the categories what you expect? Are they consistent? Are there typos in them?

Okay, excellent. All right, and next question from the audience is from Chelsea. Actually, maybe we'll make this the last one, but Chelsea asks, what visualizations do you use? I guess in the context of data.

Cleaning, you know what? People come up with all sorts of fancy visualizations, and I always use histogram, bar chart. They're my go to starting graphics. Maybe a box plot if I'm stretching the things a little bit. But histogram and bar chart, they're the ones I start with. That's probably like 80 to 90% of my visualization. You can learn so much from such simple graphics. So no need to go into fancy heat maps or things that will automatically plot everything or anything like that. Just. Yeah. Keeping it nice and basic.

All right, that seemed very good advice. Would you do. So that's gonna be one plot per column of data, is it?

Yeah, most likely, yeah. Just work through the. The different columns, create an individual plot. I sound so, like. Like, weird, but I like to do everything individually myself and go through. Through each thing. I don't tend to write a loop to just loop through all the columns or anything like that, because I want to make sure I take the time to look at the data I'm using. Having said that, if you've got a thousand columns is not super practical. So you probably want to reduce it down to what's your important data first and then take a look at each column of your data there.

All right, excellent. That's the problem with data visualizations is you get a lot of information quite quickly for it, but you need a human to understand what's going on.

Yeah.

Okay, excellent. At this point, we'll wrap up. So thank you once again, Amy, for all the insights. Very useful stuff. And thank you, Reece, to moderating. Thank you to everyone who asked a question. Thank you to everyone who showed up today. Enjoy the rest of your Valentine's fun and see you again in future webinars. All right, bye.