Last updated: 2021-05-27

Checks: 7 0

Knit directory: fa_sim_cal/

This reproducible R Markdown analysis was created with workflowr (version 1.6.2). The Checks tab describes the reproducibility checks that were applied when the results were created. The Past versions tab lists the development history.

R Markdown file: up-to-date

Great! Since the R Markdown file has been committed to the Git repository, you know the exact version of the code that produced these results.

Environment: empty

Great job! The global environment was empty. Objects defined in the global environment can affect the analysis in your R Markdown file in unknown ways. For reproduciblity it’s best to always run the code in an empty environment.

Seed: set.seed(20201104)

The command set.seed(20201104) was run prior to running the code in the R Markdown file. Setting a seed ensures that any results that rely on randomness, e.g. subsampling or permutations, are reproducible.

Session information: recorded

Great job! Recording the operating system, R version, and package versions is critical for reproducibility.

Cache: none

Nice! There were no cached chunks for this analysis, so you can be confident that you successfully produced the results during this run.

File paths: relative

Great job! Using relative paths to the files within your workflowr project makes it easier to run your code on other machines.

Repository version: a6fb2e3

Great! You are using Git for version control. Tracking code development and connecting the code version to the results is critical for reproducibility.

The results in this page were generated with repository version a6fb2e3. See the Past versions tab to see a history of the changes made to the R Markdown and HTML files.

Note that you need to be careful to ensure that all relevant files for the analysis have been committed to Git prior to generating the results (you can use wflow_publish or wflow_git_commit). workflowr only checks the R Markdown file, but you know if there are other scripts or data files that it depends on. Below is the status of the Git repository when the results were generated:


Ignored files:
    Ignored:    .Rhistory
    Ignored:    .Rproj.user/
    Ignored:    .tresorit/
    Ignored:    _targets/
    Ignored:    data/VR_20051125.txt.xz
    Ignored:    data/VR_Snapshot_20081104.txt.xz
    Ignored:    renv/library/
    Ignored:    renv/local/
    Ignored:    renv/staging/

Note that any generated files, e.g. HTML, png, CSS, etc., are not included in this status report because it is ok for generated content to have uncommitted changes.

These are the previous versions of the repository in which changes were made to the R Markdown (analysis/m_01_2_exclusions.Rmd) and HTML (docs/m_01_2_exclusions.html) files. If you’ve configured a remote Git repository (see ?wflow_git_remote), click on the hyperlinks in the table below to view the files as they were in that past version.

File	Version	Author	Date	Message
html	ab90fe6	Ross Gayler	2021-05-18	WIP
Rmd	24d95c0	Ross Gayler	2021-05-15	WIP
html	24d95c0	Ross Gayler	2021-05-15	WIP
Rmd	d7b5c39	Ross Gayler	2021-05-15	WIP
Rmd	1df745a	Ross Gayler	2021-05-14	WIP
Rmd	e1b609b	Ross Gayler	2021-05-14	WIP
Rmd	4109078	Ross Gayler	2021-05-13	WIP
html	4109078	Ross Gayler	2021-05-13	WIP
Rmd	411de1e	Ross Gayler	2021-04-04	WIP
html	411de1e	Ross Gayler	2021-04-04	WIP
Rmd	ebd787e	Ross Gayler	2021-03-28	WIP
html	ebd787e	Ross Gayler	2021-03-28	WIP

# NOTE this notebook can be run manually or automatically by {targets}
# So load the packages required by this notebook here
# rather than relying on _targets.R to load them.

# Set up the project environment, because {workflowr} knits each Rmd file 
# in a new R session, and doesn't execute the project .Rprofile

library(targets) # access data from the targets cache

library(tictoc) # capture execution time
library(here) # construct file paths relative to project root

here() starts at /home/ross/RG/projects/academic/entity_resolution/fa_sim_cal_TOP/fa_sim_cal

library(fs) # file system operations
library(dplyr) # data wrangling


Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(gt) # table formatting
library(stringr) # string matching
library(vroom) # fast reading of delimited text files

# start the execution time clock
tictoc::tic("Computation time (excl. render)")

# Get the path to the raw entity data file
# This is a target managed by {targets}
f_entity_raw_tsv <- tar_read(c_raw_entity_data_file)

1 Introduction

These meta notebooks document the development of functions that will be applied in the core pipeline.

