Geek Answers Handbook

How to create a unique identification string ambiguous in r?

I have a large data set with three variables (State, Zipcode, Name). Here's a little extract:

zz <- "State Zipcode Name
IL  60693 THISISTHEFIRST  
IL 60693 TISISTHEFIRS    
OH  45271 THISISTHEFIRST  
CA 94085 THISISTHESECOND  
CA 94085 THISISTHESECOND  
CA 94085 THISISTHESECCOND 
SC 29645 THISISTHETHIRD  
SC 29645 THISISTHETHIRD  
SC 29645 THISISTHETHIRD  
SC 29645 THISISTHEFOURTH  
SC 29645 ISISTHEFOURTH"

Data <- read.table(text=zz, header = TRUE)

I need to create a unique identifier for observations characterized by the same state, Zipcode, Name. However, some of the names have a typo, even if they do represent the same object (for example, THISISTHEFIRST vs. TISISTHEFIRS).

I would like to get something similar to this:

State Zipcode Name ID
IL 60693 THISISTHEFIRST 1
IL 60693 TISISTHEFIRS 1
OH 45271 THISISTHEFIRST 2
CA 94085 THISISTHESECOND 3
CA 94085 THISISTHESECOND 3
CA 94085 THISISTHESECCOND 3
WI 53022 THISISTHETHIRD 4
WI 53022 THISISTHETHIRD 4
WI 53022 THISISTHETHIRD 4
SC 29645 THISISTHEFOURTH 5
SC 29645 ISISTHEFOURTH 5

How can I create a unique identifier in a quick and efficient way?

+4
source share
2 answers

You can do something similar agrepusing fuzzy matching. You can play with edit distance.

Data$bins <- sapply(Data$Name, function(n)
    paste(as.integer(agrepl(n, Data$Name, max.distance = 2)), collapse=""))
Data$Group <- as.integer(as.factor(Data$bins))

#    State Zipcode             Name        bins Group
# 1     IL   60693   THISISTHEFIRST 11100000000     4
# 2     IL   60693     TISISTHEFIRS 11100000000     4
# 3     OH   45271   THISISTHEFIRST 11100000000     4
# 4     CA   94085  THISISTHESECOND 00011100000     3
# 5     CA   94085  THISISTHESECOND 00011100000     3
# 6     CA   94085 THISISTHESECCOND 00011100000     3
# 7     SC   29645   THISISTHETHIRD 00000011100     2
# 8     SC   29645   THISISTHETHIRD 00000011100     2
# 9     SC   29645   THISISTHETHIRD 00000011100     2
# 10    SC   29645  THISISTHEFOURTH 00000000011     1
# 11    SC   29645    ISISTHEFOURTH 00000000011     1
+5

:

Data$Group <- group(Data[,'Name'])
Data$ID <- getanID(Data, c('State', 'Zipcode', 'Group'))[,'.id', with=F]
Data[,!names(Data) %in% 'Group']
#    State Zipcode             Name .id
# 1     IL   60693   THISISTHEFIRST   1
# 2     IL   60693     TISISTHEFIRS   2
# 3     OH   45271   THISISTHEFIRST   1
# 4     CA   94085  THISISTHESECOND   1
# 5     CA   94085  THISISTHESECOND   2
# 6     CA   94085 THISISTHESECCOND   3
# 7     SC   29645   THISISTHETHIRD   1
# 8     SC   29645   THISISTHETHIRD   2
# 9     SC   29645   THISISTHETHIRD   3
# 10    SC   29645  THISISTHEFOURTH   1
# 11    SC   29645    ISISTHEFOURTH   2

group, LegalizeIt. splitstackshape getanID.

group <- function(vec, maxdist = 2) {
  dist <- sapply(vec, adist, vec) <= maxdist
  nums <- apply(as.matrix(dist), 1, function(x) paste(as.integer(x), collapse=''))
  as.integer(factor(nums))
}
+2

More articles:


All Articles