Subscribe Bookmark RSS Feed

expanding a matrix

rogerc

Community Trekker

Joined:

Jun 23, 2011

I have a data table that is a matrix and is 124 columns and 152 rows. It something like this but bigger. The first row represents the field names in the JMP table:

L 1 3 5
1 0 5 2
3 1 9 3
5 2 2 0

"L" is the label field. The field names represent sectors in the economy and may or may no match the field "label".

I want to build that matrix into a full size matrix, in this example 5X5:

L 1 2 3 4 5
1 0 0 0 0 0
2 0 0 0 0 0
3 0 0 0 0 0
4 0 0 0 0 0
5 0 0 0 0 0

so that it looks like this:
L 1 2 3 4 5
1 0 0 5 0 2
2 0 0 0 0 0
3 1 0 9 0 3
4 0 0 0 0 0
5 2 0 2 0 0

Any ideas on how I could use JMP scripting to get it to where I want it? Thanks.
4 REPLIES
Let me see if I understand what you want. You have a data table that you want to expand. In the example you gave above, you start with a 3x3 table and you want to expand it to a 5x5. The values you want to appear in the expanded table are given in the 3x3 table. The values in the first column of the 3x3 table correspond to the row indices of the items you want to appear in the expanded table. The column names of the other columns correspond to the column indices. Everything else is zero. As an example, for your little 3x3 table, you want a number 3 to appear in the 3rd row and 5th column of the expanded table.

If I've understood you correctly, I created some JSL code to do what you want.

Requirements for the script to run properly:
-start with a data table. In your example above, it is a 3x4 table.
-the values in the first column must be integers, corresponding to the row indices of the expanded table. The max value in this column needs to be less than or equal to the number of rows in the expanded table. The name of this first column can be anything.
-the column names of the other columns must be integers, corresponding to the column indices of the expanded table. The max for these values has to be less than or equal to the number of columns in the expanded table.
-the columns must be numeric continuous.

Input for the script. These are specified at the top of the script.
-specify the data table path. For your example, this is the location of the small 3x4 table.
-specify the number of rows and columns for the expanded table.

The script works for the simple 5x5 example you gave, and for a larger example I created for testing.

For some reason, jsl code doesn't always appear correctly on this forum, so when you copy/paste the code below, you'll have to modify one line for it to work. The single line of code close to the bottom, inside the two For loops: right after and adjacent to the row_index, put a square bracket with an i in it.

Ignore the next line, it is necessary for jsl code to be put on the forum. Ignore the last line too.

rogerc

Community Trekker

Joined:

Jun 23, 2011

Jonathon, thanks, this is helpful. The problem with the script is that row 1 represents the field names in the data table: fields L, 1, 3, 5. They also represent the indexes for the column, except for the field "L". So 1 is column 1, "3" is column 3, "5" is column 5. The field input under "L" are the row names: "1" is row 1, "3" is row 3, and "5" is row 5.

L 1 3 5
1 0 5 2
3 1 9 3
5 2 2 0

In the resulting matrix it looks like this:

L 1 2 3 4 5
1 0 0 5 0 2
2 0 0 0 0 0
3 1 0 9 0 3
4 0 0 0 0 0
5 2 0 2 0 0

where the first row in the above matrix are the field names for the data table, and the column "L" is the row index. Is this possible? Thanks.
This is essentially the same solution as above, but would the following be a little easier? You'd probably want to encapsulate it within a user-defined function to make it generally useful - and I'm assuming that you can specify in advance how many rows and columns you want in your large matrix - but it should be easy enough to generalise:

rogerc

Community Trekker

Joined:

Jun 23, 2011

David, this works fine. thanks much for the help. The user defined function is also a good idea. I figure I'll be using this on multiple occasions.