53255_en-US

liliconcom

Importing Data from Plate Readers: Unfortunate Format #1: "Multiple Boxes"

I'm not sure what the most proper name for the format of data is, but I refer to it as, "multiple boxes".  In this format each reading of the plate is output into a grid. or box with the row and column label, along with the read number. Each box is separated by one empty row.

There is also an assumption that the raw data starts at A:1, that is, in Excel on the first row of the table in the first column of the table.

 

The data formatting goal is to convert this format into the minimum number of columns, where there is one variable per column, and one observation per row.  

To use the attached script, go into Excel, and select the whole page, and copy it (edit, copy). Then go in to JMP and run the script.

 

The script has some interesting parts, like it makes a table from the data saved to the clipboard.

All the rest of it is just basic formatting and logic to get the data into a useful shape.

 

 rev 2 note: 2nd version of the script works better

 

 

Names Default To Here( 1 );
dt = New Table( "raw data" );
dt << Bring Window To Front;
Main Menu( "paste" );

dt1 = Current Data Table() << Stack(
 columns(
  :Column 2,
  :Column 3,
  :Column 4,
  :Column 5,
  :Column 6,
  :Column 7,
  :Column 8,
  :Column 9,
  :Column 10,
  :Column 11,
  :Column 12,
  :Column 13
 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" )
);

dt1 = Current Data Table();

dt1 << New Column( "temp1", formula( If( Length( :Column 1, ) > 1, :Column 1, Lag( :temp1, 1 ) ) ) );

dt1 << New Column( "temp2", formula( If( :Column 1 != Lag( :Column 1, 1 ), 1, Lag( :temp2, 1 ) + 1 ) ) );

dt1 << New Column( "temp3", formula( If( :Column 1 == "" | Contains( :Column 1, ":" ) > 0, 1, 0 ) ) );

dt1 << New Column( "Well ID", formula( :Column 1 || Char( :temp2 ) ) );

dt1 << run formulas();

dt1:temp2 << Delete Formula;
dt1:temp3 << Delete Formula;
dt1:temp1 << set name( "Plate ID" );
:name("Well ID") << Delete Formula;
:name("Plate ID") << Delete Formula;

dt1 << Select Where( is missing (:Data) == 1 | is missing(:Column 1)==1);
dt1 << delete rows;

dt1:Column1 << set name( "Row ID" );
dt1:temp2 << set name( "Column ID" );

:Data<<SET selected=""></SET>

 

Most instruments will export plate reader data to an Excel file; however, the format of the data is generally difficult to format before starting analysis. This is an attempt to deal with one of those formats

Re: Importing Data from Plate Readers: Unfortunate Format #1: "Multiple Boxes"

@Octopus  - make sure you put commas in the right places when adding more columns to the list. It sounds like the comma is missing between 13 and 14.

Re: Importing Data from Plate Readers: Unfortunate Format #1: "Multiple Boxes"

Hello @Byron_JMP  I have data in the same format as you have shown but in 16x24 format (384w). I entered in to your script up to column 24, and ran the script. But the following dialog box showed up 

The namespace "Column 13" is not defined in access or evaluation of 'Column 13:Column 14' , :Column 13:Column 14/*###*/

This is my first time using JMP, and I have never written a script. Would you be able to share what is the error.

 

Thanks