CODEWITHSUNDEEP
r
Cyrus Mohammadian
Cyrus Mohammadian

Reputation: 5193

How to identify partial duplicates of rows in R

I would like to identify "partial" matches of rows in a dataframe. Specifically, I want to create a new column with a value of 1 if a particular row in a dataframe has duplicate row somewhere else in the dataframe based on a match between a subset of columns. An added complexity is that one of the columns in the dataframe is numeric and I want to match if the absolute values match. Here is example data followed by an example of my desired output.

name<-c("Richard Nixon", "Bill Clinton", "George Bush", "Richard Nixon")
state<-c("California", "Indiana", "Florida", "California")
num<-c("-258", "123", "42", "258")
date<-c("day 2", "day 15", "day 3","day 45")
(df<-as.data.frame(cbind(name,state,num, date)))
           name      state  num   date
1 Richard Nixon California -258  day 2
2  Bill Clinton    Indiana  123 day 15
3   George Bush    Florida   42  day 3
4 Richard Nixon California  258 day 45

What I'm hoping to acquire is the following dataframe:

           name      state  num   date newcol
1 Richard Nixon California -258  day 2 1
2  Bill Clinton    Indiana  123 day 15 0
3   George Bush    Florida   42  day 3 0
4 Richard Nixon California  258 day 45 1

Notice that rows 1 and 2 match along the name and state column and their absolute values match in the num column, resulting in a 1 in the added newcol column for both those rows, while the remaining rows have no such match and thus are valued at 0.

I tried the following but to no avail:

df$num<-as.numeric(df$num)
which(duplicated(df[c('name', 'state',abs('num'))]),)

Error in abs("num") : non-numeric argument to mathematical function

Of course that does not work because of the abs()

Upvotes: 0

Views: 1177

Answers (1)

dww
dww

Reputation: 31452

You can use

df$absnum = abs(as.numeric(as.character(df$num)))
df$newcol = duplicated(df[,c('name','state', 'absnum')]) | 
  duplicated(df[,c('name','state', 'absnum')], fromLast = T)

#            name      state  num   date absnum newcol
# 1 Richard Nixon California -258  day 2    258   TRUE
# 2  Bill Clinton    Indiana  123 day 15    123  FALSE
# 3   George Bush    Florida   42  day 3     42  FALSE
# 4 Richard Nixon California  258 day 45    258   TRUE

If you really need newcol to be 1 or 0, then you can convert it to integer using as.integer. But in most cases it is best to keep boolean flags as logical types.

Upvotes: 1

Related Questions