topic Re: How to modify(overwrite) existing column with formula in datatable in Discussions

How to modify(overwrite) existing column with formula in datatable

joshua — Thu, 23 Jan 2020 19:16:38 GMT

Hi,

I 'm new to JMP and would like to know how can I modify one of a column in my data table. Let's say we have iris data and would like to divide Petal length column / 100. I opened formula tab by right clicking Petal lenght and input my formula. In the end I got • in the column. What is the correct way to do this ?
thanks

Re: How to modify(overwrite) existing column with formula in datatable

Dan_Obermiller — Thu, 23 Jan 2020 19:22:31 GMT

Your formula column needs to go into a DIFFERENT column. You need to have the original data available in order to do the calculation. So, create a new column, maybe called Scaled Petal Width, and create the formula just as you have it now.

You can't overwrite the original column of data as that could cause a real conflict. Think of it this way. If you enter the formula in the column of original data, all of the values are divided by 100. But you still have a formula that says to change the entries to be divided by 100, so it should do that again. It would never stop. Further, displaying the original values along with the calculated values provides data integrity.

Now, if you REALLY want to overwrite, you can do so through scripting and is naturally more difficult. You would also not have the formula embedded in the column.

Re: How to modify(overwrite) existing column with formula in datatable

txnelson — Thu, 23 Jan 2020 19:21:24 GMT

Using a formula to modify a column that has already existing static values will not work. However, you can use JSL to modify the static values

For Each Row(
     :Petal Length = :Petal Length/100;
);

Re: How to modify(overwrite) existing column with formula in datatable

joshua — Thu, 23 Jan 2020 19:22:01 GMT

That's I don't want to do. In my real data table I have hundreds of columns and I just want to simply a simple math some of the. If I add more columns and have custom column names that increases the work I need to do! There should be I way around this.

Re: How to modify(overwrite) existing column with formula in datatable

joshua — Thu, 23 Jan 2020 19:22:49 GMT

Where should I put this script? to formula window or somewhere else ?

Re: How to modify(overwrite) existing column with formula in datatable

Dan_Obermiller — Thu, 23 Jan 2020 19:24:13 GMT

You put the script that Jim provided in a script window. Choose File > New > Script. Paste the script in there and run.

Re: How to modify(overwrite) existing column with formula in datatable

joshua — Thu, 23 Jan 2020 19:28:18 GMT

Wow! That worked. But it gave it to me 0's. ie 1.4/100 I think is not equal to 0.

Re: How to modify(overwrite) existing column with formula in datatable

txnelson — Thu, 23 Jan 2020 19:30:14 GMT

I strongly recommend you go through the JMP Welcome Kit

Help==>JMP on the Web==>JMP Welcome Kit

and take the time to read the documentation, Discovering JMP and Using JMP.

Help==>JMP Documentation Library

Re: How to modify(overwrite) existing column with formula in datatable

joshua — Thu, 23 Jan 2020 19:30:23 GMT

In addition how can I apply this formula to the columns contains certain strings. In this iris data set lets say 'length' is the string that I want to modify those column names that contains that.

Re: How to modify(overwrite) existing column with formula in datatable

txnelson — Thu, 23 Jan 2020 19:40:48 GMT

Here is one way to do that:

Names Default To Here( 1 );
dt = Current Data Table();

nameList = dt << get column names( string );

For( i = 1, i <= N Items( nameList ), i++,
	If( Contains( nameList[i], "length" ),
		For Each Row( 
			as Column( nameList[i] ) = as Column( nameList[i] ) / 100 )
	)
);

Re: How to modify(overwrite) existing column with formula in datatable

Dan_Obermiller — Thu, 23 Jan 2020 20:00:16 GMT

Check the formatting of the column. More than likely the format is not set to "Best" and you are seeing rounded values.