Menu
Home
People
Places
Arts
History
Plants & Animals
Science
Life & Culture
Technology
Reference.org
Data cleansing
open-in-new
<h2 id="motivation">Motivation</h2> <p>Administratively incorrect, inconsistent data can lead to false conclusions and misdirect <a href="/facts/Investment/VrMookcv">investments</a> on both public and private scales. For instance, the <a href="/facts/Government/N4bYL0VC">government</a> may want to analyze population census figures to decide which regions require further spending and investment on <a href="/facts/Infrastructure/QJvbsSpl">infrastructure</a> and services. In this case, it will be important to have access to reliable data to avoid erroneous fiscal decisions. In the business world, incorrect data can be costly. Many companies use customer information <a href="/facts/Database/VYcpkevb">databases</a> that record data like contact information, addresses, and preferences. For instance, if the addresses are inconsistent, the company will suffer the cost of resending mail or even losing customers. </p> <h2 id="data-quality">Data quality</h2> <p>High-quality data needs to pass a set of quality criteria. Those include: </p> <ul><li>Validity: The degree to which the measures conform to defined business rules or constraints. <i>(See also <a href="/facts/Validity_(statistics)/9LNgtTUG">Validity (statistics)</a>.)</i> When modern database technology is used to design data-capture systems, validity is fairly easy to ensure: invalid data arises mainly in legacy contexts (where constraints were not implemented in software) or where inappropriate data-capture technology was used (e.g., spreadsheets, where it is very hard to limit what a user chooses to enter into a cell, if cell validation is not used). Data constraints fall into the following categories: <ul><li><i>Data-Type Constraints</i>: values in a particular column must be of a particular data type, e.g., Boolean, numeric (integer or real), date.</li> <li><i>Range Constraints:</i> typically, numbers or dates should fall within a certain range. That is, they have minimum and/or maximum permissible values.</li> <li><i>Mandatory Constraints:</i> Certain columns must not be empty.</li> <li><i>Unique Constraints:</i> A field, or a combination of fields, must be unique across a dataset. No two persons may have the same social security number (or other unique identifier).</li> <li><i>Set-Membership constraints</i>: The values for a column come from a set of discrete values or codes. For example, a person's sex may be Female, Male, or Non-Binary.</li> <li><i>Foreign-key constraints</i>: This is the more general case of set membership. The set of values in a column is defined in a column of another table that contains unique values. For example, in a US taxpayer database, the "state" column is required to belong to one of the US's defined states or territories: the set of permissible states and territories is recorded in a separate table. The term <a href="/facts/Foreign_key/52Ns8RTD">foreign key</a> is borrowed from relational database terminology.</li> <li><i>Regular expression patterns</i>: Occasionally, text fields must be validated this way. For example, North American phone numbers may be required to have the pattern 999-999–9999.</li> <li><i>Cross-field validation</i>: Certain conditions that utilize multiple fields must hold. For example, in laboratory medicine, the sum of the components of the differential white blood cell count must be equal to 100 (since they are all percentages). In a hospital database, a patient's date of discharge from the hospital cannot be earlier than the date of admission.</li></ul></li> <li>Accuracy: The degree of conformity of a measure to a standard or a true value. <i>(See also <a href="/facts/Accuracy_and_precision/JQ47xXDq">Accuracy and precision</a>.)</i> Accuracy is very hard to achieve through data cleansing in the general case because it requires accessing an external source of data that contains the true value: such "gold standard" data is often unavailable. Accuracy has been achieved in some cleansing contexts, notably customer contact data, by using external databases that match up zip codes to geographical locations (city and state) and also help verify that street addresses within these zip codes actually exist.</li> <li>Completeness: The degree to which all required measures are known. Incompleteness is almost impossible to fix with data cleansing methodology: one cannot infer facts that were not captured when the data in question was initially recorded. (In some contexts, e.g., interview data, it may be possible to fix incompleteness by going back to the original source of data, i.e. re-interviewing the subject, but even this does not guarantee success because of problems of recall - e.g., in an interview to gather data on food consumption, no one is likely to remember exactly what one ate six months ago. In the case of systems that insist certain columns should not be empty, one may work around the problem by designating a value that indicates "unknown" or "missing", but the supplying of default values does not imply that the data has been made complete.)</li> <li>Consistency: The degree to which a set of measures are equivalent across systems. <i>(See also <a href="/facts/Consistency/IHkdnA9M">Consistency</a>).</i> Inconsistency occurs when two data items in the data set contradict each other: e.g., a customer is recorded in two different systems as having two different current addresses, and only one of them can be correct. Fixing inconsistency is not always possible: it requires a variety of strategies - e.g., deciding which data were recorded more recently, which data source is likely to be most reliable (the latter knowledge may be specific to a given organization), or simply trying to find the truth by testing both data items (e.g., calling up the customer).</li> <li>Uniformity: The degree to which a set of data measures are specified using the same units of measure in all systems. <i>(See also <a href="/facts/Unit_of_measurement/pvTpWbmC">Unit of measurement</a>.)</i> In datasets pooled from different locales, weight may be recorded either in pounds or kilos and must be converted to a single measure using an arithmetic transformation.</li></ul> <p>The term integrity encompasses accuracy, consistency and some aspects of validation <i>(see also <a href="/facts/Data_integrity/g3XGbKIH">Data integrity</a>)</i> but is rarely used by itself in data-cleansing contexts because it is insufficiently specific. (For example, "<a href="/facts/Referential_integrity/UJzY2tw5">referential integrity</a>" is a term used to refer to the enforcement of foreign-key constraints above.) </p> <h2 id="process">Process</h2> <ul><li>Data auditing: The data is audited with the use of <a href="/facts/Statistical/DNPsKGYU">statistical</a> and database methods to detect anomalies and contradictions: this eventually indicates the characteristics of the anomalies and their locations. Several commercial software packages will let you specify constraints of various kinds (using a grammar that conforms to that of a standard programming language, e.g., JavaScript or Visual Basic) and then generate code that checks the data for violation of these constraints. This process is referred to below in the bullets "workflow specification" and "workflow execution." For users who lack access to high-end cleansing software, Microcomputer database packages such as Microsoft Access or File Maker Pro will also let you perform such checks, on a constraint-by-constraint basis, interactively with little or no programming required in many cases.</li> <li>Workflow specification: The detection and removal of anomalies are performed by a sequence of operations on the data known as the workflow. It is specified after the process of auditing the data and is crucial in achieving the end product of high-quality data. In order to achieve a proper workflow, the causes of the anomalies and errors in the data have to be closely considered.</li> <li>Workflow execution: In this stage, the workflow is executed after its specification is complete and its correctness is verified. The implementation of the workflow should be efficient, even on large sets of data, which inevitably poses a trade-off because the execution of a data-cleansing operation can be computationally expensive.</li> <li>Post-processing and controlling: After executing the cleansing workflow, the results are inspected to verify correctness. Data that could not be corrected during the execution of the workflow is manually corrected, if possible. The result is a new cycle in the data-cleansing process where the data is audited again to allow the specification of an additional workflow to further cleanse the data by automatic processing.</li></ul> <p>Good quality source data has to do with “Data Quality Culture” and must be initiated at the top of the organization. It is not just a matter of implementing strong validation checks on input screens, because almost no matter how strong these checks are, they can often still be circumvented by the users. There is a nine-step guide for organizations that wish to improve data quality:<a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a><a class="footnote-ref" id="fnref:4" href="#fn:4"><sup>4</sup></a> </p> <ul><li>Declare a high-level commitment to a <a href="/facts/Data_quality/Hr9APbfG">data quality</a> culture</li> <li>Drive process reengineering at the executive level</li> <li>Spend money to improve the data entry environment</li> <li>Spend money to improve application integration</li> <li>Spend money to change how processes work</li> <li>Promote end-to-end team awareness</li> <li>Promote interdepartmental cooperation</li> <li>Publicly celebrate data quality excellence</li> <li>Continuously measure and improve data quality</li></ul> <p>Others include: </p> <ul><li>Parsing: for the detection of syntax errors. A parser decides whether a string of data is acceptable within the allowed data specification. This is similar to the way a parser works with <a href="/facts/Grammar/vprQsHIR">grammars</a> and <a href="/facts/Language/IRcTok2q">languages</a>.</li> <li>Data transformation: Data transformation allows the mapping of the data from its given format into the format expected by the appropriate application. This includes value conversions or translation functions, as well as normalizing numeric values to conform to minimum and maximum values.</li> <li>Duplicate elimination: Duplicate detection requires an <a href="/facts/Algorithm/fnl5NmRt">algorithm</a> for determining whether data contains duplicate representations of the same entity. Usually, data is sorted by a key that would bring duplicate entries closer together for faster identification.</li> <li>Statistical methods: By analyzing the data using the values of <a href="/facts/Mean/swcEd4Pg">mean</a>, <a href="/facts/Standard_deviation/qSug9BRl">standard deviation</a>, <a href="/facts/Range_(statistics)/Yu0HgeRj">range</a>, or <a href="/facts/Cluster_analysis/HUeDnWPt">clustering</a> algorithms, it is possible for an expert to find values that are unexpected and thus erroneous. Although the correction of such data is difficult since the true value is not known, it can be resolved by setting the values to an average or other statistical value. Statistical methods can also be used to handle missing values which can be replaced by one or more plausible values, which are usually obtained by extensive <a href="/facts/Data_augmentation/vf8p9qHe">data augmentation</a> algorithms.</li></ul> <h2 id="system">System</h2> <p>The essential job of this system is to find a balance between fixing <a href="/facts/Dirty_data/SV4wzpHG">dirty data</a> and maintaining the data as close as possible to the original data from the source production system. This is a challenge for the <a href="/facts/Extract,_transform,_load/WRaRgJL5">extract, transform, load</a> architect. The system should offer an architecture that can cleanse data, record quality events and measure/control the quality of data in the <a href="/facts/Data_warehouse/we5rblq9">data warehouse</a>. A good start is to perform a thorough <a href="/facts/Data_profiling/K9zFvA8r">data profiling</a> analysis that will help define the required complexity of the data cleansing system and also give an idea of the current data quality in the source system(s). </p> <h2 id="quality-screens">Quality screens</h2> <p>Part of the data cleansing system is a set of diagnostic filters known as quality screens. They each implement a test in the data flow that, if it fails, records an error in the Error Event Schema. Quality screens are divided into three categories: </p> <ul><li>Column screens. Testing the individual column, e.g. for unexpected values like <a href="/facts/Null_character/IMYNHNxR">NULL</a> values; non-numeric values that should be numeric; out-of-range values; etc.</li> <li>Structure screens. These are used to test for the integrity of different relationships between columns (typically foreign/primary keys) in the same or different tables. They are also used for testing that a group of columns is valid according to some structural definition to which it should adhere.</li> <li>Business rule screens. The most complex of the three tests. They test to see whether data, maybe across multiple tables, follow specific business rules. An example could be, that if a customer is marked as a certain type of customer, the business rules that define this kind of customer should be adhered to.</li></ul> <p>When a quality screen records an error, it can either stop the dataflow process, send the faulty data somewhere else than the target system or tag the data. The latter option is considered the best solution because the first option requires, that someone has to manually deal with the issue each time it occurs and the second implies that data are missing from the target system (<a href="/facts/Integrity/2JwWgnvN">integrity</a>) and it is often unclear what should happen to these data. </p> <h2 id="criticism-of-existing-tools-and-processes">Criticism of existing tools and processes</h2> <p>Most data cleansing tools have limitations in usability: </p> <ul><li>Project costs: costs typically in the hundreds of thousands of dollars</li> <li>Time: mastering large-scale data-cleansing software is time-consuming</li> <li>Security: cross-validation requires sharing information, giving application access across systems, including sensitive legacy systems</li></ul> <h2 id="error-event-schema">Error event schema</h2> <p>The error event schema holds records of all error events thrown by the quality screens. It consists of an error event <a href="/facts/Fact_table/3xlrQLkA">fact table</a> with <a href="/facts/Foreign_key/52Ns8RTD">foreign keys</a> to three dimension tables that represent a date (when), <a href="/facts/Batch_job/hKY2rX3N">batch job</a> (where), and screen (who produced error). It also holds information about exactly when the error occurred and the severity of the error. Also, there is an error event detail fact table with a foreign key to the main table that contains detailed information about in which table, record and field the error occurred and the error condition. </p> <h2 id="see-also">See also</h2> <ul><li><a href="/facts/Data_editing/TMc1qtcG">Data editing</a></li> <li><a href="/facts/Data_management/Cykeqxc7">Data management</a></li> <li><a href="/facts/Data_mining/AH53q5ac">Data mining</a></li> <li><a href="/facts/Database_repair/76RyMwMF">Database repair</a></li> <li><a href="/facts/Iterative_proportional_fitting/AHeIBI0X">Iterative proportional fitting</a></li> <li><a href="/facts/Record_linkage/4p1zflDA">Record linkage</a></li> <li><a href="/facts/Single_customer_view/a24UWwo4">Single customer view</a></li> <li><a href="/facts/Triangulation_(social_science)/JMpqbkS0">Triangulation (social science)</a></li></ul> <h2 id="further-reading">Further reading</h2> <ul><li>McKinney, Wes (2017). "Data Cleaning and Preparation". <i>Python for Data Analysis</i> (2nd ed.). O'Reilly. pp. 195–224. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 978-1-4919-5766-0.</li> <li>van der Loo, Mark; de Jonge, Edwin (2018). <i>Statistical Data Cleaning with Applications in R</i>. Hoboken: Wiley. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 978-1-118-89715-7.</li> <li>Chicco D, Oneto L, Tavazzi E (December 2022). <a href="https://www.ncbi.nlm.nih.gov/pmc/articles/PMC9754225">"Eleven quick tips for data cleaning and feature engineering"</a>. <i>PLOS Computational Biology</i>. 18 (12): e1010718. <a href="/facts/Doi_(identifier)/muM9Etpq">doi</a>:<a href="https://doi.org/10.1371%2Fjournal.pcbi.1010718">10.1371/journal.pcbi.1010718</a>. <a href="/facts/PMC_(identifier)/dX1zMt71">PMC</a> <a href="https://www.ncbi.nlm.nih.gov/pmc/articles/PMC9754225">9754225</a>. <a href="/facts/PMID_(identifier)/JlHAvMHt">PMID</a> <a href="https://pubmed.ncbi.nlm.nih.gov/36520712">36520712</a>. <a href="/facts/S2CID_(identifier)/ldJsHa2Y">S2CID</a> <a href="https://api.semanticscholar.org/CorpusID:254733288">254733288</a>.</li> <li>Chu, X., Ilyas, I. F., Krishnan, S., Wang, J. (2016), <i>Data Cleaning</i>, ACM, <a href="/facts/Doi_(identifier)/muM9Etpq">doi</a>:<a href="https://doi.org/10.1145%2F2882903.2912574">10.1145/2882903.2912574</a></li> <li>Côté, P.-O., Nikanjam, A., Ahmed, N., Humeniuk, D., Khomh, F. (2024), <i>Data cleaning and machine learning: a systematic literature review</i>, Springer Science and Business Media LLC, <a href="/facts/ArXiv_(identifier)/H6EtgnBe">arXiv</a>:<a href="https://arxiv.org/abs/2310.01765">2310.01765</a>, <a href="/facts/Doi_(identifier)/muM9Etpq">doi</a>:<a href="https://doi.org/10.1007%2Fs10515-024-00453-w">10.1007/s10515-024-00453-w</a></li></ul> <h2 id="external-links">External links</h2> <ul><li><a href="https://web.archive.org/web/20100313055016/http://www.computerworld.com/s/article/78230/Data_Scrubbing"><i>Computerworld: Data Scrubbing</i></a> (February 10, 2003)</li> <li>Erhard Rahm, Hong Hai Do: <a href="http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.98.8661&rep=rep1&type=pdf"><i>Data Cleaning: Problems and Current Approaches</i></a></li> <li><a href="https://datamanagement.wiki/data_quality_management_system/data_cleansing"><i>Data cleansing</i>.</a> Datamanagement.wiki.</li></ul> <h2 id="references">References</h2> <ol> <li id="fn:1"><p>Wu, S. (2013), "A review on coarse warranty data and analysis" (PDF), Reliability Engineering and System, 114: 1–11, doi:10.1016/j.ress.2012.12.021 <a href="https://kar.kent.ac.uk/32972/1/LatestVersionV01.pdf" target="_blank">https://kar.kent.ac.uk/32972/1/LatestVersionV01.pdf</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li> <li id="fn:2"><p>"Data 101: What is Data Harmonization?". Datorama. 14 April 2017. Archived from the original on 24 October 2021. Retrieved 14 August 2019. <a href="https://web.archive.org/web/20211024062426/https://datorama.com/blog/2017/04/14/data-101-what-is-data-harmonization/" target="_blank">https://web.archive.org/web/20211024062426/https://datorama.com/blog/2017/04/14/data-101-what-is-data-harmonization/</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li> <li id="fn:3"><p>Kimball, R., Ross, M., Thornthwaite, W., Mundy, J., Becker, B. The Data Warehouse Lifecycle Toolkit, Wiley Publishing, Inc., 2008. ISBN 978-0-470-14977-5 <a href="/wiki/ISBN_(identifier)" target="_blank">/wiki/ISBN_(identifier)</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li> <li id="fn:4"><p>Olson, J. E. Data Quality: The Accuracy Dimension", Morgan Kaufmann, 2002. ISBN 1-55860-891-5 <a href="/wiki/Morgan_Kaufmann" target="_blank">/wiki/Morgan_Kaufmann</a> <a href="#fnref:4" class="footnote-back-ref">↩</a></p></li> </ol>