Unifying categorical columns with typos in R

user_14587041 0
Viewed 38

I have a data like this;

df <- data.frame(cat_col = c('Avansas Paper Clps','avansas paper clips','AVANSAS PAPER CLIPS','Avansas Paper Clips',
                       'Avansas stplr','avnss stpler','Avansas Stapler','White Block note','block notes white','Colorful papers',
                       'Color paper'))

df

   cat_col            
   <chr>              
 1 Avansas Paper Clps 
 2 avansas paper clips
 3 AVANSAS PAPER CLIPS
 4 Avansas Paper Clips
 5 Avansas stplr      
 6 avnss stpler       
 7 Avansas Stapler    
 8 White Block note   
 9 block notes white  
10 Colorful papers    
11 Color paper

entries, obviously written with typos. I want to make them unified with high similarity. So my expected output is ;

   cat_col             unified
   <chr>                 <dbl>
 1 Avansas Paper Clps        1
 2 avansas paper clips       1
 3 AVANSAS PAPER CLIPS       1
 4 Avansas Paper Clips       1
 5 Avansas stplr             2
 6 avnss stpler              2
 7 Avansas Stapler           2
 8 White Block note          3
 9 block notes white         3
10 Colorful papers           4
11 Color paper               4

it can be a hard question and not highly related with programming, but I run out of the solutions.

Thanks in advance

categorical-data r
1 Answers

A possible solution, based on the following ideas:

  1. Get the distance between the words of column cat_col, ignoring case (adist).

  2. Use clustering (hclust) to split into 4 groups the words of cat_col, based on the matrix of distances previously calculated.

d <- adist(df$cat_col, df$cat_col, ignore.case = T)
h <- hclust(as.dist(d), method = "ward.D")
groups <- cutree(h, 4)
df$unified <- groups

df

#>                cat_col unified
#> 1   Avansas Paper Clps       1
#> 2  avansas paper clips       1
#> 3  AVANSAS PAPER CLIPS       1
#> 4  Avansas Paper Clips       1
#> 5        Avansas stplr       2
#> 6         avnss stpler       2
#> 7      Avansas Stapler       2
#> 8     White Block note       3
#> 9    block notes white       3
#> 10     Colorful papers       4
#> 11         Color paper       4
user_115644870
Related