The aim of the m_01 set of meta notebooks is to work out how to read the raw entity data, drop excluded cases, discard irrelevant variables, apply any cleaning, and construct standardised names. This does not include construction of any modelling features. To be clear, the target (c_raw_entity_data) corresponding to the objective of this set of notebooks is the cleaned and standardised raw data, before constructing any modelling features.

This notebook documents the process of excluding data rows that are not useful for this project.

The subsequent notebooks in this set will develop the other functions needed to generate the cleaned and standardised data.

1.1 Exclusions

There are four variables dealing with voter status. Preliminary examination of these variables shows that some records correspond to people who have been removed from the electoral roll. This project focuses on ambiguity arising from the fact that some names are common Therefore, we want the entity data to be as accurate as possible and free of duplicate records so that we don’t introduce ambiguity because of data quality issues.

Speaking of data quality issues, it is my experience that large databases often contain test records.

I will exclude all the data rows that have any of these data quality issues. I will do this early in the pipeline to minimise the number of records processed and to avoid including these records in the subsequent quality analyses.

2 Read entity data

Read the raw entity data file using the previously defined core pipeline function, raw_entity_data_read().

# Show the data file name
fs::path_file(f_entity_raw_tsv)

[1] "VR_20051125.txt.xz"

# Read the raw entity data
d <- raw_entity_data_read(f_entity_raw_tsv)

3 Voter status variables

Check the internal consistency of the voter status variables.

The data dictionary describes these as two pairs of variables, where each pair consists of a code variable and a label variable. I expect the code and label values to be in a 1:1 relationship.

3.1 Status

status_cd - Status code for voter registration
voter_status_desc - Status code descriptions

d %>% 
  dplyr::distinct(status_cd, voter_status_desc) %>% 
  dplyr::arrange(status_cd, voter_status_desc) %>% 
  gt::gt() %>% 
  gt::opt_row_striping() %>% 
  gt::tab_style(style = gt::cell_text(weight = "bold"), locations = gt::cells_column_labels()) %>% 
  gt::fmt_missing(columns = everything(), missing_text = "<NA>")

status_cd	voter_status_desc
A	ACTIVE
D	DENIED
I	INACTIVE
R	REMOVED
S	TEMPORARY REGISTRATION
<NA>	<NA>

<NA> is the missing value indicator.
The code and label values are in a 1:1 relationship. Use the label variable (voter_status_desc) because it is more explicitly meaningful.

Look at the distribution of values across the data.

d %>% 
  dplyr::count(voter_status_desc) %>% 
  gt::gt() %>% 
  gt::opt_row_striping() %>% 
  gt::tab_style(style = cell_text(weight = "bold"), locations = cells_column_labels()) %>% 
  gt::fmt_missing(columns = everything(), missing_text = "<NA>") %>% 
  gt::fmt_number(columns = n, decimals = 0)

voter_status_desc	n
ACTIVE	4,914,521
DENIED	41,348
INACTIVE	495,603
REMOVED	2,546,485
TEMPORARY REGISTRATION	5,334
<NA>	2

~60% of records are ACTIVE. All the other values have negative connotations for expected data quality.

3.2 Reason

reason_cd - Reason code for voter registration status
voter_status_reason_desc - Reason code description

d %>% 
  dplyr::distinct(reason_cd, voter_status_reason_desc) %>% 
  dplyr::arrange(reason_cd, voter_status_reason_desc) %>% 
  gt::gt() %>% 
  gt::opt_row_striping() %>% 
  gt::tab_style(style = gt::cell_text(weight = "bold"), locations = gt::cells_column_labels()) %>% 
  gt::fmt_missing(columns = everything(), missing_text = "<NA>")

