Ordering Files in Folder

[#1]

Folks,

I have a list of files that I want to concatenate (see first figure).  The preference would be to concatenate as shown, that is "surf_12275-87-1..., "surf_12275-87-2...", etc. However, when I form a list from the files in the folder (after stripping off unnecessary characters) , the sequence is "12275-87-1, 12275-87-10, 12275-87-11". The preference however, would be "12275-87-1, 12275-87-2, 12275-87-3". Obviously the straight forward way around the issue is to have the files come to me in the form of " -01, -02, ... -10, -11", but that will not always be the case. 

Is there a straight forward way to get the files into the preferred ranking format so I can open and concatenate from low to high? Current script attached.

s_path = {};
	X_path = {};
	f1 = Files In Directory( SD1, recursive(1));
	
	For( i = 1, i <= N Items( f1 ), i++,
	
			b = f1[i];
			
			c = Contains Item(b, "surf", "_");
			
			If( c==1, 
				d = Word(2, b, "_");
				Insert Into(s_path,d),
				Insert Into(X_path, d)	
			)
				
	);
	
	Show(s_path);

[#2 · Accepted solution]

JMP data table seem to be using numerical sorting if you have columns modeling type as ordinal so you could use that or for loop.

Names Default To Here(1);

a = {
	"surf_12275_87-1_r250", 
	"surf_12275_87-2_r250", 
	"surf_12275_87-3_r250", 
	"surf_12275_87-4_r250", 
	"surf_12275_87-5_r250", 
	"surf_12275_87-6_r250", 
	"surf_12275_87-7_r250", 
	"surf_12275_87-8_r250", 
	"surf_12275_87-9_r250", 
	"surf_12275_87-10_r250",
	"surf_12275_87-11_r250",
	"surf_12275_87-12_r250",
	"surf_12275_87-13_r250",
	"surf_12275_87-20_r250",
	"surf_12275_87-22_r250"
};
a = Sort List(a); //break the ordering
Show(a);
dt = New Table("",
	New Column("SortList", Character, Ordinal, Set Values(a)), private
);
dt << Sort(By(:SortList), Order(Ascending), Replace Table);
b = dt:SortList << Get Values;
Close(dt, no save);
Show(b);

c = {};
For(i = 1, i <= N Items(a), i++,
	Insert Into(c, a[i], Num(Words(a[i], "-_")[4]));
);
Show(c);

These could give some idea how to approach the problem.

Edit:

You might be also able to concatenate all tables first together while using Source column, change source column to Ordinal and then sort by that:

Names Default To Here(1);
a = {
	"surf_12275_87-1_r250", 
	"surf_12275_87-2_r250", 
	"surf_12275_87-3_r250", 
	"surf_12275_87-4_r250", 
	"surf_12275_87-5_r250", 
	"surf_12275_87-6_r250", 
	"surf_12275_87-7_r250", 
	"surf_12275_87-8_r250", 
	"surf_12275_87-9_r250", 
	"surf_12275_87-10_r250",
	"surf_12275_87-11_r250",
	"surf_12275_87-12_r250",
	"surf_12275_87-13_r250",
	"surf_12275_87-20_r250",
	"surf_12275_87-22_r250"
};
a = Sort List(a);

one = New Table(a[1], new column("a", set values([1])));
two = New Table(a[2], new column("a", set values([10])));
three = New Table(a[6], new column("a", set values([2])));

one << Concatenate(two, three, Append to first table, Create Source Column);
one:Source Table << Set Modeling Type("Ordinal");
one << Sort(By(:Source Table), Order(Ascending), Replace Table);

[#3]

Jarmo,

Many thanks for teaching me a new skill set. I've tried all your suggested approaches to improve my coding ability. Here is my latest revision which does exactly what I want. 

Thank you again

Neil

f1 = Files In Directory( SD1, recursive(1));
	
	f1 = Sort List(f1);
	dt = New Table("", 
		New Column("SortList", Character, Ordinal, Set Values(f1)), private	
	);
	
	dt << Sort( By(:SortList),  Order(Ascending), Replace Table);
	b = dt:SortList << Get Values;
	Close(dt, no save);
	
	For( i=1, i <= N Items(b), i++,
		
		file_1 = Open(SD1 || b[1]);
		If(i > 1, 
			
			file_nxt = Open(SD1 || b[i], Private);
			file_1 << Concatenate(file_nxt, Append to first table);	
		)
	);
	
	file_1 << Save(SD1 || "All Summary");

[#4]

Here is one way to sort the list, by taking the elements of the names and separating them into different columns in a data table, changing the numeric elements to numeric columns and then sorting the data table by all of the elements.

names default to here(1);

f1={"surf_12275-87-11_r250_50x_mean_2.jmp", "surf_12275-87-2_r250_50x_mean_2.jmp",
"surf_12275-87-10_r250_50x_mean_2.jmp", "surf_12275-87-4_r250_50x_mean_2.jmp",
"surf_12275-87-5_r250_50x_mean_2.jmp", "surf_12275-87-6_r250_50x_mean_2.jmp",
"surf_12275-87-7_r250_50x_mean_2.jmp", "surf_12275-87-8_r250_50x_mean_2.jmp",
"surf_12275-87-9_r250_50x_mean_2.jmp", "surf_12275-87-1_r250_50x_mean_2.jmp",
"surf_12275-87-3_r250_50x_mean_2.jmp"};

dt = new table("Sort Table",
	add rows(nitems(f1)),
	new column("File Names", character)
);
dt:File Names << set values(f1);

// Create and populate separate columns for each element in the names
sortList = {};
i=1;
While(word(i,f1[1],"_-.") != "",
	dt<<new column("Element" || char(i), character);
	insert into(sortList,"Element" || char(i) );
	For(k=1,k<=nitems(f1),k++,
		column(dt,ncols(dt))[k]=word(i,f1[k],"_-.")
	);
	i++
);

// Find the columns that are numeric and change them to numeric
For(i=2,i<=ncols(dt),i++,
	If(!isMissing(num(column(dt,i)[1])),
		column(dt,i)<<data type(numeric)
	);
);

// Sort the data table
dt<<sort(by(eval(sortList)),replace table(1));

// Reset the f1 list to the sorted list
f1 = dt:File Names << get values;

I am sure other members may have alternative methods.

[#5]

Jim,

I can't tell you how insightful this approach was for another (unrelated to this thread) problem I was having. That is, file names with unpredicted "element" arrangements. At least I'm able to dissect the filenames and "reorder" to consistent labeling.

Thanks again for all your help.

Neil 

[#6]

@DonMcCormackthis could also be a possible option for a JMP Challenge: Natural Sorting . I usually try to handle situations like this with additional padded zeros when I can, but it is quite common for them to be missing...