Given data table with inconsistent item descriptions, how could I most effectively assign an item category using R (i.e. dplyr), MySQL, or Python? An R based solution is preferred.

MySQL is the data source. As is, case-when logic assigns an item category based on an item description. There is no common key or category to aggregate the items, hence the need to create one. However, not all like items are named the same. These are the same items, but sold across different locations. The is little to no consistency for item descriptions between locations. Real world data provides plenty of unstructured learning opportunities.

For example, consider an item like 'Whole Cheese Pizza'. This item exists with multiple descriptions such as 'Whl Cheese Pizza', 'Pizza Chs Whole', 'Cheese Pizzza Whole', 'Whole Cheese' etc. Ideally these all roll into one category named 'Pizza'. Case-when logic uses finds item descriptions like '%Pizza%' or 'Whl Cheese%' or 'Whole Cheese%' to assign the 'Pizza' category.

Clearly not ideal nor really scaleable. More pragmatic than programmatic.

Without seeing the catalog, are there any suggestions on how to apply a more programmatic method to catalog items with inconsistent text descriptions for aggregation?

Can provide additional context or details as needed. Thank you for time and expertise!

edit: Sample file here with item descriptions and rollups attached. Goal is to recreate the rollup category without case-when logic. Note the missing rollup category is the default case.

edit: fixed link to file

  • $\begingroup$ I don't see a link to a sample file. I'm guessing what you want is a clustering algorithm that uses some sort of string "distance" to identify strings that refer to the same thing. $\endgroup$
    – Spacedman
    Jul 27, 2017 at 21:54
  • $\begingroup$ Stringdist package might help $\endgroup$
    – HEITZ
    Jul 27, 2017 at 22:42
  • $\begingroup$ Regular expressions $\endgroup$
    – grldsndrs
    Jul 28, 2017 at 0:31
  • $\begingroup$ thank you all for comments and edits! Appreciate the expertise to think thru the task. $\endgroup$ Jul 28, 2017 at 22:07

1 Answer 1


Thanks for the real-world problem! Interesting challenge there. Some thoughts:

  • regex (regular expressions) may not work because "Whole Cheese" (the description) doesn't even contain "pizza" (the category)!
  • the "case-when logic" you mentioned is SQL's equivalent of handling conditional (if/else) logic using SQL's CASE statement. This can be helpful but super-tedious due to the huge number of rules required. (And then what happens when you have misspelt descriptions e.g. pizzza?)
  • wrt the comment by @Spaceman, my thought is that using stringdist to calculate string distance to then perform clustering, which is unsupervised, wastes all the useful labelled data which can be used!

My suggestion:

Use a combination of "case-when logic" and classification algorithms, to generate a set of labelled data (the labels being the rollups) in which then you can perform some supervised classification for the unlabelled data.

In your sample file, some categories have already been labelled. You can use that as training data for your supervised learning (e.g. SVM).

This may require some text mining knowledge (e.g. DocumentTermMatrix, tokenizers). I refer you to the wonderful tm and RTextTools package in R.

  • $\begingroup$ Like the hybrid approach. Thank you for inspection and input. I was trying to create a bag of words with qdap, though I was only using the bag of words to refine if/then or case logic. Sounds like this is my ante to label observations for training. Thanks. $\endgroup$ Jul 28, 2017 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.