Subscribe Bookmark RSS Feed

Keeping duplicate columns

adam

Contributor

Joined:

Sep 20, 2017

Hi,

I have 2 data table and these table are similar to certain extent where the test parameters(which means perhaps 30% of the columns sharing the same name) are same as they are different by test location hence the test result will be different as well. I intend to combine this table using Update and at the same time maintaining the 2nd table columns data but automatic rename those columns so that I can view them side by side. Can this done via scripts.

 

Thanks.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
ih

Community Trekker

Joined:

Sep 30, 2016

Solution

I am not sure exactly what you want to do, but yes you can rename columns using a script.  If you open each data table and keep separate references to each, you can send messages to all of them to rename columns:

Names default to here( 1 );

//Open two tables
dt1 = Open("$SAMPLE_DATA/Iris.jmp"); //first table
dt1 << Set Name( "Table 1" );
dt2 = Open("$SAMPLE_DATA/Iris.jmp"); //second table (different filename)
dt2 << Set Name( "Table 2" );

//Concatenate tables
dtJoined = dt1 << Concatenate( dt2 );
dtJoined << Set Name( "Combined" );

//Change column names
Column( dt1, "Sepal length" ) << Set Name( "Sepal_length" );
Column( dt2, "Sepal length" ) << Set Name( "Sepal_length" );
Column( dtJoined, "Sepal length" ) << Set Name( "Sepal_length" );

If changing a lot of column names you could use a function to replace the last three lines:

 

//Make a function to change column names in all tables
ChangeAllNames = function({original, new},
	Column( dt1, original ) << Set Name( new );
	Column( dt2, original ) << Set Name( new );
	Column( dtJoined, original ) << Set Name( new );
);

//Then call the function
ChangeAllNames( "Sepal width", "Sepal_width" );
ChangeAllNames( "Petal length", "Petal_Length" );
txnelson

Super User

Joined:

Jun 22, 2012

Solution

What you are asking for is the default action taken in JMP when you Join 2 data tables.  If they have columns of the same name in both data tables, JMP renames the columns.  Here is a script version, however, if you have 2 tables, you can interactively do this by

     Tables==>Join

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA\big class.jmp" );
dt2 = Open( "$SAMPLE_DATA\big class Families.jmp" );

Data Table( "big class Families" ) << Join( With( Data Table( "big class" ) ), 
	By Row Number );
Jim
5 REPLIES
ih

Community Trekker

Joined:

Sep 30, 2016

Solution

I am not sure exactly what you want to do, but yes you can rename columns using a script.  If you open each data table and keep separate references to each, you can send messages to all of them to rename columns:

Names default to here( 1 );

//Open two tables
dt1 = Open("$SAMPLE_DATA/Iris.jmp"); //first table
dt1 << Set Name( "Table 1" );
dt2 = Open("$SAMPLE_DATA/Iris.jmp"); //second table (different filename)
dt2 << Set Name( "Table 2" );

//Concatenate tables
dtJoined = dt1 << Concatenate( dt2 );
dtJoined << Set Name( "Combined" );

//Change column names
Column( dt1, "Sepal length" ) << Set Name( "Sepal_length" );
Column( dt2, "Sepal length" ) << Set Name( "Sepal_length" );
Column( dtJoined, "Sepal length" ) << Set Name( "Sepal_length" );

If changing a lot of column names you could use a function to replace the last three lines:

 

//Make a function to change column names in all tables
ChangeAllNames = function({original, new},
	Column( dt1, original ) << Set Name( new );
	Column( dt2, original ) << Set Name( new );
	Column( dtJoined, original ) << Set Name( new );
);

//Then call the function
ChangeAllNames( "Sepal width", "Sepal_width" );
ChangeAllNames( "Petal length", "Petal_Length" );
adam

Contributor

Joined:

Sep 20, 2017

Thanks ih. Though this is not exactly what I want but the idea is there. Attached file is something that I wanna do.Joining 2 data table and auto renamed 2nd data table columns that matched the 1st data table. Sorry, if there is any confusion from my initial message.

txnelson

Super User

Joined:

Jun 22, 2012

Solution

What you are asking for is the default action taken in JMP when you Join 2 data tables.  If they have columns of the same name in both data tables, JMP renames the columns.  Here is a script version, however, if you have 2 tables, you can interactively do this by

     Tables==>Join

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA\big class.jmp" );
dt2 = Open( "$SAMPLE_DATA\big class Families.jmp" );

Data Table( "big class Families" ) << Join( With( Data Table( "big class" ) ), 
	By Row Number );
Jim
ih

Community Trekker

Joined:

Sep 30, 2016

To expand on Jim's point you will need to join by row as you do not appear to have a key pair as in a typical join.  I am having a hard time understanding why you would join the tables in this way; does SN AX0001 relate to STX0030 but not STX0031?  If so maybe there is another column you could add to both tables to join them in a more meaningful way.

adam

Contributor

Joined:

Sep 20, 2017

Thanks ih & Jim. That's right in normal circumstances, with less variables the table can be joined without any issues.