topic Re: Counting values in each row in Discussions

Counting values in each row

itzikd — Fri, 09 Jun 2023 19:52:35 GMT

I have a script with col 1-6 in a list

I am trying to write a JSL script that will count how many times the word POR appear, and how many times the word test appears

example below (did it manually the last 2 columns)

reading the forum I understand I need to convert DT into a matrix, but I am not able to make it work.

thanks!

Re: Counting values in each row

Mauro_Gerber — Thu, 15 Jul 2021 14:24:33 GMT

Small question, do you need it per column or over all?

Re: Counting values in each row

txnelson — Thu, 15 Jul 2021 14:27:38 GMT

Here are the formulas that are one way of calculating the counts

count = 0;
For( i = 1, i <= 6, i++,
	count = Sum( count, Contains( As Column( i ), "test" ) )
);
count;

count = 0;
For( i = 1, i <= 6, i++,
	count = Sum( count, Contains( As Column( i ), "POR" ) )
);
count;

Re: Counting values in each row

itzikd — Thu, 15 Jul 2021 18:02:25 GMT

Hey, this does not work

for example if I run it on this

POR	POR	POR	POR
POR	POR	POR	POR
test	POR	POR	test
test	POR	POR	test
POR	POR	POR	POR
POR	POR	POR	POR
test	POR	POR	test
test	POR	test	test

the counts come out as 0

what I would expect when I run the "test" one is: 0,2,2,0,0,2,3

so the output is like this:

POR	POR	POR	POR	0
POR	POR	POR	POR	0
test	POR	POR	test	2
test	POR	POR	test	2
POR	POR	POR	POR	0
POR	POR	POR	POR	0
test	POR	POR	test	2
test	POR	test	test	3

Re: Counting values in each row

itzikd — Thu, 15 Jul 2021 18:03:42 GMT

for all, but it is per a row
like each row counts how many times a values appeared inside that row and then at the end of that row i write the "count" value
see my example below i added some table

Re: Counting values in each row

txnelson — Thu, 15 Jul 2021 18:29:12 GMT

I created in JMP the table as you provided

I then created a new column and applied the following formula to that column

count = 0;
For( i = 1, i <= 4, i++,
	count = Sum( count, Contains( As Column( i ), "test" ) )
);
count;

Please note, the For() loop only goes up to 4, because there are only 4 columns in the table to process

It gives the following results

I have attached the data table

I strongly suggest that you take the time to read the document, Discovering JMP, available under the Help pull down menu

Re: Counting values in each row

brady_brady — Sat, 17 Jul 2021 02:57:23 GMT

Hi,

Yet another way involves concatenating all the columns with a 1-click transform.

Since "test" is 1 character more than POR, and you've no missing data, the resultant string will increase by 1 character for every "test" contained, compared to a sting of only "POR"s.

To concatenate, select the columns of interest and right-click, selecting as below. Then rename the column "concat" for brevity.

In the example table below, there are 6 columns of interest; a string with only "POR"s would thus have 6 * 3 = 18 characters.

Hence, we use the formulas:

length(:concat) - 18 for the number of "test" == n_test.

and

6 - n_test for the number of "POR".

In general, for n_test we'd use (length(:concat) - 18)/k, where k is the difference in characters between the 2 phrases. Here, since k == 1, we omit it.

Cheers,

Brady

Re: Counting values in each row

txnelson — Thu, 15 Jul 2021 23:47:45 GMT

cool idea

Re: Counting values in each row

brady_brady — Fri, 16 Jul 2021 13:36:07 GMT

Hi,

Please check out JMP's "Value Labels" property, as well as the "Labels to Codes" and "Codes to Labels" columns utilities.

I would be remiss if I did not advocate for the use of numeric 1 for POR and 0 for test (1: test and 0: POR if you prefer). Using the Value Label column property, we can make these values APPEAR as if they are "test" and "POR", but internally, they are 0s and 1s.

Since the values are numeric, computations can be done using native column and row formulas. In the case of 1s and zeros, the sum gives the number of 1s, while the mean gives the proportion of ones. In this example, if we store POR as 0 and "test" as 1, we can find the number of tests in a given row simply by selecting all columns of interest, right-clicking and choosing New Formula Column > Combine > Sum from the pop-up menu.

Below is an image of how this looks in practice. The cells appear to contain text, as the words POR and test are left-justified, but when you click on a cell you can see that in fact JMP is storing a number internally, similar to what is done with dates and times.

By clicking on the * to the right of the column names in the Columns pane (left center of data table), we can select "Value Labels" to view the current settings of the property:

Cheers,

Brady

Re: Counting values in each row

Craige_Hales — Fri, 16 Jul 2021 14:22:09 GMT

Another way.

Column formula to count across row

This formula uses data table subscripting to get a list like {"test","POR","test","test"} for row 8.

Above I used 1::4, which turns into Index(1,4) as a shortcut. You should use {Column1, Column 2, ... Column N} instead because when I changed one of the values to "xyzzy" the formula columns' values did not update until I forced the formulas to rerun. If JMP can't tell a column references another column, it will not reevaluate.

This works better:

formula with explicit column names

Re: Counting values in each row

brady_brady — Sat, 17 Jul 2021 03:01:22 GMT

I like this, Craige.

It looks like the design matrix feature can handle this idea, too-try this formula to count the number of "a"s.

dt = :Col1 << get data table;
Sum( Design( dt[Row(), {:Col1, :Col2, :Col3, :Col4, :Col5}], {"a"} ) );

Re: Counting values in each row

txnelson — Fri, 16 Jul 2021 16:23:26 GMT

Very cool........but I think ya stepped a bit into the great abyss

Re: Counting values in each row

brady_brady — Fri, 16 Jul 2021 19:32:15 GMT

I know... but it is a surprisingly good method from a time standpoint. Interestingly the concat method, as odd as it is, is really fast as well.

For example, for a 200,000 row x 10 column table (compressed file attached) of these values, here are the runtimes:

Names Default To Here( 1 );

dt = Current Data Table();

t1 = HP Time();

dt << new column("xx",
	formula(sum( design (dt[row(), {:col1, :col2, :col3, :col4, :col5, :col6, :col7, :col8, :col9, :col10}], {"test"})))
);

t2 = hptime();

dt << New Column( "xx",
	formula(
		count = 0;
		dt = :Col1 << get data table;
		For( i = 1, i <= 10, i++,
			If( Column( dt, i )[Row()] == "test", count	++)
		);
		count;
	)
);

t3 = hptime();

dt << new column("xx", formula(nrow(loc(dt[row(), {:col1, :col2, :col3, :col4, :col5, :col6, :col7, :col8, :col9, :col10}], "test"))));

t4 = hptime();

dt << new column("concat", character, formula(Col1 || :Col2 || :Col3 || :Col4 || :Col5 || :Col6 || :Col7 || :Col8 || :Col9 ||:Col10));
dt << new column("xx", formula(length(:concat)-30));

t5 = hptime();

print(evalinsert("Design matrix method:	^(t2-t1)/1e6^ seconds."));
print(evalinsert("Looping method: 			^(t3-t2)/1e6^ seconds."));
print(evalinsert("Nrow(Loc()) method:		^(t4-t3)/1e6^ seconds."));
print(evalinsert("Concat method: 				^(t5-t4)/1e6^ seconds."));