checking duplicate in rows for data frame

user_16577173 0
Viewed 47

In my data frame i have more than 1000 columns and I am validating duplicate entries in data frame.

so in actual I have columns from ID1- ID 1000 and I want to check if there is any duplicate entry in any row from ID1-ID100 then I want to mutate new column with text "duplicate entry" and want to add that column before "Name" column. i am trying to create a function for this.

Below dataframe is sample dataframe

df <- data.frame(ID =c("DEV2962","KTN2252","ANA2719","ITI2624","DEV2698","HRT2921",NA,"KTN2624","ANA2548","ITI2535","DEV2732","HRT2837","ERV2951","KTN2542","ANA2813","ITI2210"),
                 city=c("DEL","mum","DEL","MUM","DEL","del","MUM","DEL","del","MUM","mum","mum","mum","mum","DEL","DEL"),
                 Name= c("dev,akash","singh,Ajay","abbas,salman","lal,ram","singh,nkunj","garg,prabal","ali,sanu","singh,kunal","tomar,lakhan","thakur,praveen","ali,sarman","khan,zuber","singh,giriraj","sharma,lokesh","pawar,pooja","sharma,nikita"),
                 ID2 = c("A0011","A0011","A0011","A0011","A0011","A0012","AB702","AB328","AC728","AC314","AC742","AC919","AC062","AD712","AD021","AD920"),
                 ID3 = c("A0012","A0012","A0012","A0012","A0012","A0013","AB712","AB712","AB702","AB328","AC314","AC728","AB702","AB712","AC742","AC919"),
                 ID4 = c(NA,"A0013","A0013","A0013","AB982","AB982",NA,"AB982","A0013","A0012","A0012","A0012","A0012",NA,"A0013","A0012"),
                 ID5 =c(NA,"A0012","AB012","AB012",NA,"AB702",NA,"A0013",NA,"A0011","A0011",NA,"A0011",NA,NA,NA),
                 ID6 = c(NA,NA,NA,"AB982",NA,NA,NA,"A0012",NA,NA,NA,NA,NA,NA,NA,NA),
                 ID7 = c(NA,NA,NA,NA,NA,NA,NA,"A0011",NA,NA,NA,NA,NA,NA,NA,NA),
                 ID8 =c(NA,NA,NA,NA,NA,NA,NA,"AB982",NA,NA,NA,NA,NA,NA,NA,NA))



hasDups <- function(df){
  
  checkDup <- function(x){
    
    ifelse(max(table(x)) > 1, "duplicate available", "")
    
  }
  
  df$hasDup <- apply(df, 1, checkDup)
  
  col_nams <- c(c("ID", "city", "Name", "hasDup"), 
                setdiff(names(df), c("ID", "city", "Name", "hasDup")))
  df <- df[, col_nams]
  
  return(df)
  
}

hasDups(df)
r
2 Answers

Have you considered using R's inbuild duplicated()?

You can use it in the entire dataset (in which case a row is only considered a duplicate if there is another row before it with the same value in all columns), or column-by-column. It returns a boolean with TRUE when it is a duplicate and FALSE when it isn't.

I will assume you want to test the entire dataset so something as simple as:

df$isduplicated = ifelse(duplicated(df),"Duplicated entry","")

Will create a column that you want. You can them reorder the data.frame columns at your own leisure.

user_58508920

You can choose specific columns with grep.

hasDups <- function(df){
  cols <- grep('ID\\d+', names(df), value= TRUE)
  checkDup <- function(x){
    if(max(table(x)) > 1) "duplicate entry" else ""
  }
  df$hasDup <- apply(df[cols], 1, checkDup)
  col_nams <- c(setdiff(names(df), c("hasDup", "Name", cols)), "hasDup", "Name", cols)
  df <- df[, col_nams]
  return(df)
}

hasDups(df)

#        ID city          hasDup           Name   ID2   ID3   ID4   ID5   ID6   ID7   ID8
#1  DEV2962  DEL                      dev,akash A0011 A0012  <NA>  <NA>  <NA>  <NA>  <NA>
#2  KTN2252  mum duplicate entry     singh,Ajay A0011 A0012 A0013 A0012  <NA>  <NA>  <NA>
#3  ANA2719  DEL                   abbas,salman A0011 A0012 A0013 AB012  <NA>  <NA>  <NA>
#4  ITI2624  MUM                        lal,ram A0011 A0012 A0013 AB012 AB982  <NA>  <NA>
#5  DEV2698  DEL                    singh,nkunj A0011 A0012 AB982  <NA>  <NA>  <NA>  <NA>
#6  HRT2921  del                    garg,prabal A0012 A0013 AB982 AB702  <NA>  <NA>  <NA>
#7     <NA>  MUM                       ali,sanu AB702 AB712  <NA>  <NA>  <NA>  <NA>  <NA>
#8  KTN2624  DEL duplicate entry    singh,kunal AB328 AB712 AB982 A0013 A0012 A0011 AB982
#9  ANA2548  del                   tomar,lakhan AC728 AB702 A0013  <NA>  <NA>  <NA>  <NA>
#10 ITI2535  MUM                 thakur,praveen AC314 AB328 A0012 A0011  <NA>  <NA>  <NA>
#11 DEV2732  mum                     ali,sarman AC742 AC314 A0012 A0011  <NA>  <NA>  <NA>
#12 HRT2837  mum                     khan,zuber AC919 AC728 A0012  <NA>  <NA>  <NA>  <NA>
#13 ERV2951  mum                  singh,giriraj AC062 AB702 A0012 A0011  <NA>  <NA>  <NA>
#14 KTN2542  mum                  sharma,lokesh AD712 AB712  <NA>  <NA>  <NA>  <NA>  <NA>
#15 ANA2813  DEL                    pawar,pooja AD021 AC742 A0013  <NA>  <NA>  <NA>  <NA>
#16 ITI2210  DEL                  sharma,nikita AD920 AC919 A0012  <NA>  <NA>  <NA>  <NA>
user_39629140
Related