Stats of two columns combined, grouped by third

WendyLou315

I have been trying to run basic stats (Mean, StDev, Range) on a 2million record set of data.  The catch is that I need to average two columns, not just one.  Is this possible?  The number of records for each group will vary. Tabulate will average Val1 and Val2 separately, I need them averaged together.  The data table attached should average Val1 and Val2 based on each ID. 

 

Results should be:

ID A	Avg 2.000	StDev 0.8944	Range 2
B	3.500	1.0488	3
C	2.625	1.4079	4
D	2.700	1.9465	5

 

Is there JSL code that I can use to get the results I'm looking for?

JMP QUESTION.jmp

txnelson

You will need to stack the columns.  Here is a simple script for illustration

Names default to here(1);
dt=New Table( "JMP QUESTION",
	Add Rows( 15 ),
	New Column( "ID",
		Character( 1 ),
		"Nominal",
		Set Values(
			{"A", "A", "A", "B", "B", "B", "C", "C", "C", "C", "D", "D", "D", "D",
			"D"}
		),
		Set Display Width( 57 )
	),
	New Column( "Val1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 3, 2, 1, 3, 4, 5, 3, 6, 2, 1] ),
		Set Display Width( 57 )
	),
	New Column( "Val2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 3, 2, 4, 3, 2, 1, 2, 5, 3, 1, 2, 5, 1, 1] ),
		Set Display Width( 57 )
	)
);

// Stack the data
dtStack = dt << Stack(invisible,
	columns( :Val1, :Val2 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Val 1,2" ),
	Name( "Non-stacked columns" )(Keep( :ID ))
);

// Generate the Statistics 
dtStack << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Statistics( Mean ), Analysis Columns( :Name( "Val 1,2" ) ) ),
		Column Table(
			Statistics( Std Dev ),
			Analysis Columns( :Name( "Val 1,2" ) )
		),
		Column Table( Statistics( Range ), Analysis Columns( :Name( "Val 1,2" ) ) ),
		Row Table( Grouping Columns( :ID ) )
	)
);