Motivation
When we receive an updated version of a dataset, dfdiffs
helps us answer three questions:
- What rows are here now that weren’t here before?
- What rows were here before that aren’t here now?
- What values have been changed?
This vignette covers the two functions that answer the third
question: create_changed_data() and
create_modified_data(). Both are built on the same base R
engine (diff_values_by_key() and
compare_values()), and they differ only in the shape of
their output:
Example data
We’ll use two datasets from the package to test what’s been
modified.
Initial Data
InitialData is the base (original) dataset:
InitialData
|
subject_id
|
record
|
text_value_a
|
text_value_b
|
created_date
|
updated_date
|
entered_date
|
|
A
|
1
|
Issue unresolved
|
Fatigue
|
2021-07-29
|
2021-09-29
|
2021-09-29
|
|
A
|
2
|
Issue unresolved
|
Fatigue
|
2021-07-29
|
2021-10-03
|
2021-10-29
|
|
B
|
3
|
Issue resolved
|
Fever
|
2021-07-16
|
2021-09-02
|
2021-08-18
|
|
C
|
4
|
Issue resolved
|
Joint pain
|
2021-08-24
|
2021-10-03
|
2021-10-03
|
|
C
|
5
|
Issue resolved
|
Joint pain
|
2021-08-24
|
2021-09-20
|
2021-10-20
|
Changed Data
ChangedData is the dataset we’ll compare against
InitialData:
ChangedData
|
subject_id
|
record
|
text_value_a
|
text_value_b
|
created_date
|
updated_date
|
entered_date
|
|
A
|
1
|
Issue resolved
|
Fatigue
|
2021-07-29
|
2021-10-03
|
2021-11-30
|
|
A
|
2
|
Issue resolved
|
Fatigue
|
2021-07-29
|
2021-11-27
|
2021-11-30
|
|
B
|
3
|
Issue resolved
|
Fever
|
2021-07-16
|
2021-10-20
|
2021-11-21
|
|
C
|
4
|
Issue resolved
|
Joint pain, stiffness and swelling
|
2021-08-24
|
2021-10-13
|
2021-11-11
|
|
C
|
5
|
Issue resolved
|
Joint pain
|
2021-08-24
|
2021-10-14
|
2021-11-16
|
Creating join columns
We’ll use create_new_column() to build a
join_var column from subject_id and
record in both datasets:
ChangedDataJoin <- create_new_column(
data = ChangedData,
cols = c("subject_id", "record"),
new_name = "join_var")
InitialDataJoin <- create_new_column(
data = InitialData,
cols = c("subject_id", "record"),
new_name = "join_var")
ChangedDataJoin now starts with the new
join_var column:
ChangedDataJoin
|
join_var
|
subject_id
|
record
|
text_value_a
|
text_value_b
|
created_date
|
updated_date
|
entered_date
|
|
A-1
|
A
|
1
|
Issue resolved
|
Fatigue
|
2021-07-29
|
2021-10-03
|
2021-11-30
|
|
A-2
|
A
|
2
|
Issue resolved
|
Fatigue
|
2021-07-29
|
2021-11-27
|
2021-11-30
|
|
B-3
|
B
|
3
|
Issue resolved
|
Fever
|
2021-07-16
|
2021-10-20
|
2021-11-21
|
|
C-4
|
C
|
4
|
Issue resolved
|
Joint pain, stiffness and swelling
|
2021-08-24
|
2021-10-13
|
2021-11-11
|
|
C-5
|
C
|
5
|
Issue resolved
|
Joint pain
|
2021-08-24
|
2021-10-14
|
2021-11-16
|
create_changed_data()
Call structure
create_changed_data() is built on
diff_values_by_key(), a base R engine that uses
compare_values() to compare matched rows column by column.
It also uses the same rename_join_col() and
create_new_column() helpers as the other comparison
functions. The call tree below was generated from the package source
with stackcallr
(pak::pak("mjfrigaard/stackcallr")). Only functions defined
in dfdiffs are shown.
stackcallr::call_tree_dir("R", root = "create_changed_data")
█─create_changed_data
├─select_cols
├─█─diff_values_by_key
│ ├─compare_values
│ └─class_diffs
├─rename_join_col
├─create_new_column
└─class_diffs
Worked examples
No by column: row-by-row comparison
When we supply only the two datasets,
create_changed_data() compares them row by row. The
$num_diffs and $var_diffs tables are shown
side by side below:
|
Variable name
|
Modified Values
|
|
subject_id
|
0
|
|
record
|
0
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
created_date
|
0
|
|
updated_date
|
5
|
|
entered_date
|
5
|
|
|
Variable name
|
rownumber
|
Current Value
|
Previous Value
|
|
text_value_a
|
1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|
updated_date
|
1
|
2021-10-03
|
2021-09-29
|
|
updated_date
|
2
|
2021-11-27
|
2021-10-03
|
|
updated_date
|
3
|
2021-10-20
|
2021-09-02
|
|
updated_date
|
4
|
2021-10-13
|
2021-10-03
|
|
updated_date
|
5
|
2021-10-14
|
2021-09-20
|
|
entered_date
|
1
|
2021-11-30
|
2021-09-29
|
|
entered_date
|
2
|
2021-11-30
|
2021-10-29
|
|
entered_date
|
3
|
2021-11-21
|
2021-08-18
|
|
entered_date
|
4
|
2021-11-11
|
2021-10-03
|
|
entered_date
|
5
|
2021-11-16
|
2021-10-20
|
|
A single by column
Passing join_var as the by column matches
rows on that key instead of on row position:
|
Variable name
|
Modified Values
|
|
subject_id
|
0
|
|
record
|
0
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
created_date
|
0
|
|
updated_date
|
5
|
|
entered_date
|
5
|
|
|
Variable name
|
join_var
|
Current Value
|
Previous Value
|
|
text_value_a
|
A-1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
A-2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
C-4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|
updated_date
|
A-1
|
2021-10-03
|
2021-09-29
|
|
updated_date
|
A-2
|
2021-11-27
|
2021-10-03
|
|
updated_date
|
B-3
|
2021-10-20
|
2021-09-02
|
|
updated_date
|
C-4
|
2021-10-13
|
2021-10-03
|
|
updated_date
|
C-5
|
2021-10-14
|
2021-09-20
|
|
entered_date
|
A-1
|
2021-11-30
|
2021-09-29
|
|
entered_date
|
A-2
|
2021-11-30
|
2021-10-29
|
|
entered_date
|
B-3
|
2021-11-21
|
2021-08-18
|
|
entered_date
|
C-4
|
2021-11-11
|
2021-10-03
|
|
entered_date
|
C-5
|
2021-11-16
|
2021-10-20
|
|
Multiple by columns, a new by_col, and
cols
This is the most complete case. The by columns are
combined into a single key named with by_col, and only the
columns in cols are compared:
create_changed_data(
compare = ChangedData,
base = InitialData,
by = c("subject_id", "record"),
by_col = "join",
cols = c("text_value_a", "text_value_b"))
|
Variable name
|
Modified Values
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
|
Variable name
|
join
|
Current Value
|
Previous Value
|
|
text_value_a
|
A-1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
A-2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
C-4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|
create_modified_data()
create_modified_data() answers the same question as
create_changed_data(), but its output is shaped for the
“Review Changes” report instead of compare_data().
arsenal::comparedf(), for comparison
Before version 2.1.0, create_modified_data() was built
directly on arsenal::comparedf(). To see where the current
output comes from, we’ll run arsenal::comparedf() on the
same data:
modified_cdf <- arsenal::comparedf(
x = ChangedDataJoin,
y = InitialDataJoin,
by = "join_var")
modified_cdf_sum <- summary(modified_cdf)
The summary contains several tables, but we’re only interested in
diffs.byvar.table and diffs.table:
modified_cdf_sum$diffs.byvar.table
|
var.x
|
var.y
|
n
|
NAs
|
|
subject_id
|
subject_id
|
0
|
0
|
|
record
|
record
|
0
|
0
|
|
text_value_a
|
text_value_a
|
2
|
0
|
|
text_value_b
|
text_value_b
|
1
|
0
|
|
created_date
|
created_date
|
0
|
0
|
|
updated_date
|
updated_date
|
5
|
0
|
|
entered_date
|
entered_date
|
5
|
0
|
modified_cdf_sum$diffs.table
|
var.x
|
var.y
|
join_var
|
values.x
|
values.y
|
row.x
|
row.y
|
|
text_value_a
|
text_value_a
|
A-1
|
Issue re….
|
Issue un….
|
1
|
1
|
|
text_value_a
|
text_value_a
|
A-2
|
Issue re….
|
Issue un….
|
2
|
2
|
|
text_value_b
|
text_value_b
|
C-4
|
Joint pa….
|
Joint pain
|
4
|
4
|
|
updated_date
|
updated_date
|
A-1
|
2021-10-03
|
2021-09-29
|
1
|
1
|
|
updated_date
|
updated_date
|
A-2
|
2021-11-27
|
2021-10-03
|
2
|
2
|
|
updated_date
|
updated_date
|
B-3
|
2021-10-20
|
2021-09-02
|
3
|
3
|
|
updated_date
|
updated_date
|
C-4
|
2021-10-13
|
2021-10-03
|
4
|
4
|
|
updated_date
|
updated_date
|
C-5
|
2021-10-14
|
2021-09-20
|
5
|
5
|
|
entered_date
|
entered_date
|
A-1
|
2021-11-30
|
2021-09-29
|
1
|
1
|
|
entered_date
|
entered_date
|
A-2
|
2021-11-30
|
2021-10-29
|
2
|
2
|
|
entered_date
|
entered_date
|
B-3
|
2021-11-21
|
2021-08-18
|
3
|
3
|
|
entered_date
|
entered_date
|
C-4
|
2021-11-11
|
2021-10-03
|
4
|
4
|
|
entered_date
|
entered_date
|
C-5
|
2021-11-16
|
2021-10-20
|
5
|
5
|
create_modified_data() returns only a small fraction of
this output, reshaped into $diffs_byvar and
$diffs. The extra var.y column is dropped,
var.x becomes Variable name, and
values.x and values.y become
Current Value and Previous Value. The row
position columns (row.x and row.y) are also
dropped in favor of the join key.
We can see the reshaped output by running
create_modified_data() on the same joined datasets:
modified <- create_modified_data(
compare = ChangedDataJoin,
base = InitialDataJoin,
by = "join_var")
modified$diffs_byvar
|
Variable name
|
Modified Values
|
|
subject_id
|
0
|
|
record
|
0
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
created_date
|
0
|
|
updated_date
|
5
|
|
entered_date
|
5
|
modified$diffs
|
Variable name
|
join_var
|
Current Value
|
Previous Value
|
|
text_value_a
|
A-1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
A-2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
C-4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|
updated_date
|
A-1
|
2021-10-03
|
2021-09-29
|
|
updated_date
|
A-2
|
2021-11-27
|
2021-10-03
|
|
updated_date
|
B-3
|
2021-10-20
|
2021-09-02
|
|
updated_date
|
C-4
|
2021-10-13
|
2021-10-03
|
|
updated_date
|
C-5
|
2021-10-14
|
2021-09-20
|
|
entered_date
|
A-1
|
2021-11-30
|
2021-09-29
|
|
entered_date
|
A-2
|
2021-11-30
|
2021-10-29
|
|
entered_date
|
B-3
|
2021-11-21
|
2021-08-18
|
|
entered_date
|
C-4
|
2021-11-11
|
2021-10-03
|
|
entered_date
|
C-5
|
2021-11-16
|
2021-10-20
|
Call structure
create_modified_data() is built on the same
diff_values_by_key() engine as
create_changed_data(). The call tree below was generated
with stackcallr
(pak::pak("mjfrigaard/stackcallr")). Only functions defined
in dfdiffs are shown.
stackcallr::call_tree_dir("R", root = "create_modified_data")
█─create_modified_data
├─select_cols
├─█─diff_values_by_key
│ ├─compare_values
│ └─class_diffs
├─rename_join_col
├─create_new_column
└─class_diffs
Worked examples
No by column: row-by-row comparison
When we supply only the two datasets,
create_modified_data() compares them row by row. The
$diffs_byvar and $diffs tables are shown side
by side below:
|
Variable name
|
Modified Values
|
|
subject_id
|
0
|
|
record
|
0
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
created_date
|
0
|
|
updated_date
|
5
|
|
entered_date
|
5
|
|
|
Variable name
|
rownumber
|
Current Value
|
Previous Value
|
|
text_value_a
|
1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|
updated_date
|
1
|
2021-10-03
|
2021-09-29
|
|
updated_date
|
2
|
2021-11-27
|
2021-10-03
|
|
updated_date
|
3
|
2021-10-20
|
2021-09-02
|
|
updated_date
|
4
|
2021-10-13
|
2021-10-03
|
|
updated_date
|
5
|
2021-10-14
|
2021-09-20
|
|
entered_date
|
1
|
2021-11-30
|
2021-09-29
|
|
entered_date
|
2
|
2021-11-30
|
2021-10-29
|
|
entered_date
|
3
|
2021-11-21
|
2021-08-18
|
|
entered_date
|
4
|
2021-11-11
|
2021-10-03
|
|
entered_date
|
5
|
2021-11-16
|
2021-10-20
|
|
A single by column
Passing join_var as the by column matches
rows on that key instead of on row position:
|
Variable name
|
Modified Values
|
|
subject_id
|
0
|
|
record
|
0
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
created_date
|
0
|
|
updated_date
|
5
|
|
entered_date
|
5
|
|
|
Variable name
|
join_var
|
Current Value
|
Previous Value
|
|
text_value_a
|
A-1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
A-2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
C-4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|
updated_date
|
A-1
|
2021-10-03
|
2021-09-29
|
|
updated_date
|
A-2
|
2021-11-27
|
2021-10-03
|
|
updated_date
|
B-3
|
2021-10-20
|
2021-09-02
|
|
updated_date
|
C-4
|
2021-10-13
|
2021-10-03
|
|
updated_date
|
C-5
|
2021-10-14
|
2021-09-20
|
|
entered_date
|
A-1
|
2021-11-30
|
2021-09-29
|
|
entered_date
|
A-2
|
2021-11-30
|
2021-10-29
|
|
entered_date
|
B-3
|
2021-11-21
|
2021-08-18
|
|
entered_date
|
C-4
|
2021-11-11
|
2021-10-03
|
|
entered_date
|
C-5
|
2021-11-16
|
2021-10-20
|
|
Multiple by columns, a new by_col, and
cols
As with create_changed_data(), the by
columns are combined into a single key named with by_col,
and only the columns in cols are compared:
create_modified_data(
compare = ChangedData,
base = InitialData,
by = c("subject_id", "record"),
by_col = "new_join_var",
cols = c("text_value_a", "text_value_b"))
|
Variable name
|
Modified Values
|
|
text_value_a
|
2
|
|
text_value_b
|
1
|
|
|
Variable name
|
new_join_var
|
Current Value
|
Previous Value
|
|
text_value_a
|
A-1
|
Issue resolved
|
Issue unresolved
|
|
text_value_a
|
A-2
|
Issue resolved
|
Issue unresolved
|
|
text_value_b
|
C-4
|
Joint pain, stiffness and swelling
|
Joint pain
|
|