Subscribe Bookmark RSS Feed

How do I select data from a column between two empty rows in JMP?

Hi Guys,

I am trying to select data from a column between two empty rows and paste it another row.(i.e. transpose it). Here is what I have:
----------------------------
Column 1
(empty row)
(empty row)
3
4
5
6
7
8
9
(empty row)
(empty row)
3
4
5
(empty row)
(empty row)
34
6
7
8
(empty row)
(empty row)
---------------------

The number of data points between two sets of empty rows is variable. So here is the output I would like to have:
ROW1: 3 4 5 6 7 8 9
ROW2: 3 4 5
ROW3: 34 6 7 8

I am a newbie and any help is appreciated.
Thanks.
5 REPLIES
Anybody?
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Hi there,

Thanks for showing both what you have and what you want. That helps to figure out how to do it.

I started by creating a new column with a formula that would show which column that row should end up in in a new table.

Here's the formula for that new column:

If( Is Missing( :Column 1 ),
.,
If( Is Missing( Lag( :Column 1, 1 ) ),
1,
Lag( :Column 2, 1 ) + 1
)
)

Here's the table that results:

Column 1 Column 2
(empty row)
(empty row)
3 1
4 2
5 3
6 4
7 5
8 6
9 7
(empty row)
(empty row)
3 1
4 2
5 3
(empty row)
(empty row)
34 1
6 2
7 3
8 4
(empty row)
(empty row)


Now, with this table you can use the Tables->Split command to create the table that you want.

Use Column 1 as the Split Column and Column 2 as the Split By column. You'll end up with your second table.

Jeff Perkinson
JMP Product Manager
-Jeff
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Forum user mpb pointed out to me privately that my solution didn't actually work.

He's right. The groups of non-missing values don't end up in the same rows without a Grouping column.

So, you'll need another new column - call it Group - to identify groups of non-missing values. Here's a formula:

If(Row() == 1, 1, If(Is Missing(Lag(:Column 1, 1)) & !Is Missing(:Column 1), Lag(:Group, 1) + 1, Lag(:Group, 1)))

You can use this as the Group column in the Split dialog and you'll get what you want.

However, mpb also points out that once you have this Group column you can use Tables ->Transpose with Column 1 in the Transpose Columns role and Group in the By role. Which means that you don't need the column from my first solution at all.

Sorry for the confusion.

Jeff
-Jeff
Thanks, Jeff and mpb.

I will try your suggestions and see if they work for me.
ms

Super User

Joined:

Jun 23, 2011

If you are grep savvy this particular problem may be easier to solve using a text editor.
Paste your data in a text editor
Find: (\d+)\r
Replace with: \1\t
Now you have your rows (tab separated)
To get rid of empty rows (and the dots representing missing values in jmp in case of continuous variables)
Find: \t\.*\r\.*
Replace with: nothing
Paste back into a new jmp data table.
However the trick given above should work as well.

(syntax may vary with grep dist)

Message was edited by: MS