1

I have a data.frame which has duplicate observations, how do I delete all the duplicated ones based on the first column (if their first data is the same, then delete these entries entirely)?

> a=c(1,4,5,5,6,6)
> b=c(2,5,7,4,4,2)
> c=c("a","b","c","a","b","c")
> test=data.frame(a,b,c)
> test
  a b c
1 1 2 a
2 4 5 b
3 5 7 c
4 5 4 a
5 6 4 b
6 6 2 c

I don't want to keep any of the duplicate rows so that my final output will be

  a b c
1 1 2 a
2 4 5 b

I've tried unique and duplicate function but they both keep the first duplicate rows (i.e., if there are 5 duplicate records then 4 of them will be deleted), like

  a b c
1 1 2 a
2 4 5 b
3 5 7 c
4 6 4 b

What should I do? Thanks!

Natalia
  • 369
  • 3
  • 15
  • 1
    I do not understand why your final output should be that. I do not understand why you consider other rows as duplicated – Pop Jul 22 '14 at 08:03
  • How is `6 4 b` a duplicate? Why do you want to delete it – Rentrop Jul 22 '14 at 08:05
  • @Pop I mean duplicated ones based on the first column. The 3rd and 4th rows have the same 5 as their [,1], and the 5th and 6th rows have the same 6 as their [,1]. – Natalia Jul 22 '14 at 08:05
  • @Floo0 Because the next row `6 2 c` also starts with 6. – Natalia Jul 22 '14 at 08:06

5 Answers5

3

You can use table() to get a frequency table of your column, then use the result to subset:

singletons <- names(which(table(test$a) == 1))
test[test$a %in% singletons, ]

  a b c
1 1 2 a
2 4 5 b
Andrie
  • 176,377
  • 47
  • 447
  • 496
2

Using dplyr

require(dplyr)
test <- test %>% group_by(a) %>% filter(n()==1)
test

  a b c
1 1 2 a
2 4 5 b
Rentrop
  • 20,979
  • 10
  • 72
  • 100
  • Thanks, it also works! Actually I know nothing about `dplyr` before. Andrie answered first so I accepted his answer. Thanks again! – Natalia Jul 22 '14 at 08:25
1

You first search for the first column values of the duplicated rows:

val <- test[duplicated(test[,1]),1]
[1] 5 6

Then you search for the rows in which these values can be found

rows <- test[,1] %in% test[duplicated(test[,1]),1]
[1] FALSE FALSE  TRUE  TRUE  TRUE  TRUE

Then you select all rows except these:

test[! rows,]
  a b c
1 1 2 a
2 4 5 b
Pop
  • 12,135
  • 5
  • 55
  • 68
0

Strange request, but if you want to remove all rows where there is a duplicate in any column while ignoring the other columns:

test[!duplicated(test$a) & ! duplicated(test$b) & ! duplicated(test$c),]
  a b c
1 1 2 a
2 4 5 b
3 5 7 c

But I don't see how '5 7 c' is a duplicate in your example.

JeremyS
  • 3,497
  • 1
  • 17
  • 19
0

Easy one step removal of duplicates:

my_df <- my_df[-which(duplicated(my_df)), ]
markhogue
  • 1,056
  • 1
  • 6
  • 16