topic Re: How do I count changes in row values? in Discussions

How do I count changes in row values?

KevinS — Sat, 10 Jun 2023 23:46:09 GMT

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

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.

Re: How do I count changes in row values?

txnelson — Fri, 18 Mar 2022 21:42:30 GMT

Here are a couple of ways to handle this

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

Re: How do I count changes in row values?

jthi — Fri, 18 Mar 2022 21:44:25 GMT

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;

Maybe something like this could also work:

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

Re: How do I count changes in row values?

KevinS — Mon, 21 Mar 2022 20:26:27 GMT

It seems to be working! Thanks!