Subscribe Bookmark RSS Feed

How can I select a value from an external table by indexing on values in two columns in the JMP table?

ranjan_mitre_or

Community Trekker

Joined:

Oct 16, 2013

I am working in a very large JMP table. I would like to select a value from an external two dimensional small table and put that value in a new column. In order to select the value from the external table, I would like to use values in each row of the two columns in the current JMP table as the two subscripts of the external small table. Can anyone help me to do this?

I have  two files. The Big_data_matrix contains the first column that can only have values between 1 - 24. Column 2 is empty. Values.jmp is a small table that contains one column with 24 entries. For each row in Big_data_matrix I would like to read the value in column 1, and if it is equal to k, then I would like to pick the k-th entry (k=1:24) from values. jmp table, and enter it in the second column of Big_data_matrix.

My original Big_data_matrix is very large (>1M rows) and I have to do this for many different Big_data_matrix data tables.

This is probably easy to do in jsl, but I have 0  experience with JSL, and would like to do this within JMP, if at all possible.

Thanks.

Message was edited by: Ranjan Sonalkar

1 ACCEPTED SOLUTION

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

Here is an example on how to use jsl to get values from another tables based on 2D-coordinates in the main table.

//Example tables

dt1 = New Table("BigTable",

    Add Rows(10),

    New Column("row_index", Set Values([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])),

    New Column("column_index", Set Values([1, 2, 3, 1, 2, 3, 1, 2, 3, 1]))

);

dt2 = New Table("SmallTable",

    Add Rows(5),

    New Column("Column 1", Set Values([1, 2, 3, 4, 5])),

    New Column("Column 2", Set Values([6, 7, 8, 9, 10])),

    New Column("Column 3", Set Values([11, 12, 13, 14, 15]))

);

// Add new columns values from small tables corresponding to the two index columns

dt1 << New Column("data", numeric);

For Each Row(dt1:data = Column(dt2, Eval(:column_index[]))[:row_index]);

6 REPLIES
Wendy_Murphrey

Joined:

Jun 23, 2011

Hello,

Could you provide a small example of your tables and the desired output?  That should help to clarify what you are trying to accomplish.

Thanks.

Wendy
ranjan_mitre_or

Community Trekker

Joined:

Oct 16, 2013

I have  two files. The Big_data_matrix contains the first column that can only have values between 1 - 24. Column 2 is empty. Values.jmp is a small table that contains one column with 24 entries. For each row in Big_data_matrix I would like to read the value in column 1, and if it is equal to k, then I would like to pick the k-th entry (k=1:24) from values. jmp table, and enter it in the second column of Big_data_matrix.

My original Big_data_matrix is very large (>1M rows) and I have to do this for many different Big_data_matrix data tables.

This is probably easy to do in jsl, but I have 0  experience with JSL, and would like to do this within JMP, if at all possible.

Thanks.

ms

Super User

Joined:

Jun 23, 2011

Ok, the indexing is one-dimensionalI. Then I suggest that you use Update.

First, add a second column to Values.jmp, with the values 1 to 24. Then, activate the big table and select Update in the tables menu. Select Values.jmp in the list box and check "Match columns". Then you match the first column in big table with the newly created ID column in Values.jmp.

A new column with values corresponding to the number in column 1 is then added to the big table (if the empty column 2 in big table are given the same name as the value column in the small table, this column will be filled instead of a new column).

And yes, It could easily be scripted.

ranjan_mitre_or

Community Trekker

Joined:

Oct 16, 2013

MS

Thanks. Simple solution and works nicely.

ms

Super User

Joined:

Jun 23, 2011

Solution

Here is an example on how to use jsl to get values from another tables based on 2D-coordinates in the main table.

//Example tables

dt1 = New Table("BigTable",

    Add Rows(10),

    New Column("row_index", Set Values([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])),

    New Column("column_index", Set Values([1, 2, 3, 1, 2, 3, 1, 2, 3, 1]))

);

dt2 = New Table("SmallTable",

    Add Rows(5),

    New Column("Column 1", Set Values([1, 2, 3, 4, 5])),

    New Column("Column 2", Set Values([6, 7, 8, 9, 10])),

    New Column("Column 3", Set Values([11, 12, 13, 14, 15]))

);

// Add new columns values from small tables corresponding to the two index columns

dt1 << New Column("data", numeric);

For Each Row(dt1:data = Column(dt2, Eval(:column_index[]))[:row_index]);

pmroz

Super User

Joined:

Jun 23, 2011

You can use the UPDATE statement to update the large table with values from the small table.  Here's a JSL example:

dt_big = New Table( "Big_data_matrix",

    Add Rows( 2400 ),

    New Column( "First Column", Numeric, Continuous,  Format( "Best", 12 ) )

);

dt_val = New Table( "Values", Add Rows( 24 ),

    New Column( "Column 1", Character, Nominal,

        Set Values( {"AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH", "II", "JJ", "KK", "LL",

            "MM", "NN", "OO", "PP", "QQ", "RR", "SS", "TT", "UU", "VV", "WW", "XX"} ) ),

    New Column( "Row Number", Numeric, Continuous, Format( "Best", 12 ),

        Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,

            21, 22, 23, 24] ) )

);

// Fill with random integers from 1-24

for (i = 1, i <= 2400, i++,

    column(dt_big, "First Column")[i] = round(random uniform(1, 24), 0);

);

dt_big << Update(

    With( dt_val ),

    Match Columns( :First Column = :Row Number ),

    Add Columns from Update table( :Column 1 )

);