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

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

optflow — Wed, 16 Sep 2015 23:08:34 GMT

Greetings,

If I have the following sample data&colon;

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.

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

ms — Thu, 17 Sep 2015 00:26:36 GMT

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.

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

jvillaumie — Wed, 19 Oct 2016 00:39:34 GMT

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

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

optflow — Fri, 18 Sep 2015 20:53:18 GMT

Thanks so much!

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

optflow — Fri, 18 Sep 2015 20:54:23 GMT

Thanks so much!

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

brady_brady — Fri, 18 Sep 2015 21:16:24 GMT

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