What is the easiest way to clean up messy rowdata dplyr

user_9144036 0
Viewed 48

I have a messy dataframe:

df <- data.frame(name = c('Chicken','Chicken1','ChiCKen','Chicke_N',
                      'Eg_g','EGG','egg'))

levels(as.factor(df$name))

[1] "Chicke_N" "Chicken"  "ChiCKen"  "Chicken1" "Eg_g"     "egg"      "EGG"

What would be the most efficient way of cleaning up these names, making them either 'chicken' or 'egg'?

I thought something like this would work:

df <- df%>% mutate(name = ifelse(name %in% c('Chicken','Chicken1','ChiCKen','Chicke_N'),'chicken','egg'))

I was wrong.

dplyr r
4 Answers

This changes to lower case and removes all punctuation and (contiguous) numbers, so it works on the given examples.

data.frame(name = c('Chicken','Chicken1','ChiCKen','Chicke_N',
                    'Eg_g','EGG','egg')) %>% 
  mutate(name = tolower(name) %>% stringr::str_remove("[[:punct:]]|\\d+"))
user_68518250

For a versatile approach, you might consider joining by stringdistance. Make sure to read the helpfiles on the different methods for computing stringdistance (i.e. osa, lv, dl, hamming, lcs, qgram, cosine, jaccard, jw and soundex).

df.valid <- data.frame(name = c("chicken", "egg"))

library(tidyverse)
library(fuzzyjoin)

df %>% stringdist_left_join(df.valid, ignore_case = TRUE, max_dist = 5)

#     name.x  name.y
# 1  Chicken chicken
# 2 Chicken1 chicken
# 3  ChiCKen chicken
# 4 Chicke_N chicken
# 5     Eg_g     egg
# 6      EGG     egg
# 7      egg     egg
user_63562780

Another alternative is to use approximate matching by using agrepl.

df |> 
 mutate(cleaned = ifelse(agrepl("chicken", df$name, ignore.case = TRUE), "chicken", "egg"))

      name cleaned
1  Chicken chicken
2 Chicken1 chicken
3  ChiCKen chicken
4 Chicke_N chicken
5     Eg_g     egg
6      EGG     egg
7      egg     egg
user_148121700

An alternative is to use lookup tables:

df <- data.frame(name = c('Chicken','Chicken1','ChiCKen','Chicke_N',
                      'Eg_g','EGG','egg'))

lkup <- data.frame(name = levels(as.factor(df$name)),
                   clean = c('chicken','chicken','chicken','chicken',
                             'egg','egg','egg'))
inner_join(df,lkup,by='name') %>% mutate(name = clean) %>% select(name)

This method is more verbose, but in some cases the name might not be coercible, for example when trying to match 'bird' to chicken..

user_91440360
Related