How to get lowest two row values in grouped data ?

joshua — Sat, 10 Jun 2023 23:18:22 GMT

Hi,

Lets say we have data like this and want to get lowest two row values.

Group by Age and Sex, get the two lowest height values.

How can we do this in JSL? Thanks

Re: How to get lowest two row values in grouped data ?

txnelson — Thu, 27 Aug 2020 18:46:06 GMT

Here is one way to do it

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );

dtSum = dt << Summary(
	private,
	Group( :age, :sex, :height ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);

dtSum << delete columns( "N Rows" );

dtSum << New Column( "Lowest Values",
	ordinal,
	formula(
		If(
			Row() == 1, x = 1,
			:age != Lag( :age ) | :sex != Lag( :sex ), x = 1,
			x
			++);
		x;
	)
);

dtSum:Lowest Values << delete formula;

dtSum << select where( :Lowest Values > 2 );
dtSum << delete rows;

dtSum << Tabulate(
	Change Item Label( Statistics( Mean, " " ) ),
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Grouping Columns( :Lowest Values ),
			Analysis Columns( :height ),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :age, :sex ) )
	)
);

topic Re: How to get lowest two row values in grouped data ? in Discussions

How to get lowest two row values in grouped data ?

Re: How to get lowest two row values in grouped data ?