topic Re: How to analyze the numerical order of repetetive values in rows? in Discussions
https://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68292#M34793
<P>Since the column Nr is in sorted order, ths can be done with a column formula. Before presenting the formula, you should look into Main Menu > Help > Scripting Index > Functions > Row or search for Lag. I created a column called Count and added this formula. By default the first row has a count of 1. The second condition :Nr == Lag( :Nr, 1) is the condition, if the current row is equal to the previous row, then take the previous count, Lag( :Count, 1 ) and increment it. Otherwise, the count is 1. </P><P> </P><PRE><CODE class=" language-jsl">If(
Row() == 1, 1,
:Nr == Lag( :Nr, 1 ), Lag( :Count, 1 ) + 1,
1
)</CODE></PRE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 999px;"><img src="https://kvoqx44227.i.lithium.com/t5/image/serverpage/image-id/12057i1B2AD802C4271F40/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span>Note JMP Lag works on text as well as numbers. However, if I had a column of text, I'd recommend modifying the function to be </P><PRE><CODE class=" language-jsl">If(
Row() == 1, 1,
Trim( Lowercase( :txtCol ) ) == Trim( Lowercase (Lag( :txtCol, 1 ) ) ) , Lag( :Count, 1 ) + 1,
1
)</CODE></PRE><P> Hope that helps.</P>Thu, 16 Aug 2018 10:27:36 GMTgzmorgan02018-08-16T10:27:36ZHow to analyze the numerical order of repetetive values in rows?
https://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68288#M34791
<P>Hi,</P><P>I am one of the few persons in Sweden who uses JMP whereas most of my academic collegues use SPSS. However, I love JMP and have published 70 articles in surgery with it so it works fine with for me. I am Associate Professor and would describe myself as an advanced JMP amateur that can programme somewhat however there is one issue that I am having problems with. I have posted this question previously but did not exactly understand the answers on the forum. The problem is as follows:</P><P>I have a file called <EM><STRONG>Results</STRONG></EM>.</P><P>In this file I have a column of <EM><STRONG>5700 numbers</STRONG></EM> that are sorted in order like:</P><P><EM><STRONG>Nr</STRONG></EM></P><P>3456</P><P>3666</P><P>3687</P><P>3687</P><P>3687</P><P>5444</P><P>5444</P><P>5445</P><P>I would appreciate information on how I should write a script or if I should write anything in an extra column in order to get the order of repetetive appearances of the numbers. The reason is that I am only interested in the first nr. and if I can get the order I could make a separate file consisting of only order 1.</P><P>like</P><P><EM><STRONG>Nr Order</STRONG></EM></P><P>3456 1</P><P>3666 1</P><P>3687 1</P><P>3687 2</P><P>3687 3</P><P>5444 1</P><P>5444 2</P><P>5445 1</P><P>Since I am an amateur I would appreciate if you could write the answer in a simple way.</P><P> </P><P>Sincerely yours</P><P>Lars Enochsson</P><P>Associate Professor</P><P>UmeĆ„ University</P><P>Sweden</P><P> </P>Thu, 16 Aug 2018 09:58:24 GMThttps://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68288#M34791LarsBirger2018-08-16T09:58:24ZRe: How to analyze the numerical order of repetetive values in rows?
https://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68292#M34793
<P>Since the column Nr is in sorted order, ths can be done with a column formula. Before presenting the formula, you should look into Main Menu > Help > Scripting Index > Functions > Row or search for Lag. I created a column called Count and added this formula. By default the first row has a count of 1. The second condition :Nr == Lag( :Nr, 1) is the condition, if the current row is equal to the previous row, then take the previous count, Lag( :Count, 1 ) and increment it. Otherwise, the count is 1. </P><P> </P><PRE><CODE class=" language-jsl">If(
Row() == 1, 1,
:Nr == Lag( :Nr, 1 ), Lag( :Count, 1 ) + 1,
1
)</CODE></PRE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 999px;"><img src="https://kvoqx44227.i.lithium.com/t5/image/serverpage/image-id/12057i1B2AD802C4271F40/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span>Note JMP Lag works on text as well as numbers. However, if I had a column of text, I'd recommend modifying the function to be </P><PRE><CODE class=" language-jsl">If(
Row() == 1, 1,
Trim( Lowercase( :txtCol ) ) == Trim( Lowercase (Lag( :txtCol, 1 ) ) ) , Lag( :Count, 1 ) + 1,
1
)</CODE></PRE><P> Hope that helps.</P>Thu, 16 Aug 2018 10:27:36 GMThttps://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68292#M34793gzmorgan02018-08-16T10:27:36ZRe: How to analyze the numerical order of repetetive values in rows?
https://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68293#M34794
<P>Try a column formula with the <EM>Col Rank()</EM> function with a by-variable It may require JMP 13 or later, not sure when support for by-variables in ColRank first was introduced.</P>
<P> </P>
<P>Try the script below. It should give the same result as the previous solution. However, it does not depend on sorting.</P>
<P> </P>
<PRE><CODE class=" language-jsl">Current Data Table() << New Column("Order", Formula(Col Rank(:Nr, :Nr)));</CODE></PRE>
<P>(Yet another JMP user in Sweden)</P>Thu, 16 Aug 2018 11:02:41 GMThttps://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68293#M34794ms2018-08-16T11:02:41ZRe: How to analyze the numerical order of repetetive values in rows?
https://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68295#M34796
<P>Sweet! ColRank() with a by variable was not available until JMP 13.</P><P> </P><P>As an FYI, here is a script to check performance. If there were 1,000,000 or more rows, I'd probably sort and use lags. </P><PRE><CODE class=" language-jsl">Names Default to Here(1);
dt = New Table("Test It", add rows(6000),
New Column("Nr", numeric, continuous,<<set each value(Random Integer(1,2500)) )
);
t0 = hptime();
dt << New Column("Count", <<Formula(ColRank(:Nr, :Nr)));
t1 = hptime();
dt:Count << delete formula;
t2 = hptime();
dt << Sort( By(:Nr), Order( Ascending ), Replace Table);
t3 = hptime();
t4 = hptime();
dt << New Column("Count2", <<Formula(If(
Row() == 1, 1,
:Nr == Lag( :Nr, 1 ), Lag( :Count, 1 ) + 1,
1
)));
t5 = hptime();
show(t1-t0, t3-t2, t5-t4); //Col Rank(), Sort(), using Lag()<BR /><BR /></CODE></PRE><P> For 6000 rows, using Col Rank() = 3.13*using lags<BR /> t1 - t0 = 18836;<BR /> t3 - t2 = 1863;<BR /> t5 - t4 = 4161;<BR />For 1,000,000 rows, using Col Rank() = 2.45*using lags<BR /> t1 - t0 = 1521679;<BR /> t3 - t2 = 694813;<BR /> t5 - t4 = 621142;<BR />*/</P>Thu, 16 Aug 2018 11:38:39 GMThttps://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68295#M34796gzmorgan02018-08-16T11:38:39ZRe: How to analyze the numerical order of repetetive values in rows?
https://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68297#M34797
Thanks. Excellent help. This solved the problem./LarsThu, 16 Aug 2018 11:58:54 GMThttps://community.jmp.com/t5/Discussions/How-to-analyze-the-numerical-order-of-repetetive-values-in-rows/m-p/68297#M34797LarsBirger2018-08-16T11:58:54Z