Data Cleaning Explained
Research data can include many kinds of information such as observations, measurements, answers to surveys, textual descriptions, readings from instruments, images, and categorizations. Different researchers collect and record data in varied ways. They may use different names, abbreviations, date formats, decimal symbols, languages, units of measure, or codes for missing data.
These differences can easily add up if several people collect data, the data comes from different locations, or the research spans many years.
Data cleaning refers to the process of identifying and resolving these issues, by e.g., inspecting for and deleting duplicates and incompletely filled fields, standardising names, categories, units and dates, transposing tables, or clarifying codes for missing data so that it can be used, understood, shared, preserved and reused.
The golden rule: you must not alter your original (raw) data. Document each cleaning step, transformation or recode in a cleaning log, README file, script or similar.
Why is Data Cleaning Important?
Inconsistencies, even small ones, can be a source of later confusion: two fields with the same meaning but a different name; a different decimal separator; an incorrect sign; a mispelling; an ambiguous symbol for missing information can make analysing or understanding a dataset incredibly complicated and lead to errors in data presentation. Early cleaning and tidying of data will enable you to catch potential problems and misunderstandings in analysis, interpretation, presentation of results. It is a critical step that enhances the data's understanding, analysis, reuse, sharing and preservation value.
It will also make it clear to others how your data was generated and what modifications have been made to reach its final form.
The main steps of data cleaning
The process typically involves six stages:
1. Examine the raw data
The very first step is to thoroughly look through your raw data without making any changes.
Many common problems will naturally become apparent: typing errors, duplicate records pasted in by mistake, inconsistent use of abbreviations, extra spaces, different date formats, and so on.
Review everything – column headings, units of measure, date values, categorical values, numeric value ranges, indicators of missing information, and relationships between tables. If any data item is unclear, try to trace it back to its original source – field notes, laboratory protocols, questionnaires, codebooks or device logs.
As an example, an identical date could have been written as 15.04.2026, 2026-04-15 and 15/04/2026. A typo could lead to 2025 being used instead of 2026.
2. Standardise names and categories
Small text variations, such as the use of capital or lowercase letters, different spellings or translations, extra spaces or mere typing mistakes, can split what should be one category into several separate ones. Create a consistent and logical way of naming variables and categories, and ensure it's used for all related files. If working with multiple languages, keep the original text label along with the chosen translation.
For example, Oak, oak, OAK, Oak and Oka might all refer to the same species; make sure to check this assumption before merging these records.
3. Identify and clarify missing or invalid data
Not all missing information is explicitly recorded.
Often, it is replaced with symbols such as N/A, -99, 9999, ?, or words like unknown. Missing values can occur for various reasons, such as an entry was missed, the measurement failed, it was not taken at all or was irrelevant. These circumstances represent different situations that should be treated differently – investigate the meaning with research team or original notes before making a decision.
For instance, -99 may indicate missing information in some databases, while in others it is a valid negative value.
4. Harmonise units and formats
Often in collaboration or by combining different data sources, data can be captured using different formats or measurement units. A dataset may contain decimal points, whilst another uses decimal commas, and values may be expressed in meters, centimeters, or millimeters. Consolidate everything into one standardized format and unit and document how the data has been transformed.
Check for common entry errors like typing 150 instead of 15.0. For example, measurements of length in meters, centimeters, and millimeters would need to be converted into one consistent unit.
5. Restructure the dataset if necessary
The data may contain all the correct information, but it might be presented in a way that hinders easy analysis or interpretation.
Perhaps individual cells contain multiple pieces of data, or yearly values are presented across many different columns, or single records comprise information for multiple observations. This type of structure typically results from manual data entry or uncoordinated organizing efforts by multiple individuals. Rearrange the data in a clear and functional format, usually one row per observation, and one column per variable.
For example, 12 columns representing months can be re-arranged into one 'month' column and one 'value' column.
6. Review, document, and save the cleaned dataset
After the initial cleaning process, double-check all steps taken, as new mistakes can occur during cleaning. Count records again, check the counts of each category, review missing values, scan through variable ranges, verify connections between files, and compare a selection of records against the raw data. Save the cleaned dataset as a separate file and meticulously record every step of the cleaning process in your cleaning log or README file, for example, counting the unique categories of variables again to ensure nothing essential has been omitted during standardization.
How the Open Science center MENDELU can Help
If you have a completed, or nearly completed, dataset ready to be shared, archived in a repository or submitted for publication, the MENDELU Open Science center - Data Stewardship Team is able to help you to prepare your data.
Depending on the project, we can support you by:
- reviewing the structure and formats of your datasets,
- identifying potential technical issues and inconsistencies,
- helping with standardization of variables, categories, units of measurement and dates,
- assisting with terminology and documentation of multilingual data,
- developing a repeatable cleaning protocol,
- writing documentation (README files, codebooks, data dictionaries, cleaning logs),
- ensuring the files and documentation meet data repository/academic journal submission standards.
We do not make decisions about the scientific meaning of your data; the interpretation and decision-making about corrections, exclusions, translation and missing values remains entirely the responsibility of the researchers.
We can help you technically. Your data are handled securely and confidentially and are protected against unauthorised access or disclosure.
Software and Tools
There are several programs and tools that you can use for data cleaning, including statistical programs (e.g., R, Stata, SPSS), spreadsheet programs (e.g., Excel, Google Sheets), dedicated cleaning tools (e.g., OpenRefine), or scripting languages (e.g., R, Python). The choice of tool depends on the complexity and size of the dataset, your team's skills and the possibility of reusing the cleaning process for other data. If you are cleaning a small and simple dataset manually, a spreadsheet may be sufficient.
However, if your data is large, complex or will require similar cleaning steps again, using a scripted approach (R or Python) is generally more reproducible and maintainable.
Whatever tool you use, it is important to document the steps of your cleaning process.
Practical Guidance
Researchers using R are welcome to consult our quick R cheat sheet showing one possible approach to data cleaning (including data import, checking, organisation and modification, final checks, and data saving).
Download: From Raw Data to Clean Data in R
The cheat sheet provides one practical example of a data-cleaning workflow. It is not a required method, and researchers may use other tools or procedures that are more suitable for their data.
Contact the Data Stewardship Team
If you are preparing a dataset for an open or institutional repository, you can contact the MENDELU OSC for an initial consultation. A short questionnaire will help us understand the type and structure of your dataset, the stage of your project, your intended repository and users, and any language or documentation needs.
After reviewing this information, we can discuss which parts of the data-cleaning and documentation process may require support.