Choose Language Hide Translation Bar
Highlighted
johnsingh
Level II

Delete rows based upon condition

I have a dataset with ~2000 rows and ~100 columns. For this question only two columns are relevant: Class and Property1. Both are continuous numerical variables.

 

Class goes from 0 to 280 and denotes the 'class' of each row. Each class has at least one entry (row); there could be multiple rows with the same class. Each row has a value for Property1.

 

Now, under each class i find that sometimes there are multiple rows with the same value of Property1. When this happens, i would like to delete/ (hide & exclude) all the rows with the same class and Property1 except one.

After doing this i would like to summarize the statistics for the Property1 under each class:things like mean, median, min, max, std deviation.

 

Is there a script i can use to do this? As you can probably guess i am a novice JMP scripter...

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Delete rows based upon condition

You do not need to script this.  It can be handled in just a couple of steps.

1.  Create a Summary data table using

     Tables==>Summary

     a. Select both Class and Property1 as "Group" columns

     b. Uncheck the "Link to original data table"

     c. Change the "statistics column nmae format" to "Column"

     d. Click OK

2. Using the new Summary table, Join it with the original table using

     Tables==>Join

     a. Select the original table as the "with" table

     b. Select Group from both tables and add it to the Match selection box

     c. Select Property1 from both data tables and add it to the Match selection box

     d. Check the "Drop multiples" for the With Table

     e. Check the "Merge same name columns 

      f. Click OK

This will give you your specified data table with only one row for each Class Property1 combination.

Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Delete rows based upon condition

You do not need to script this.  It can be handled in just a couple of steps.

1.  Create a Summary data table using

     Tables==>Summary

     a. Select both Class and Property1 as "Group" columns

     b. Uncheck the "Link to original data table"

     c. Change the "statistics column nmae format" to "Column"

     d. Click OK

2. Using the new Summary table, Join it with the original table using

     Tables==>Join

     a. Select the original table as the "with" table

     b. Select Group from both tables and add it to the Match selection box

     c. Select Property1 from both data tables and add it to the Match selection box

     d. Check the "Drop multiples" for the With Table

     e. Check the "Merge same name columns 

      f. Click OK

This will give you your specified data table with only one row for each Class Property1 combination.

Jim

View solution in original post

Highlighted
johnsingh
Level II

Re: Delete rows based upon condition

Thanks so much, Jim!

Article Labels

    There are no labels assigned to this post.