Subscribe Bookmark RSS Feed

Use If Then on Matrix

terapin

Community Trekker

Joined:

Jun 23, 2011

Hi Folks,

I have some instrument data that periodically exhibits large changes in the data due to readjusting the instrument.  I'm trying to create a new variable that does the following:  If (data - data[i-1] > 4)  or (data - data[i-1] < -4), Then adj_data = adj_data[i-1], Else, data = adj_data + (data - data[i-1]); Basically, if the current data value exceeds the previous value by 4 or -4, then make new adjusted value equal to previous adjusted value, Else, add difference between current and previous data value to adjusted value.

I've figured out how to read the data into a matrix and remove any missing data, but I'm not sure how to complete the math outlined above on the matrix.  Any suggestions on how to accomplish this would be appreciated.

Clear Log();

Names Default To Here( 1 );

dt = New Table( "Adjust for missing data",

  Add Rows( 24 ),

  Compress file when saved( 1 ),

  New Column( "Date",

  Numeric,

  Continuous,

  Format( "m/d/y", 19 ),

  Input Format( "m/d/y" ),

  Set Values(

  [3447619200, 3447705600, 3447792000, 3447878400, 3447964800,

  3448051200, 3448137600, 3448224000, 3448310400, 3448396800,

  3448483200, 3448569600, 3448656000, 3448742400, 3448828800,

  3448915200, 3449001600, 3449088000, 3449174400, 3449260800,

  3449347200, 3449433600, 3449520000, 3449606400]

  )

  ),

  New Column( "Data1",

  Numeric,

  Continuous,

  Format( "Best", 12 ),

  Set Values(

  [11, 13, 50, 15, ., 17, 18, -20, 20, ., 21, 22, ., 24, 25, 23, ., .,

  ., ., 25, 26, 27, 28]

  )

  ),

  New Column( "Adj Data1",

  Numeric,

  Continuous,

  Format( "Best", 12 ),

  Set Values(

  [0, 2, ., 4, ., 6, 7, ., 9, ., 10, 11, ., 13, 14, 12, ., ., ., ., 14,

  15, 16, 17]

  )

  )

);

//Put relevant data into matrix

date_time = :Name( "Date" ) << Get Values;

data = :Name( "Data1" ) << Get Values;

//Determine rows that only include data that aren't missing

row_list = dt << get rows where(

  And(

  !Is Missing( :Data1 ),

  Contains( data, :Data1 ),

  Contains( data, :Data1 ) == Contains( date_time, Format( :Date, "m-d-y" ) )

  )

);

// Converts row_list, currently a matrix [ ], to a list { }

Is List( row_list = As List( row_list ) );

date_time = date_time[Loc( !Is Missing( data ) )];

data = data[Loc( !Is Missing( data ) )];

8 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

I think this will do what you want.  I'm operating directly on your table, btw.

dt = data table("Adjust for missing data");

for (i = 2, i <= nrows(dt), i++,

    di   = column(dt, "Data1")[i];

    dim1 = column(dt, "Data1")[i-1];

    adji = column(dt, "Adj Data1")[i];

    if (!is missing(di) & !is missing(dim1),

        if (abs(di - dim1) > 4,

        // then

            column(dt, "Adj Data1")[i] = column(dt, "Adj Data1")[i-1];

            ,

        // else

            if (! is missing(adji),

                column(dt, "Data1")[i] = adji + (di - dim1);

            );

        );

    );

);

terapin

Community Trekker

Joined:

Jun 23, 2011

Thanks PMroz for the suggestion.  This will definitely work on the data in the table, but the table has some 500,000 records, and I was thinking that making the adjustments in a matrix would be quicker than dealing with the data table directly.  Do you have any suggestions on the syntax for using conditional commands on a matrix?  I've looked in the various JMP "books" but haven't found anything that clearly specifies the syntax.  Thanks.

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Matrices work like regular variables – more accurately called scalars – with most arithmetic and comparison operators.

For example:

x=[1,2,3,4];

y=[2,1,2,5];

x>y;

