cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
KevinS
Level I

How do I count changes in row values?

I have a large data set where I have blocks of repeating values like this:

A

A

B

A

C

C

C

I would like to count the number of changes in row values or, in other words, a count of rows ignoring consecutive repeated rows.  In this example, the count would be 4 (The third 'A' is a change from 'B', so it counts).  Is there a way to have JMP count rows ignoring repeated and consecutive duplicates?

I'm using JMP 15.2.1.

 

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How do I count changes in row values?

One way would be to create formula using Lag and then you can get max value of that column

If(
	Row() == 1, 
		counter = 1;
	, Lag(:col, 1) != :col, 
		counter++
);
counter;

jthi_0-1647639220121.png

 

 

Maybe something like this could also work:

 

Col Cumulative Sum(Lag(:col) != :col)

 

 

 

-Jarmo

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: How do I count changes in row values?

Here are a couple of ways to handle this

txnelson_0-1647639727269.png

Names Default To Here( 1 );
dt = Current Data Table();

For Each Row(
	If(
		Row() == 1, count = 1,
		:column 1 != Lag( :column 1 ), count
		++)
);
Show( count );
// Look in the log for the value of count

// or

dt << New Column( "The Count",
	formula(
		If(
			Row() == 1, count = 1,
			:column 1 != Lag( :column 1 ), count
			++);
			count;
	)
);

 

Jim
jthi
Super User

Re: How do I count changes in row values?

One way would be to create formula using Lag and then you can get max value of that column

If(
	Row() == 1, 
		counter = 1;
	, Lag(:col, 1) != :col, 
		counter++
);
counter;

jthi_0-1647639220121.png

 

 

Maybe something like this could also work:

 

Col Cumulative Sum(Lag(:col) != :col)

 

 

 

-Jarmo
KevinS
Level I

Re: How do I count changes in row values?

It seems to be working!  Thanks!