recognize similar elements in a string

0
votes

I have a large data frame comprising products of an online store, several products recorded in different ways in it like this:

1:  milk 1-liter low, fat
2:  M I L K 1-liter L ow fat
3:  Milk. 1_liter LOW FAT
4:  Milk 1_liter L F A T
5:  MILK 1.5_liter Hi gh FAT

and I need to split it

I expect to get this out of my data:

 V1   v2        v3    v4
milk  1-liter   low   fat
MILK  1-liter   Low   fat
Milk. 1_liter   LOW   FAT
Milk  1_liter   L     FAT
MILK  1.5_liter High  FAT
2
r

2 Answers

0
votes

Another option which may be broader:

out = data.frame(
V1=gsub("(.*) \\d.*","\\1",z$z),
V2=gsub(".*(\\d.*liter).*","\\1",z$z),
V3=gsub(".*liter (.*)[F|f].*","\\1",z$z),     
V4=gsub(".*([f|F].*)","\\1",z$z),
stringsAsFactors=FALSE)

out[]=apply(out[],2,function(x) gsub(" ","",x))
> out
     V1      V2   V3  V4
1  milk 1-liter low, fat
2  MILK 1-liter  Low fat
3 Milk. 1_liter  LOW FAT
4  Milk 1_liter    L FAT
5  MILK 5_liter High FAT

The apply() is only used to remove spaces.

Data

z= structure(list(z = c("milk 1-liter low, fat", "M I L K 1-liter L ow fat", 
"Milk. 1_liter LOW FAT", "Milk 1_liter L F A T", "MILK 1.5_liter Hi gh FAT"
)), row.names = c(NA, -5L), class = "data.frame")
0
votes

I'm keen to know if someone else has a more automated solution because I often have similarly messy data.

The only way I know how to do this is code a bunch of regular expressions (via stringr::str_replace()) to harmonise the rows in your data frame. You can use tidyr::separate() to then split your product column into multiple columns:

library(stringr)
library(dplyr)
library(tidyr)

dat <- tibble(product = c("milk 1-liter low, fat",
       "M I L K 1-liter L ow fat",
       "Milk. 1_liter LOW FAT",
       "Milk 1_liter L F A T",
       "MILK 1.5_liter Hi gh FAT"))

dat %>%
  mutate(product = str_replace(product, "(milk|MILK|Milk|M I L K|Milk)\\.*", "milk"),
         product = str_replace(product, "(low|LOW|L\\sow|L),*", "low"),
         product = str_replace(product, "(HIGH|Hi\\sgh|H)", "high"),
         product = str_replace(product, "(FAT|Fat|F A T)", "fat"),
         product = str_replace(product, "-liter", "_liter")) %>%
  separate(product, into = c("V1", "V2", "V3", "V4"), sep = " ", extra = "merge")

# A tibble: 5 x 4
  V1    V2        V3    V4   
  <chr> <chr>     <chr> <chr>
1 milk  1_liter   low   fat  
2 milk  1_liter   low   fat  
3 milk  1_liter   low   fat  
4 milk  1_liter   low   fat  
5 milk  1.5_liter high  fat