topic Re: Increment by levels of character column (scripting) in Discussions

Increment by levels of character column (scripting)

Newbie2Jumpie — Wed, 23 Oct 2019 12:30:21 GMT

Maybe I'm again not seeing the wood before the trees; I just want to apply some numeric increment for every distinct level of a character column.

Same levels get the same increment, different levels get different numbers.

Assumption: Data are sorted by "Character_Column_Have". "Numeric_Increment_Want" should start with 1.

Character_Column_Have Numeric_Increment_Want

AAA 1

ABC 2

BBB 3

CCD 4

EEE 5

EEE 5 ...

The only result I achieved so far was to repeatedly crash my JMP by using "col **bleep** rank".

JMP and me could do better, right?

Thanks for every pointer!

Newbie

Re: Increment by levels of character column (scripting)

txnelson — Wed, 23 Oct 2019 13:17:31 GMT

Here is the formula that I would use

If( Row() == 1,
	Summarize( groups = by( :Character_Column_Have ) )
);
Contains( groups, :Character_Column_Have );

There are also other methods.

Use Recode to create a new column, changing the values found for the new numeric values wanted
Use the Summary Platform with Character_Column_Have as the Group column to create a new data table with one row per value of Character_Column_Have. Then create a new column that has a formula of Row(). Finally use the Update Platform to join the summary table with the original table.

Re: Increment by levels of character column (scripting)

Mark_Bailey — Wed, 23 Oct 2019 13:38:16 GMT

I like Jim's answer. You could also use a simple formula:

If(
	Row() == 1, 1,
	:Have == Lag( :Have ), Lag( :Want ),
	Lag( :Want ) + 1
)

Or the equivalent script:

For Each Row( If( Row() == 1, 1, :Have == Lag( :Have ), Lag( :Want ), Lag( :Want ) + 1 ) );

Re: Increment by levels of character column (scripting)

Newbie2Jumpie — Thu, 24 Oct 2019 13:56:45 GMT

I tried all your approaches but it seems I cannot make them work.

One approach puzzles me conceptually. It addresses two input columns, while only character column is available for input.

Could you demonstrate one approach on the JMP dataset "Dolphins" please (excerpt below)?

Travel Morning 6
Feed Morning 28
... Travel Evening 13
Feed Evening 56
Social Evening 10

For the "Dolphins" levels Morning, Noon, Afternoon, Evening I'd expect the increments 1,2,3 resp. 4 (subject to sorting order)

Re: Increment by levels of character column (scripting)

txnelson — Thu, 24 Oct 2019 14:11:36 GMT

Here is the formula for the Numeric_Increment_Want column

If( Row() == 1,
	Summarize( myGroups = by( :Period ) )
);
Contains( myGroups, :Period );

I had to change the previous code because the reference to "Groups" in the old code, conflicts with the Dolphins data table having a column named Groups. So I changed the variable name to myGroups.

Attached is the data table with the new column added

Re: Increment by levels of character column (scripting)

Newbie2Jumpie — Fri, 25 Oct 2019 07:40:02 GMT

	New Column( "Increment",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If( Row() == 1,
	Summarize( myGroups = by( :Period ) )
);
Contains( myGroups, :Period );
		)
	);

I cannot explain why it now works. I also adjusted for "Groups". Weired. Anyway, I solved it like that and it works like a charm. Jim, thank you very much. Again!