Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How do I select data from a column between two emp...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 14, 2009 3:11 PM
(976 views)

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 15, 2009 8:51 AM
(941 views)

Anybody?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 15, 2009 11:55 AM
(941 views)

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 15, 2009 2:24 PM
(941 views)

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 16, 2009 9:35 AM
(941 views)

Thanks, Jeff and mpb.

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 16, 2009 3:02 PM
(941 views)

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

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