topic Re: Replacing Matrix Values in Discussions

Replacing Matrix Values

dg1 — Wed, 10 Jul 2019 22:38:50 GMT

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

Re: Replacing Matrix Values

txnelson — Wed, 10 Jul 2019 23:53:56 GMT

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?

Re: Replacing Matrix Values

Mark_Bailey — Thu, 11 Jul 2019 00:57:37 GMT

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.

Re: Replacing Matrix Values

dg1 — Thu, 11 Jul 2019 16:32:41 GMT

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

dg1 — Thu, 11 Jul 2019 16:35:16 GMT

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

Mark_Bailey — Thu, 11 Jul 2019 17:25:51 GMT

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

Re: Replacing Matrix Values

dg1 — Thu, 11 Jul 2019 20:27:04 GMT

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

Re: Replacing Matrix Values

Mark_Bailey — Thu, 11 Jul 2019 20:35:39 GMT

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.

Re: Replacing Matrix Values

txnelson — Thu, 11 Jul 2019 20:46:37 GMT

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.

Re: Replacing Matrix Values

dg1 — Thu, 11 Jul 2019 21:00:10 GMT

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.

Re: Replacing Matrix Values

txnelson — Thu, 11 Jul 2019 21:43:24 GMT

So once you have the data into the matrices, how do you move them to the permanent locations?

Re: Replacing Matrix Values

dg1 — Thu, 11 Jul 2019 21:54:43 GMT

Thank you for your help and interest. Mark Bailey's associative array method has worked. Now I am trying to figure out how to put the associative array into a data table so I can export it as a CSV file.

Re: Replacing Matrix Values

txnelson — Fri, 12 Jul 2019 00:37:49 GMT

I may be a bit redundant, but here is an alternative way of handling this given the requirement to output to a csv file. It uses data table manipulation to get the data into the correct form, and then writes the data table to a csv file

Names Default To Here( 1 );

// Create some sample data
dt = New Table( "Example",
	add rows( 72 ),
	New Column( "c1", formula( Random Uniform( 1, 500 ) ) ),
	New Column( "c2", formula( Random Uniform( 1, 500 ) ) ),
	New Column( "c3", formula( Random Uniform( 1, 500 ) ) )
);
dt << run formulas;
dt:c1 << delete property( "formula" );
dt:c2 << delete property( "formula" );
dt:c3 << delete property( "formula" );

// Get the list of columns
colNamesList = dt << get column names( string, numeric );

// Loop across the columns and process them
For( i = 1, i <= N Cols( dt ), i++,
	// Create a new table will only the single column
	dtTemp = dt << subset(invisible, selected rows( 0 ), columns( Column( colNamesList[i] ) ) );
	// If there are not 96 rows, add them
	If( N Rows( dtTemp ) < 96,
		rowCount = N Rows( dt );
		dtTemp << add rows( 96 - rowCount );
		For( k = rowCount + 1, K <= 96, k++,
			Column( dtTemp, colNamesList[i] )[k] = 0
		);
	);
	// Create a sequence column to allow for proper splitting of the data
	dtTemp << New Column( "sequence",
		formula(
			x = Mod( Row(), 8 );
			If( x == 0, x = 8 );
			x;
		)
	);
	dtTemp << run formulas;
	
	// Split the data into 8 columns.....if this should be 12 just change the sequence formula above
	dtSplit = dtTemp << Split(invisible, Split By( :sequence ), Split( Column( dtTemp, colNamesList[i] ) ), Sort by Column Property );
	dtSplit << run formulas;
	
	// Close the un needed temporary data table
	Close( dtTemp, nosave );
	
	// Change the output preferences to get rid of headers
	current_pref = Char( Arg( Parse( (Char( Get Preferences( Export settings ) )) ), 1 ) );
	// Set prefs (comma delimited, no headers)
	Pref( Export Settings( End Of Field( Comma ), Export Table Headers( 0 ) ) );
	// Save csv file

	dtSplit << save( "$TEMP\" || colNamesList[i] || ".csv" );
	
	//Restore oribinal prefs
	Eval( Parse( "pref(" || current_pref || ")" ) );
	
	// Close the un necessary table
	Close( dtSplit, nosave );
	
);

Re: Replacing Matrix Values

Mark_Bailey — Fri, 12 Jul 2019 13:44:41 GMT

Please take a look at this post. You might find it useful in your work with experiments on plates.

Re: Replacing Matrix Values

Mark_Bailey — Fri, 12 Jul 2019 13:46:53 GMT

I suspect that as a new JMP user, your mindset might still be that of a spreadsheet user. I do not know the destination of the CSV file, but you can do much in JMP using a tall table layout.

Why do you need the 8x12 layout? Why do you need to export the matrices as CSV files?

We might be able to help you use JMP to your advantage if we knew where this analysis was going.

Re: Replacing Matrix Values

Mark_Bailey — Fri, 12 Jul 2019 13:49:20 GMT

See Help > Scripting Index. Click the drop down button and select Functions. Search for As Table() function.

Re: Replacing Matrix Values

dg1 — Fri, 12 Jul 2019 18:28:29 GMT

I need the layout for my setup and a csv file is the only type of file read by the device

Re: Replacing Matrix Values

Mark_Bailey — Fri, 12 Jul 2019 19:31:30 GMT

Ok, this updated version of the script should do the trick:

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

content = table << Get Contents;
For( t = 1, t <= N Items( content ), t++,
	dt = As Table( content[t][2] );
	Close( dt, Save( content[t][1] || ".CSV" ) );
);

Unfortunately, I do not know how to suppress the first line with the header names in the CSV files. Perhaps someone else knows. We could immediately read the file back as text, delete the first line, and save it out again.

Re: Replacing Matrix Values

dg1 — Fri, 12 Jul 2019 20:06:09 GMT

This worked!! However, I actually wanted to keep the the column names( or in this case content[t][1]) in the csv file as the first line. It currently does not show. Do you know how I can keep it in the first cell of the csv file?

Re: Replacing Matrix Values

Mark_Bailey — Mon, 15 Jul 2019 16:24:42 GMT

Please be patient. I am having difficulty with saving the CSV file for some unknown reason. I am working with Technical Support.

While I wait for them to reply, I want to be sure that you want to replace the first row of the CSV file (original data column headers) with just the name of the original data column name.