How to retain one of the duplicated rows randomly in r (not the first duplicated row)

user_12974405 0
Viewed 1072

I have a large dataset with many duplicated rows. I want to remove duplicated rows and retain only unique rows. But I don't want to retain the first duplicated row, which is the case in dplyr::distinct(). What I want is for example if there are three rows with similar answer, I want r to retain one of them randomly (not always the first duplicated row).

For example if my data frame looks like this:

ID<-c(1:10)
age<-c(18,18,19,19,19,20,20,20,21,21)
Answer1<-c("yes", "yes", "no", "no", "no", "no", "no", "yes", "no", "no")
Answer2<-c("yes", "yes", "no", "no", "no", "no", "no", "yes", "yes", "no")

df<-data.frame(ID, age, Answer1, Answer2)
print(df)

ID age Answer1 Answer2
1  18     yes     yes
2  18     yes     yes
3  19      no      no
4  19      no      no
5  19      no      no
6  20      no      no
7  20      no      no
8  20     yes     yes
9  21      no     yes
10 21      no      no

Those individuals who are at the same age plus whose first and second answer match, I consider them as duplicates and I want to randomly retain one of them (and delete the rest). I tried the following dplyr command. The problem is the command retains the first duplicated row.

df.new<-df%>%
  group_by(age) %>%
  distinct(Answer1, Answer2,.keep_all = TRUE)

Is there a simple command to randomly retain one of the duplicated rows?

If that is not possible do you know how I can create additional column that indicate if individual is unique or duplicate (something like this)

   ID age Answer1 Answer2    Status
1  18     yes     yes        duplicate
2  18     yes     yes        duplicate
3  19      no      no        duplicate
4  19      no      no        duplicate
5  19      no      no        duplicate
6  20      no      no        duplicate
7  20      no      no        duplicate
8  20     yes     yes           unique
9  21      no     yes           unique
10 21      no      no           unique

Note that here an individual get duplicate status if there is at least one individual with similar age, Answer1 and Answer2 in the dataset.

dplyr dataframe duplicates aggregate r
2 Answers

Without worrying about the duplicates you could select one random row for each unique value of age, Answer1 and Answer2.

library(dplyr)
df %>% group_by(age, Answer1, Answer2) %>% slice_sample(n = 1)
#With sample_n
#df %>% group_by(age, Answer1, Answer2) %>% sample_n(1)

If there is only 1 row in a group this will by default select that row.

user_39629140

Using data.table

library(data.table)
setDT(df)[, .SD[sample(seq_len(.N), 1)], .(age, Answer1, Answer2)]
user_37322710
Related