nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Counting values in each row

itzikd

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!

Accepted answers

All comments

Mauro_Gerber

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

txnelson

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;

example.jmp

itzikd

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

itzikd

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

txnelson

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

Untitled 122.jmp

brady_brady

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

brady_brady

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

Craige_Hales

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

brady_brady

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"} ) );

txnelson

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