reason_cd	voter_status_reason_desc
A1	UNVERIFIED
A2	CONFIRMATION PENDING
AA	ARMED FORCES
AL	LEGACY DATA
AN	UNVERIFIED NEW
AP	VERIFICATION PENDING
AV	VERIFIED
DI	UNAVAILABLE ESSENTIAL INFORMATION
DU	VERIFICATION RETURNED UNDELIVERABLE
IL	LEGACY - CONVERSION
IN	CONFIRMATION NOT RETURNED
IU	CONFIRMATION RETURNED UNDELIVERABLE
R2	DUPLICATE
RA	ADMINISTRATIVE
RC	REMOVED DUE TO SUSTAINED CHALLENGE
RD	DECEASED
RF	FELONY CONVICTION
RL	MOVED FROM COUNTY
RM	REMOVED AFTER 2 FED GENERAL ELECTIONS IN INACTIVE STATUS
RP	REMOVED UNDER OLD PURGE LAW
RQ	REQUEST FROM VOTER
RS	MOVED FROM STATE
RT	TEMPORARY REGISTRANT
SM	MILITARY
SO	OVERSEAS CITIZEN
SP	PREVIOUSLY REGISTERED
<NA>	<NA>

<NA> is the missing value indicator.
The code and label values are in a 1:1 relationship. Use the label variable (voter_status_reason_desc) because it is more explicitly meaningful.

Look at the distribution of values.

d %>% 
  dplyr::count(voter_status_reason_desc) %>% 
  gt::gt() %>% 
  gt::opt_row_striping() %>% 
  gt::tab_style(style = cell_text(weight = "bold"), locations = cells_column_labels()) %>% 
  gt::fmt_missing(columns = everything(), missing_text = "<NA>") %>% 
  gt::fmt_number(columns = n, decimals = 0)

voter_status_reason_desc	n
ADMINISTRATIVE	59,008
ARMED FORCES	50
CONFIRMATION NOT RETURNED	181,320
CONFIRMATION PENDING	71,296
CONFIRMATION RETURNED UNDELIVERABLE	303,197
DECEASED	443,486
DUPLICATE	78,951
FELONY CONVICTION	63,501
LEGACY - CONVERSION	10,585
LEGACY DATA	523,899
MILITARY	3,975
MOVED FROM COUNTY	888,056
MOVED FROM STATE	89,049
OVERSEAS CITIZEN	1,307
PREVIOUSLY REGISTERED	51
REMOVED AFTER 2 FED GENERAL ELECTIONS IN INACTIVE STATUS	551,073
REMOVED DUE TO SUSTAINED CHALLENGE	662
REMOVED UNDER OLD PURGE LAW	367,511
REQUEST FROM VOTER	4,194
TEMPORARY REGISTRANT	729
UNAVAILABLE ESSENTIAL INFORMATION	6,991
UNVERIFIED	13,737
UNVERIFIED NEW	7,517
VERIFICATION PENDING	198,333
VERIFICATION RETURNED UNDELIVERABLE	34,357
VERIFIED	4,100,220
<NA>	238

<NA> is the missing value indicator.
~50% of records are “VERIFIED”. That value seems to have the most positive connotations for expected data quality.

3.3 Status \(\times\) Reason

Look at the distribution of combinations of the two variables.

d %>% 
  dplyr::count(voter_status_desc, voter_status_reason_desc) %>% 
  gt::gt() %>% 
  gt::opt_row_striping() %>% 
  gt::tab_style(style = cell_text(weight = "bold"), locations = cells_column_labels()) %>% 
  gt::fmt_missing(columns = everything(), missing_text = "<NA>") %>% 
  gt::fmt_number(columns = n, decimals = 0)

