401233_en-US

liliconcom

Counting values in each row

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!

ScriptingRe: Counting values in each rowSmall question, do you need it per column or over all?
Re: Counting values in each row

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 rowfor 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 tableRe: Counting values in each row

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 rowVery cool........but I think ya stepped a bit into the great abyssRe: Counting values in each row

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

 

 

Re: Counting values in each row

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

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

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