{"id":8353,"date":"2019-08-22T12:24:10","date_gmt":"2019-08-22T19:24:10","guid":{"rendered":"https:\/\/www.springboard.com\/?p=8353"},"modified":"2023-07-07T15:46:52","modified_gmt":"2023-07-07T22:46:52","slug":"data-cleaning","status":"publish","type":"post","link":"https:\/\/www.springboard.com\/blog\/data-analytics\/data-cleaning\/","title":{"rendered":"Data Cleaning: The Why and the How"},"content":{"rendered":"\n

In my posts on data analysis methods and machine learning, I briefly mentioned data cleaning and its importance. Those two articles were not focused on data cleansing, so they were indeed brief mentions. However, the importance of using (relatively) clean data is paramount in machine learning and statistics.<\/span><\/p>\n\n\n\n

Do We Really Need to Clean the Data?<\/h2>\n\n\n\n

Yes. Bad data will lead to bad results, plain and simple. The saying \u201cgarbage in, garbage out\u201d is well-known in the computer science world for a reason. Computers are not magical gods, they\u2019re machines performing calculations very rapidly. They have no insight or intuition, nor do they have intellect or sentience (as far as we know!) to determine when something \u201cjust doesn\u2019t make sense.\u201d<\/span> So, it is very important for any data analysts<\/a> to ensure the quality of a data.<\/p>\n\n\n\n

To accurately reflect reality, our input data must remove errors and issues that trip up our algorithms. Data cleaning (or pre-processing, if you prefer) is how we do this. Data cleansing is a <\/span>time-consuming and unpopular aspect of data analysis<\/span><\/a> (PDF, p5), but it must be done.<\/span><\/p>\n\n\n\n

Note 1: In this article, <\/span><\/i>rows<\/b> will be instances of datapoints while <\/span><\/i>columns<\/b> will be variable\/field names. Row 1 may be Jane, row 2 may be John. Column 1 may be age, column 2 may be income. <\/span><\/i><\/p>\n\n\n\n

Note 2: Most links to explain programming terms will link to <\/span><\/i>Python<\/span><\/i><\/a> resources, one of the most widely used data science languages. Common libraries are <\/span><\/i>NumPy<\/span><\/i><\/a>, <\/span><\/i>SciPy<\/span><\/i><\/a>, and <\/span><\/i>pandas<\/span><\/i><\/a>. If you haven\u2019t programmed before, Python is a high-level, relatively intuitive language with many code libraries already created for you. I strongly recommend it for beginners.<\/span><\/i><\/p>\n\n\n\n

What Causes Data to Be Unclean?<\/h2>\n\n\n\n

Data from real-world applications may be unstructured and therefore its capture automated. This leads to all kinds of consistency and quality issues in data analytics<\/a> process. Sometimes data sets are aggregated from multiple sources, leading to other issues. Sometimes inputs are sourced from bad sensors. There are many reasons our data can be unusable for algorithms.<\/span><\/p>\n\n\n\n

Input Errors<\/span><\/h3>\n\n\n\n

There are plenty of ways a human can enter the wrong information. They may mistype, miscalculate, or misread. For open-ended fields, like unverified text, there could be typos that a computer will not interpret as identical (e.g., \u201cyes,\u201d \u201cyeah,\u201d \u201cY\u201d). For numeric fields, there could be confusion about magnitude (000s or 000,000s?). I\u2019m sure you can think of myriad other ways humans can errantly enter data.<\/span><\/p>\n\n\n\n

Malfunctioning Sensors<\/span><\/h3>\n\n\n\n

Machines can err, too. A washing machine sensor shouldn\u2019t register 534 C for the water temperature. An elevator\u2019s current load cannot equal -150 kilograms. Humans easily catch these errors, but computers will absolutely miss them (unless the code specifically checks for these errors).<\/span><\/p>\n\n\n\n

Mangled Data<\/span><\/h3>\n\n\n\n

When sensors malfunction, they are likely to generate values outside the acceptable range (like a negative weight). Sometimes the sensors are generating valid data, but that data is mangled on its way to the collection site. Proprietary data formats may not be readable by different programs. Interference during public-internet transmission might cause dropped packets and thus partial input.<\/span><\/p>\n\n\n\n

Duplicates<\/span><\/h3>\n\n\n\n

If the initial data set is an amalgamation of multiple sources, there is a high probability of duplicates. Sometimes these are not identified by a simple key check, because one data source might use the <\/span>Name<\/span><\/i> Sensor AA<\/i><\/b> while another data source may use <\/span>Device 01<\/i><\/b>. Both entries are identical, but your algorithm relies on the <\/span>Name<\/span><\/i> field, thus double-counting the double-agent AA\/01.<\/span><\/p>\n\n\n\n

Lack of Standardization<\/span><\/h3>\n\n\n\n

When using multiple data sources, lack of standardization is common. To achieve true results, all data that is similar in reality must be represented similarly in the input. This is obvious, but it is not always obvious how to achieve this.<\/span><\/p>\n\n\n\n

Even within single data source projects, standardization problems can still arise, particularly with open-ended human input. Some people might have different spelling or capitalization habits, and people on different teams within an organization may even use different names for the same topic or product!<\/span><\/p>\n\n\n

Get To Know Other\tData Analytics Students<\/h4>
\"Nelson<\/a>

Nelson Borges<\/p>

Insights Analyst at LinkedIn<\/p><\/div>

<\/div>

Read Story<\/a><\/p><\/div><\/div>

\"Jon<\/a>

Jon Shepard<\/p>

VP Of AI Research Strategy And Execution at J.P. Morgan<\/p><\/div>

Read Story<\/a><\/p><\/div><\/div>

\"Sarah<\/a>

Sarah Savage<\/p>

Content Data Analyst at EdX<\/p><\/div>

Read Story<\/a><\/p><\/div><\/div><\/div><\/div>\n\n\n\n

Identifying Problematic Data<\/h2>\n\n\n\n

In this section, let\u2019s explore some data constraints and how to approach them.<\/span><\/p>\n\n\n\n

Range Constraints<\/span><\/h3>\n\n\n\n

One of the easiest restraints to check, and one of the easiest to understand, is the range<\/span> constraint. Water is only physically water between 0 and 100 degrees Celsius. If your data says the water is 435 C, it isn\u2019t water anymore. In fact, it\u2019s steam\u2014it\u2019s steam at a very high pressure. Your washing machine is going to explode\u2026 If the reading is -30 C, your washer is probably frozen solid.<\/span><\/p>\n\n\n\n

There is likely a \u201cmax\u201d and a \u201cmin\u201d function in your chosen language. You can very quickly check a range constraint this way. These are generally optimized as fundamental functions, too, so they will run quickly even on huge data sets. Another way is to use a <\/span>Boolean mask<\/span><\/a> and check for any values that violate the constraint, or graph your data and look for abnormal <\/span>distributions<\/span><\/a>.<\/span><\/p>\n\n\n\n

Categorical Constraints<\/span><\/h3>\n\n\n\n

Categorical variable<\/span><\/a> values can only be chosen from a specific set of options. A device can only be <\/span>on<\/span><\/i> or <\/span>off<\/span><\/i>. A marital status may only be <\/span>single<\/span><\/i>, <\/span>engaged, married<\/span><\/i>, <\/span>divorced<\/span><\/i>, or <\/span>widowed<\/span><\/i>. An animal may only be one species, and it must be a valid species.<\/span><\/p>\n\n\n\n

You can check categorical constraints by running some kind of unique search function (in <\/span>Python<\/span><\/a>, in <\/span>SQL<\/span><\/a>) and viewing the output. If there are eight possible categories, your unique search should produce eight or fewer results. You can easily check that all the identified categories are legitimate.<\/span><\/p>\n\n\n\n

Categories that contain many classes may require another method. DNA sequences come to mind, as there could be hundreds of unique and valid sequences in a single data set. If you have a list of all possible combinations, you can use a function that cross-references the input data with a set of known sequences. If you don\u2019t, you <\/span>might<\/span><\/i> be able to use something like a <\/span>Regex<\/span><\/a> (regular expression), but that\u2019s up to you to decide.<\/span><\/p>\n\n\n\n

Consistency<\/span><\/h3>\n\n\n\n

The strings \u201cMarried,\u201d \u201cmarried,\u201d and \u201cmar.\u201d will not be recognized as identical. If some columns are in 000s but a specific data source is quoting raw numbers, some of your rows will be off by three orders of magnitude. Percentages that are entered in decimal form will not be comparable to percentages entered in basis-point form.<\/span><\/p>\n\n\n\n

You can find these, at least for categorical data with few classes, by manually reviewing the list of classes (use a unique function to find them all). Finding these errors for numeric data may require some ingenuity, though range constraints are a start.<\/span><\/p>\n\n\n\n

Cross Field Constraints<\/span><\/h3>\n\n\n\n

This one is hardest to check because it doesn\u2019t concern a single field. This constraint pertains to validity across columns or rows. For example, $100,000 may be a valid income, and 3 may be a valid age, but a datapoint of Alex with an age of 3 and an income of $100,000 is very unlikely. It can be even more complicated, like this example:<\/span><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Obviously, one of these entries is incorrect. Finding out which one<\/span>\u2014<\/span>and indeed just finding this cross-reference error<\/span>\u2014<\/span>can be extremely difficult. If your project relies critically on the veracity of cross-column (age, income) or cross-row (1234 \/ 9876) information, you\u2019ll need to develop code to catch these kinds of errors. Perhaps here you can simply iterate through every ID and ensure the relationship is reciprocal. 1234 should be married to 9876 and 9876 should be married to 1234. The same should appear for 4233 \/ 9324 and 9324 \/ 4233. Any deviations can be collected in a list and manually reviewed.<\/span><\/p>\n\n\n\n

Visualizations<\/span><\/h3>\n\n\n\n

Visualizations are a good way to easily find outliers, strange distributions, and other problems. If you believe it is a normal distribution, but in fact you have a bimodal distribution, you will need to adjust your starting assumptions. Visualization techniques, like box-and-whisker plots, histograms, and scatterplots, can be immensely helpful in immediately catching some issues.<\/span><\/p>\n\n\n\n

Visualizations will also assist you in identifying trends, which can be helpful in scaling. Excel has some of the most powerful <\/span>visualization capabilities<\/span><\/a>, but there are plenty of libraries in other languages <\/span>like Python<\/span><\/a>.<\/span><\/p>\n\n\n\n

Counting the Errors<\/span><\/h3>\n\n\n\n

If you find constraint violations, you\u2019ll want to count them. Just one may be a fluke. If 50% of the datapoints are in violation, there may be an inapplicable constraint or bad input data. Even if you use visualizations, make sure you also count the errors. If a sensor spits out 500 identical, invalid instances, they will all be hidden behind the same visual datapoint on the screen.<\/span><\/p>\n\n\n\n

Missing Values<\/span><\/h3>\n\n\n\n

Missing values is probably the most common type of data issue that must be addressed. Values may be missing because you combined two data sets from different sources, the entry process somehow skipped a row, or the value was inadvertently deleted. <\/span><\/p>\n\n\n\n

One or two missing values probably isn\u2019t a problem, but if you notice there is a high density of missing values, you should investigate the reason. Is a sensor malfunctioning? Is a certain group conspicuously missing? If surveys are missing from all tenants in a particular building, perhaps the building management is avoiding distributing the surveys. This could be important in itself.<\/span><\/p>\n\n\n\n

Missing values can often impart and imply a lot of information, so don\u2019t just ignore them if they\u2019re occurring frequently. <\/span><\/p>\n\n\n\n

Data Cleaning Techniques<\/h2>\n\n\n\n

Once you\u2019ve identified data to be cleaned, there are a few main ways to actually go about that data cleanup. <\/span><\/p>\n\n\n\n

Removal<\/span><\/h3>\n\n\n\n

This is the most frowned-upon method. For missing values, it is better to investigate the reason instead of simply eliminating the rows or columns that contain the missing values. This is not always avoidable, though. If an entire column is 85% missing and you cannot find another data source, you may not be able to use that column.<\/span><\/p>\n\n\n\n

Additionally, it\u2019s not optimal to remove outliers, as this is a kind of results doctoring. If you do remove datapoints, explain the reasoning for doing so (such as 85% of the data is irrecoverable) in the results and report.<\/span><\/p>\n\n\n\n

Before removing many datapoints, it\u2019s important to get input from experts in the field. This is critically true if you want to remove an entire column. Removing the Tactile Strength column from an engineering study is probably a bad idea, since tactile strength may heavily influence the decision that comes out of the analysis.<\/span><\/p>\n\n\n\n

The only datapoints regularly eliminated are duplicates that are clearly duplicates, and only when the presence of duplicates does not affect your assumptions or results.<\/span><\/p>\n\n\n\n

Direct Correction<\/span><\/h3>\n\n\n\n

This one is a little more interesting, and if possible, infinitely better than removal. Consistency issues are often simple to fix, though they must be identified first.<\/span><\/p>\n\n\n\n

For string consistency correction in smaller categorical sets, it can be trivial to run a unique values search and then write a couple of if-statements to replace errors. If you have something like city names, it may be difficult to go with explicit if-statements. You may want to use a <\/span>fuzzy search<\/span><\/a> and make corrections that way.<\/span><\/p>\n\n\n\n

Numerical consistency errors, such as order of magnitude mismatches, are simple to fix by multiplication or division. Binary consistency issues can be corrected if you can accurately assign the non-binary input to one of the binary categories. In the set {on, off, broken}, you can probably safely map <\/span>broken<\/span><\/i> to <\/span>off<\/span><\/i>. <\/span><\/p>\n\n\n\n

Errors that arise from malfunctioning sensors or human input errors should also be corrected from the source, if possible. If you are using publicly available or large-scale, one-time-collection data sets, though, this won\u2019t be possible. In those cases, you may want to impute the values.<\/span><\/p>\n\n\n\n

Scaling<\/span><\/h3>\n\n\n\n

Scaling changes the ranges of data so some features do not dominate solely because they naturally produce larger values. For example, temperature for a city tends to have a much smaller range than the population for a city. Distance-based algorithms will assign much greater importance to the population variable, possibly entirely ignoring the temperature variable. Scaling brings variables in line with each other while retaining the proportional relationships within the variable. This is seen when you convert to percentages or baseline to 100.<\/span><\/p>\n\n\n\n

Imputation<\/span><\/h3>\n\n\n\n

This technique is most closely associated with filling in missing values, but it can be used for incorrect values, too, especially when a direct correction cannot be made. <\/span><\/p>\n\n\n\n

Imputation<\/span><\/i> is a fancy way to say <\/span>guess<\/span><\/i>. However, since we are in the field of data science, this will be a data-driven guess, not just a random guess. You can impute<\/a> values with statistical indicators (like mean, median, mode), hot-decking, stratification, and others.<\/span><\/p>\n\n\n\n

One approach is to replace every missing value with a statistical indicator. However, be very careful here. You may reinforce the pattern already gleaned from the known data, potentially masking a critical pattern belied by the fact that the data itself is missing. In our missing building survey example above, if you just used the mean score for all missing data, you may overlook a strong negative sentiment in that building (which was why the building manager \u201cforgot\u201d to distribute the survey).<\/span><\/p>\n\n\n\n

Hot-decking fills in missing values by randomly selecting a value from the set of already-known values. Again, this can cause you to overlook important information belied by \u201cmissingness.\u201d However, hot-decking won\u2019t drag your whole data set toward a statistical indicator like repeatedly filling in statistical indicators does.<\/span><\/p>\n\n\n\n

Finally, stratification is beneficial if you already know some patterns in your data. The heights of women are, on average, shorter than the heights of men. You could split your data set into men and women, then use those sub-indicators for replacement or hot-deck from the subsets of men and women. Is it perfect? No, but it\u2019s better than using the indicators or hot-decking from the entire population.<\/span><\/p>\n\n\n\n

If you have a mission-critical project and it\u2019s absolutely impossible to retrieve the correct data, it is strongly recommended to look for known patterns in the data set and impute values in line with those known patterns. You want to avoid introducing your own or latent biases as much as possible when imputing data, so get a second opinion if you can.<\/span><\/p>\n\n\n\n

Flagging<\/span><\/h3>\n\n\n\n

This is particularly useful for missing values when you don\u2019t want to drop all of them. For numeric data, you can add another column to your data set and flag any missing values there. This will inform your algorithm of missing values, which may turn out to be influential. For categorical variables, simply create a \u201cMissing\u201d or \u201cUnknown\u201d class.<\/span><\/p>\n\n\n\n

If your flagged variable becomes important, you will probably need to correct or impute values to reflect reality or gather any useful insights. If you cannot do this, ensure the report mentions this shortcoming.<\/span><\/p>\n\n\n\n

Conclusion<\/h2>\n\n\n\n

Data cleansing is not glamorous, and most data scientists actually do not seem to enjoy this part of the job. However, it is absolutely vital to feed clean, qualify data into your machine learning algorithms if you want useful, truthful, and actionable insights.<\/span><\/p>\n\n\n\n

Ready to learn more? Consider Springboard\u2019s Data Analytics Career Track<\/a>. You\u2019ll master both the technical and business thinking skills to get hired\u2014job guaranteed!<\/strong><\/p>\n\n\n\n

Since you’re here…
<\/strong>Switching to a career in data analytics is possible, no matter your background. We\u2019ve helped over 10,000 students <\/a>make it happen. Check out our free data analytics curriculum<\/a> to gauge your interest, or go all-in with our Data Analytics Bootcamp<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

In my posts on data analysis methods and machine learning, I briefly mentioned data cleaning and its importance. Those two articles were not focused on data cleansing, so they were indeed brief mentions. However, the importance of using (relatively) clean data is paramount in machine learning and statistics. Do We Really Need to Clean the […]<\/p>\n","protected":false},"author":73,"featured_media":8413,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_eb_data_table":"","footnotes":""},"categories":[134],"tags":[],"marketing_tags":[],"class_list":{"0":"post-8353","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-data-analytics"},"acf":[],"_links":{"self":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts\/8353"}],"collection":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/users\/73"}],"replies":[{"embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/comments?post=8353"}],"version-history":[{"count":3,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts\/8353\/revisions"}],"predecessor-version":[{"id":47548,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts\/8353\/revisions\/47548"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/media\/8413"}],"wp:attachment":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/media?parent=8353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/categories?post=8353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/tags?post=8353"},{"taxonomy":"marketing_tags","embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/marketing_tags?post=8353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}