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);

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Replacing Matrix Values

dg1

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");<BR />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);

Accepted answers

Mark_Bailey

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 );

All comments

txnelson

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?

Mark_Bailey

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

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?

dg1

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.

Mark_Bailey

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

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 );

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

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

Mark_Bailey

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

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.