Subscribe Bookmark RSS Feed

Sorting JMP data table and deleting rows in JMP Script

sudipta_bera

Community Member

Joined:

Jul 10, 2014

Hi:

I'm a rookie with JMP. I want to sort and format a data table based on conditions on first two columns. The first column is a string of text ("LaserID" ) and the second column is time when data is taken ("time"). My goal is to to derive another table with exact same number of columns as the original table so that there is only one row of data of a particular "LaserID" (i.e. no repeat). The elimination of multiple row(s) with same "LaserID" will be based on when the data is taken ("time").

In another word, the new table will consist of row with a unique "LaserID" with data taken at the latest time.

I am attaching the Original and Final table I want to achieve. Any help in writing a script for this task will be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
David_Burnham

Super User

Joined:

Jul 13, 2011

Solution

Something like this should do it:

Get Latest Row = Function({dt,ID},{Default Local},

      // this can be much simpler if we assume datetime order!

      //get only those rows matching the specified laser ID

      lstRows = dt << Get Rows Where(:Laser ID == ID);

     

      //get the corresponding times as a list

      lstTimes = Column(dt,"Time")[lstRows];

     

      // locate the maximum time

      loc = Loc Max(lstTimes);

     

      // return the row containing the maximum time

      lstRows[loc]

     

);

//source data table

dt = Data Table("Initial");

// get list of unique values for Laser ID

Summarize(lstIDs = By(:Laser ID));

// create empty final table

newDt = dt << Subset(Copy Formulas(0));

newDt << Select All Rows;

newDt << Delete Rows;

newDt << Set Name("Final");

// create a row for each result

Column(newDt,"Laser ID") << Set Values(lstIDs);

//iterate through each ID to find the row with the latest date

For (i=1,i<=NItems(lstIDs),i++,

      currentID = lstIDs[i];

      r = Get Latest Row(dt,currentID); 

      show(r);

      //place values from r'th row into new data table

      For (c=1,c<=NCols(dt),c++,

            Column(newDt,c)[i] = Column(dt,c)[r]

      )   

);

-Dave
3 REPLIES
David_Burnham

Super User

Joined:

Jul 13, 2011

Solution

Something like this should do it:

Get Latest Row = Function({dt,ID},{Default Local},

      // this can be much simpler if we assume datetime order!

      //get only those rows matching the specified laser ID

      lstRows = dt << Get Rows Where(:Laser ID == ID);

     

      //get the corresponding times as a list

      lstTimes = Column(dt,"Time")[lstRows];

     

      // locate the maximum time

      loc = Loc Max(lstTimes);

     

      // return the row containing the maximum time

      lstRows[loc]

     

);

//source data table

dt = Data Table("Initial");

// get list of unique values for Laser ID

Summarize(lstIDs = By(:Laser ID));

// create empty final table

newDt = dt << Subset(Copy Formulas(0));

newDt << Select All Rows;

newDt << Delete Rows;

newDt << Set Name("Final");

// create a row for each result

Column(newDt,"Laser ID") << Set Values(lstIDs);

//iterate through each ID to find the row with the latest date

For (i=1,i<=NItems(lstIDs),i++,

      currentID = lstIDs[i];

      r = Get Latest Row(dt,currentID); 

      show(r);

      //place values from r'th row into new data table

      For (c=1,c<=NCols(dt),c++,

            Column(newDt,c)[i] = Column(dt,c)[r]

      )   

);

-Dave
pmroz

Super User

Joined:

Jun 23, 2011

Here's an alternate approach that uses tabulate with max, and then update:

dt1 = Data Table( "Initial" );

tab1 = dt1 << Tabulate(

    Show Control Panel( 0 ),

    Add Table(

        Column Table( Analysis Columns( :time ), Statistics( Max ) ),

        Row Table( Grouping Columns( :LaserID ) )

    ), invisible

);

   

dt2 = tab1 << Make Into Data Table;

tab1 << close window();

dt2 << Update(

    With( dt1 ),

    Match Columns( :LaserID = :LaserID, :Name( "Max(time)" ) = :time ),

    Add Columns from Update table(

        :PRF,

        :Name( "Power(W)" ),

        :Name( "Pmon(W)" ),

        :PRF2,

        :Name( "Power2(W)" ),

        :Name( "Pmon2(W)" )

    )

);

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

You've gotten a pair of scripting responses. If you'd like to do this interactively, try these steps.

Sort the table by LaserID and Time.

7004_JMPScreenSnapz004.png

Then create a new column with a formula to select the rows where the LaserID in the current row doesn't match the LaserID in the next row. Since the table is sorted by LaserID and Time you'll get the last row of each LaserID series.

The trick here is to use a column with a data type of Row State and the Selected State state function in the formula.

Choose Cols -> New Column... and set the data type to Row State.

7005_JMPScreenSnapz005.png

Then add a Formula.

7006_JMPScreenSnapz006.png

For the formula, start with Row State -> Selected State.

7007_JMPScreenSnapz007.png

Then put the comparison inside the Selected State() function. This comparison uses the Lag() function with a -1 argument to compare with the value of LaserID in the next row.

7008_JMPScreenSnapz008.png

Now you just need to copy the row states in your new column to the active row state area by clicking on the star icon in front of the column name.

7009_JMPScreenSnapz009.png

Now you can use Tables->Subset to subset the selected rows.

7010_JMPScreenSnapz010.png

I hope that helps!

-Jeff

-Jeff