topic Re: Col Functions and Row States in Discussions

Col Functions and Row States

David_Burnham — Fri, 09 Jun 2023 22:03:59 GMT

According to the release notes for version 15 of JMP:

The Col functions honor excluded rows through the Excluded(Row State()) function.
Col Maximum( :height, :sex, Excluded( Row State() ) );

However, this syntax doesn't seem to work. Can anyone suggest the corect syntax?

Re: Col Functions and Row States

jthi — Fri, 22 Jan 2021 14:11:38 GMT

I quickly tested and seemed that using only Excluded() seemed to work (I'm using 15.2.1).

Re: Col Functions and Row States

David_Burnham — Fri, 22 Jan 2021 15:56:24 GMT

Thanks for letting me know. That doesn't work with 15.1 - I'll try and do a comparison with 15.2.

Re: Col Functions and Row States

jthi — Sat, 23 Jan 2021 07:39:40 GMT

I'll add this very simple example here if anyone else comes around:

Names Default To Here(1);
New Table("ExcludeColMax",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("A",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 10, 100, 2, 20, 200]),
		Set Display Width(46)
	),
	New Column("B",
		Character,
		"Nominal",
		Set Values({"A", "A", "A", "B", "B", "B"})
	),
	New Column("C",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(Col Maximum(:A, :B, Excluded())),
		Set Display Width(65)
	),
	Set Row States([0, 0, 6, 6, 6, 0])
);

Re: Col Functions and Row States

David_Burnham — Sat, 23 Jan 2021 13:21:51 GMT

I've tested with v15.2 and the issue is the same. Note the use case is using the function within a script, not within a column formula (not sure why I would want a column statistic in a row).

Re: Col Functions and Row States

jthi — Sun, 24 Jan 2021 12:14:28 GMT

Maybe honoring means in this case that the excluded is just additional byVar in column statistics formulas?

If you want maximum value of column without excluded values, could you use Summarize instead of Col Maximum? It seems to ignore excluded rows.

Names Default To Here(1);
dt =New Table("ExcludeColMax",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("A",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 10, 100, 2, 20, 200]),
		Set Display Width(46)
	),
	New Column("B",
		Character,
		"Nominal",
		Set Values({"A", "A", "A", "B", "B", "B"})
	),
	New Column("C",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Formula(Col Maximum(:A, :B, Excluded())),
		Set Display Width(65)
	),
	Set Row States([0, 0, 6, 6, 0, 6])
);

Write("Looping col max:");
For Each Row(
	Show(Col Maximum(:A, :B, Excluded()))
);

Write("\!N\!NOnly col max:"); 
Show(Col Maximum(:A, :B, Excluded()));

Write("\!N\!NSummarize:");
Summarize(dt, exg = By(:B), exm = Max(:A));
Show(Eval List({exg, exm}));
Show(Max(exm));

Re: Col Functions and Row States

David_Burnham — Sun, 24 Jan 2021 19:40:49 GMT

I can code a solution - my question was specifically about whether version 15 allowed it to be done solely using the column functions - that was the impression I got from the release notes.

Re: Col Functions and Row States

txnelson — Sun, 24 Jan 2021 22:27:01 GMT

Dave,
Very confusing. I think this is a question for our friend Wendy.

Re: Col Functions and Row States

ian_jmp — Mon, 25 Jan 2021 13:58:19 GMT

Interesting. A pre-release build of JMP 16 seems to get it right for r=1 and r=2, but not thereafter:

