Subscribe Bookmark RSS Feed

How to write a command on Formula-Character

bernie426

Community Trekker

Joined:

Feb 9, 2015

I will like to know how to write a workable command using Formula-Character functionality.

For example, I have a column of character data that have data like leveled from Class 1.0 to Class 9.0.

I would like convert this information to another column that only show 1.0 to 9.0, how can I use the formula character function to write a command for this?

The other request I like to know is how can I change a column of character data to another statement in a new column.

For example, I have a character column of data showing sweet, tasty, sour,...and I would like to change it to be sweet-->Bin1, tasty-->Bin2, sour-->Bin3. How can I use the formula character to convert the sweet-->Bin1 in a new column.

Thanks,

10 REPLIES
julian

Staff

Joined:

Jun 25, 2014

Hi bernie426,

To extract a piece of a your column you could use the Word() function, which takes two arguments: the first is which "word" to extract (you will enter a number here), and the second is the column with text you wish to extract from. There is a third, optional argument in which you can specify the delimiters that separate words, but in your case the default of a "space" will work. So, to use this, make a new formula column with Word(2,:Class)  (where :Class is whatever variable your original data is stored in).  For example (also in the attached dataset):

8049_Screen Shot 2015-02-13 at 1.23.26 PM.png

This will take the second "word" which is just whatever is after the first space encountered, thus just the numbers.

For your second question I would use the match function (below). You can write this yourself, or use the column recode utility to write it for you. To do the latter, select the original column, then go to Cols > Recode. Specify what new values you wish your variable to have, then select "Formula Column" instead of "In Place."  JMP will make you a new column using the match function.

8048_Screen Shot 2015-02-13 at 1.23.20 PM.png

You could use Cols > Recode in this same way for your first question, but if you have many levels using Word() will be faster than manually specifying the recoding.

I've attached an example dataset with these formulae.

I hope this helps!

julian

bernie426

Community Trekker

Joined:

Feb 9, 2015

Thanks a lots for the useful information Julinan.

Bernie

julian

Staff

Joined:

Jun 25, 2014

You're welcome! I just read your initial post again and it occurred to me you might have been looking for a scripting solution rather than a column formula solution. If that is the case let me know and I can help you set this up through a script. You may have noticed in Col > Recode there is even an option to have JMP write the recoding as a script, so I would start there and we could adapt the code to use Word() to address your first question.

Julian

bernie426

Community Trekker

Joined:

Feb 9, 2015

Hi Julian,

So appreciate that you read my initial post again. Yes, it would be even better if there was having a script option since I am using this a daily based.

julian

Staff

Joined:

Jun 25, 2014

Hi Bernie,

I'm glad I asked! There are a few ways you can approach this, depending on whether you would like to write a formula column, or write the actual values. Different circumstances call for one or the other, so I've included methods for both below.

To write the actual values:

Using Word() to extract just the numbers

//Make a new column

Current Data Table() << New Column("ClassNumberOnly", Character);

//stop table update until all changes are made, good for large tables

Current Data Table() << Begin Data Update;

//Apply changes to each row

For Each Row(

  :ClassNumberOnly = Word( 2, :Class )

);

// End "Data Update"

Current Data Table() << End Data Update;



Using Match() for recoding

//Make a new column

Current Data Table() << New Column("AttributeRecode", Character);

//stop table update until all changes are made, good for large tables

Current Data Table() << Begin Data Update;

//Apply changes to each row

For Each Row(

  :AttributeRecode = Match( :Attribute,

  "Tasty", "Bin3",

  "Sweet", "Bin2",

  "Sour", "Bin1",

  :Attribute

  )

);

// End "Data Update"

Current Data Table() << End Data Update;



To write a formula column:

Using Word() to extract just the numbers

//Make the formula column

Current Data Table() << New Column("ClassNumberOnly", Character,

  Formula(

  Word( 2, :Class )

  )

);



Using Match() for recoding

//Make the formula column

Current Data Table() << New Column("AttributeRecode", Character,

  Formula(

  Match( :Attribute,

    "Tasty", "Bin3",

    "Sweet", "Bin2",

    "Sour", "Bin1",

    :Attribute

    )

  )

);





Hope this helps!


julian


bernie426

Community Trekker

Joined:

Feb 9, 2015

Hi Julian,

I got the “Match” script or formula working perfectly, and I love it so much.

However, I cannot get the “Word” function work on either formula or script.

The case I have is an existing column that is Character of “Class 1” to Class 9”, and I will like to have a new column beside it that only show the number (1 to 9) and it is still in Character/Attribute format.

Hence, based on the Word() script you wrote for me as an example, Do we need an input information for the mother column (Class1…Class 9) and use the Word() function?

Thanks,

Bernie

Using Words() to pull out just the numbers

//Make a new column

Current Data Table() << New Column("ClassNumberOnly", Character);

//stop table update until all changes are made, good for large tables

Current Data Table() << Begin Data Update;

//Apply changes to each row

For Each Row(

:ClassNumberOnly = Word( 2, :Class )

);

// End "Data Update"

Current Data Table() << End Data Update;

pmroz

Super User

Joined:

Jun 23, 2011

Try using the NUM function to convert the string number to an actual number.

a = "Class 9";

b = num(word(2, a));

julian

Staff

Joined:

Jun 25, 2014

Hi bernie426

It sounds like you want to have the resulting column be numeric, is that right? If so, Peter's suggestion above is the right way to handle this. When you extract a piece of a string it stays a string, unless you tell JMP that you want to convert the string to be a number. Here is a completed script with the Num() function wrapped around the Word() function, and also one more change to make the new column be numeric when it is created.

Hope this works for you!

julian

//Make a new NUMERIC column

Current Data Table() << New Column("ClassNumberOnly", Numeric);

 

//stop table update until all changes are made, good for large tables

Current Data Table() << Begin Data Update;

 

//Apply changes to each row

//The function below will look for a column named "Class"

//if the data are in column with a different name

//change :Class to be whatever the column name is, but

//be sure to retain the : since that lets JMP know you are

//referencing a column.

//this function also wraps the Word() function in Num()

//which will convert the character output to be a number

//allowing the resulting column to stay numeric

For Each Row(

  :ClassNumberOnly = Num( Word( 2, :Class ) )

);

// End "Data Update"

Current Data Table() << End Data Update;

bernie426

Community Trekker

Joined:

Feb 9, 2015

Hi Julian,

Actually, I would like to keep it as a character rather than converting them to numeric.

What I only would like is to get rid of the "Class " from  original "Class 9.0", and have only "9.0"showing.

I used the word (2, Class),  but it did not work out.

Thanks,