cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
vishwasanj
Level V

Searching a value in a column- JSL

Hi All,

 

Can anyone help me with the search for a particular value in a column and try to find the count of that value with the overall count? In the below example, I am trying to search for value "1" in B column. I found 3 occurences in 10 instances so C is marked with 30%. Similarly for A=2, I found 4 occurence of "1" in 12 instances, so C is marked with 33%.

ABC
1130%
187 
190 
165 
11 
165 
13 
11 
134 
15 
2133%
25 
22 
247 
21 
254 
21 
285 
21 
21 
222 
221 

 

 

Thank you 

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Searching a value in a column- JSL

I think this should work:

 

col number(:b, :a, :p )/col number(:b, :a)

 

You need to take a look at the documentation on Col Number() in the Scripting Index

     Help==>Scripting Index==>Col Number

Jim

View solution in original post

10 REPLIES 10
txnelson
Super User

Re: Searching a value in a column- JSL

You can use the Tabulate Platform and specify the % Column Total as the statistic.

You can also create a new column and use the following formula to get the answer

Col Number( :b, :p ) / Col Number( :p )
Jim
vishwasanj
Level V

Re: Searching a value in a column- JSL

Thank you txnelson. What is :p represent? I am sorry , still new to this idea. I tried Tabulate also, I don't really understand the syntax which i should use as a grouping column?
txnelson
Super User

Re: Searching a value in a column- JSL

:p represents column "b", from the data table you listed in the original question.  The formula,

     Col Number( :b, :b) / Col Num( :p )

is interpreted as:

the count of all of the values in column b, for the current rows value of column b, divided by the total count of the values for column b.

In the example data you provided, the calculation for the first row would be:

   The count of values found in column b, that match the value for column b.  That is, the value of the first row for column b is "1", so it would count the number of "1"s found in column b, which is 8.  It would then divide by the total number of the values found in column b, which is 22.

 

Concerning the Tabulate Platform, what you would do, is to change the modeling type of column b to Nominal, so Tabulate will treat the column as a grouping column.  Then you would drag column b to the Drop Zone for Rows.  I will then list out each of the values found for column b.  By default, the statistic column that is listed is the count(N).  To change it to the Column %, you would just drag the Column % statistic to the "N" column, and it would replace the statistic with the column %

column percent.PNG

Jim
vishwasanj
Level V

Re: Searching a value in a column- JSL

Thank you for the quick solution. Is there a way to segregate by column A. For example, instead of taking all 7 1's and 22 values together, can we do it by column A (10, 12) and getting the corresponding column % statistic?

Thanks.
txnelson
Super User

Re: Searching a value in a column- JSL

I think this should work:

 

col number(:b, :a, :p )/col number(:b, :a)

 

You need to take a look at the documentation on Col Number() in the Scripting Index

     Help==>Scripting Index==>Col Number

Jim
vishwasanj
Level V

Re: Searching a value in a column- JSL

Wow. This is amazing. Thank you txnelson
DC1123
Level II

Re: Searching a value in a column- JSL

HI 

I checked the Col Number. Is this what you mean? but it won't work.


Names Default To Here( 1 );
Open( "$SAMPLE_DATA/Big Class.jmp" );
Col Number( :height,:height )/Col Num( :height )

txnelson
Super User

Re: Searching a value in a column- JSL

you misspelled "col number()" as "col num()"

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "thepercent", 
	formula( Col Number( :height, :height ) / Col Number( :height ) ) 
);
Jim
vishwasanj
Level V

Re: Searching a value in a column- JSL

Is there a way not to duplicate the values? As in, every 1's will be corresponding to the %. If I have multiple 1's in the each 1 will have 30% next to it. Can I make it appear once per Group A just like my example above? Helps me in the plotting and subsequent analysis.