Make Categorical Data Numeric

cpituley

Hi Alteryx Community, 

I am trying to take a categorical data set and transform it into a purely numeric. Edit: I should add that this doesn't need to be ordinal since there is no relation between variables in a given column. 

I am thinking this involves the following steps: 

1) Remove duplicates from each column

2) Assign a unique ID (starting base 0) to each unique value in each column

3) Map the original data set back to the unique values

Example as follows: 

Before

Field 1 | Field 2 | Field 3

abc      | def       | ghi

abc      | def       |stu

jkl        | def       | ghi

jkl        | pqr       | stu

mno    | pqr       | ghi

mno    | pqr       | zzz

After:

Field 1 | Field 2 | Field 3

0          | 0          |0

0          | 0          |1

1          | 0          |0

1          | 1          |1

2          | 1          |0

2          | 1          | 2

Looking forward to your help. 

Thank you,

Cameron

CharlieS

I've attached a couple of solutions for scenarios like this. 

"UniqueCategorical.yxmd" will assign a numeric value (1+) to unique string characters in each field. This solution relies on the Tile tool configured to unique value. To start the assignment with 0, use a Formula tool after the tile tool to update the Tile_SequenceNum field to [Tile_SequenceNum]-1.

If you'll be using these values in statistic testing, I have another solution that converts string field values to binary flags for each field/value combination. This is the "DynamicDummies.yxmd" attachment. 

20180525-DynamicDummies.yxmd

20180525-UniqueCategorical.yxmd