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
markschahl
Level V

How to rename columns using a second data table

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?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How to rename columns using a second data table

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]] );
	);
);
Jim

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: How to rename columns using a second data table

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]] );
	);
);
Jim
markschahl
Level V

Re: How to rename columns using a second data table

Jim:

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

Re: How to rename columns using a second data table

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

Greetings