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:
| Step | Main purpose | What to do | Example |
|---|---|---|---|
| 1. Examine the raw data | Understand the dataset before making any changes and identify obvious errors or inconsistencies. | Check column names, units, dates, categories, numeric ranges, missing values, duplicates and relationships between tables. Trace unclear values back to the original source. | The same date may appear as 15.04.2026, 2026-04-15 or 15/04/2026. A typo may record 2025 instead of 2026. |
| 2. Standardise names and categories | Make variable names and categorical values consistent across the dataset. | Standardise spelling, capitalisation, abbreviations, translations and spaces. Keep original labels where useful, especially in multilingual datasets. | Oak, oak, OAK, Oak and Oka may represent the same species — but this should be verified before merging them. |
| 3. Identify and clarify missing or invalid data | Determine which values are truly missing or invalid and understand why. | Look for codes such as N/A, -99, 9999, ?, unknown. Check their meaning with documentation, notes or the research team before replacing or removing them. | -99 may mean “missing” in one dataset, but it may be a valid negative value in another. |
| 4. Harmonise units and formats | Ensure that measurements and formats are consistent across all data sources. | Convert values into common units, standardise date and number formats, and document all transformations. Check for data-entry errors. | Convert measurements recorded in m, cm and mm into one unit. Check errors such as 150 instead of 15.0. |
| 5. Restructure the dataset if necessary | Organise the data into a structure suitable for analysis. | Separate multiple values stored in one cell, reshape wide tables if needed, and aim for one row per observation and one column per variable. | Twelve separate month columns can be transformed into one month column and one value column. |
| 6. Review, document, and save the cleaned dataset | Verify that the cleaning process did not introduce new errors and keep a reproducible record of all changes. | Recount records and categories, review missing values and ranges, verify links between files, compare selected records with the raw data, and save the cleaned dataset separately. Document all changes in a cleaning log or README. | Recounting unique categories after standardisation can reveal whether any important category was accidentally omitted. |
How MENDELU's Open Science center 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.
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.
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
Open Science centre MENDELU prepared data cleaning tutorials for researchers using RStudio and Excel / PowerQuery.Researchers using R are also 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). 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.
Download: From Raw Data to Clean Data in R