topic Re: Extracting data from Graph/Data Table in Discussions

Extracting data from Graph/Data Table

bhosse0 — Wed, 19 Oct 2016 01:50:46 GMT

I have a data table that plots the below. I need to find the corresponding x values for the points that are nearest to y=0. I need the first two points (red lines) that cross (nearest) zero, the third crossing is not of interest.

I can find the closest value using the script:

value = 0;

d = Abs((:"col" << get values) - value);

rows = Loc(d == Min(d));

Show(rows);

Data Table( "DT" ) << select rows(rows);

This returns the value that is closest to the zero line regardless of when it crosses.

These two x-values will go into the dt as a new column and used for further calculations. Am I on the right track with the above script?

BTW, I'm new to JMP scripting (and to programming in general).

Thanks!

Re: Extracting data from Graph/Data Table

txnelson — Wed, 10 Feb 2016 02:14:08 GMT

I think the way that you need to approach this is to pass through the data, and then find the first and second time the data cross zero. When the data crosses 0, then find which of the values is the closest to zero and then record the value and the row. Then repeat for the second occurrence, and then stop reading. Finally select the found rows. Here is a simple script that does exactly what I described above:

Names Default To Here( 1 );

dt = Current Data Table();

FirstMin = .;

SecondMin = .;

For( i = 1, i <= N Rows( dt ) - 1, i++,

If( (:y >= 0 & :y[i + 1] <= 0) | (:y <= 0 & :y[i + 1] >= 0),

If( Abs( :y ) < Abs( :y[i + 1] ),

min = :y;

minrow = I;

min = :y[i + 1];

minrow = i + 1;

);

If( Is Missing( FirstMin ),

FirstMin = min;

FirstRow = minrow;

SecondMin = min;

SecondRow = minrow;

Break();

);

selected(rowstate(Firstrow))=1;

selected(rowstate(Secondrow))=1;

Re: Extracting data from Graph/Data Table

bhosse0 — Thu, 11 Feb 2016 13:21:49 GMT

Thanks Jim!

This is exactly what I needed and works as you described in your post. I'm now working to incorporate the collected data into further analysis scripting.

Thanks again.

Re: Extracting data from Graph/Data Table

bhosse0 — Thu, 11 Feb 2016 19:13:52 GMT

Another question on getting this script to step through a table with many parts. I have 3 columns in the data table; Part ID, Step, Value. There are many different parts and each one has several hundred steps. I want to get the two zero crossings, as above, for each unique Part ID in table. Not sure the best approach on how to get unique Part ID and insert into the above script to get the two needed values for each Part ID?

Re: Extracting data from Graph/Data Table

txnelson — Fri, 12 Feb 2016 11:23:49 GMT

Here is a rework of the code to incorporate Part ID into the mix

Names Default To Here( 1 );

dt = Current Data Table();

dtout = New Table( "Outputs",

New Column( "Part ID", character ),

New Column( "FirstMin" ),

New Column( "SecondMin" )

);

Current Data Table(dt);

For( i = 1, i <= N Rows( dt ) - 1, i++,

// At the start of the looping through the data and when the Part ID

// changes, the FirstMin and SecondMin values need to be set to

// missing values

If( i == 1,

FirstMin = .;

SecondMin = .;,

dt:Part ID != dt:Part ID[i - 1],

FirstMin = .;

SecondMin = .;

);

// If a value is found crossing zero, process the data

If( (DT:y >= 0 & DT:y[i + 1] <= 0) | (DT:y <= 0 & DT:y[i + 1] >= 0),

If( Abs( DT:y ) < Abs( DT:y[i + 1] ),

min = DT:y;

minrow = I;

min = DT:y[i + 1];

minrow = i + 1;

);

// If this is the frist crossing, set the values for firstMin

If( Is Missing( FirstMin ),

FirstMin = min;

FirstRow = minrow;,

// If this is the second crossing, set the values for SecondMin

// and also select the rows in the input data table

// and finally output first and second results to the output

// data table

// Because we are only collecting data for the first 2 crossings

// and are using the FirstMin and SecondMin values being

// missing as triggers in the script, once both First and Second

// minimums are set to actual values, all subsequent crossings

// will be ignored, until a Part ID change is detected

Is Missing( SecondMin ),

SecondMin = min;

SecondRow = minrow;

// Point back to the input data table to select the rows

current data table(dt);

Selected( Row State( Firstrow ) ) = 1;

Selected( Row State( Secondrow ) ) = 1;

dtout << add rows( 1 );

dtout:Part ID[N Rows( dtout )] = dt:Part ID[SecondRow];

dtout:FirstMin[N Rows(dtout)] = FirstMin;

dtout:SecondMin[N Rows(dtout)] = SecondMin;

);

Re: Extracting data from Graph/Data Table

bhosse0 — Fri, 12 Feb 2016 19:42:40 GMT

Thanks Jim!

This works great.

Re: Extracting data from Graph/Data Table

bhosse0 — Wed, 19 Oct 2016 01:53:32 GMT

I have another question. I have another parameter I am collecting from my data set and have created a summary table that finds the max value for that parameter. From there I take half that value and record this in a new column ("half value"). I would like to use the half value in the script to get the corresponding x values for each Part ID:

Each Part ID will have a different half value. The above is the section of the script where the half value needs to be inserted to obtain the corresponding step value. I'm having trouble getting the half value into the script properly and indexing through each Part ID and it's associated half value. What's the best way to insert the correct half value into the script for each Part ID?

Re: Extracting data from Graph/Data Table

txnelson — Mon, 15 Feb 2016 01:03:58 GMT

If I am interpreting correctly what you are asking, I believe the easiest way to take your summary table and to do a Tables==>Join matching on Part ID. It will add the Max value to each row for the appropriate Part ID

Re: Extracting data from Graph/Data Table

bhosse0 — Wed, 19 Oct 2016 01:53:42 GMT

Since I'm taking half the value of the max there is no corresponding data value in the data table to match and get the x value. I have done this to get the x value that goes with the max, but now the is not a value that matches since it is a calculated value. I am trying to use the half value as the value to extract the corresponding x values. Similar to the above script where it gets the values that cross at zero, I need to get the values that are closest to the calculated half value. I was trying set the half value as a variable for each Part ID and step through each Part ID and get the values closest to the half value:

This would work simlar to the script above that get the values nearest zero except it would get the values nearest the half values which is calculated for each Part ID.

Re: Extracting data from Graph/Data Table

txnelson — Mon, 15 Feb 2016 04:13:10 GMT

If you take the data from the summary table that you said you have calculated and perform a Tables==>Join that data to your original data table, then you can reference in your script. Or if you manually add the data in, that will work to. So if the column that is called "Half Value" there will be a value for "Half Value" for each row, then you can change the "0"s to

Half Value and Half Value[I+1]

If( (dt:y >= dt:Half Value & dt:y[I+1]<=dt:Half Value[I+1]......................