Extracting a string from a column heading and adding to another

[#1]

Hello,

I have an import script which I use to import results into JMP from excel. The template excel is used by lots of people and is locked for editing. We have two results columns in the excel which are both called "Results - XXX" where XXX is replaced using an excel marco to what is being studied so changes in each file. We also have columns called JMP Result 1 and JMP Result 2 which are equal to the results columns and in number format. which we use as JMP import can't import a scientific number from excel. When I open the excel in JMP I get something like the image below:

I'm trying to write a script which will extract the word following "Results -" from the column heading and replace the JMP Result Column heading but making sure it replaces the correct heading. so the result would be:

Not sure if this is possible in JMP but any help would be greatly apricated. Not even sure how to start by extracting the end of the string of the column heading let alone the later steps. Thanks in advance.

[#2 · Accepted solution]

Here is pure JSL option. It can (and will will) break fairly easily depending on your data as I had to make quite a few assumptions

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Results - DNA", Numeric, "Continuous", Format("Best", 12), Set Values([.]), ),
	New Column("Results - Protein", Numeric, "Continuous", Format("Best", 12), Set Values([.])),
	New Column("JMP Result 1", Numeric, "Continuous", Format("Best", 12), Set Values([.])),
	New Column("JMP Result 2", Numeric, "Continuous", Format("Best", 12), Set Values([.]))
);

// Assume that you extract last word from result columns
// and results are first then jmp columns and there is equal amount
result_columns = {};
jmp_columns = {};

colnames = dt << Get Column Names("String");
For Each({colname}, colnames,
	If(Starts With(colname, "Results - "),
		Insert into(result_columns, Word(-1, colname, " "));
	,
		Insert Into(jmp_columns, colname)
	)
);

For Each({{rescol, jmpcol}}, Across(result_columns, jmp_columns),
	Column(dt, jmpcol) << Set Name(rescol);
);

Write();

[#3]

Also non-scripting option came to my mind.

Create stacked table

Create last word column from Label

Now you have mapping table

Go back to your original table and Recode column names

Use Apply Mapping for Table...

And click Recode

Too bad JMP isn't able to create proper script for this automatically as it will just use the mapping values directly in the script so this might be manual process unless you are able to create "full mapping table" somehow to get that filled in to the script

// Recode column names
Local({dt = Data Table("Untitled 2"), names},
	names = Recode(
		dt << Get Column Names(String),
		{Map Value(
			_rcOrig,
			{"JMP Result 1", "DNA", "JMP Result 2", "Protein"},
			Unmatched(_rcNow)
		)}
	);
	For Each({name, i}, names, Column(dt, i) << Set Name(name));
);

[#4]

Hi, 

I attached a test data table. Try this script:

dt = current data table();

	for each({name,i}, dt << get column names(string),
		if (contains(name, "Results -"),
			new_name_string = word(2, name, "- ");
			column(dt,i) << set name(substitute(name, "Results -", "Results_Excel -" ));
			for each({name2,p}, dt << get column names(string),
					if (contains(name2, "JMP Result"),
						column(dt,p) << set name(new_name_string);
						Break();
						,
						);
					); 
				,
			);
	
	);

This should do what you are aiming for. Please let us know if not.

Edit: Just saw Jarmos great solution. You might choose this one if you do not want to change the names of the "excel" columns.

Cheers,

Jonas