topic Re: How to write a command on Formula-Character in Discussions

How to write a command on Formula-Character

bernie426 — Fri, 13 Feb 2015 15:50:32 GMT

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,

Re: How to write a command on Formula-Character

jules — Tue, 18 Oct 2016 22:34:40 GMT

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):

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.

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

Re: How to write a command on Formula-Character

bernie426 — Sat, 14 Feb 2015 15:52:09 GMT

Thanks a lots for the useful information Julinan.

Bernie

Re: How to write a command on Formula-Character

jules — Sat, 14 Feb 2015 21:54:12 GMT

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

Re: How to write a command on Formula-Character

bernie426 — Sun, 15 Feb 2015 03:30:34 GMT

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.

Re: How to write a command on Formula-Character

jules — Sun, 15 Feb 2015 06:29:35 GMT

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 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;

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 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 ) ) );

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

Re: How to write a command on Formula-Character

bernie426 — Fri, 20 Feb 2015 00:10:01 GMT

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;

Re: How to write a command on Formula-Character

pmroz — Fri, 20 Feb 2015 14:08:04 GMT

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

a = "Class 9";

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

Re: How to write a command on Formula-Character

jules — Sat, 21 Feb 2015 21:48:44 GMT

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;

Re: How to write a command on Formula-Character

bernie426 — Sun, 22 Feb 2015 05:28:53 GMT

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,

Re: How to write a command on Formula-Character

jules — Sun, 22 Feb 2015 06:42:04 GMT

Hi Bernie,

I'm sorry this isn't working for you! I think I may have chosen an ambiguous column name which I think might be the source of the confusion. In the attached dataset I have a column named "OriginalColumn" and the script below references that column in the Word() function. The word function doesn't take any arguments for which words it is looking for, only which word number, and the column to look in. It was my mistake to use a column name of "Class" when that was what you were looking to remove! Let me know if the script below works for the attached table. If it does, you should be able to change it around to work for any table you want by changing " :OriginalColumn " to match whatever the name is of the column in your table.

julian

//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, :OriginalColumn )

);

// End "Data Update"

Current Data Table() << End Data Update;