Subscribe Bookmark RSS Feed

Adding a Column and Populating with Data

fuscod

Community Trekker

Joined:

Mar 18, 2015

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.  

2 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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 )

Jim
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

10784_JMPScreenSnapz001.png

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.

10785_JMPScreenSnapz002.png

Here's a video showing Recode.


-Jeff

-Jeff