How to clean and prepare your data for analysis, in R and Python

The worst cleaning jobs were avoidable. They started the day collection let a free-text box accept nine spellings of "Female" and an age typed as a word. A data dictionary defines every variable, its type, its allowed values, and its validation rules before the first participant is entered, so the tool refuses many errors at the point of entry instead of leaving them for you now.

It does not certify that a value is correct, only that it is possible. That distinction drives the next several steps.

Full guide: build a data dictionary that validates

The export opens with two header rows: the short question code and the full question text stacked on top of each other. R and Python read the second row as the first data row, every column becomes text, and nothing computes. You need to combine the two headers into one clean name and drop the extra row before anything else.

Full guide: my survey export has two header rows

Every number is left-aligned text and no sum works. A Finnish, Swedish, or German locale wrote the file with decimal commas and semicolon separators, so read_csv saw "3,14" as a string, not a number. The fix is to read with the right separator and decimal mark, not to find-and-replace inside the file.

Full guide: my numbers turned to text in the CSV

Your model quietly drops a third of the rows and you cannot see why. Missingness is hiding in four costumes at once: a real blank, the string "Unknown", a numeric sentinel like 99 that means "not asked", and an actual NA. Text sentinels you can catch on import; numeric ones you cannot, because 99 is a valid value in some columns and a code for missing in others. Recode those one variable at a time, never in bulk.

library(dplyr)

# blanks and text sentinels become NA on import
d <- readr::read_csv("data.csv", na = c("", "NA", "Unknown"))

# numeric sentinels are per-variable, never global
d <- d |>
  mutate(pain_score = na_if(pain_score, 99),
         comorbid   = na_if(comorbid, 9))

# how many missing, per column
colSums(is.na(d))

import pandas as pd

d = pd.read_csv("data.csv", na_values=["", "NA", "Unknown"])

# numeric sentinels are per-variable, never global
d["pain_score"] = d["pain_score"].replace(99, pd.NA)
d["comorbid"]   = d["comorbid"].replace(9, pd.NA)

# how many missing, per column
d.isna().sum()

Some studies use values such as 9, 99, or 999 for different kinds of missing data (unknown, not measured, not asked). These codes are specific to each variable and belong in the data dictionary. Never assume the same code means the same thing across the dataset: in a pain score 99 may be missing, while in a count it is a real value. Recode one variable at a time, from its documented meaning.

Age sorts as 1, 10, 100, 2 because it imported as text. A date column is a string, so nothing subtracts and no timeline plots. A grouping variable came in as free text with trailing spaces, so "Male" and "Male " count as two groups. Look at the type of every column first, then set each one deliberately.

library(dplyr)

# what type did each column actually get?
glimpse(d)

d <- d |>
  mutate(age_years  = as.integer(age_years),
         sex        = factor(sex, levels = c(1, 2),
                             labels = c("Male", "Female")),
         visit_date = as.Date(visit_date, format = "%d/%m/%Y"))

import pandas as pd

# what type did each column actually get?
d.dtypes

d["age_years"]  = pd.to_numeric(d["age_years"], errors="coerce")
d["sex"]        = d["sex"].astype("category")
d["visit_date"] = pd.to_datetime(d["visit_date"],
                                 format="%d/%m/%Y", errors="coerce")

coerce turns anything it cannot parse into NA rather than stopping. Count the missing values before and after the conversion: a jump means real values failed to parse, and you want to see which ones, not lose them quietly.

You trusted the dictionary and never checked that the export still obeys it. An age slipped to 220 during a manual entry override, a category picked up a value that is not in the coded set, and nobody flagged it. Run the dictionary rules against the actual data and get a report of which rows break which rule. In R, pointblank is built for exactly this.

library(pointblank)

agent <- create_agent(tbl = d) |>
  col_vals_between(age_years, 18, 120) |>
  col_vals_in_set(sex, c("Male", "Female")) |>
  col_vals_not_null(record_id) |>
  interrogate()

agent   # a report of how many rows failed each rule

bad_age    = d[~d["age_years"].between(18, 120)]
bad_sex    = d[~d["sex"].isin(["Male", "Female"])]
missing_id = d[d["record_id"].isna()]

len(bad_age), len(bad_sex), len(missing_id)

Validation tells you a value is possible, not that it is true. A patient aged 52 entered as 25 passes every rule here. Treat a clean report as "nothing impossible got through", not as "the data is correct".

You dedupe on patient ID and lose every follow-up visit. Or you keep a row that is an accidental double entry because it looked like a real repeat. The two are only distinguishable by the key you choose: the same person on the same visit date is a suspect duplicate; the same person on different dates is a real repeat visit you must keep. So flag them, look, and decide by eye. Do not let a function delete rows for you.

library(janitor)

