You Ask, I Answer: Best Tools for Cleaning Data?

In today's episode, Jessica asks, what are the best tools for cleaning data? It's a fairly broad question. Um it's really heavily dependent on what the data is, because every data set, every data type uh is different. And our definition of cleaning data also is going to be very different based on what it is we're trying to do. Uh there's a bunch of different types of cleansing you'd want to do.

Uh do you want to identify anomalies? And do you want to get rid of them? Do you want to uh fix missing data? Uh what kinds of uh things are you looking for? Are you trying to detect corrupted data?

Um of these different situations require different types of tools. For identifying anomalies, that one's pretty straightforward. You can do that, you know, even in Excel. Uh depending on the size of your data, you may not want to, but uh for smaller data sets for sure, uh the spreadsheet will will do fine for at least just identifying anomalies, doing basic exploratory data analysis and summarizing your tables. So things like means and mediums, interquartile ranges, all these are good for understanding sort of the shape of the data set and what it does.

Uh for identifying corrupted data, that's a lot harder. Um that requires sampling and inspection. So, real simple example, if you were to go through uh your email list, what are the different ways that you could identify bad emails, right? Uh there are gonna be some that are obvious, like someone who types in gmail.com but forgets the letter I in there, you know, and up with gmail.com. Um that's something that you can programmatically try to address.

Uh common misspellings among the most uh well-known domains would be uh an obvious thing to do. Um other things, again, using email as an example, you may need specialized tools. Uh there's a tool that we use um for you upload your email list and it checks them for validity and spits back, hey, here's a list of the addresses that have gone bad. Uh you will definitely need something like that for that specific use case, and that's again a very specialized tool. Depending on the type of data it is, if it's if it's categorical or continuous.

Categorical means non-numeric, uh continuous is numeric data. For numeric data, you can do things like uh predictive mean matching, for example, to try to uh infer or impute the data that's missing. Uh there's actually a whole bunch of tools that are really good at this. Uh I use a bunch in R, there's a bunch in Python as well, that can do everything up to uh really sophisticated neural networks to essentially guess at what likely values of the data would be. These have flaws.

Um, particularly they have flaws on um cumulative data sets. So if you're doing a a running total uh and you've got a day or two of missing data, uh they don't do well with that. I'm I'm not sure why. If you have categorical data, um, there are tools like uh random forests that can again do that amputation, kind of guess what the missing label is, with a caveat that the more data that's missing, the harder it is for these tools to get it right. If you've got you know a thousand lines in a spreadsheet and got six rows that are missing an attribute, these tools are going to probably do a pretty decent job of filling in those blanks.

If you got a thousand lines and five hundred are missing, you're gonna get salad back. You're gonna get tossed salad, and it's not gonna be any use because so much of it's gonna be wrong. Um the general rule of thumb with a lot of data sets is if you're between anywhere between 25 and 40% of uh the data is missing, you're not gonna be able to do imputation well. And again, to the point of detecting bad inputs, that's gonna be really hard. Really, really hard.

There's some stuff that's gonna be easy, right? You know, somebody types in test at test.com and you're in your uh marketing automation system, you can filter those out pretty easily. But non-obviously faked addresses, very difficult. Uh, and cleaning those out is gonna be a lot of work. Um, especially if they're valid but incorrect.

So uh this is something called spiking. Uh, you can have somebody spike a data set. There was a uh a political rally not too long ago where uh a bunch of uh K-pop folks and TikTokers uh reserved a bunch of tickets and flooded the system with bad data. The challenge is, and this is this should strike fear into the heart of every marketer. Um if you collect spurious data and it is in violation of a law and you use that data, you are liable.

Right? So uh let's say that um let's say that my my company is based in California, right? Uh it's not and you put in my my work email into a system like that but it was harvested or it was faked and you the marketer send me email at assuming that I signed up for this thing and I say I I did not sign up for this and you don't adhere to you know basic best best practices for unsubscribes and stuff which a lot of political campaigns don't uh you can be sued. You can be sued for uh under the California Consumer Privacy Act. So identifying bad data is very important but also very very difficult.

That said the most powerful not the fastest but the most important uh tool for cleaning data is a neural network. It's this one right here. Right. The tool between your ears is essential for every single one of these scenarios because you need to bring domain expertise to the data set to understand what needs to be cleaned and what does not you need to bring uh data science experience to the data set to understand what's possible to clean and what the limitations are and you need to bring good old fashioned common sense and the willingness to say you know what this isn't gonna go well. This is gonna go really badly let's not do this or let's uh find some other way to get this data if you're allowed to do so.

That's the hardest part of cleaning data by far. Um tools are less important than process, and that in turn is less important than uh the people who are doing the work. Because everything that can go wrong with data uh at some point will, and you're gonna need assistance getting that fixed up. So lots lots of challenges in cleaning data. And and cleaning data is one of the things that marketing has traditionally not been really good at.

Uh hopefully, as more people embrace marketing data science, as more people uh do work in the field, we will elevate our overall proficiency at cleaning data and making sure that it is useful and reliable. The best place to start for learning how to do this honestly is with something like a spreadsheet and a small data set and you going in and learning all the ways data can go wrong in a data set that you know very well. So I would start there uh to teach yourself how to do these things. And then as you get into more sophisticated stuff like imputation of missing values, uh that's when you're gonna need to bring in extra tools or or different tools. Chances are you'll get to a point where you will need custom tools that you build yourself in order to clean the most complex uh challenges.

And so uh expect to do that at some point. If you have follow-up questions, leave them in the comments box below. Subscribe to the YouTube channel and the newsletter. I'll talk to you soon. Take care.

Want help solving your company's data analytics and digital marketing problems? Visit TrustInsights.ai today and let us know how we can help you.