Subscribe Bookmark RSS Feed

How to rename columns using a second data table

markschahl

Community Trekker

Joined:

Jun 18, 2012

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

Joined:

Jun 22, 2012

Solution

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
2 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Community Trekker

Joined:

Jun 18, 2012

Jim:

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