Choose Language Hide Translation Bar
Highlighted
Newbie2Jumpie
Level IV

Join equal jmp datasets by loop (scripting)

I have 100 structurally identical datasets. All of them contain an ID and a constant/field, that contains a name of that dataset.

To concatenate them (format "long") is easy-peasy.

Now I need to merge them into format "wide", is that equally easy?

I think of using that constant as a prefix for each JMP datasets, but how could I make that work in a loop.

Have any suggestions?

Thank you very much and Kind regards

Newbie

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Join equal jmp datasets by loop (scripting)

I am not exactly clear on what you want your output data table to look like, however, here is a simple example that will merge data tables, similar to the structure your discription is

Names Default To Here( 1 );

// Create example data tables to be used in the merging
dt = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

dt << delete columns( {"Subject", "Dose"} );
dt << New Column( "ID", character, formula( "A" || Char( Row() ) ) );
dt << Go To( :ID );
dt << Move Selected Columns( To First );
For( i = 2, i <= N Cols( dt ), i++,
	current data table(dt);
	dt2 = dt << subset( selected rows( 0 ), columns( :ID, Column( i ) ) );
	dt2 << New Column("Table Name", character, set each value(dt2<<get name))
);
wait(0);
close(dt,nosave);


// Merge the tables together

// Get the table Names
tableList = {};
for(i=1,i<=N Table(), i++,
	insert into(tableList, data table(i)<<get name);
);

// Loop across the tables and using Update put them back together
For( i = 2, i <= N Items( tableList ), i++,
	Data Table( tableList[1] ) << Update( With( Data Table( tableList[i] ) ), Match Columns( :ID = :ID ) )
);
Jim

View solution in original post

5 REPLIES 5
Highlighted
txnelson
Super User

Re: Join equal jmp datasets by loop (scripting)

I am not exactly clear on what you want your output data table to look like, however, here is a simple example that will merge data tables, similar to the structure your discription is

Names Default To Here( 1 );

// Create example data tables to be used in the merging
dt = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

dt << delete columns( {"Subject", "Dose"} );
dt << New Column( "ID", character, formula( "A" || Char( Row() ) ) );
dt << Go To( :ID );
dt << Move Selected Columns( To First );
For( i = 2, i <= N Cols( dt ), i++,
	current data table(dt);
	dt2 = dt << subset( selected rows( 0 ), columns( :ID, Column( i ) ) );
	dt2 << New Column("Table Name", character, set each value(dt2<<get name))
);
wait(0);
close(dt,nosave);


// Merge the tables together

// Get the table Names
tableList = {};
for(i=1,i<=N Table(), i++,
	insert into(tableList, data table(i)<<get name);
);

// Loop across the tables and using Update put them back together
For( i = 2, i <= N Items( tableList ), i++,
	Data Table( tableList[1] ) << Update( With( Data Table( tableList[i] ) ), Match Columns( :ID = :ID ) )
);
Jim

View solution in original post

Highlighted
Newbie2Jumpie
Level IV

Re: Join equal jmp datasets by loop (scripting)

Hi Tx,

I don't have access to JMP at the momement (I'm travelling) but the result should look like this.

Now, looking at the structure myself, I think concatentating, then flipping/looping the contatenated table may be a straightforward approach (the IDs are not that important). However, I would still like to learn whether this could be solved by a simplest (macro) loop.

File1:

ID   Constant Value

1        AB        123

2        AB        456

File2:

ID   Constant Value

1        DEF        123

2        DEF        456

...

File100:

ID   Constant Value

1        Z        123

2        Z        456

 

"Loop" By CONSTANT

 

Desired result

AB_ID   AB_Constant  AB_Value  DEF_ID  DEF_ Constant  DEF_Value ... Z_ID Z_ Constant  Z_Value

1        AB        123  1        DEF        123    ...   1        Z        123

2        AB        456  2        DEF        456    ...   2         Z        123

Highlighted
Newbie2Jumpie
Level IV

