Replacing Matrix ValuesHi! 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></GETVALUES>Re: Replacing Matrix ValuesThank you! this works perfectlyRe: Replacing Matrix ValuesI don't have your example so I had to use one of the JMP sample data tables. Replace those lines for the initial data table with your own.
Names Default to Here( 1 );
// use sample data to illustrate
dt = Open( "$SAMPLE_DATA/Fitness.jmp" );
// delete non-numeric data columns
dt << Delete Columns( { :Name, :Sex } );
// make empty container
table = Associative Array();
// get each data column and create (key, value) pair (key = col name, value = 8x12 matrix)
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 ) );
);
Close( dt, No Save );
dir = Get Default Directory();
content = table << Get Contents;
For( t = 1, t <= N Items( content ), t++,
dt = As Table( content[t][2] );
file path = dir || content[t][1] || ".CSV";
dt << Save( file path );
Close( dt, No Save );
text = Load Text File( file path );
eol = Contains( text, "\!n" );
text = Munger( text, 1, eol - 1, content[t][1] );
Save Text File( file path, text );
);Re: Replacing Matrix ValuesThank you so much for your help. I appreciate it. I was able to save the file using your code, but currently the first row of the csv file is the first line of data, there is no column name. I would like to have the first cell to have the name of the column and right below that the 8 by 12 data.Re: Replacing Matrix ValuesPlease 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.
Re: Replacing Matrix ValuesThis 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 ValuesOk, 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 ValuesI need the layout for my setup and a csv file is the only type of file read by the deviceRe: Replacing Matrix ValuesSee Help > Scripting Index. Click the drop down button and select Functions. Search for As Table() function.
Re: Replacing Matrix ValuesI 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 ValuesPlease take a look at this post. You might find it useful in your work with experiments on plates.
Re: Replacing Matrix ValuesI 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 ValuesThank 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 ValuesSo once you have the data into the matrices, how do you move them to the permanent locations?Re: Replacing Matrix ValuesI 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 ValuesI 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 ValuesThe 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 ValuesThank you!! This works well. However, the column name is not stored. Do you know how I could fix that?Re: Replacing Matrix ValuesAre 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 ValuesThank 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 Valuesthanks 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 ValuesJim 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 ValuesI 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?