voter_status_desc	voter_status_reason_desc	n
ACTIVE	ARMED FORCES	50
ACTIVE	CONFIRMATION PENDING	71,295
ACTIVE	LEGACY - CONVERSION	1
ACTIVE	LEGACY DATA	523,897
ACTIVE	UNVERIFIED	13,731
ACTIVE	UNVERIFIED NEW	7,516
ACTIVE	VERIFICATION PENDING	198,331
ACTIVE	VERIFIED	4,099,700
DENIED	UNAVAILABLE ESSENTIAL INFORMATION	6,990
DENIED	VERIFICATION RETURNED UNDELIVERABLE	34,357
DENIED	VERIFIED	1
INACTIVE	CONFIRMATION NOT RETURNED	181,320
INACTIVE	CONFIRMATION RETURNED UNDELIVERABLE	303,197
INACTIVE	LEGACY - CONVERSION	10,584
INACTIVE	LEGACY DATA	2
INACTIVE	VERIFICATION PENDING	1
INACTIVE	VERIFIED	499
REMOVED	ADMINISTRATIVE	59,008
REMOVED	CONFIRMATION PENDING	1
REMOVED	DECEASED	443,486
REMOVED	DUPLICATE	78,951
REMOVED	FELONY CONVICTION	63,501
REMOVED	MOVED FROM COUNTY	888,055
REMOVED	MOVED FROM STATE	89,049
REMOVED	PREVIOUSLY REGISTERED	1
REMOVED	REMOVED AFTER 2 FED GENERAL ELECTIONS IN INACTIVE STATUS	551,072
REMOVED	REMOVED DUE TO SUSTAINED CHALLENGE	662
REMOVED	REMOVED UNDER OLD PURGE LAW	367,511
REMOVED	REQUEST FROM VOTER	4,194
REMOVED	TEMPORARY REGISTRANT	729
REMOVED	UNAVAILABLE ESSENTIAL INFORMATION	1
REMOVED	UNVERIFIED	4
REMOVED	UNVERIFIED NEW	1
REMOVED	VERIFICATION PENDING	1
REMOVED	VERIFIED	20
REMOVED	<NA>	238
TEMPORARY REGISTRATION	MILITARY	3,975
TEMPORARY REGISTRATION	OVERSEAS CITIZEN	1,307
TEMPORARY REGISTRATION	PREVIOUSLY REGISTERED	50
TEMPORARY REGISTRATION	UNVERIFIED	2
<NA>	MOVED FROM COUNTY	1
<NA>	REMOVED AFTER 2 FED GENERAL ELECTIONS IN INACTIVE STATUS	1

~50% are “ACTIVE” and “VERIFIED”

On a common-sense interpretation of the labels, these are the records that have survived the registration checking process, so are most likely free of errors and duplicates.

Write a function to filter the records to keep only those that are “ACTIVE” and “VERIFIED”.

# Function to exclude records based on voter status
raw_entity_data_excl_status <- function(
  d # data frame - raw entity data
) {
  d %>%
    dplyr::filter(
      voter_status_desc == "ACTIVE" & voter_status_reason_desc == "VERIFIED"
    )
}

Apply the filter before moving on to the next exclusion condition and track the number of rows before and after the filter.

# number of rows before filtering
nrow(d)

[1] 8003293

d <- d %>% raw_entity_data_excl_status()

# number of rows after filtering
nrow(d)

[1] 4099700

Result as expected

4 Test cases

Initial poking through the data showed the words DUMMY, PRACTICE, TEST, THIS, THISIS, and THISISA as person names appeared to be associated with records that were subjectively judged as being likely to be test cases. Look for any records that contain those strings as a word (i.e. enclosed by word boundaries) in the person name columns.

# define the target regular expression
target <- regex(
  "\\bDUMMY\\b|\\bPRACTICE\\b|\\bTEST\\b|\\bTHIS\\b|\\bTHISIS\\b|\\bTHISISA\\b", 
  ignore_case = TRUE
)

d %>% 
  dplyr::select(
    last_name, first_name, midl_name,
    street_name, street_type_cd, res_city_desc,
    sex, phone_num
  ) %>% 
  dplyr::filter(
    stringr::str_detect(last_name, target)|
      stringr::str_detect(first_name, target) |
      stringr::str_detect(midl_name, target)
  ) %>% 
  dplyr::arrange(last_name, street_name) %>% 
  gt::gt() %>% 
  gt::opt_row_striping() %>% 
  gt::tab_style(style = cell_text(weight = "bold"), locations = cells_column_labels()) %>% 
  gt::fmt_missing(columns = everything(), missing_text = "<NA>") %>% 
  gt::tab_style(
    style = gt::cell_fill(color = "yellow"),
    locations = list(
      gt::cells_body(columns = last_name, rows = stringr::str_detect(last_name, target)),
      gt::cells_body(columns = first_name, rows = stringr::str_detect(first_name, target)),
      gt::cells_body(columns = midl_name, rows = stringr::str_detect(midl_name, target))
    )
  )

