How to rename columns using a second data table

markschahl — Tue, 05 Dec 2017 18:45:46 GMT

I work with chemical manufacturing process data (think temperature, pressure, flow, composition, etc.) that is retrieved from a process historian. Each variable has both an equipment (tag) name like: LP1LCA0311 and a descriptor (glossary) like: F-301 Level. When automatically exported from Excel to JMP, the glossary is used as column name. The glossary is created by humans, and thus are not usually consistent. Often, it makes sense to use an alias instead of the glossary: functionally, F-301 is the Low Pressure Dust Collector; alias = LVL.LP.DC.

We are building dashboards to monitor our processes. Here is an example:

six reactors with 31 measurement columns + timestamp
need to rename the columns from glossary to standard alias format like: RX#.LVL; RX#.F.Feed; etc.
standard format will allow for easy stacking: can create a column named Reactor which uses the LEFT() or WORD() functions; rename the Data column using RIGHT() function.

I would like to:

Use a second data table (DT2) to rename the columns in the first data table (DT1)

DT2 contains 2 columns: Old.Name = DT1 column names; New.Name =manually entered new column names (alias)

Script should take Old.Name and insert into new column property = "OldName" in DT1

Script then renames DT1 columns using DT2 New.Name

The code snippet below from Jerry Cooper shows how to do the column property step:

// Script to take units from column name and assign to column property
// received from Jerry Cooper of SAS 03-Mar-2014
// Data table should have column names of form: Name|units
// Script assumes that units are after "|"
// Easy to set up header row in Excel to concatenate name and units

Names Default To Here( 1 );
dt = Current Data Table();
mycols = dt << Get Column Names( string );
For( i = 1, i <= N Items( mycols ), i++,
If( Contains( mycols[i], "|" ),
myunits = Substr( mycols[i], Contains( mycols[i], "|", -1 ) + 1 );
newname = Substr( mycols[i], 1, Contains( mycols[i], "|", -1 ) - 1 );
Column( i ) << Set Name( newname );
Column( i ) << Set Property( "Units", eval(myunits) );
)
);

How would I script the steps to rename the columns in DT1

Does anyone have a better way to accomplish what I want?

Re: How to rename columns using a second data table

txnelson — Thu, 22 Jun 2017 14:40:45 GMT

Try this and see if it is what you want

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );
dt2 = New Table( "Lookup",
	Add Rows( 1 ),
	New Column( "Old Name", Character, "Nominal", Set Values( {"Sex"} ) ),
	New Column( "New Name", Character, "Nominal", Set Values( {"Gender"} ) )
);

ColList = dt << get column names( string );
For( i = 1, i <= N Items( ColList ), i++,
	FoundName = dt2 << get rows where( Uppercase( :OldName ) == Uppercase( ColList[i] ) );
	If( N Rows( FoundName ) > 0,
		Column( dt, ColList[i] ) << set property( "Old Name", Eval( ColList[i] ) );
		Column( dt, ColList[i] ) << set name( dt2:New Name[FoundName[1]] );
	);
);

Re: How to rename columns using a second data table

markschahl — Thu, 22 Jun 2017 15:27:09 GMT

Jim:

Thanks for the quick reply! This does what I need. I owe you a beer.

Re: How to rename columns using a second data table

Aziza — Wed, 18 Apr 2018 13:44:08 GMT

Wow! this worked in one go! Thank you!!!

topic Re: How to rename columns using a second data table in Discussions

How to rename columns using a second data table

Re: How to rename columns using a second data table

Re: How to rename columns using a second data table

Re: How to rename columns using a second data table