# same person AND same visit date = suspect duplicate
d |> get_dupes(record_id, visit_date)

# same person, different dates = real repeat visits, keep them

# flag every row that repeats on the key that should be unique
dupes = d[d.duplicated(subset=["record_id", "visit_date"],
                       keep=False)]
dupes.sort_values(["record_id", "visit_date"])

get_dupes and duplicated only show you the candidates. Deleting is a separate, deliberate decision, and it belongs in a documented line of code, not a manual delete in the spreadsheet.

You join labs onto patients and the row count jumps from 400 to 1,300. A key that is not unique, or a one-to-many match you did not expect, quietly multiplied the data. Two things prevent it: standardise the key names on both sides first, then state the shape of the join you expect and let the tool raise an error the moment the data does not match it. That way the row explosion stops at the merge, not three analyses later.

library(dplyr)
library(janitor)

patients <- clean_names(patients)   # standardise names first
labs     <- clean_names(labs)

labs <- rename(labs, record_id = patient_id)  # match the key

merged <- left_join(patients, labs,
                    by = "record_id",
                    relationship = "one-to-many")

patients.columns = patients.columns.str.lower().str.strip()
labs = labs.rename(columns={"patient_id": "record_id"})

merged = patients.merge(labs, on="record_id", how="left",
                        validate="one_to_many")

relationship= in dplyr and validate= in pandas fail loudly if the join is not the shape you claimed. Always state the shape you expect; a merge that changes your row count without warning is the single most common way a clean dataset turns wrong.

You fixed a typo in the raw file, saved it, and now you cannot say what the original held. The one copy that should never change is the one you changed. Treat the raw export as read-only. Every fix from the steps above is a line of code that reads the raw file and writes a separate analysis file, so the cleaning is repeatable and the source stays intact.

raw <- readr::read_csv("data/raw/export.csv")   # never edited

analysis <- raw |>
  # all the cleaning steps above, in order
  mutate(age_years = as.integer(age_years))

readr::write_csv(analysis, "data/analysis/clean.csv")

raw = pd.read_csv("data/raw/export.csv")   # never edited

analysis = raw.copy()
# all the cleaning steps above, in order
analysis["age_years"] = pd.to_numeric(analysis["age_years"],
                                      errors="coerce")

analysis.to_csv("data/analysis/clean.csv", index=False)

Keep raw and analysis in separate folders, and never open the raw file in a spreadsheet to "just fix one thing". If a value in the raw file is genuinely wrong, correct it in code with a comment saying why, so the change is visible and reversible.

Six months later a reviewer asks why n dropped from 512 to 488, and you cannot answer because the cleaning happened by hand, in a spreadsheet, across an afternoon you no longer remember. If the steps are not written down, they are gone. Put the whole path in a script or a notebook, one commented decision at a time, and keep it under version control. The script is the record.

• Every drop, recode, and merge is a line of code, not a manual edit.
• Each non-obvious decision has a short comment saying why.
• The script reads raw and writes analysis, so it reruns from scratch.
• It lives in version control, so the history of changes is visible.

When the analysis is finished, the cleaning script is part of the record you can be asked to show. Giving that code a release and a citable identifier is its own short step: see make your analysis code citable.

A collaborator asks for the dataset and you are one click from emailing a file with names, dates of birth, and hospital IDs. Deleting the name column is not enough: under GDPR a file you can still trace back to a person is personal data, and a birth date plus a rare diagnosis often traces back to one person. Before anything leaves your machine, strip the direct identifiers and replace the record key with a random study ID whose mapping you keep separately.

• Remove direct identifiers: name, initials, address, hospital or national ID, contact details, and free-text fields that can name a person.
• Replace the record ID with a random study ID; hold the mapping in a separate, access-controlled file.
• Reduce indirect identifiers: shift or coarsen exact dates, and group rare categories that single someone out.

library(dplyr)

# random study id; mapping stays in a separate secured file
key <- d |>
  distinct(record_id) |>
  mutate(study_id = sample(seq_len(n())))

shared <- d |>
  left_join(key, by = "record_id") |>
  select(-record_id, -initials, -date_of_birth)

import numpy as np

ids = d["record_id"].unique()
mapping = dict(zip(ids, np.random.permutation(len(ids))))
# keep `mapping` in a separate secured file, not with the data

shared = d.assign(study_id=d["record_id"].map(mapping))
shared = shared.drop(columns=["record_id", "initials",
                              "date_of_birth"])

Pseudonymised data is still personal data under GDPR, because the mapping lets someone re-identify it. True anonymisation means individuals are no longer reasonably identifiable; for many clinical research datasets, especially small or rare-disease cohorts, that standard is difficult to achieve. This is design guidance under stated assumptions, not legal advice; check your own data protection rules and your ethics approval before sharing.