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
- :
- Syntax: How do I create an index column by ID

Topic Options

- 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
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 7, 2014 6:35 AM
(4024 views)

Hello JMP Community,

I am trying to create a column that indexes rows by an ID column.

In other scripting language, this is accomplished with the following lines.

By ID: gen IDIndex = _n

By ID: gen IDCount = _N

I can do the following, but I index and count the whole table

New Column( "IDIndex",

formula(Row()));

New Column( "IDCount",

formula(NRow()));

Advice on this would be great!

Very Sincerely,

ID | IDIndex | IDCount |
---|---|---|

444 | 1 | 4 |

444 | 2 | 4 |

444 | 3 | 4 |

444 | 4 | 4 |

333 | 1 | 3 |

333 | 2 | 3 |

333 | 3 | 3 |

1 ACCEPTED SOLUTION

Accepted Solutions

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

Oct 7, 2014 7:25 AM
(5169 views)

Solution

In your formula you need to check the ID value to see if it has changed from the previous row and reset your index if it has.

Try this formula:

If(:ID != Lag(:ID, 1) | Row() == 1, 1, Lag(:IDIndex, 1) + 1)

Your ID Count column has use IDIndex to get its value:

Col Maximum( :IDIndex, :ID )

The second argument to the Col Maximum function is the By variable.

I hope that helps!

-Jeff

-Jeff

5 REPLIES

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

Oct 7, 2014 7:25 AM
(5170 views)

In your formula you need to check the ID value to see if it has changed from the previous row and reset your index if it has.

Try this formula:

If(:ID != Lag(:ID, 1) | Row() == 1, 1, Lag(:IDIndex, 1) + 1)

Your ID Count column has use IDIndex to get its value:

Col Maximum( :IDIndex, :ID )

The second argument to the Col Maximum function is the By variable.

I hope that helps!

-Jeff

-Jeff

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

Oct 7, 2014 7:40 AM
(3476 views)

Great, that worked perfectly!

Thanks for quick reply.

Have a great day,

Matt

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

Oct 7, 2014 7:54 AM
(3476 views)

Jeff is correct and i will only add that the formula for IDIndex depends on previous sorting.

If the table needs to be re-sorted later you would need to suppress evaluation or delete it.

column (dt, "IDIndex") << suppress eval ( true );

or:

column (dt, "IDIndex") << delete formula;

to produce the column IDCount without sorting you can use the table summery option:

dt << Summary (Group ( :ID );

ron

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

Oct 7, 2014 8:07 AM
(3476 views)

I think the below formula will work for IDIndex independently of the initial sorting of the ID column:

Sum( :ID[Index( 1, Row() )] == :ID )

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

Oct 7, 2014 8:59 AM
(3476 views)

Thanks MS, the command you shared does work independently of the sorting.

Have a great day!