24  Data Validate

Warning

This module is currently under development, and not available in the main version of Metacheck. To try it out, install the development version of Metacheck:

remotes::install_github("scienceverse/metacheck@data_check")

24.1 What it checks

The data_validate module runs automated data-quality checks on every column extracted by data_check, flagging the kinds of problems that are easy to miss by eye but common in shared data. Every check is a small, dependency-free base-R function that returns a uniform list(problem, message, values), so the module can run them all over every column and collect the findings. The checks are:

  • Values outside a rating scale — a value outside a Likert/rating scale’s valid range, classified as a miscoded missing value, a keying typo, or unexplained.
  • Constant and near-constant columns — a column with a single repeated value that carries no information.
  • Empty columns — a column with no observed values at all.
  • SPSS filter variables — a filter_$ variable left in the export, signalling the data are a pre-filtered subset.
  • Numeric-as-text — a mostly-numeric column contaminated by a few text entries.
  • Category casing — the same category written two ways ("Male" and "male").
  • Whitespace — leading/trailing spaces that split what should be one category.
  • Problematic column names — names carrying file-illegal characters, control characters, or excessive length that break downstream reuse.
  • Colliding column names — sibling names that become identical when special characters are stripped.
  • Mixed / legacy encoding — text whose bytes are not valid UTF-8.
  • Personal information — columns whose name or values look like data that should not be shared openly (emails, IP addresses, national IDs, credit-card numbers, geographic coordinates, free text).

For every spreadsheet file (.xlsx, legacy .xls, OpenDocument .ods/.fods), data_validate also inspects the raw workbook, not just the data it extracts from it — colour-coded cells, merged ranges, empty rows and columns, and a header row that is not the first row of the sheet. These were formerly checked by a separate excel_check module; that module no longer exists, and its checks now run automatically as part of data_validate, on the same file data_check already located.

It also produces an inventory of demographic columns, a Qualtrics survey metadata summary, a careless-responding screen for survey data, and a combined distribution figure of the numeric columns.

Note

This chapter is offline. We build small folders with deliberately planted problems and check them with local_path.

24.2 A worked example

repo <- file.path(tempdir(), "data_validate_demo")
dir.create(file.path(repo, "data"), recursive = TRUE, showWarnings = FALSE)

study <- data.frame(
  id        = 1:40,
  rating    = c(sample(1:7, 39, replace = TRUE), 99),  # 99 in a 1-7 scale
  age       = c(rnorm(39, 30, 5), -99),                # miscoded missing
  condition = c(rep("Control", 20), rep("control", 19), "X"),  # casing
  constant  = rep(1, 40)                               # no information
)
utils::write.csv(study, file.path(repo, "data", "study.csv"), row.names = FALSE)
mo <- module_run(test_paper(), "data_validate",
                 local_path = repo, local_only = TRUE)

mo$traffic_light
#> [1] "red"
cat(mo$summary_text)
#> We ran automated data-quality checks on 5 columns; 3 columns have at least one potential issue (1 column with inconsistent category casing; 1 column with a single constant value; 1 column flagged by values outside the scale). We detected demographic column for Age.

Running data_validate automatically runs data_check first (to read and classify the columns) and then applies the quality checks to the result.

24.3 The findings table

Every flagged column appears in the table, one row per (column, check):

mo$table[, c("column", "check", "detail")] |>
  knitr::kable()
column check detail
rating Values outside the scale 1 value outside the 1–7 scale: 99 (looks like a missing-data code → recode to NA)
condition Case issues Categories differing only by case: Control/control
constant Constant Column is constant: every value is “1”.

The check column names the category of problem, and detail explains it in words. Below we describe each check and the function that implements it.

24.4 Values outside a rating scale: data_check_scale_values()

The single most informative numeric check. Earlier versions ran a raw Tukey-outlier flag on every numeric column, but that is misleading: a long tail on a reaction-time or score column is normal, not an error. Instead, data_check_scale_values() fires only on bounded rating scales — the one place a value can be genuinely “impossible”. A rating scale has a small set of consecutive valid integer levels, and any value outside that set is a data problem.

