We can distinguish several types of deduplication when thinking about table - across one column we could use
unique()) to check (or remove) which records are duplicated; across one row we could iterate over rows and use the same
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.
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) <- data.frame(tel_1 = c(111, 222, 444, 555), initial_table 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")) <- dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5), table_deduplicated cols_expand = "name") table_deduplicated
We can see that:
enable_dropwas set (be default) to
TRUE- fifth column would contain only missing data (NA), so was removed
cols_dedupefirst 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
NAvalues were moved to right. If you want different result, like moving them to the left or even top or bottom, check out the
Of course, columns passed to
cols_expand are treat as separate columns, so data is not mixed:
<- dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5), table_deduplicated 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
<- dedupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5), table_deduplicated cols_expand = c("name", "nace"), max_new_cols = 2) table_deduplicated
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)
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
::setDTthreads() data.tablededupe_wide(initial_table, cols_dedupe = paste0("tel_", 1:5), cols_expand = "name")
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.
At first, we need to gather all columns which will be used for deduplication into one column, but keeping indexes to know which records belong to the same row (below only first 6 rows are shown):
Now we can group by value and summarize indexes (column V1):
|3, 5, 6, 9, 10, 11, 12, 4||3|
|7, 2, 4||2|
|8, 1, 10, 12||1|
We want also to have a main index which will be used as a replace. It will be the lowest index, so later we can easily keep original order of rows.
We can see that now not all indexes are connected as they should, e.g. index 7 is not connected with index 3, but should (because 7 and 3 are connected with 4).
To fix this, we will - step by step, for each index - replace rest indexes by main index. At first, let’s make a pairs: main index - one of the rests indexes:
We have also sorted them by descending order, at first by rest_indexes, then by main_index and removed duplicated pairs.
We can now group by rest_indexes and for each group replace the next rest_index by previous main_index, e.g. for 12 we will have:
After iterate over each index (each iterate needs to end by reorder the table - again we need to sort them by descending order, at first by rest_indexes, then by main_index), removing duplicated: pairs and more than one occurrence of rest_indexes, we will get:
The last thing to do is just to replace each rest_index by main_index in our main table (table passed to
dedupe_wide), starting from the top of our pairs.