cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ctwede
Level I

JSL Options for Reordering Columns Numerically

Are there any options for reordering columns in JSL beyond "Reorder by Name, Modeling Type, Data Type"?

I'm looking to reorder some columns with names and numerical indexing. The columns are named in the following manner- "Column Data 1 (1), Column Data 1 (2), ...., Column Data 99 (1), Column Data 99 (2),...".

Using the reorder by name option basically reorders the data (1), (100), (101), (102),.. ordering it by text rather than numerically.

Are there any options JSL introduces that would make sorting this data simpler? I have ~2200 columns to organize.

Using JMP 14.1.0

 

2 REPLIES 2
txnelson
Super User

Re: JSL Options for Reordering Columns Numerically

names default to here(1);

// Create a new data table with columns that resemble your columns
dt = New Table( "Example" );

// Create a bunch of new columns
For(i=1,i<=100,i++,
	dt << new column( "Column Data " || char(randominteger(1,10)) || "(" ||
		char(randominteger(1,99)) || ")"
	)
);
// Get rid of columns that are duplicates
For(i=100,i>=1,i--,
	if(substr(column(dt,i)<<get name,length(column(dt,i)<<get name),1)!=")",
		dt << delete columns(i);
	)
);
wait(5);
// Create a data table of the names and 2 additional columns that will allow for sorting
colNames = dt << get column names(string);

dtSort = New Table( "Sort Names", New Column( "Name", character, set values(colNames)));
	dtSort << New Column("Primary Number", formula( Num( Word( -2, :Name, " ()"))));
	dtSort << New Column("Secondary Number", formula( Num( Word( -1, :Name, " ()"))));
	
// Sort in numerical order
dtSort << sort(By(:Primary Number, :Secondary Number),order(ascending,ascending), replace table(1));

// Order the columns in the original data table by the order in the sorting table
For(i=1,i<=N Rows(dtSort),i++,
	eval(parse("dt << go to (:Name(\!"" || column(dtSort,"name")[i] || "\!"));"));
	dt << move selected columns( To Last );
);

 Above is a script that first builds a sample data table, and then reorders the columns numerically

 

 

 

Jim
Craige_Hales
Super User

Re: JSL Options for Reordering Columns Numerically

Cool! 

Craige