/*:

[0, 1, 1, 0]

As you can see I now have a matrix that indicates where the y matrix is greater than the x matrix.

But you're not limited to comparing one matrix to another. You can compare with a scalar as well.

x>2;

/*:

[0, 0, 1, 1]

I can also perform arithmetic operations on the entire matrix at once.

x-4;

/*:

[-3, -2, -1, 0]

You can index into a matrix to get only a portion of the matrix so you can easily compare one portion of the matrix to another.

y[2::nrow(y)]>y[1::nrow(y)-1];

/*:

[0, 1, 1]

You can also use functions on a matrix.

z=[1,5,1,-6];

abs(z[2::nrow(z)]-x[1::nrow(z)-1])>4;

/*:

[0, 0, 1]

Combining all of this you should be able to do what you're looking for with matrices.

However, a column formula in the data table would be more straightforward and probably just as efficient. I'll follow-up tomorrow with an example of that.

-Jeff

-Jeff
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

As I alluded yesterday, column formulas are, generally speaking, pretty efficient.

See the attached data table. The column New Adj Data has a formula that comes close to what you're looking for.

This is still a small dataset but it should still perform well with many rows.

-Jeff

-Jeff
terapin

Community Trekker

Joined:

Jun 23, 2011

Thanks Jeff for providing an example of a column formula, as well as the matrix examples. As you might imagine, my case is a bit more complicated and requires that I skip rows where the data are missing but carry forward the previous adjusted data value.  I'm able to accomplish this with the following JSL code, but can't figure out how to use local variables in a column formula.  Do you have any guidance on how I can accomplish the following JSL code using local variables in a column formula?  Thanks.

Clear Log();

lag_adj_data=0;

:adj_data1[1] = 0;

For( i = 2, i <= N Rows(dt1), i++,

  If( Abs( :data1 - :data1[i - 1] ) > 4,

  :adj_data1 = :adj_data1[i - 1],

  :adj_data1 = :adj_data1[i - 1] + (:data1 - :data1[i - 1] )

  );

  If( Is Missing ( :data1 ) & !Is Missing( :adj_data1[i-1] ),

  lag_adj_data = :adj_data1[i-1];

  );

  If( Is Missing ( :data1[i-1] ) & !Is Missing( :data1 ) ,

  :adj_data1 = lag_adj_data

  );

);

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Hi Terapin,

I'm afraid I'm having a hard time deciphering what you're looking to do based on the JSL you sent.

The data table that I attached above deals with the missing values in data1 without the need of a local variable in the formula. It checks to see if Data1 is missing in the current row or the previous row with the !Is Missing(:Data1 - Lag(:Data1,1)). If either are missing then the difference will be missing too.

Here's the formula in the New Adj Data column:

If(

  Row() == 1, 0,

  Abs( :Data1 - Lag( :Data1, 1 ) ) > 4 | Is Missing( :Data1 - Lag( :Data1, 1 ) ), Lag( :New Adj Data, 1 ),

  Lag( :New Adj Data, 1 ) + (:Data1 - Lag( :Data1, 1 ))

)

If this isn't what you need can you post a sample data table along with an explanation of exactly what you'd like the value for this new formula-based column to be in each row? That will help me understand exactly what you're looking for.

Thanks!

-Jeff

-Jeff
pmroz

Super User

Joined:

Jun 23, 2011

Do you have any guidance on how I can accomplish the following JSL code using local variables in a column formula?

If you are defining variables in a JSL program, and want to use them in a column formula, they need to be global variables.  Prefix the variables with "::" and you can use them in a column formula.  For example ::g_one_variable

terapin

Community Trekker

Joined:

Jun 23, 2011

Sorry Jeff I wasn't more clear about what I'm trying to accomplish.  I may have confused things by talking about both JSL code and column formulas.

The attached data file shows the "desired" output in :adj_data1 and the output from the column formula you provided in New Adj Data.  Note that the New Adj Data column inserts the previous New Adj Data value when :data1 is missing, even though the goal is to have New Adj Data show missing data when :data1 is missing. 

In order to create the :adj_data1 column, I was trying to adapt your column formula so that:

  1. When :data1 is missing, the previous New Adj Data value is placed into a local variable and carried forward until needed in step 2
  2. If current :data1 value is not missing & previous :data1 value is missing, then New Adj Data is set to current value list in local variable.

Where I'm getting hung up is in adding the New Adj Data value in step 1 to a local variable and then retrieving/using that value for step 2. 

Perhaps I'm making it more complicated than it needs to be, and you have an easy way to accomplish this.  I would certainly appreciate any help you can provide.  If your solution doesn't use a local variable in the column formula, I would also appreciate it if you could either provide a simple example of how use a local variable like I've described above, or point me to an existing JMP dataset that makes use of local variables in the column formula.  It seem like being able to successfully use local variables in column formulas would be very helpful.  Thanks again.