cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
fuscod
Level I

Adding a Column and Populating with Data

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 2
txnelson
Super User

Re: Adding a Column and Populating with Data

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 Community Manager

Re: Adding a Column and Populating with Data

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