Update Datasets With replacer

Dragos Bandur

2021-09-01

The following terms are specific to this utility:

• lookup table: a csv format data file containing at least 4 standard columns with standard column names:

  • “vars”: names of data columns involved in value replacement
  • “oldVals”: values set for replacement found in the data file
  • “newVals”: replacement values associated with oldVals
  • “id”: index containing row numbers and special values such as 0, NA (or empty)

• simple (or 1:1) replacement: one value from newVals replaces one value from oldVals

• multiple (or 1:many) replacement: one value from newVals replaces identical values from oldVals

• split: in this context, recursive partition of multiple replacements into simple and multiple replacements

• generic value: restricted replacement value used for multiple replacements of missing data

• involved column: data column with the name listed in vars column of lookup

• request: implicit information containing value type, value missingness, value uniqueness, value location, replacement type, replacement value - in one row, spanning the standard columns of lookup

Lookup may contain a combination of simple and multiple replacements, simple replacements only, multiple replacements only (i.e. missing only, duplicated values only, or mixed) as needed. Multiple replacements can be split as needed.

On special circumstances, column “id” may be absent from lookup while the presence of the other three standard columns is mandatory. Modifying standard columns’ names will result in error. Any extra columns in lookup will be automatically removed during internal conformance checks on lookup.

Multiple replacements apply to missing or duplicated non-missing values. While replacement values of duplicated values could be regarded as generic, they are entirely decided by the User. They are not restricted in this sense.

In this version, replacements of missing data are restricted to words formed with the word roots MIS|mis, PRE|pre, UN|un, ABS|abs and the adverbs YES|yes, NO|no for missing values of type character and with a number equal to- or larger than 3 integer 9s (i.e. 999, 9999 etc.) for missing values of double or integer types.

Short sentences beginning with any word root or adverb above may be accepted.

Typical data replacement workflow: the initial data file and the corresponding lookup file (here named “data.csv” and “lookup.csv”) are saved in a directory, here named dir. Once the package is installed, the following commands should be typed at the R prompt:

> require(replacer)
> dir = 'C:/path/to/directory'
> replaceVals(dir) ## to update the data

Results will show on screen (Section “Screen Output Example” presents the output of a different data/lookup pair). A csv file similar to Out-Data is saved on dir.

This small example covers the complete set of lookup requests with index present. Column “Rw” is here for convenience only. It should not be manually created.

The left-hand side table, In-Data, contains 3 columns of data. The two columns (“a” and “b”), involved in replacements, are of character respectively, of numeric data types and both contain missing, duplicates and unique values. The “uninvlvd” column in In-Data is not listed in lookup’s vars therefore, not involved in replacements and remains unchanged in Out-Data. Non-standard column source in Lookup is not standard hence, is automatically removed.

The Out-Data result requires a User-made lookup file with index; the replacement requests are as follows:

• simple replacements: missing and non-missing values at rows 2, 7, 8, 9 and 12
• multiple replacements:
  • of duplicated values (id = 0) at rows 3, 6, and 9
  • of missing values (id = empty) at rows 7, 10, 11 and 12
• split replacements: i.e. column “a” undergoes a split on the duplicated value “cc” present in 3 rows of In-Data; a default multiple replacement of length 3 was split in a length 2 multiple replacement at rows 3 and 6 replaced by “DD” and a simple replacement at row 12 replaced by “CA.”

A User-made index may or, may not be necessary in lookup:

There are two functions designed for direct use and set to display a series of messages and comments informing the User on the computational path taken and on findings along the way. While the messages can be turned off, the comments remain visible.

Single file replacements are processed with function replaceVals(). When data and lookup files are named simply “data.csv” and “lookup.csv” the function only requires the path to the directory where these files are stored, written as length 1 quoted character, with forward “/” or, double backward “\\” slash and without end slash.

At start, this function performs a series of conformance checks on lookup, excludes non-standard columns then separates the data into involved and uninvolved columns, rejoining them on exit.

Batch file replacements are processed with function bReplace(). When messages are set to TRUE it offers an almost full range of messages/comments for each data/lookup input pair and request type. During run, bReplace() calls replaceVals() function as many times as data/lookup pairs are on the list, displaying a named list of messages, comments, updated data and counts for each input pair.

Both functions save their updated data to the directory above, in csv file format.

Helper function sReplace() (described in Manual) is the data replacement workhorse for this utility. When called by replaceVals(), it firstly checks for index presence in lookup. Upon the result, the function moves along the branches of a decision tree:

help(Chile, package = "carData")
require(replacer)
dir = system.file("extdata", package = "replacer")
replaceVals(dir, 'chile.csv', 'chile_nadup.csv', save = FALSE)

reading data from: C:/R/R-4.1.0/library/replacer/extdata ...

completed reading data ...

checking standard columns in lookup ...

starting replacements ...

found no request for 1:1 replacements ...

searching for 1:many replacements ...

found request for multiple duplicated value replacments: creating index ...

replacing multiple duplicated values ...

