topic Re: Reading particular data from excel in Discussions

Reading particular data from excel

rajanbista1 — Wed, 19 Oct 2016 03:00:29 GMT

Hello,

I am kind of new in JMP scripting. So, I would really appreciate if anybody can help me in this basic scripting steps.

I would like to read above data as following:

Read Test 1 (or Test 2) data for Lab A (or Lab B) and put it in the following format as an example:

Data for Lab A Test 1 = [2, 3, 4, 2, 1]

Data for Lab A Test 2 = [1, 3, 2, 4, 6]

and so on.

Is it possible to show the above data in the scripting window itself so that I could write more scrips to analyze the data (like mean, standard deviation etc)?

Thank you so much in advance.

Re: Reading particular data from excel

txnelson — Wed, 19 Oct 2016 03:01:16 GMT

Here is a script that takes your data table, and generates the 2 variables that you specified in your question.

Names Default To Here( 1 );

dt = Current Data Table();

dt << select where( :Lab == "A" );

Data for Lab A Test 1 = :Test 1[dt << get selected rows];

Data for Lab A Test 2 = :Test 2[dt << get selected rows];

Show( Data for Lab A Test 1, Data for Lab A Test 2 );

What I am curious about is your question about putting the above data into a script window. I am not sure where you are going with that? While you can certainly write scripts in JMP, much like you have to do in R or Python, doing most analyses in JMP are much simpler to just use the interactive items. You mention that you want to put the data into a script so you can analyze the data, like mean, std dev, etc. Getting such results using the Tabulate or Distribution platforms, where you just specify which columns to analyze seem to be what you should really be doing.

Re: Reading particular data from excel

ron_horne — Wed, 08 Jun 2016 10:51:22 GMT

hi rajanbista1,

Jim is right, much better to use built in platforms in jmp to produce the results.

otherwise, this is another option.

Names Default To Here( 1 );

dt = Current Data Table();

// more robust than selection

labArows = dt << get rows where ( dt:Lab == "A" );

labBrows = dt << get rows where ( dt:Lab == "B" );

show (labArows, labBrows);

// get the values for lab A for each test

Data for Lab A Test 1 = :Test 1[labArows];

Data for Lab A Test 2 = :Test 2[labArows];

show (Data for Lab A Test 2, Data for Lab A Test 1 );

// extract the values you want and give the a name to be used later

MeanlabAtest1 = mean (Data for Lab A Test 1 );

SDlabAtest1 = std dev (Data for Lab A Test 1 );

MeanlabAtest2 = mean (Data for Lab A Test 2 );

SDlabAtest2 = std dev (Data for Lab A Test 2 );

// just to show the numbers in the log.

show (MeanlabAtest1, SDlabAtest1, MeanlabAtest2, SDlabAtest2 );

Re: Reading particular data from excel

rajanbista1 — Wed, 08 Jun 2016 16:42:14 GMT

Hi Ron,

Thank you so much. It is exactly what I was looking for.

One more question, can I make a loop to calculate the results for both Lab A/Lab B and for both of test 1/test 2? It is easy to do it in Matlab but as I said I am still learning to write script in JMP. Also, if possible saving or exporting the final result in excel would be nice.

Thanks again for help.

Re: Reading particular data from excel

ron_horne — Wed, 08 Jun 2016 20:21:18 GMT

This is by far not best practice. Using this generically with more files may question robustness.

If you need to repeat this more than once do yourself a favor and get a copy of the book: "Jump into jmp scripting". Most academic libraries have it.

Otherwise try this.

Names Default To Here( 1 );

// define the data source

dt = Current Data Table();

//stack the data so all tests are in one column

dtstack = dt << Stack(

columns( :Test 1, :Test 2 ), // add to this all your tests columns

Source Label Column( "Label" ),

Stacked Data Column( "Data" )

);

// get the unique values for Lab

laboratry = Associative Array( dtstack:lab ) << get keys;

// get unique values for tests

test = Associative Array( dtstack:Label ) << get keys;

// define a results table

dt1 = New Table( "results",

New Column( "Lab", character, nominal ),

New Column( "Test", character, nominal ),

New Column( "Mean" ),

New Column( "SD" ),

);

// loop through labs ans tests in the stack data table to extract results.

For( i = 1, i <= N Items( laboratry ), i++,

For( it = 1, it <= N Items( test ), it++,

labtestrows = dtstack << get rows where( And( dtstack:Lab == laboratry[i], dtstack:Label == test[it] ) );

dt1 << add rows( 1 );

dt1:Lab[N Row( dt1 )] = laboratry[i];

dt1:Test[N Row( dt1 )] = test[it];

dt1:Mean[N Row( dt1 )] = Meanlabtest1 = Mean( dtstack:Data[labtestrows] );

dt1:SD[N Row( dt1 )] = SDlabtest1 = Std Dev( dtstack:Data[labtestrows] );

)

);

// close the results table and save as excell

Close( dt1, save( "D:\My Documents\results.xlsx" ) );

Re: Reading particular data from excel

Mark_Bailey — Fri, 10 Jun 2016 16:24:37 GMT

So, to be clear, you do not have a problem getting the data from Excel. That is, it is already in a JMP data table, correct? If not, then consider accessing the workbook as if it were a database and use a SQL query to isolate the groups of data that you need.

If the data is already in JMP and arranged as shown at the top, then why can you use Tabulate to obtain the results that you want for each group?