Mark all duplicate lines in R, as in Stata

Question

Following my question here , I am trying to replicate in R the functionality of the Stata command duplicates tag, which allows me to mark all rows of a data set that are duplicated in terms of a given set of variables:

clear *
set obs 16
g f1 = _n
expand 104
bys f1: g f2 = _n
expand 2
bys f1 f2: g f3 = _n
expand 41
bys f1 f2 f3: g f4 = _n
des  // describe the dataset in memory

preserve
sample 10  // draw a 10% random sample
tempfile sampledata
save `sampledata', replace
restore

// append the duplicate rows to the data
append using `sampledata'
sort f1-f4

duplicates tag f1-f4, generate(dupvar)
browse if dupvar == 1  // check that all duplicate rows have been tagged

Edit

Here's what Stata produces (added to @Arun's query):

f1   f2   f3   f4   dupvar  
 1    1    1    1        0  
 1    1    1    2        0  
 1    1    1    3        1  
 1    1    1    3        1  
 1    1    1    4        0  
 1    1    1    5        0  
 1    1    1    6        0  
 1    1    1    7        0  
 1    1    1    8        1  
 1    1    1    8        1

Note that for (f1, f2, f3, f4) = (1, 1, 1, 3)there are two lines, and both are marked dupvar = 1. Similarly, for two lines that are duplicates of (f1, f2, f3, f4) =(1, 1, 1, 8).

R:

The basic function duplicatedmarks only the second duplicate. So, I wrote a function to replicate the Stata function in R using ddply.

# Values of (f1, f2, f3, f4) uniquely identify observations
dfUnique = expand.grid(f1 = factor(1:16),
            f2 = factor(1:41),
            f3 = factor(1:2),
            f4 = factor(1:104))

# sample some extra rows and rbind them
dfDup = rbind(dfUnique, dfUnique[sample(1:nrow(dfUnique), 100), ])

# dummy data 
dfDup$data = rnorm(nrow(dfDup))

# function: use ddply to tag all duplicate rows in the data
fnDupTag = function(dfX, indexVars) {
  dfDupTag = ddply(dfX, .variables = indexVars, .fun = function(x) {
    if(nrow(x) > 1) x$dup = 1 else x$dup = 0
    return(x)
  })
  return(dfDupTag)
}

# test the function
indexVars = paste0('f', 1:4, sep = '')
dfTemp = fnDupTag(dfDup, indexVars)

But as with the related question, performance is a huge problem. Another possible solution is

dfDup$dup = duplicated(dfDup[, indexVars]) | 
  duplicated(dfDup[, indexVars], fromLast = TRUE) 
dfDupSorted = with(dfDup, dfDup[order(eval(parse(text = indexVars))), ])

:
1. ddply ?
2. duplicated? ? 3. data.table? ?

Answer 1

. ( , ).

## Assuming DT is your data.table
DT[, dupvar := 1L*(.N > 1L), by=c(indexVars)]

:= dupvar (, , , ). .N - data.table, , (, f1,f2,f3,f4).

?data.table ( ), . .

, , indexVars, , .N > 1L, , TRUE. 1L, integer logical.

, setkey.

---

( v1.9.3 - ) setorder, data.table , . . ( , setkey ).

:

setorder(DT, f1, f2, f3, f4)
## or equivalently
setorderv(DT, c("f1", "f2", "f3", "f4"))

, DT[order(...)] , data.table . DT[order(...)] DT[forder(DT, ...)], , base order. , data.table , :

DT_sorted <- DT[order(f1, f2, f3, f4)] ## internally optimised for speed
                                       ## but still copies!

Answer 2

, . Stata R Stata duplicates. subset, merge all=TRUE, .

.

# my more Stata-ish approach
system.time({
    dupes <- dfDup[duplicated(dfDup[, 1:4]), 1:4]
    dupes$dup <- 1
    dfTemp2 <- merge(dfDup, dupes, all=TRUE)
    dfTemp2$dup <- ifelse(is.na(dfTemp2$dup), 0, dfTemp2$dup)
})

.

> system.time({
+ fnDupTag = function(dfX, indexVars) {
+   dfDupTag = ddply(dfX, .variables = indexVars, .fun = function(x) {
+     if(nrow(x) > 1) x .... [TRUNCATED] 
   user  system elapsed 
 118.75    0.22  120.11 

> # my more Stata-ish approach
> system.time({
+     dupes <- dfDup[duplicated(dfDup[, 1:4]), 1:4]
+     dupes$dup <- 1
+     dfTemp2 <- merge(dfDup,  .... [TRUNCATED] 
   user  system elapsed 
   0.63    0.00    0.63 

( all.equal).

> # compare
> dfTemp <- dfTemp[with(dfTemp, order(f1, f2, f3, f4, data)), ]

> dfTemp2 <- dfTemp2[with(dfTemp2, order(f1, f2, f3, f4, data)), ]
> all.equal(dfTemp, dfTemp2)
[1] "Attributes: < Component 2: Mean relative difference: 1.529748e-05 >"