Usage and Algorithm Explained

Introduction

We can distinguish several types of deduplication when thinking about table - across one column we could use duplicated() (or unique()) to check (or remove) which records are duplicated; across one row we could iterate over rows and use the same duplicated() (or unique()) or gather multiple columns into one (by changing table from wide format to long) and then perform deduplication across one, long column.

Finally, we can think of deduplication as a process of finding duplicated data across different columns and rows, using transitive relation:

In the example above, we could treat records in rows 1, 2 and 3 as the same record. Why? Because row 1 and 2 has one the same phone number (222), row 2 and 3 has one the same phone number (666) and, because row 1 is duplicated with row 2, row 2 is duplicated with row 3, then row 1 is duplicated with row 3. User can see the result of this specific deduplication by calling (after installation of package):

example("dedupe_wide", "dedupewider") # look at the first example

This method was used in CATI surveys (especially on businesses databases) to minimize the chance that interviewers will call independently the same respondent and thus irritate her or him. It is a chance that the same, suitable person to participate in the survey, works in more than one company and that these companies exist as a separate records in the database (sometimes just as a separate companies, sometimes as a branches). When trying to find participant in company X, interviewer can be switched to company C to speak with employee E and the second interviewer, when calling company Y, can also be switched to company C to speak with employee E. If some data in database (like phone numbers) can be use to collapse companies X, Y and C into one record, the chance for this inconvenience will be much lower.

Usage

To attach the package, use: library(dedupewider). We will show the usage of dedupe_wide() function using the table presented below:

We already know that rows 1, 2, 3 will be collapsed into one. Let’s see the default behavior of function:

library(dedupewider)

initial_table <- data.frame(tel_1 = c(111, 222, 444, 555),
                            tel_2 = c(222, 666, 666, 555),
                            tel_3 = c(NA, NA, NA, 555),
                            tel_4 = c(NA, NA, NA, 555),
                            tel_5 = c(NA, NA, NA, 555),
                            name = paste0("name", 1:4),
                            nace = c("01.19", "01.64", "55.90", "09.10"))

table_deduplicated <- dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5),
                                  cols_expand = "name")
table_deduplicated

We can see that:

• columns used to dedupe are not just renamed, but also the number of columns is lower than initially. That’s because argument enable_drop was set (be default) to TRUE - fifth column would contain only missing data (NA), so was removed
• deduplication was performed also on the same row, so for company “name4” only one phone number was left
• all unique information from column “name” was preserved and these columns were renamed
• for the rest columns (“nace”) just one code was preserved - it is always the data from the row which is closest to the beginning of the table
• order of rows and columns was preserved, however, it is important to note that for cols_dedupe first element of names passed to it will be used as a base for new names of columns and to determine the place for these columns
• NA values were moved to right. If you want different result, like moving them to the left or even top or bottom, check out the na_move function.

Of course, columns passed to cols_expand are treat as separate columns, so data is not mixed:

table_deduplicated <- dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5),
                                  cols_expand = c("name", "nace"))
table_deduplicated

Sometimes user can think that there is a fixed number of new columns which will be enough (for example no more than 2 phone numbers to company will be needed). Argument max_new_cols can be use to set the number of max. new columns to create and it will be used to limit number of columns from cols_dedupe and cols_expand:

table_deduplicated <- dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5),
                                  cols_expand = c("name", "nace"),
                                  max_new_cols = 2)
table_deduplicated

Functions duplicated() or unique() can treat missing data as duplicated data, e.g.

duplicated(c(NA, NA)) # returns FALSE TRUE
unique(c(NA, NA)) # returns NA (vector of length 1)

But dedupe_wide cannot treat missing data as duplicated data, e.g. in case of this table:

Rows 1 and 2 will be collapsed to one, but the rest of rows remain unchanged:

It is also important to note that this function was implemented using data.table and thus parallel computing is possible. To enable this, user can just call data.table::setDTthreads() before using dedupe_wide():

data.table::setDTthreads()
dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5),
                                  cols_expand = "name")

Algorithm

The aim of this section is to show algorithm used to find duplicated records - this can be use for cross-checking or can be a inspiration to implement more efficient solution if needed. Table below will be our starting point:

We can split the algorithm into two main steps - finding duplicates and cascade them.