Adding a Column and Populating with Data

Question

JMP Users I have a simple question I would like to create a new column and populate it with data based upon information in and existing column.  For examples, say I have a column of "Animals" as so:

ANIMAL

CAT

DOG

HUMAN

HORSE

SNAKE

LIZARD

CROCODILE

TURTLE

TURKEY

CHICKEN

EAGLE

DUCK

ANT

BEE

SPIDER

FLY

I WOLD LIKE TO WRITE JSL SCRIPT TO ADD A "CATEGORY" COLUMN AND TO POPULATE THE CATEGORY COLUMN WITH DATAT.  WITH THE RESULTS BEING:

ANIMAL                    CATEGORY

CAT                           MAMMAL

DOG                         MAMMAL

HUMAN                    MAMMAL

HORSE                    MAMMAL

SNAKE                     REPTILE

LIZARD                    REPTILE

CROCODILE           REPTILE

TURTLE                  REPTILE

TURKEY                 BIRD

CHICKEN               BIRD

EAGLE                    BIRD

DUCK                     BIRD

ANT                         INSECT

BEE                        INSECT

SPIDER                  INSECT

FLY                         INSECT

SO IF THE ANIMAL IS A MAMMAL I WANT THE CATEGORY COLUMN TO POPULATE "MAMMAL", IF THE ANIMAL IS A REPTILE I WANT THE CATEGORY COLUMN TO POPULATE "REPTILE", ETC.

Thank you,

Dan F.

Jeff_Perkinson · Answer

To do this by hand (without a script) you can use Cols->Utilities->Recode on your original column.

When you click the Done button you'll have the option of creating a Formula Column which will create a new column with a formula that will assign values based on the animal.

Here's a video showing Recode.

https://www.youtube.com/watch?v=xcP476FSnQE

-Jeff

txnelson · Answer

The easiest way to do this would be to create a lookup data table that has the master set of animals matched with the category;  Then all that has to be done is to Update the data table with the lookup table, specifying to keep all rows in the original table, but only the matching rows from the lookup table.

Names Default To Here( 1 );

dt = Data Table( "Data" );

dtud = Data Table( "Lookup" );

Data Table( "Data" ) << Update(

With( Data Table( "Lookup" ) ),

Match Columns( :ANIMAL = :ANIMAL )