Subscribe Bookmark RSS Feed

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

sadhammer

Community Trekker

Joined:

Sep 14, 2013

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
Solution

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())))

9883_JMP forum.png

5 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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.

sadhammer

Community Trekker

Joined:

Sep 14, 2013

Thanks so much!

Solution

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())))

9883_JMP forum.png

sadhammer

Community Trekker

Joined:

Sep 14, 2013

Thanks so much!

brady_brady

Staff

Joined:

Jun 9, 2012

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