cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
dg1
dg1
Level III

Replacing Matrix Values

Hi! I am trying to input all my column data into a 8 by 12 matrix in order( filling in the columns first in a downwards manner). There are only 72 values in my column, so I want the remaining values that are not filled to be 0.  I am having a lot of problems trying to get the code below to work. I would appreciate any help!

dt = Open("Example.jmp");
Rows = nrows(dt); names= dt << Get Column Names (String); Table =[]; Table = J(8,12,0); For(i=1, i <= ncol(dt),i++, data = column(dt, names[i]) <<getValues; For(a=1, a <=12, a++, For(b =1, b <=8, b++, Table[a,b] = operation; // i get errors at this portion ); ); //unsure of the correct way to implement the code below operation= For (c =1, c= Rows, c++, DataInput[c] = Data[c]; ); ); Print(Table);
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Replacing Matrix Values

Are your data columns for the results from a micro-titre plate?

 

You want to re-shape the data in each column into an 8x12 table. Your script will only keep the table for the last column. Here is a script that process every data column and stores the table result with the column name in an associative array.

 

Names Default to Here( 1 );

dt = Open( "Example.jmp" );

table = Associative Array();

For( c = 1, c <= N Col( dt ), c++,
	data = Column( dt, c ) << Get As Matrix;
	data[Loc(Is Missing( data ))] = 0;
	table[Column( dt, c ) << Get Name] = Transpose( Shape( data, 12, 8 ) );
);

Show( table << Get Contents );

 

I assume that you have at least 96 rows. If there are more, they are lost. If there are less, then the assignment of 0 should be changed to a concatenation that pads with 0. This version pads instead:

 

Names Default to Here( 1 );

dt = Open( "Example.jmp" );

table = Associative Array();

For( c = 1, c <= N Col( dt ), c++,
	data = Column( dt, c ) << Get As Matrix;
	If( N Row( data ) < 96,
		data |/= J( 96 - N Row( data ), 1, 0 );
	)
	table[Column( dt, c ) << Get Name] = Transpose( Shape( data, 12, 8 ) );
);

Show( table << Get Contents );

View solution in original post

22 REPLIES 22
txnelson
Super User

Re: Replacing Matrix Values

I am really not sure I understand what you are trying to do.  But below is a script that will fill zeros after the matrix called data runs out of values

dt = Open( "Example.jmp" );
Rows = N Rows( dt );
names = dt << Get Column Names( String );
Table = [];
Table = J( 8, 12, 0 );
For( i = 1, i <= N Col( dt ), i++,
	data = Column( dt, names[i] ) << getValues;
	For( a = 1, a <= 12, a++,
		For( b = 1, b <= 8, b++,
			// I believe that (a - 1 ) * b + b will be equal to the 
			// row number of the data matrix you read in.  Once it's 
			// value is greater than the number of rows in the dt data 
			// table, it will enter zeros.
			If( (a - 1) * b + b <= Rows,
				Table[a, b] = data[(a - 1) * b + b],
				Table[a, b] = 0
			)
		)
	);
);
Print( Table );

I am confused about what you are doing with the outer For() loop, going from column to column.  How many columns are in the data table Example?  

Jim
dg1
dg1
Level III

Re: Replacing Matrix Values

thanks for the response Jim! the data table has 5 columns right now, but It can change depending on the user input. I am basically trying to put a data column into a matrix with 12 columns and 8 rows.

(a - 1) * b + b does not seem to be working for me, I guess because for the first column data it will always be data[0]. Do you know what is going wrong?

Re: Replacing Matrix Values

Jim offers a very good solution but I am also not sure what you want to do so I offer another way to illustrate another point.

 

Names Default to Here( 1 );

// open example
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );

// make illustrative example with colums of different sizes (missing values)
dt2 = dt1 << Split(
	Split By( :age ),
	Split( :weight ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

Close( dt1, No Save );

// input data table as matrix
mat = dt2 << Get As Matrix;

// replace missing values with zero
mat[Loc(Is Missing(mat))] = 0;

// see result
Show( mat );

Hope it helps.

dg1
dg1
Level III

Re: Replacing Matrix Values

Thank you for your help! Can I use the Get As Matrix function to put the data into a matrix with specifications? I need the data from the column to go into a 8 by 12 matrix.

Re: Replacing Matrix Values

Are your data columns for the results from a micro-titre plate?

 

You want to re-shape the data in each column into an 8x12 table. Your script will only keep the table for the last column. Here is a script that process every data column and stores the table result with the column name in an associative array.

 

Names Default to Here( 1 );

dt = Open( "Example.jmp" );

table = Associative Array();

For( c = 1, c <= N Col( dt ), c++,
	data = Column( dt, c ) << Get As Matrix;
	data[Loc(Is Missing( data ))] = 0;
	table[Column( dt, c ) << Get Name] = Transpose( Shape( data, 12, 8 ) );
);

Show( table << Get Contents );

 

I assume that you have at least 96 rows. If there are more, they are lost. If there are less, then the assignment of 0 should be changed to a concatenation that pads with 0. This version pads instead:

 

Names Default to Here( 1 );

dt = Open( "Example.jmp" );

table = Associative Array();

For( c = 1, c <= N Col( dt ), c++,
	data = Column( dt, c ) << Get As Matrix;
	If( N Row( data ) < 96,
		data |/= J( 96 - N Row( data ), 1, 0 );
	)
	table[Column( dt, c ) << Get Name] = Transpose( Shape( data, 12, 8 ) );
);

Show( table << Get Contents );
dg1
dg1
Level III

Re: Replacing Matrix Values

Thank you!! This works well. However, the column name is not stored. Do you know how I could fix that?

Re: Replacing Matrix Values

The column names are stored in the associative array. They are the keys. You can verify this claim by adding this to the script:

 

Show( table << Get Keys );

I suggest that you see Help > Books > Scripting Guide, then search for 'associative array.' You can iterate over the pairs and do many other tasks easily. The examples in the guide are good, but return here if you have any questions.

txnelson
Super User

Re: Replacing Matrix Values

I have been following this discussion from the beginning.  It seems to have evolved into a scripting exercise, without exposing what is the real end goal.  @Mark_Bailey has provided a nice solution with his use of an Associative Array, but what I see missing, is what is the real reason for creating these matricies?  If that was known, there might be a simpler solution.

Jim
dg1
dg1
Level III

Re: Replacing Matrix Values

I apologize for the confusion. I am trying to store each of my column data into a format that is 8 by 12 ( which is the size of micro-titre plate with 96 wells), in order to store the results of my experiments in correlation to its location on the plate. Each column represents a different experiment.