Skip to contents

Compare two versions of a dataset

Source: R/CompareDatasets.R
CompareDatasets.Rd

Compare an old dataset and a new dataset using one or more key variables. This function identifies record-level, variable-level, and cell-level changes between dataset versions. It is useful when reviewing updated data extracts, revised REDCap exports, cleaned spreadsheet versions, or vendor-delivered dataset updates.

Usage

CompareDatasets(OldData, NewData, keys, Keys = lifecycle::deprecated())

Arguments

OldData

A data frame representing the earlier dataset version.

NewData

A data frame representing the newer dataset version.

keys

Character vector of key variables used to align records across the two datasets. Multiple keys are supported, such as c("study_id", "TimePoint").

Keys

Deprecated (since 19.15.0). Use keys instead.

Value

A list with dataset comparison results, including:

SummaryText

A plain-text summary of the dataset comparison.

Summary

One-row tibble with core comparison metrics.

Fingerprint

Tibble comparing rows, columns, and unique key combinations.

KeyTypes

Tibble showing key variable classes before coercion.

Checks

Tibble summarizing comparison checks and pass/warning/fail status.

StructureChanges

Tibble of variables added to or removed from NewData, using normalized variable names.

AddedVariables

Tibble of variables present in NewData but not OldData, using normalized variable names.

RemovedVariables

Tibble of variables present in OldData but not NewData, using normalized variable names.

AddedRecords

Tibble of key combinations present in NewData but not OldData.

RemovedRecords

Tibble of key combinations present in OldData but not NewData.

DuplicateKeys

List containing duplicated key rows from OldData and NewData.

ComparisonKeys

List describing matching keys, compared keys, and keys excluded from cell comparison due to duplicate key combinations.

NameRepairAudit

Tibble describing variables whose raw names differ after removing tibble-style ...number suffixes.

ComparisonVariableMap

Tibble mapping normalized variable names to the raw OldData and NewData names used for cell-level comparison.

ClassAudit

Tibble comparing variable classes for common non-key variables.

ModifiedValues

Long-format tibble of cell-level value changes.

VariableChangeSummary

Tibble summarizing changes by variable.

TopChangedVariables

Top changed variables by number of modified values.

SuspiciousChanges

Tibble of high-change-rate or class-change variables.

Details

The function always returns both detailed cell-level changes and summary-level outputs. Cell-level changes are stored in long format, with one row per changed value.

Key variables are coerced to character internally before comparison because IDs are often stored as numeric in one file and character in another. The original key classes are preserved in the KeyTypes output.

Variable names are also audited for common tibble/readxl name-repair suffixes such as ...372. These suffixes are ignored when identifying variables added or removed, while raw variable names are still preserved in the output.

If duplicate key combinations are detected, the function still runs, but cell-level comparison is performed only for key combinations that are unique in both datasets. Duplicate keys are returned separately and flagged in Checks.

Reading the result

Start with Checks, the traffic-light table. Everything that changed between the two extracts is classified, counted, and explained there, so a release can be signed off - or stopped - without reading any raw data. Summary carries the same story as headline metrics, one row per measure.

From there, ModifiedValues drills down to the individual cells that moved, with the old and new value side by side and each change classified; VariableChangeSummary rolls those up per variable, which is usually what gets circulated to collaborators. SummaryText is a plain-text version of the whole comparison, for a log or an email.

Examples

# \donttest{
data(SampleData)

# A revised extract: corrected ages, lost values, dropped rows, a new column
df_OldData <- dplyr::mutate(
  SampleData,
  ParticipantID = seq_len(nrow(SampleData)),
  .before = 1
)

set.seed(4)
df_NewData <- df_OldData
df_NewData$age[1:5] <- df_NewData$age[1:5] + 1
df_NewData$MMP7[c(2, 9, 14)] <- NA
df_NewData$NewBiomarker <- stats::rnorm(nrow(df_NewData))
df_NewData <- df_NewData[-c(3, 7), ]

comparison <- CompareDatasets(
  OldData = df_OldData,
  NewData = df_NewData,
  keys = "ParticipantID"
)

ShowTable <- function(x, height = "300px") {
  htmltools::browsable(htmltools::HTML(as.character(
    FreezeTableHeader(x, height = height, full_width = TRUE)
  )))
}

# The traffic-light checks
ShowTable(comparison$Checks, height = "360px")

 Check 
    Count 
    Status 
    Details 
  
 Key Types 
    0 
    PASS 
    Key storage classes match across dataset versions. 
  
 Duplicate Keys 
    0 
    PASS 
    No duplicate key combinations detected. 
  
 Records Added 
    0 
    PASS 
    No added records detected. 
  
 Records Removed 
    2 
    WARNING 
    Some key combinations are present in OldData but not NewData. 
  
 Variables Added 
    1 
    WARNING 
    NewData contains variables not present in OldData after ignoring tibble-style name-repair suffixes. 
  
 Variables Removed 
    0 
    PASS 
    No removed variables detected after ignoring tibble-style name-repair suffixes. 
  
 Name Repair Differences 
    0 
    PASS 
    No tibble-style name-repair differences detected among common variables. 
  
 Variables Skipped From Cell Comparison 
    0 
    PASS 
    No variables were skipped because of ambiguous normalized names. 
  
 Class Changes 
    0 
    PASS 
    No class changes detected among compared variables. 
  
 Values Modified 
    7 
    WARNING 
    At least one cell-level value changed among compared records. 
  
 Suspicious Changes 
    0 
    PASS 
    No high change-rate or class-change variables detected. 
  
 Duplicate Keys Excluded From Cell Comparison 
    0 
    PASS 
    No duplicate matching keys were excluded from cell comparison. 
  

