Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- JMP - Column that generates unique row numbers by comparing between existing row...

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 16, 2015 4:08 PM
(9664 views)

Greetings,

If I have the following sample data:

ID 1 Date 1

ID 1 Date 2

ID 1 Date 3

ID 2 Date 4

ID 2 Date 5

ID 2 Date 6

ID 2 Date 7

ID 3 Date 8

ID 3 Date 9

ID 3 Date 10

ID 3 Date 11

ID 3 Date 12

For a given row (aka unique ID and Date pair), how to create a new column to indicate this row is Date number X (of Y rows for each unique ID)?

ID 1 Date 1 1

ID 1 Date 2 2

ID 1 Date 3 3

ID 2 Date 4 1

ID 2 Date 5 2

ID 2 Date 6 3

ID 2 Date 7 4

ID 3 Date 8 1

ID 3 Date 9 2

ID 3 Date 10 3

ID 3 Date 11 4

ID 3 Date 12 5

For this small sample set, I can easily do manually. However, I do not know how to write a script using available JMP/jsl functions to compared between rows to do this for a large data set.

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Sep 17, 2015 8:47 AM
| Last Modified: Oct 18, 2016 5:39 PM
(12120 views)
| Posted in reply to message from sadhammer 09-16-2015

You could also use one of these formulas, **after using sort by (ID, Date).** One formulas counts the number of times there is a date (any date) for a given ID, the second one counts the number of times there is a **new** date for a given ID (see rows 12 to 14 in screenshot).

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, :Date# for that ID[Row() - 1,Empty()] + 1, If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, If(:Date[Row() - 1,Empty()] != :Date, :Unique Date# for that ID[Row() - 1,Empty()] + 1, :Unique Date# for that ID[Row() - 1,Empty()]), If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

5 REPLIES 5

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

There are several ways to do this. For example, copy the code below and paste it into the formula property of a new column. It's supposed to return the number of all instances of an ID up to the current row (the ID column is assumed to be named "ID").

Eval**(**Eval Expr**(**Col Sum**(**Row**()** <= Expr**(**Row**())**, :ID**)))**

For very big data tables there are probably faster approaches.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

Thanks so much!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Sep 17, 2015 8:47 AM
| Last Modified: Oct 18, 2016 5:39 PM
(12121 views)
| Posted in reply to message from sadhammer 09-16-2015

You could also use one of these formulas, **after using sort by (ID, Date).** One formulas counts the number of times there is a date (any date) for a given ID, the second one counts the number of times there is a **new** date for a given ID (see rows 12 to 14 in screenshot).

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, :Date# for that ID[Row() - 1,Empty()] + 1, If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, If(:Date[Row() - 1,Empty()] != :Date, :Unique Date# for that ID[Row() - 1,Empty()] + 1, :Unique Date# for that ID[Row() - 1,Empty()]), If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

Thanks so much!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

For large datasets you might try the following; assuming the first column is sorted as in your example, the expression below will work as a formula in a new column. It takes my machine about 2 seconds on a 1M row dataset.

(Row() - Col Min( Row(), :ID )) + 1

Cheers,

Brady

Article Labels

There are no labels assigned to this post.