CODEWITHSUNDEEP
rdataframe
CRISSI
CRISSI

Reputation: 1

Get duplicated rows in one column with different values in another column

I have this data frame:

A = data.frame(a = c("q", "e", "f", "q"), b = 1:4)

I want to get 2 rows (duplicated q in a, with different values, 1,4 in b)

Upvotes: 0

Views: 800

Answers (2)

moodymudskipper
moodymudskipper

Reputation: 47350

I think you want the function duplicated:

A[A$a %in% A$a[duplicated(A$a)],]
#   a b
# 1 q 1
# 4 q 4

This will give you any row containing a value of a found 2 or more times in the data.frame.

@Sotos proposed this, it's 20% faster on 100.000 rows:

A[duplicated(A$a)|duplicated(A$a, fromLast = TRUE),]

@mohamed-nidabdella proposed this solution with dplyr which is 2.5 times faster:

A %>% group_by(a) %>% filter(n()>1)

The benchmark:

A = data.frame(a =sample(letters,100000,replace=TRUE), b = 1:100000)
library(microbenchmark)

microbenchmark(
  a = A[A$a %in% A$a[duplicated(A$a)],],
  b =A[duplicated(A$a)|duplicated(A$a, fromLast = TRUE),],
  c = A[A$a %in% unique(A$a[duplicated(A$a)]),],
  d = A %>% group_by(a) %>% filter(n()>1),
  times=100)

# Unit: milliseconds
# expr       min        lq     mean   median        uq       max neval
# a 23.549739 24.732701 27.71688 27.70747 28.463392 115.58251   100
# b 20.703155 21.485692 24.66477 21.79380 24.790283 113.42992   100
# c 23.215580 24.166518 26.83627 24.99078 27.824526 113.99780   100
# d  8.647365  9.099141 10.17412  9.25546  9.548462  46.96581   100

Upvotes: 1

mlegge
mlegge

Reputation: 6913

library("data.table")
setDT(A)
rbind(A, A[a == "q", .(a, b = c(1, 4))])
#    a b
# 1: q 1
# 2: e 2
# 3: f 3
# 4: q 4
# 5: q 1
# 6: q 4

Upvotes: 0

Related Questions