The valid range is ground truth when a codebook supplies it (enumerated valid codes or a declared missing scheme, passed in from codebook_check); otherwise it is inferred by .detect_likert_scale(), which grows a dense consecutive core outward from the modal value, bridges small interior gaps between common levels, anchors the floor to the natural scale start (0 or 1), and returns everything outside [lo, hi] as suspects. Crucially it infers the range from the dense core, not min/max, so a single stray 99 cannot destroy the scale it is meant to reveal.

Each out-of-scale value is then classified:

  • a missing-data code (a -99 / 999 from the sentinel list .data_missing_sentinels, or a codebook-declared missing code) → recode to NA;
  • a keying typo of an in-scale value (a 33 for 3, a 55 for 5) via .scale_typo_of(), which names the probable intended value;
  • otherwise unexplained.
data_check_scale_values(c(sample(1:7, 50, replace = TRUE), 99, 33))$message
[1] "2 values outside the 1–7 scale: 33 (looks like a typo of 3),
     99 (looks like a missing-data code → recode to NA)"

The sentinel list is deliberately conservative: the 9x-block (97/98/99), repeated-digit 8- and 7-families, wide placeholders, and the two attested negative codings (-99, -999). Single digits 7/8/9 are excluded (valid Likert points), and a sentinel only ever fires when it sits outside the scale, so a 99 in an age column or a 97 in a 0–100 score does not trip it.

The related helper data_check_outliers() still computes the Tukey 1.5×IQR fences, but they are now used only as visual context — the dashed red lines on the distribution figure — and as the basis for the careless-responding IRV screen, not as a per-column finding.

24.5 Constant, empty, and SPSS filter columns

These fire for any column type, and are tiered by how likely they are to signal a real problem:

  • data_check_empty() — an all-missing column (all NA, or for text all blank/whitespace). Always flagged: a variable that never recorded anything.
  • data_check_constant() — a single repeated value (or ≥99% one value, “near-constant”). A constant column is flagged when it is numeric, or when its name looks like a design/condition variable (data_check_design_name() — condition/group/treatment/arm/dose, with a word boundary so “charm” ≠ “arm”), because one value there suggests the file was filtered to a single condition before export. A constant text column is usually intentional file-level metadata (a version number, a language code) and is only listed in an informational note, not counted as an issue.
  • data_check_spss_filter() — an SPSS “Select Cases” filter variable (filter_$). Flagged whether constant or not: constant-1 means the shared file is a pre-filtered subset; varying means the reported analyses likely used only the selected rows and the filter must be re-applied.
data_check_empty(c(NA, NA, NA))$message
#> "Column is empty: all 3 values are missing."
data_check_constant(rep(1, 40))$message
#> "Column is constant: every value is \"1\"."
data_check_spss_filter("filter_$", rep(1, 30))$problem
#> TRUE

24.6 Categorical checks

For non-numeric columns:

  • data_check_numeric_in_text() — a mostly-numeric column (≥80% coercible) stored as text, usually because a stray note or coding legend was typed into a data cell. When this fires, the case check is skipped (every distinct number would otherwise look like a spurious “level”).
  • data_check_case_issues() — categories differing only by letter case ("Male"/"male"), likely the same category entered inconsistently.
  • data_check_whitespace() — values with leading/trailing whitespace ("Male " vs "Male"), which silently split a category.
data_check_case_issues(c("Male", "male", "Female"))$message
#> "Categories differing only by case: Male/male"
data_check_numeric_in_text(c("1", "2", "3", "see note", "5"))$problem
#> TRUE

24.7 Column-name quality: data_check_colname() and collisions