Re: Join equal jmp datasets by loop (scripting)

For whatever reason my post from Friday afternoon seemd to get lost...

That's the price you pay when you work an a train, or so it seems.

I have 100 datasets, each contains a different constant :

e.g.

Dataset1

ID  CONSTANT VALUE1 VALUE2

1     A                   123      123

2     A                   123      123

 

Dataset2

1    B                   456      456

2    B                   456      456

 

Dataset3

1    C                   789      789

2    C                   789      789

 

all I want is to get them into a single dataset with the following structure:

 

A_ID  A_CONSTANT A_VALUE1 A_VALUE2  B_ID  B_CONSTANT B_VALUE1 B_VALUE2

1               A                           123 123                1            B                              456 456

2              A                            123 123                2            B                             456 456                                etc,

 

I see two ways to get there:

(1) add a prefix to each single column within each dataset first and then merge them by ID, or

(2) contatente all the datasets first and then transpose/flip/loop them using the constant levels as a prefix (however, I found that JMP doesn´t like mixed datatypes)

 

Any idea how to get there... ?

Any help greatly appreciated

Newbie

Highlighted
txnelson
Super User

Re: Join equal jmp datasets by loop (scripting)

Here is a rework of my original script, taking into account the new information you provided.

You also might want to look into using your contatenated data table, and using 

     Tables==>Split

It can get you very close to what you want

Names Default To Here( 1 );

// Create example data tables to be used in the merging
dt = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

constants = "ABCDEFGHIJKLMNOP";

dt << delete columns( {"Subject", "Dose"} );
dt << New Column( "ID", character, formula( Char( Row() ) ) );
dt << Go To( :ID );
dt << Move Selected Columns( To First );
For( i = 2, i <= N Cols( dt ), i++,
	Current Data Table( dt );
	dt2 = dt << subset( selected rows( 0 ), columns( :ID, Column( i ) ) );
	Column( dt2, 2 ) << set name( "Value1" );
	dt2 << New Column( "Value2", formula( Floor( :Name( "Value1" ) + Random Integer( 5 ) ) ) );
	dt2 << New Column( "Constant", character, set each value( Substr( constants, i - 1, 1 ) ) );
);
Wait( 0 );
Close( dt, nosave );


// Merge the tables together

// Get the table Names
tableList = {};
For( i = 1, i <= N Table(), i++,
	Insert Into( tableList, Data Table( i ) << get name )
);
theConstant = Column( Data Table( tableList[N Items( tableList )] ), "Constant" )[1];
Data Table( tableList[N Items( tableList )] ) << New Column( theConstant || "_ID", character, formula( :ID ) );
Column( Data Table( tableList[N Items( tableList )] ), "Value1" ) << set name( theConstant || "_Value1" );
Column( Data Table( tableList[N Items( tableList )] ), "Value2" ) << set name( theConstant || "_Value2" );

// Loop across the tables and using Update put them back together
For( i = N Items( tableList ) - 1, i >= 1, i--,
	theConstant = Column( Data Table( tableList[i] ), "Constant" )[1];
	Data Table( tableList[i] ) << New Column( theConstant || "_ID", character, formula( :ID ) );
	Column( Data Table( tableList[i] ), "Value1" ) << set name( theConstant || "_Value1" );
	Column( Data Table( tableList[i] ), "Value2" ) << set name( theConstant || "_Value2" );
	Data Table( tableList[N Items( tableList )] ) << Update( With( Data Table( tableList[i] ) ), Match Columns( :ID = :ID ) );
);

 

Jim
Highlighted

Re: Join equal jmp datasets by loop (scripting)

I cannot imagine what the data sets represent or what the resulting combined data set is for. That information might help us solve your problem better. If the data sets are identical in structure and contain only repeating constants, then it seems to me that there are more efficient data structures and data methods for your problem.

 

So what are you going to do in JMP with the combined data set?

Learn it once, use it forever!
Article Labels

    There are no labels assigned to this post.