topic Re: Need to combine every n =6 number of columns using JMP script. in Discussions

Need to combine every n =6 number of columns using JMP script.

HSS — Fri, 09 Jun 2023 18:05:58 GMT

Hi All, I need to combine every 6 columns of the data file and make it a new column. I am find if all the combine columns can be stored in new data file just to make code easy. Please see the attached sample data and output image I am looking for.

Also how can I extract every 'n' element /and first 'n' element from a list ?

Any help please.
Thanks.

Re: Need to combine every n =6 number of columns using JMP script.

frank_wang — Wed, 24 Nov 2021 06:00:48 GMT

Hi Hari

My solution as annex. (base on your sample data)

Have a good day.

Re: Need to combine every n =6 number of columns using JMP script.

txnelson — Wed, 24 Nov 2021 10:02:18 GMT

Given your input JMP table, the script below generates the new columns as your JPG file shows. The JSL works if the input data are character columns or numeric columns.

names default to here(1);
dt=current data table();

// Get all columns in data table
allCols = dt << get column names(character,string);
// Setup the names of the new columns to be created
varNames = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

// Loop across the columns 6 at a time creating the new column
For(i=1,i<=nitems(allCols),i=i+6,
	dtName = substr(varNames,(i+5)/6,1);
	// Create the JSL command to generate the formula
	theExpr = "new column(dtName, character,
		formula(
			theList = {};
			insert into(theList,char(:\!"" || allCols[i] || "\!"n)); 
			insert into(theList,char(:\!"" || allCols[i+1] ||  "\!"n));
			insert into(theList,char(:\!"" || allCols[i+2] ||  "\!"n));
			insert into(theList,char(:\!"" || allCols[i+3] ||  "\!"n));
			insert into(theList,char(:\!"" || allCols[i+4] ||  "\!"n));
			insert into(theList,char(:\!"" || allCols[i+5] ||  "\!"n));
			Concat Items(theList,\!"-\!");
		)
	)";
	// Run the created JSL
	eval(parse(theExpr));
	// Turn column values into static values
	column(dt,dtName)<<delete formula;
	
	// Move the current 6 column after the created column
	dt << Clear Column Selection();
	selList = allCols;
	remove from(selList, i+6, nitems(selList)-i+5);
	remove from(selList,1,i-1);
	dt << select columns(selList);
	eval(parse("dt << Move Selected Columns(After(:"||dtName||"));"));
);
dt << Clear Column Selection();

Re: Need to combine every n =6 number of columns using JMP script.

HSS — Sat, 27 Nov 2021 09:22:40 GMT

Thanks Nelson, It worked.

Thanks again.