found request for multiple missing value replacements: replacing ...

helper function has completed!

$` updated_chile_using_chile_nadup`
              region population    sex age      education income statusquo       vote
   1:          North     200000   Male  65        Primary  35000   1.00820         Y
   2:          North     200000   Male  29 Post-secondary   7500  -1.29617         N
   3:          North     200000 Female  38        Primary  15000   1.23072         Y
   4:          North     200000 Female  49        Primary  35000  -1.03163         N
   5:          North     200000 Female  23      Secondary  35000  -1.10496         N
  ---                                                                                       
2696: Metro Santiago      15000   Male  42        Primary  15000  -1.26247         N
2697: Metro Santiago      15000 Female  28        Primary  15000   1.32950         Y
2698: Metro Santiago      15000 Female  44        Primary  75000   1.42045         Y
2699: Metro Santiago      15000   Male  21      Secondary  75000   0.18315 PREFERS NO ANSWER
2700: Metro Santiago      15000   Male  20 Post-secondary  35000   1.38179         Y

$` multiple_dups_repl_counts`
          vars oldVals          newVals education population region  sex
 1:  education       P          Primary      1107         NA     NA   NA
 2:  education      PS   Post-secondary       462         NA     NA   NA
 3:  education       S        Secondary      1120         NA     NA   NA
 4: population  175000           200000        NA        140     NA   NA
 5: population   25000            50000        NA        360     NA   NA
 6:     region       C          Central        NA         NA    600   NA
 7:     region       M   Metro Santiago        NA         NA    100   NA
 8:     region       N            North        NA         NA    322   NA
 9:     region       S            South        NA         NA    718   NA
10:     region      SA City of Santiago        NA         NA    960   NA
11:        sex       F           Female        NA         NA     NA 1379
12:        sex       M             Male        NA         NA     NA 1321

$` multiple_NAs_repl_counts`
    region population        sex        age  education     income  statusquo       vote 
         0          0          0          1         11         98         17        168 

This update replaced the abbreviations used in the source data with full names and complete words, as well as, updated the population totals on few regions with fictitious values. For exemplification, missing values in column vote were replaced by a short sentence using the word root “PRE” listed above.

The requests were processed through a lookup table without User-made index.

The first output block named “updated_chile_using_chile_nadup” displays the head and the tail of the updated data file.

To view the complete updated dataset on screen, type:

upData = replaceVals(dir, 'chile.csv', 'chile_nadup.csv', save = FALSE)[[1]]
View(upData)

Next block, named “multiple_dups_repl_counts,” displays a count of duplicated value updates, processed on each involved column, by value and replacement value. In this example, the left-hand side of this block contains the complete cases of lookup.

Finally, the third block named “multiple_NAs_repl_counts” shows counts of missing values replaced by generic values within respective involved columns. In other cases, unrequested replacements within any of the involved columns will be commented in the screen output (see Examples replaceVal()).

All block names change upon input and request type; reading these names shows what was processed and what was not. In case of error, the displayed messages/comments should hint of where in the process the error occurred.

The datasets in this package cover situations encountered in practice. They contain fictitious values inserted for exemplification purpose with the exception of “chile” dataset, a copy of the “Chile” dataset from package carData; some value replacements within the associated lookup files are also fictitious.

The batch-file example (function bReplace()) includes data and associated lookup files presented below. Duplicated values and counts of missing values are shown for all these datasets. Column name indexing appears when several distinctive subsets of duplicated values are present in respective column.

#> R version 4.1.0 (2021-05-18)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 19043)
#> 
#> Matrix products: default
#> 
#> locale:
#> [1] LC_COLLATE=C                          
#> [2] LC_CTYPE=English_United States.1252   
#> [3] LC_MONETARY=English_United States.1252
#> [4] LC_NUMERIC=C                          
#> [5] LC_TIME=English_United States.1252    
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] replacer_1.0.1    kableExtra_1.3.4  knitr_1.33        data.table_1.14.0
#> 
#> loaded via a namespace (and not attached):
#>  [1] rstudioapi_0.13   xml2_1.3.2        magrittr_2.0.1    munsell_0.5.0    
#>  [5] rvest_1.0.1       viridisLite_0.4.0 colorspace_2.0-2  R6_2.5.1         
#>  [9] rlang_0.4.11      fastmap_1.1.0     highr_0.9         stringr_1.4.0    
#> [13] httr_1.4.2        tools_4.1.0       webshot_0.5.2     xfun_0.25        
#> [17] jquerylib_0.1.4   systemfonts_1.0.2 htmltools_0.5.2   yaml_2.2.1       
#> [21] digest_0.6.27     lifecycle_1.0.0   formatR_1.11      sass_0.4.0       
#> [25] glue_1.4.2        evaluate_0.14     rmarkdown_2.10    stringi_1.7.4    
#> [29] compiler_4.1.0    bslib_0.2.5.1     scales_1.1.1      svglite_2.0.0    
#> [33] jsonlite_1.7.2