# The headline metrics
ShowTable(
  data.frame(
    Metric = names(comparison$Summary),
    Value = vapply(comparison$Summary, format, character(1), trim = TRUE),
    row.names = NULL
  ),
  height = "300px"
)

 Metric 
    Value 
  
 OldRows 
    333 
  
 NewRows 
    331 
  
 RowDifference 
    -2 
  
 OldColumns 
    132 
  
 NewColumns 
    133 
  
 ColumnDifference 
    1 
  
 OldUniqueKeys 
    333 
  
 NewUniqueKeys 
    331 
  
 MatchingKeys 
    331 
  
 OldMatchRate 
    99.4 
  
 NewMatchRate 
    100 
  
 AddedRecords 
    0 
  
 RemovedRecords 
    2 
  
 AddedVariables 
    1 
  
 RemovedVariables 
    0 
  
 SchemaChangeCount 
    1 
  
 NameRepairDifferences 
    0 
  
 VariablesSkippedFromCellComparison 
    0 
  
 CommonVariablesMapped 
    131 
  
 CommonVariablesCompared 
    131 
  
 ComparedKeys 
    331 
  
 ComparedCells 
    43361 
  
 DuplicateKeyGroups_Old 
    0 
  
 DuplicateKeyGroups_New 
    0 
  
 ExcludedDuplicateKeys 
    0 
  
 KeyTypeMismatches 
    0 
  
 ClassChanges 
    0 
  
 ModifiedValues 
    7 
  
 PercentValuesModified 
    0.016 
  
 VariablesChanged 
    2 
  
 SuspiciousChanges 
    0 
  

# The individual cells that moved
ShowTable(comparison$ModifiedValues)

 ParticipantID 
    Variable 
    OldVariable 
    NewVariable 
    OldValue 
    NewValue 
    OldClass 
    NewClass 
    ChangeType 
  
 2 
    MMP7 
    MMP7 
    MMP7 
    -5.968190729 
    NA 
    numeric 
    numeric 
    Old present, New missing 
  
 9 
    MMP7 
    MMP7 
    MMP7 
    -5.844645406 
    NA 
    numeric 
    numeric 
    Old present, New missing 
  
 14 
    MMP7 
    MMP7 
    MMP7 
    -1.380617019 
    NA 
    numeric 
    numeric 
    Old present, New missing 
  
 1 
    age 
    age 
    age 
    52 
    53 
    numeric 
    numeric 
    Different non-missing values 
  
 2 
    age 
    age 
    age 
    61 
    62 
    numeric 
    numeric 
    Different non-missing values 
  
 4 
    age 
    age 
    age 
    97 
    98 
    numeric 
    numeric 
    Different non-missing values 
  
 5 
    age 
    age 
    age 
    73 
    74 
    numeric 
    numeric 
    Different non-missing values 
  

# A per-variable roll-up
ShowTable(comparison$VariableChangeSummary, height = "220px")

 Variable 
    Changes 
    OldVariable 
    NewVariable 
    OldClass 
    NewClass 
    ClassChanged 
    NameChanged 
    Compared 
    SkipReason 
    ComparedRecords 
    PercentChanged 
  
 age 
    4 
    age 
    age 
    numeric 
    numeric 
    FALSE 
    FALSE 
    TRUE 
    NA 
    331 
    1.2 
  
 MMP7 
    3 
    MMP7 
    MMP7 
    numeric 
    numeric 
    FALSE 
    FALSE 
    TRUE 
    NA 
    331 
    0.9 
  

# A plain-text version, for a log or an email
cat(comparison$SummaryText, sep = "\n")
#> DATASET COMPARISON SUMMARY
#> 
#> Keys:
#> ParticipantID
#> 
#> Rows:
#> Old: 333
#> New: 331
#> Difference: -2
#> 
#> Columns:
#> Old: 132
#> New: 133
#> Difference: 1
#> 
#> Unique Key Combinations:
#> Old: 333
#> New: 331
#> Matching: 331
#> Old Match Rate: 99.4%
#> New Match Rate: 100%
#> 
#> Records:
#> Added: 0
#> Removed: 2
#> 
#> Variables:
#> Added: 1
#> Removed: 0
#> Schema Change Count: 1
#> Name Repair Differences: 0
#> Variables Skipped From Cell Comparison: 0
#> Added Variables: NewBiomarker
#> Removed Variables: None
#> 
#> Key Type Mismatches:
#> None
#> 
#> Duplicate Key Groups:
#> Old: 0
#> New: 0
#> Excluded Matching Keys: 0
#> 
#> Cell-Level Changes:
#> Compared Cells: 43361
#> Modified Values: 7
#> Percent Values Modified: 0.016%
#> Variables Changed: 2
#> Most Changed Variables: age (4; 1.2%), MMP7 (3; 0.9%)
#> 
#> Suspicious Changes:
#> None
# }