topic Re: Creating new columns and inserting formula based on the original column cells in Discussions

Creating new columns and inserting formula based on the original column cells

Lharbicht — Thu, 04 Jan 2018 05:11:06 GMT

Hello,

I'm an inexperienced JMP scriptor and I'm trying to write my first larg(er) script. I'm having trouble with one part. Here's what I'm trying to do: I have various data tables that I will use this script on. I would like the script to look at all of the columns, excluding the first column (column and row numbers will vary for each data table) and create a new column for each column already present in the table. I would like the name of each of the new columns to be the same as each of the original columns but with a "_code" at the end, and preferably be positioned next to the original column in the table. Within each new column I would like the formula to be an if/then statement. If "Column A" has the text "<" in the cell, then 0, otherwise 1.

I hope my explanation makes sense. I've posted my script although it seems to break at line 8 and I'm not sure why.

Thank you for any help!

Lacey

Names Default To Here( 1 );

dt = Current Data Table();

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

For( i = 2, i <= nitems( col_list ), i++,
	
		data_col = ":" || col_list[i];
		
		code_col = col_list[i] || "_Code";
		
		str = evalinsert (
		
			"\[dt << New Colum ("^code_col^", Numeric, Continuous,
			Format("Best",12),
			Formula(If( Contains( "^code_col^", "<" ),
	0,
	1
)));
				
			);]\");
			
			eval(parse(str));
);

Re: Creating new columns and inserting formula based on the original column cells

txnelson — Thu, 04 Jan 2018 06:32:03 GMT

Here is a modification of your code that I believe meets all of your needs. One item that I had to deal with, is that the columns that you start with (2-13) have to be character, so the formula does not error out. The Column 13 does error out the script, since it is a formula column that can not be changed to character, because as soon as it runs it's formula, it will force the column to be numeric.

Names Default To Here( 1 );

dt = Current Data Table();

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

For( i = 13, i <= N Items( col_list ), i++,
	Eval(
		Substitute(
				Expr(
					Column(col_list[i])  << Data Type( Character );
					dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
					Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
				),
			Expr( __NewColName__ ), col_list[i] || "_Code",
			Expr( __OrigCol__ ), Parse( ":" || col_list[i]  )
		)
	)
	
);

Re: Creating new columns and inserting formula based on the original column cells

Lharbicht — Thu, 04 Jan 2018 14:03:50 GMT

That worked great! Thanks!

It was my mistake that I left that "column 13" in the datatable. That was just me making sure my if then statement was working and I forgot to delete it when I posted it online. So I just deleted that out of my datatable, changed the 13 to a 2 in the wonderful script you sent and then it worked like a charm.

Thank you so much!

Lacey

Re: Creating new columns and inserting formula based on the original column cells

Lharbicht — Thu, 04 Jan 2018 15:27:02 GMT

Hi Jim,

I went to try the script on a new data table and I ran into a problem with my column names. I deal with environmental data, and many times the analyte names (ie. column names) will have symbols in the name such as "-", "[", "]", "(", ")", and/or ",". When I ran the script it errored on the first column which was "3-Methyl-1-butene". Is there a fix for this?

Thank you,

Lacey

Re: Creating new columns and inserting formula based on the original column cells

txnelson — Thu, 04 Jan 2018 17:11:39 GMT

The :Name() function is designed to handle this issue. Below is a modification I made to your script that I believe will solve the issue. I have not tested this modification, but I think it will work. If not, you should be able to see what I am attempting to do, and you should be able to get it to work.

Names Default To Here( 1 );

dt = Current Data Table();

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

For( i = 13, i <= N Items( col_list ), i++,
	Eval(
		Substitute(
				Expr(
					Column(col_list[i])  << Data Type( Character );
					dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
					Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
				),
			Expr( __NewColName__ ), col_list[i] || "_Code" ,
			Expr( __OrigCol__ ), Parse( ":Name(\!"" || col_list[i] || "\!:)" )
		)
	)
	
);

Re: Creating new columns and inserting formula based on the original column cells

Lharbicht — Thu, 04 Jan 2018 17:52:51 GMT

Yes, I got that to work. Thank you so much!