Column names are checked too, because a bad name breaks downstream reuse — as a file name, in an analysis script, or on import into another package. data_check_colname() flags names carrying characters illegal in file names (< > : " / \ | ? *), control characters, or more than 64 characters (the most SPSS accepts; SAS and Stata stop at 32; Windows’ 260-character path limit also blocks the codebook’s per-variable figures for very long names). A garbled name usually means the header row was not exported or parsed as intended.

data_check_colname_collisions() flags sibling columns whose names become identical once special characters are sanitized away (e.g. t' next to a t-with-diacritic) — tools that sanitize names on import cannot tell them apart.

data_check_colname("this/is:an*illegal|name")$problem
#> TRUE

24.8 Spreadsheet formatting

Every check so far reads the data after data_check has parsed it into a data frame — but a spreadsheet file can be badly formatted in ways that are invisible once it is read that way. data_validate also inspects the raw workbook directly for four kinds of non-machine-readable formatting:

  • Colour coding — cells whose fill colour is used to encode information (for example, marking excluded participants in red). Colour is invisible to any analysis script and is lost on CSV export.
  • Merged cells — merged ranges break the rectangular grid that data frames require, and typically indicate multi-level headers that a program cannot parse.
  • Empty rows / empty or unnamed columns — fully blank rows inside the data range, or columns with a blank header or no data beneath one.
  • Header not on the first row — a banner, blank, or units row sitting above the real column header, so the file does not read as a clean table (the reader takes the junk row as the header and invents names like ...1, ...4, or spreads one merged label across many columns).

This runs on every spreadsheet format data_check recognises — .xlsx, OpenDocument .ods/.fods, and legacy .xls — reading the workbook’s own XML directly for colour and merge information that a data-reading package like readxl discards entirely. It runs on the file data_check already located (via its structure table), independently of whether that file could actually be extracted into a clean tabular preview: a merged banner cell or an offset header is very often why extraction failed in the first place, so this check must not depend on a successful preview to run.

Note

This was formerly a separate module, excel_check. It no longer exists on its own — every check it performed is now part of data_validate, run automatically alongside the column-level checks, on the same files.

repo_xl <- file.path(tempdir(), "spreadsheet_demo")
dir.create(file.path(repo_xl, "data"), recursive = TRUE, showWarnings = FALSE)

# a messy workbook: colour, a merge, and an empty column
wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, "Data")
df <- data.frame(id = 1:4, group = c("a", "b", "a", "b"),
                 notes = rep(NA, 4), score = c(10, 20, 30, 40))
openxlsx::writeData(wb, "Data", df)
openxlsx::addStyle(wb, "Data", openxlsx::createStyle(fgFill = "#FFCC00"),
                   rows = 2, cols = 4)                 # a colour-coded cell
openxlsx::mergeCells(wb, "Data", cols = 1:2, rows = 7)  # a merged range
openxlsx::saveWorkbook(wb, file.path(repo_xl, "data", "messy.xlsx"),
                       overwrite = TRUE)

# a clean workbook, for comparison
openxlsx::write.xlsx(data.frame(id = 1:3, score = c(1.1, 2.2, 3.3)),
                     file.path(repo_xl, "data", "clean.xlsx"))
mo_xl <- module_run(test_paper(), "data_validate",
                    local_path = repo_xl, local_only = TRUE)
mo_xl$table[!is.na(mo_xl$table$label) | mo_xl$table$check %in%
             c("Colour coding", "Merged cells", "Empty or unnamed columns"),
           c("source_file", "label", "check", "detail")] |>
  knitr::kable()
source_file label check detail

Only messy.xlsx is flagged; the clean workbook produces no rows. These findings carry column = NA in the $table (the issue is not about one column, it is about the sheet), and the sheet name is shown under label instead. They join the same “Issues Identified” report table as every other check, so a reviewer sees every problem with a file in one place rather than in a separate report.

Two further, related checks fire on any spreadsheet regardless of formatting:

  • “Not a rectangular dataset” — data_check itself already decided this file does not read as a usable dataset (mostly free text, or almost entirely empty); data_validate adds the structural note here so the finding shows up in the same table rather than only in data_check’s own output.
  • “Un-inspectable (.xls)” — legacy binary .xls has no XML to inspect for colour or merges; it is still checked for an offset header (which does not need the XML structure), but colour/merge/empty-row-or-column checks are skipped with a note recommending conversion to .xlsx or .ods.

24.9 Mixed / legacy encoding

data_check records, per column, how many values had to be re-interpreted as Latin-1 because their bytes were not valid UTF-8 (the utf8_repaired count). data_validate turns any non-zero count into a Mixed encoding finding: metacheck read the file by re-interpreting those bytes, but on another system such characters corrupt silently (é becomes é or �), so the researcher is told to re-save the file as UTF-8 (with the exact steps for Excel, R, and SPSS).

24.10 Personal-information screening

data_validate screens each column for content that should not be shared openly, reported as “review before sharing” prompts that never echo the matching value. Four detectors run:

  • data_check_pii_values() — values that look like emails, IP addresses, national IDs, or credit-card numbers.
  • data_check_pii_name() — a column name such as email, firstname, address, dob, ssn.
  • data_check_pii_geo() — geographic coordinates (a latitude/longitude column name with in-range values).
  • data_check_pii_freetext() — long open typed free-text columns that may contain identifying prose (numeric columns are exempt).
pii <- file.path(tempdir(), "pii_demo")
dir.create(file.path(pii, "data"), recursive = TRUE, showWarnings = FALSE)
utils::write.csv(data.frame(
  participant = 1:3,
  email       = c("a@example.com", "b@example.com", "c@example.com"),
  full_name   = c("Ada Lovelace", "Alan Turing", "Grace Hopper")
), file.path(pii, "data", "responses.csv"), row.names = FALSE)

mo_pii <- module_run(test_paper(), "data_validate",
                     local_path = pii, local_only = TRUE)
mo_pii$table[, c("column", "check")] |>
  knitr::kable()
column check
email Personal info (values)
email Personal info (column name)
full_name Personal info (column name)

24.11 Codebook ground truth

When codebook_check has run, data_validate uses its output as ground truth for the numeric checks. The internal codebook_of() lookup decodes the documented valid values (a code list) and declared missing values from the codebook table’s JSON columns: a documented 1–5 value set makes a 6 out-of-range even when the data alone would not reveal the scale, and a declared -99 is flagged as missing directly. The label_of() lookup attaches each column’s documented label to its findings for context.

24.12 Demographic variables

Almost every human-subjects study reports the same three demographic variables — age, gender/sex, and race/ethnicity — so data_validate picks them out and lists them, reusing data_check’s precomputed concept facet (or recomputing it with data_check_demographic() against an older data_check run). Detection requires the column name and values to agree, so a condition column coded 1/2 is never mistaken for gender.

demo <- file.path(tempdir(), "demo_demo")
dir.create(file.path(demo, "data"), recursive = TRUE, showWarnings = FALSE)
utils::write.csv(data.frame(
  id        = 1:30,
  age       = sample(18:65, 30, replace = TRUE),
  gender    = sample(c("Male", "Female", "Non-binary"), 30, replace = TRUE),
  ethnicity = sample(c("Hispanic", "Non-Hispanic"), 30, replace = TRUE),
  score     = rnorm(30)
), file.path(demo, "data", "study.csv"), row.names = FALSE)

mo_demo <- module_run(test_paper(), "data_validate",
                      local_path = demo, local_only = TRUE)
mo_demo$demographics |>
  knitr::kable()
source_file column demographic
study.csv age age
study.csv gender gender
study.csv ethnicity race

This is an inventory, not a problem flag — it helps a reviewer see at a glance whether the shared data documents its sample’s demographics (and, with the personal-information screen above, whether doing so raises disclosure concerns). The result is returned in $demographics.

24.13 Qualtrics survey metadata

Qualtrics exports have a fixed, recognisable shape (see the Data Check chapter). data_validate summarises the metadata that is reliably extractable from any Qualtrics file with .dv_qualtrics_summary(), locating each metadata column by its semantic tag (.dv_q_col()) rather than its exact name:

  • preview / unfinished responses — rows where Status marks a preview or spam, Finished is false, or Progress is below 100, which usually need dropping before analysis;
  • the completion-time distribution — the median Duration, with a count of implausibly fast responses (under half the median and under two minutes);
  • the data-collection window — the date range from RecordedDate / StartDate (parsed tolerantly by .dv_q_datetime()); and
  • which Qualtrics personal-information fields are present (IP address, email, location, external reference, recipient name).

The substantive question columns are deliberately not interpreted here — that is the job of the scale-block detection in Codebook Check. The summary is returned in $qualtrics and rendered by .dv_qualtrics_report().

24.14 Careless responding

For survey data, data_validate additionally screens for careless responding — respondents who straightline (give the same answer to every item) or answer unusually flatly or erratically. This uses the careless package’s longstring and intra-individual response variability (IRV) indices, computed per scale block by .dv_careless_block().

Because these indices only make sense on a block of items measured on a common scale, and a finding is only actionable if you can point to which respondent, the check runs only when both conditions hold:

  1. the file contains a block of Likert-type items — detected by .detect_scale_blocks(), a maximal run of adjacent integer columns (3–11 response options) sharing a variable-name prefix (via .scale_name_prefix()), with at least .scale_min_items (3) items; and
  2. the file has an identifier column (from data_check’s identifier role, a name-pattern fallback, or else the check is skipped).

Item blocks are split by their variable-name prefix, so panas_1 … panas_10 and rse_1 … rse_5 are treated as two separate scales even when they sit next to each other. Within a block, a respondent is flagged when either their longest run of identical answers covers ≥80% of the items (straightlining) or their IRV is a Tukey outlier for the block (flat or erratic answering).

survey <- file.path(tempdir(), "careless_demo")
dir.create(file.path(survey, "data"), recursive = TRUE, showWarnings = FALSE)

set.seed(1)
n <- 60
items <- as.data.frame(matrix(sample(2:4, n * 10, replace = TRUE), nrow = n))
names(items) <- paste0("mood_", 1:10)
# add one straightliner (all 3s) and one erratic responder (1,5,1,5,...)
items <- rbind(items,
               as.data.frame(matrix(rep(3, 10), nrow = 1)) |> setNames(names(items)),
               as.data.frame(matrix(rep(c(1, 5), 5), nrow = 1)) |> setNames(names(items)))
survey_df <- cbind(participant_id = seq_len(n + 2), items)
utils::write.csv(survey_df, file.path(survey, "data", "mood.csv"), row.names = FALSE)

mo_car <- module_run(test_paper(), "data_validate",
                     local_path = survey, local_only = TRUE)
mo_car$careless |>
  knitr::kable()
respondent source_file n_blocks_flagged scales reasons max_longstring irv short_scale_only
3 61 mood.csv 1 mood (1-5, 10 items) straightlining + IRV outlier 10 0.00 FALSE
1 4 mood.csv 1 mood (1-5, 10 items) IRV outlier 5 0.53 FALSE
2 55 mood.csv 1 mood (1-5, 10 items) IRV outlier 4 0.47 FALSE
4 62 mood.csv 1 mood (1-5, 10 items) IRV outlier 1 2.11 FALSE

The per-block findings are aggregated to one row per respondent by .dv_careless_by_respondent(), which records how many blocks flagged each person, on which scales, the worst longstring and most extreme IRV, and a short_scale_only flag — TRUE when every flag is short-scale straightlining (a run of identical answers on a scale of ≤7 items), which on a short unidirectional scale is often normal coherent answering rather than carelessness. These are prompts to inspect those rows, not automatic exclusions.

Note

The careless check needs the optional careless package. If it is not installed, the check is skipped and the report notes how many survey files it would have screened.

24.14.1 Two real examples

The synthetic example above shows the mechanics. To see the check on real research data, here are two openly shared datasets. The code is shown but not executed here (the data are not redistributed with this book); run it yourself against a local copy of each repository to reproduce the output.

A long scale with a clear straightliner. The replication data of Gervais et al. (2017) — two preregistered replications of the “anticipating divine protection” hypothesis — include a 40-item scale (variable prefix dos, responses 1–5) alongside a participant id.

mo <- module_run(test_paper(), "data_validate",
                 local_path = "divine-protection-replication", local_only = TRUE)
mo$careless[, c("scale", "respondent", "longstring", "irv", "reason")]
                scale respondent longstring  irv                      reason
1 dos (1-5, 40 items)        129         40 0.00 straightlining + IRV outlier
2 dos (1-5, 40 items)        ...          9 0.63                 IRV outlier
# ... 19 respondents flagged in total

Respondent 129 answered all 40 items identically (longstring = 40, IRV = 0) — an unambiguous straightliner. A further 18 respondents are flagged as IRV outliers for a closer look.

Several short scales, split by prefix. The “Parents’ Failure Mindsets” data of Haimovitz & Dweck (2016) contain three separate failure-mindset scales in adjacent columns, named fail_*, failbad_*, and failgood_*, all on a 1–6 metric.

mo <- module_run(test_paper(), "data_validate",
                 local_path = "parents-failure-mindsets", local_only = TRUE)
unique(mo$careless$scale)
[1] "fail (1-6, 6 items)"     "failbad (1-6, 5 items)"  "failgood (2-6, 5 items)"

Even though the three scales sit next to each other on the same response metric, the variable-name prefix splits them into three distinct blocks, and careless indices are computed within each.

24.15 Distributions in a report

When you pass plot_distributions = TRUE (and have ggplot2 installed), data_validate draws a single combined figure with one small histogram per numeric column, the outlier fences drawn as dashed red lines, built by data_validate_dist_facets(). Earlier versions drew one plot per column, which was slow and unreadable on wide files (a survey export can have hundreds of numeric columns). The combined figure renders once as a self-contained inline image (so a moved report keeps its plot) and caps the number of panels at max_facets (default 40), noting how many columns were omitted if it truncates. All columns are still checked — only the figure is limited.

module_run(test_paper(), "data_validate", local_path = repo,
           local_only = TRUE, plot_distributions = TRUE, max_facets = 60)

24.16 The traffic light

Light Meaning
green columns were checked and no issues were found
yellow fewer than a quarter of columns were flagged, or only careless-responding respondents or spreadsheet-formatting issues were found
red a quarter or more of columns were flagged
na there were no readable tabular data files to validate, and no spreadsheet-formatting issues either

Spreadsheet-formatting findings are file-level, not column-level, so they are never counted in the quarter-of-columns red threshold — a workbook with a colour-coded cell does not turn the whole result red the way a quarter of columns having out-of-range values would. If no columns could be validated at all (no readable tabular data) but a spreadsheet file was found with formatting problems, the result is yellow, not na — there is a real, actionable finding to report even though nothing could be checked at the column level.

24.17 Options

# make the outlier rule stricter or more lenient (default k = 1.5)
module_run(test_paper(), "data_validate",
           local_path = repo, local_only = TRUE, outlier_k = 3)

A larger outlier_k widens the fences used for the distribution figure and the IRV screen, so only more extreme values are flagged.

24.18 What you get back

Element What it contains
$traffic_light green / yellow / red / na
$summary_text plain-text summary with a per-check breakdown
$summary_table paper_id, column_n, flagged_n, spreadsheet_file_n, spreadsheet_flagged_file_n
$table one row per (column, check): source_file, column, label, check, detail — spreadsheet-formatting findings join the same table with column = NA and the sheet name in label
$demographics one row per detected demographic column: source_file, column, demographic
$qualtrics one row per detected Qualtrics export: response count, preview/unfinished rows, median time, collection window, PII fields present
$careless one row per flagged respondent (survey data), aggregated across scale blocks
$report formatted report with the issue breakdown, out-of-range table, spreadsheet-formatting table, combined distribution figure, Qualtrics summary, and careless-responding table

spreadsheet_file_n counts every spreadsheet file examined (.xlsx/.xls/.ods/.fods), whether or not it had any issues; spreadsheet_flagged_file_n counts how many of those had at least one formatting finding.