topic Re: How to select every other and third row? in Discussions

How to select every other and third row?

mmcclusk0 — Fri, 08 Jul 2016 18:07:42 GMT

Hello,

I am a new user, so please excuse me if this seems like an easy question to answer.

I have a data set (21k+ rows) where I want to select the 2nd and 3rd row, unselect the 4th, then select the 5th and 6th, unselect the 7th....so on...

After they are all selected, they can be deleted, as I don't need the data in those rows.

What would be the easiest way to do this?

Thank you very much.

-Matt

Re: How to select every other and third row?

ms — Fri, 08 Jul 2016 18:40:12 GMT

One way to do it: Create a new numeric column and type 1, 2, 3 in the first three rows. Then select these rows and right click, select Fill -> Repeat Sequence to End of Table. Finally select both a "2" and a "3", right click to select Select matching Cells. Now every other and third row should be selected and ready to be deleted.

Re: How to select every other and third row?

ron_horne — Fri, 08 Jul 2016 18:42:53 GMT

hi mmcclusk0,

perhaps once basic way of doing this is:

Names Default To Here (1);

dt = Open( "$SAMPLE_DATA/Hurricanes.jmp" );

dt << new column ("selection", formula (sequence (1,3,1 )));

dt:selection << delete formula;

// select each second and third row

selectedrows = dt << select where (:selection >1 );

dt << delete rows ();

Re: How to select every other and third row?

Jeff_Perkinson — Wed, 19 Oct 2016 03:21:11 GMT

This is a fun question because there are probably lots of strategies for doing this. I'll look forward to seeing other suggestions.

Assuming you want to do this interactively (as opposed to automating with JSL to do it again later) here are a couple ways.

1) Add a new numeric column. Type 1, 2, and 3 in the first three rows of your table. Then select those cells and right click on them and choose Repeat sequence to end of table.

This will create a column with a repeating 1, 2, 3. You now need to select the rows with a 2 or a 3 in them. This is easy. Select any "2" cell and any "3" cell (shift-click the second one if you need to or drag select two side by side). Right click on one of the selected cells and choose Select Matching Cells.

This will select all of the "2" rows and all of the "3" rows. Now choose Rows -> Delete Rows.

2) You could create a Row State column with a formula using the Selected State function to select every 2nd and 3rd row.

Notice that in the Rows to Delete column every 2nd and 3rd row is selected. I did this with the Modulo() function.

Now right click on the star next to the Rows to Delete column and choose Copy to Row States to select those rows in the active row state area. Now you can choose Rows -> Delete Rows.

I'm sure the Community has other ways to do this. It'll be interesting to see them.

-Jeff

Re: How to select every other and third row?

mmcclusk0 — Fri, 08 Jul 2016 19:43:58 GMT

Thanks everyone for the quick and easy answers.

I did end up using the added sequence numbers and selecting and deleting as needed.

-Matt

Re: How to select every other and third row?

Daniel_Valente — Fri, 08 Jul 2016 21:08:00 GMT

Ha! ron_horne beat me to the "select where" strategy. This is one of my favorite things about JMP, though. You can solve problems like this in a way that your mind likes to work. If you are a scripting person, there is a good strategy here, and if you want to do it interactively, jeff.perkinson's ways are good too.

Re: How to select every other and third row?

ms — Sun, 10 Jul 2016 23:09:55 GMT

Yet another way to do this. No requirements for adding an extra column or selecting rows:

dt = Current Data Table();

dt << delete rows(dt << get rows where(Mod(Row() + 2, 3)));