last_name	first_name	midl_name	street_name	street_type_cd	res_city_desc	sex	phone_num
TEST	KAY	ANN	ELM	ST	WELDON	FEMALE	<NA>
TEST	DAVID	W	GREENWAY	AVE	CHARLOTTE	MALE	<NA>
TEST	KATHERINE	COOKE	GREENWAY	AVE	CHARLOTTE	FEMALE	<NA>
TEST	FREDERICK	HAROLD	HENDERSONVILLE	RD	ASHEVILLE	MALE	<NA>
TEST	DANIEL	W	HICKORY KNOLL	CT	GREENSBORO	MALE	<NA>
TEST	THIS	<NA>	HIGH POINT	RD	JAMESTOWN	MALE	<NA>
TEST	GEORGE	A	ROSWELL	AVE	CHARLOTTE	MALE	<NA>
TEST	DORIS	L	ROSWELL	AVE	CHARLOTTE	FEMALE	<NA>
TEST	KENNETH	FARREL	WAVERLY	DR	CLAYTON	MALE	<NA>
THIS	KELLY	RENEE	HATTIE HILL	RD	VILAS	FEMALE	2627458

Online search finds that “Test” is a known last name in the USA.
The name “TEST, THIS” looks like a test case (with ACTIVE VERIFIED status).

Write a function to filter the records to exclude those with name “TEST, THIS”.

There are likely to be many other test cases not detected by this filter. However, they are probably only a tiny fraction of the records - so it’s not a big problem for this project if they are missed. If I find any other test cases later I will come back here and revise the exclusion criteria.

# Function to exclude test records based on names
raw_entity_data_excl_test <- function(
  d # data frame - raw entity data
) {
  d %>%
    dplyr::filter(
      ! (
        stringr::str_detect(last_name, regex("\\bTEST\\b", ignore_case = TRUE)) &
          stringr::str_detect(first_name, regex("\\bTHIS\\b", ignore_case = TRUE))
      )
    )
}

Apply the filter and track the number of rows before and after the filter.

# number of rows before filtering
nrow(d)

[1] 4099700

d <- d %>% raw_entity_data_excl_test()

# number of rows after filtering
nrow(d)

[1] 4099699

Behaviour as expected

Timing

Computation time (excl. render): 62.505 sec elapsed

sessionInfo()

R version 4.1.0 (2021-05-18)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 20.10

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.9.0
LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.9.0

locale:
 [1] LC_CTYPE=en_AU.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_AU.UTF-8        LC_COLLATE=en_AU.UTF-8    
 [5] LC_MONETARY=en_AU.UTF-8    LC_MESSAGES=en_AU.UTF-8   
 [7] LC_PAPER=en_AU.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_AU.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices datasets  utils     methods   base     

other attached packages:
[1] vroom_1.4.0   stringr_1.4.0 gt_0.3.0      dplyr_1.0.6   fs_1.5.0     
[6] here_1.0.1    tictoc_1.0.1  targets_0.4.2

loaded via a namespace (and not attached):
 [1] tidyselect_1.1.1  xfun_0.23         bslib_0.2.5       purrr_0.3.4      
 [5] colorspace_2.0-1  vctrs_0.3.8       generics_0.1.0    htmltools_0.5.1.1
 [9] yaml_2.2.1        utf8_1.2.1        rlang_0.4.11      jquerylib_0.1.4  
[13] later_1.2.0       pillar_1.6.1      glue_1.4.2        withr_2.4.2      
[17] bit64_4.0.5       lifecycle_1.0.0   munsell_0.5.0     gtable_0.3.0     
[21] workflowr_1.6.2   codetools_0.2-18  evaluate_0.14     knitr_1.33       
[25] callr_3.7.0       httpuv_1.6.1      ps_1.6.0          parallel_4.1.0   
[29] fansi_0.4.2       Rcpp_1.0.6        backports_1.2.1   checkmate_2.0.0  
[33] renv_0.13.2       promises_1.2.0.1  scales_1.1.1      jsonlite_1.7.2   
[37] bit_4.0.4         ggplot2_3.3.3     digest_0.6.27     stringi_1.6.2    
[41] bookdown_0.22     processx_3.5.2    rprojroot_2.0.2   grid_4.1.0       
[45] cli_2.5.0         tools_4.1.0       magrittr_2.0.1    sass_0.4.0       
[49] tibble_3.1.2      crayon_1.4.1      whisker_0.4       pkgconfig_2.0.3  
[53] ellipsis_0.3.2    data.table_1.14.0 rmarkdown_2.8     R6_2.5.0         
[57] igraph_1.2.6      git2r_0.28.0      compiler_4.1.0

[meta] Apply row exclusions

m_01_2_exclusions

Ross Gayler

2021-05-14