String binning tool (or other alternative to nested ifs for grouping)?

[scotthingram]

Hello,

I'm curious if there exists a tool that can be used to bin string field values into another column.  Ideally the configuration would allow you to select a column, then would display all unique values in that column and allow manual drag-and-drop binning (similar to the grouping capability in Power BI). 

An example would be a database of animals, with an "Animal" column with the following set of unique values:

Dog

Cat

Alligator

Rooster

Lion

Gecko

Dove

I envision a tool that would allow you to select column "Animals" and set categories (e.g. Mammal, Bird, Reptile) for output to a new column then drag and drop the different animals into each category. Historically I've used large nested if functions (e.g. if animal="dove" then "bird" elseif animal = "gecko" then "reptile", etc....) but this can become tedious. 

Would appreciate any alternatives to the nested if solution.

[clmc9601 · Accepted solution]

Hi @scotthingram,

Here are two of my favorite solutions for something like this:

- Find replace tool. Add a text input tool with the groups you want to create, with the unique animal names in one column (you can generate this first using summarize if desired) and the groups in the other. For example:

 Animal | Group 

Dog | Mammal

Cat | Mammal

Alligator | Reptile

Rooster | Bird

Lion | Mammal

Gecko | Reptile

Dove | Bird

(with the pipes representing different columns). Then use the find replace tool or a join tool to append the group field onto the data.

- the IN function in the formula tool. You can simplify your IF statement as follows:

IF [Animal] IN ("Dog", "Cat", "Lion")

THEN "Mammal"

ELSEIF [Animal] IN("Rooster", "Dove")

THEN "Bird"
ELSEIF [Animal] IN("Gecko", "Alligator")

THEN "Reptile"

ELSE "Not Categorized"

ENDIF

It's slightly shorter than what you're already using but allows you to easily add more animals into each category.

Those are two of my favorites. To my understanding, there's not a GUI drag and drop method for this within Alteryx, but I could be wrong. I'm interested to know if anyone comes up with even better solutions!

[clmc9601]

Hi again @scotthingram, actually, the cool thing about Alteryx is that you can create your own GUI! Worth it for a process you'll reuse, but it's much more work than the simpler solutions above.  

Customized binning GUIInside the macro