How to check whether two rows have an equal value pr id for a dataframe in R

user_12148688 0
Viewed 1994

I have a dataframe, and I would like to check whether there are duplicated values for a specific column for each id. How can I identify duplicated rows, that are not only duplicated on id, but also the 'value' column?

df <- data.frame('id' = c('1','1', '2', '3', '3', '4','5','5','5'), "value" = c('apple','apple', 'orange', 'banana', 'banana',  'apple','orange','banana','orange'), "shop" = c('supermarket','café', 'café', 'supermarket', 'café',  'supermarket','supermarket','supermarket','café'))

My approach has been

#extract duplicates in the dataframe on value
df_dup <- df[duplicated(df$value), ]
#from this df, extract duplicates on id
df_dup1 <- df_dup[duplicated(df_dup$id), ]

However this method does not work. The output I am looking for is a reduced dataframe where only id's that have a duplicated value on the 'value'-column are kept together with the other variables in the dataframe:

df_exp <- data.frame('id' = c('1','1','3', '3', '5','5'), "value" = c('apple','apple', 'banana', 'banana', 'orange','orange'), "shop" = c('supermarket', 'café', 'supermarket', 'café' ,'supermarket','café'))

Thanks in advance!

subset dataframe duplicates r
3 Answers

Here is a base R option using subset + ave

subset(df,ave(1:nrow(df),id,value, FUN = length)>1)

which gives

  id  value        shop
1  1  apple supermarket
2  1  apple        café
4  3 banana supermarket
5  3 banana        café
7  5 orange supermarket
9  5 orange        café
user_121587570

We could also use duplicated:

df[duplicated(df[,1:2])|duplicated(df[,1:2], fromLast =TRUE),]
user_83802720

You can select groups (id, value) where number of rows are greater than 1.

Using dplyr, you can do :

library(dplyr)
df %>% group_by(id, value) %>% filter(n() > 1)

# id    value  shop       
#  <chr> <chr>  <chr>      
#1 1     apple  supermarket
#2 1     apple  café       
#3 3     banana supermarket
#4 3     banana café       
#5 5     orange supermarket
#6 5     orange café

Or with data.table :

library(data.table)
setDT(df)[, .SD[.N > 1], .(id, value)]
user_39629140
Related