topic Re: Counting values across multiple columns in Discussions

Counting values across multiple columns

seoleelvjs — Mon, 21 Aug 2017 21:00:08 GMT

Hello,

I am primarily a SPSS user and new to the JMP, and I want to find a similar/identical SPSS function of "count values within cases" in JMP.

I wish to calculate the number of respondents who reported either one of "Not at all," (0) "Slightly," (1) "Somewhat," (2) "Moderatey," (3) and "Extremely" (4) across columns Know_1 to Know_3.

Then, I will create a separate column called "Know_total" that has total counts of respodents with each answer to yield a conclusion some thing like "35 of respondents replied "Moderately" in Knowledge assessment"

Because in SPSS, it's a two step process ( Count the number of certain values > Recode them into desirable ones in another column), and that is why I worded my question in such a way, but please educate me with a quicker option if any.

I do not care whether it's in JSL or not =)

Re: Counting values across multiple columns

pmroz — Mon, 21 Aug 2017 23:49:08 GMT

Here's an approach that uses STACK, a formula column and TABULATE. I assumed that the original table has numbers for the answers.

dt1 = New Table( "", Add Rows( 6 ),
	New Column( "Know_1", Numeric, "Ordinal", Format( "Best", 12 ),
		Set Values( [2, 1, 2, 3, 4, 2] )
	),
	New Column( "Know_2", Numeric, "Ordinal", Format( "Best", 12 ),
		Set Values( [2, 1, 2, 4, 2, 3] )
	),
	New Column( "Know_3", Numeric, "Ordinal", Format( "Best", 12 ),
		Set Values( [0, 1, 1, 1, 2, 4] )
	)
);

dt2 = dt1 << Stack(
	columns( :Know_1, :Know_2, :Know_3 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

dt2 << New Column( "Value", Character, "Nominal", 
		Formula( 
			If(	:Data == 0, "Not at all",
				:Data == 1, "Slightly",
				:Data == 2, "Somewhat",
				:Data == 3, "Moderately",
				:Data == 4, "Extremely"
			)
		)
	);

dtab = dt2 << Tabulate(
	Show Control Panel( 0 ),
	Add Table( Row Table( Grouping Columns( :Value ) ) )
);

Re: Counting values across multiple columns

ian_jmp — Tue, 22 Aug 2017 08:49:44 GMT

You could also consider using the Categorical platform which is very functional. The code below reporoduces the example above:

NamesDefaultToHere(1);
// Test data with 'Value Labels'
dt = New Table( "Test",
				Add Rows( 6 ),
				New Column( "Know_1",
					Numeric,
					"Ordinal",
					Format( "Best", 12 ),
					Value Labels(
						{0 = "Not at all", 1 = "Slightly", 2 = "Somewhat", 3 = "Moderately", 4 = "Extremely"}
					),
					Use Value Labels( 1 ),
					Set Values( [2, 1, 2, 3, 4, 2] )
				),
				New Column( "Know_2",
					Numeric,
					"Ordinal",
					Format( "Best", 12 ),
					Value Labels(
						{0 = "Not at all", 1 = "Slightly", 2 = "Somewhat", 3 = "Moderately", 4 = "Extremely"}
					),
					Use Value Labels( 1 ),
					Set Values( [2, 1, 2, 4, 2, 3] )
				),
				New Column( "Know_3",
					Numeric,
					"Ordinal",
					Format( "Best", 12 ),
					Value Labels(
						{0 = "Not at all", 1 = "Slightly", 2 = "Somewhat", 3 = "Moderately", 4 = "Extremely"}
					),
					Use Value Labels( 1 ),
					Set Values( [0, 1, 1, 1, 2, 4] )
				)
			);
// Try 'Analyze > Consumer Research > Categorical' with 'Multiple Resoponses'
dt << Categorical( Multiple Response( :Know_1, :Know_2, :Know_3 ), Legend( 0 ) );

Re: Counting values across multiple columns

seoleelvjs — Wed, 23 Aug 2017 20:02:38 GMT

Hello pmroz and ian_jmp thank you for the prompt responses!
I have just one more question about the Set Values ([]).
I have total 3,465 rows and it would look like
dt1 = New Table( "", Add Rows( 3,465 ),
...?
Thus, I understand in the script you provided, Set values are the numerical values from row 1 to row 6, and with the change of row numbers, set values script would change as well,
but I do not get what it does...sorry I am not familar with JSL.

Re: Counting values across multiple columns

pmroz — Wed, 23 Aug 2017 20:03:49 GMT

We were just creating dummy tables, and adding values with the SET VALUES command. I expect your data is already present in your tables?

Re: Counting values across multiple columns

seoleelvjs — Wed, 23 Aug 2017 20:17:22 GMT

Hello, pmroz

Right, but then when I tried with the name of my dataset ("New"), the script didn't work (more like nothing happened T.T),

and I just assumed all sequences of scripts were needed somehow and perhaps the first sequence of the script had something to do with it...

Do you mind picking what did I do wrong here?

dt2 = New << Stack(
	columns( :Know_1, :Know_2, :Know_3 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

dt2 << New Column( "Value", Character, "Nominal", 
		Formula( 
			If(	:Data == 0, "Not at all",
				:Data == 1, "Slightly",
				:Data == 2, "Somewhat",
				:Data == 3, "Moderately",
				:Data == 4, "Extremely"
			)
		)
	);

dtab = dt2 << Tabulate(
	Show Control Panel( 0 ),
	Add Table( Row Table( Grouping Columns( :Value ) ) )
);

Thank you

Re: Counting values across multiple columns

pmroz — Thu, 24 Aug 2017 13:17:55 GMT

You are referencing your dataset named "New" with a variable New. However the variable doesn't point to anything. This should fix things:

dt1 = data table("New");

dt2 = dt1 << Stack(
	columns( :Know_1, :Know_2, :Know_3 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);