topic I need to reorder my columns in an efficient manner (there are 57 instances of 5 groups each (57 x 5) = 285 columns to rearrange) in Discussions

I need to reorder my columns in an efficient manner (there are 57 instances of 5 groups each (57 x 5) = 285 columns to rearrange)

learning_JSL — Sat, 02 Sep 2023 00:39:17 GMT

Hi - I have 5 groups of 57 columns (5 x 57) that I need to rearrange so that likes are with likes. I've cut and pasted examples of the columns from my JMP table. The columns names have five sets of prefixes (again, each of these groups has 57 column names corresponding to chemical compounds):

1 - RES_VALUES_

2 - RES_TEXT_

3 - DETECT_FLAG_

4 - RL_

5 - DILUTION_FACTOR_

Each of these is followed by a suffix corresponding to the chemical compound name (there are 57 of these).

I want to have each chemical compound name grouped together. So, for example, one chemical compound, say "4:2 FTS", would be listed in the column order as follows: RES_VALUE_4:2 FTS, RES_TEXT_4:2 FTS, DETECT_FLAG_4:2 FTS, RL_4:2 FTS, DILUTION_FACTOR_4:2 FTS. This sequence would be repeated for each chemical compound. What I would end up with is a table that I can easily explore, where the relevant info for each compound is together. The first 5 columns would be 4:2 FTS. The second 5 would be the next compound, 6:2 FTS. And so on.

Is there an efficient way to do this rather than drag and drop? Thanks in advance!

Re: I need to reorder my columns in an efficient manner (there are 57 instances of 5 groups each (57 x 5) = 285 columns to rearrange)

txnelson — Sat, 02 Sep 2023 04:21:06 GMT

Here is a little script that I think will do what you want. It orders the columns as you suggested, plus it groups each of the compounds into separate column groups.

Names default to here(1);
groupList = {"RES_VALUE","RES_TEXT", "DETECT_FLAG", "RL", "DILUTION_FACTOR" };

// The comment lines below, when uncommented will create a small example data table
// that was used to test the code
/*compList = {"4:2 FTS", "6:2 FTS", "ADONA" };
New Table("Example");
for each({group}, groupList,
	for each({comp},compList,
		New Column(group || " " || comp)
	)
);*/

dt = current data table();

// Find all of the different compounds.  This is done by using the "RES_VALUE" columns
// and stripping off the compound names from each of them
compList = {};
For( i=1,i<=N Cols(dt), i++,
	colName = column(dt,i)<<get name;
	if(contains( colName,"RES_VALUE")==1,
		insert into(compList,substr(colName,11))
	)
);

// Order each of the Compound columns together
For Each( {comp}, compList,
	varList = {};
	For Each( {group}, groupList,
		insert into(varList, group || " " || comp)
	);
	dt << select columns( varList);
	dt << Move Selected Columns( To last );
	dt << group columns( comp, varList);
	
);

Re: I need to reorder my columns in an efficient manner (there are 57 instances of 5 groups each (57 x 5) = 285 columns to rearrange)

learning_JSL — Sat, 02 Sep 2023 12:13:08 GMT

Thanks Jim! It worked beautifully - simple and concise!

I really appreciate your help.