topic Re: Apply same formula to several columns in Discussions

Apply same formula to several columns

bordini — Sun, 23 Jun 2019 02:48:00 GMT

I have about 200 columns, and I want to apply the same formula to almost all of these columns.

The formula I want to apply is the square root of “Range 0 to 1 + 1” to each colum. I don’t want to write the name of the columns in a script because they are many.

Could anyone help me to figure out a script for this purpose?

Thank you!

Re: Apply same formula to several columns

txnelson — Sun, 23 Jun 2019 03:48:31 GMT

I am a little confused about what you are asking for. Do you want the same calculation performed on the 200 columns, or do you want to create 200 new columns and apply a formula to the new column?

Additionally, this might help. You can easily get the names of the columns in a data table by using the "<< get column names" message and apply it to your data table

colNamesList = dt << get column names(string, numeric);

The above code will create a list called colNamesList and will place in it the quoted name of each of the numeric column names. The "string" qualifier, will make each element in the members of the colNamesList, string variables. You can then loop through the columns and generate the calculations you need for each of the columns in the list.

This is all documented in the Scripting Guide

Help==>Books==>Scripting Guide

I suggest you take the time to read through the guide.

Re: Apply same formula to several columns

bordini — Sun, 23 Jun 2019 15:43:20 GMT

@txnelson yes, I want the same calculation applied to all 200 columns (which are all the continuous columns I have). I want to apply the same formula to all of them. It would be okay to create new columns with the formula applied to them, or to apply the formula directly to the columns. The formula is the square root of (Range 0 to 1 +1).

Thanks for sharing that scrip with me, but it didn’t run. Do I need to do anything special to run this script? I copied and pasted it into the Script window, and hit “run”.

I’m sorry if my questions are too obvious. I don’t know anything about scripts.

Thank you,

Isadora

Re: Apply same formula to several columns

jules — Sun, 23 Jun 2019 19:00:19 GMT

Hi @bordini,

If you're okay with an interactive solution, using Cols > Standardize Attributes will allow you to standardize the formula across your 200 new columns, and will, importantly, allow you to substitute the column references used in the formula. Here's a link to the documentation that has an example of standardizing a formula.

In your case, you can also take advantage of JMP's Instant formulas to make the initial Range columns. Then, use Cols > Standardize Attributes (or right click column header > standardize attributes) to add the +1 to each column, while preserving the unique column references in each formula column (which is what Substitute Column Reference will do). See the video below for a quick demonstration.

(view in My Videos)

I hope this helps!

@jules

//Edit: I missed the square root part. You can add that when you're editing the formula. Just select the outermost formula container and click the root button in the toolbar:

Re: Apply same formula to several columns

bordini — Sun, 23 Jun 2019 19:10:11 GMT

Hi @jules ,

That’s exactly what I needed! The video was very helpful! Thank you very much!

Re: Apply same formula to several columns

Thomas1 — Sun, 23 Jun 2019 21:58:30 GMT

Right click the column with your formula and choose "Copy Column Properties". Select all your columns to which the formula should be applied. Right click and choose "Paste Multiple Columns Properties".

Re: Apply same formula to several columns

bordini — Mon, 24 Jun 2019 13:45:52 GMT

Thank you!

Re: Apply same formula to several columns

nac — Mon, 04 May 2020 08:25:33 GMT

Hi,

I also have similar issue. I want to calculate natural logarithm on 10 different columns. But I want to see these calculations on new columns. For example as shown in jmp file. There are 3 columns. I want to calculate their natural logarith on 3 new columns. How can I make it easily. 3 column not a big size. But when you have 20 column it is difficult to apply the same formula for 20 column.

could you please help on this?

Re: Apply same formula to several columns

txnelson — Mon, 04 May 2020 10:32:02 GMT

This can be handled in the Standardize Columns Attributes

Create a new column and enter in the formula you want to use
Create the number of new columns required, that you are going to want to replicate the formula for
Select all of the columns, including the first column that already has the formula, and then from the red triangle in the columns panel, select, Standardize Attributes
In the Standardize Attributes dialog window, click on the down arrow for Column Properties and select Formula.
In the expanded window, with the Formula information in it, select the Check Box, "Substitute Column Reference"
Click on OK and the new Formulas will be created

Re: Apply same formula to several columns

Mark_Bailey — Mon, 04 May 2020 12:51:37 GMT

Select all the original data columns. Right-click on the name of one of them and select New Formula Column > Transform > Log.