topic Re: Adding a Column and Populating with Data in Discussions

Adding a Column and Populating with Data

fuscod — Thu, 14 Jan 2016 19:22:52 GMT

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.

Re: Adding a Column and Populating with Data

txnelson — Thu, 14 Jan 2016 20:11:18 GMT

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 )

Re: Adding a Column and Populating with Data

Jeff_Perkinson — Mon, 14 Nov 2016 09:28:07 GMT

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.

-Jeff