NamesDefaultToHere(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

mw = J(NRow(dt), 2, .);
for(r=1, r<=NRow(dt), r++,
	Row() = r;
	RowState(Row()) = ExcludedState(1);
	mw[r,1] = ColMean(:weight, Excluded(RowState(Row())));
	mw[r,2] = ColMean(:weight, Excluded());
	RowState(Row()) = ExcludedState(0);
	);
AsTable(mw);

dt << Distribution(
			Continuous Distribution(
				Column( :weight ),
				Quantiles( 0 ),
				Histogram( 0 ),
				Outlier Box Plot( 0 )
			),
			Local Data Filter(
				Add Filter(
					columns( Transform Column( "Row", Nominal, Formula( Row() ) ) ),
					Where( Transform Column( "Row", Nominal, Formula( Row() ) ) == 1 ),
					Display(
						Transform Column( "Row", Nominal, Formula( Row() ) ),
						N Items( 15 ),
						Find( Set Text( "" ) )
					)
				)
			)
		);

Re: Col Functions and Row States

David_Burnham — Mon, 25 Jan 2021 15:49:41 GMT

Working with Big Class (of course):

colmaximum(:height,excluded(rowstate()))

This is working. But on one computer but not another. Both running 15.1.0.

Now I am totally confused!

Screenshots on next post ...

Re: Col Functions and Row States

David_Burnham — Mon, 25 Jan 2021 15:48:37 GMT

On one computer it works:

And on the another it fails:

Re: Col Functions and Row States

Mark_Bailey — Mon, 25 Jan 2021 19:37:02 GMT

It looks like it is used with a column formula where the Row() function is defined as it is evaluated.

Re: Col Functions and Row States

Wendy_Murphrey — Mon, 25 Jan 2021 21:02:22 GMT

According to the documentation, when a By variable is specified the function should be used in a column formula or For Each Row():

(Optional) A By variable to compute statistics across groups of rows. Use the By variable in a column formula or in a For Each Row() function.

In reviewing some of the examples from earlier in this post, I would agree that it appears to be treating excluded values as their own By level.

Re: Col Functions and Row States

David_Burnham — Mon, 25 Jan 2021 21:23:58 GMT

OK thanks. I can't really get my head around the idea of generating a column statistic for every row of the table but I guess there must be use-cases. I'll stick with using a user-defined function to calculate column stats whilst taking into account row exclusions.

Re: Col Functions and Row States

Mark_Bailey — Tue, 26 Jan 2021 12:39:51 GMT

I think that it is the same concept as the one behind binning. You are mapping one value to another for each observation, in this case, a group value. It might be a bin. It might be a measure of central tendency.

Re: Col Functions and Row States

Oren_Barilan — Fri, 08 Dec 2023 12:34:45 GMT

Adding to this discussion, I would consider the Col functions behaviour to being odd and different from what is typical in JMP and therefore, unexpected and confusing.

Col functions ignore exclusion of rows and filtering, both local and global.

This means that local filtering within an analysis output is ignored with col functions and getting results with filtering or by subgroups is not possible or actually the obtained results are incorrect.

The ability to exclude some rows by using a formula is still very limiting.

I see this a problem with JMP since this could potentially confuse or mislead users. I hope JMP team would address this to make Col functionality adhere to that of other functions.

Re: Col Functions and Row States

txnelson — Fri, 08 Dec 2023 16:34:36 GMT

@Oren_Barilan ,

To take a ten thousand foot view of your concern about the Col functions not following the Excluded functionality of the JMP, Platforms, one would have to include many more functions than just the Col functions. A simple SQRT() function, or a SUM() should also be included in your argument, for if they are processed for values on an Excluded row, they too should return a null value. And if this was the case, how do you handle the situation where you do not want the rows ignored?

So from my point of view, JMP takes a more functional approach, allowing the programmer to have it which every way they need it.

Col Mean( :weight );

calculates the Mean over all rows in the data table, while

Col Mean( If( Excluded( Row State( Row() ) ), ., :weight ) )

calculates the Mean on only the non Excluded rows

Re: Col Functions and Row States

jthi — Fri, 08 Dec 2023 17:24:05 GMT

I agree with Jim.

There is also third option, where you handle Excluded as it's own "group" (not sure how it is handled in reality)

Col Mean(:weight, Excluded())

My only "issue" with Col Functions is that they can be dangerous due to how they handle excluded rows AND you can create them so easily from right-click menu. Maybe this risk could be reduced, if JMP did auto-include Excluded() to those formulas (or make it a preference)?