topic Re: Formula Countif column in Discussions

Formula Countif column

jorgeramosdealm — Mon, 20 Feb 2012 18:56:48 GMT

I am trying to use a condition to create a formula on a column.

The datatable example is:

Variable 1

CASSYGDGGTGELFF

CATSRSSGYEQYF

CASSPAAGAAYEQYF

CASSVGSDAYEQYF

CASSVGGVSYNEQFF

CASSVSGSAYEQYF

CASSPAAGAAYEQYF

And I would like to create a variable that would be the count of variable 1 for each row.

Variable 1 Count of Variable 1

CASSYGDGGTGELFF 1

CATSRSSGYEQYF 1

CASSPAAGAAYEQYF 3

CASSVGSDAYEQYF 1

CASSVGGVSYNEQFF 1

CASSVSGSAYEQYF 1

CASSPAAGAAYEQYF 3

In excel I would use

B1=countif(A:A,A1)

...

B8=countif(A:A,A8)

I know how to do it through tabulate and I can get around this problem in different ways. However, this is one brick of a more complex formula that I would like to implement in JMP.

Thanks,

Jorge Almeida

Formula Countif column

Mon, 20 Feb 2012 22:32:22 GMT

I'm not a JMP user, thus probably shouldn't be attempting to answer your question. However, upon looking at the JMP documentation, it appears that one can use SQL in JMP.

Using the combination of count and group by statements, with SQL, would do the same thing and much easier than having all the individual statements in Excel.

Formula Countif column

jorgeramosdealm — Mon, 20 Feb 2012 22:59:48 GMT

If that would do it, sure. However, I am not very experienced in JMP and I have not used the SQL scripting. If anyone would be able to do it through the SQL and show it to me, I will be extremely thankful. Thanks art.

Formula Countif column

pmroz — Tue, 21 Feb 2012 15:27:16 GMT

JMP does not have SQL as part of JSL. There are SQL-like features, like select where, table joins, updates, etc., but you can't issue SQL commands like you can in SAS. That would be a really great feature (hint hint!).

Anyway I've needed to do something similar and used tabulate and then joined the results back to the original table. I'm not sure a column formula would be easy to implement, and the performance might not be good for large tables.

Here's how to do it using tabulate and update. The table Untitled 8 contains your original data in a column called Variable 1.

dt = data table("Untitled 8");

tmp_tab = dt << Tabulate(

Show Control Panel( 0 ),

Add Table( Row Table( Grouping Columns( :Variable 1 ) ) ), invisible

);

tmp_tbl = tmp_tab << Make Into Data Table;

tmp_tab << close window;

Data Table( "Untitled 8" ) << Update(

With( tmp_tbl ),

Match Columns( :Variable 1 = :Variable 1 ),

Add Columns from Update table( :N )

);

close(tmp_tbl, nosave);

Formula Countif column

jorgeramosdealm — Tue, 21 Feb 2012 16:19:37 GMT

Thanks PMroz, that was exactly what I did. I am trying to transfer all my data analysis from excel to JMP and there are some things that require this kind of workaround. Anyway, if there would be some column formula that could tackle this problem, it would be great. I will keep this discussion open for a little longer in order to see if there are any other suggestions.

Formula Countif column

ms — Mon, 27 Feb 2012 14:53:42 GMT

There is a JMP counterpart to countif(): Col Number(col, <optional bycol>). However, it only works for numeric columns so character varibles has to be converted to numbers. Missing values will yield zero.

Try this column formula

New Column( "Count of Variable 1",

Numeric,

Continuous,

Formula(

Col Number( Hex To Number( Char To Hex( :Variable 1 ) ), :Variable 1 )

)

Re: Formula Countif column

jorgeramosdealm — Mon, 27 Feb 2012 16:38:32 GMT

That formula worked. Thank you very much. I was able to integrate it in my other more complex formula as in:

"Variable 1" "Name" "SharedVariable 1" "Count of shared"

CASSYGDGGTGELFF John Private 1

CATSRSSGYEQYF John Private 1

CASSPAAGAAYEQYF John Shared 3

CASSPAAGAAYEQYF Albert Shared 3

CASSVGSDAYEQYF Albert Private 1

CASSVGGVSYNEQFF Albert Private 1

CASSVSGSAYEQYF Albert Private 1

CASSPAAGAAYEQYF Michael Shared 3

New Column( "SharedVariable1",

Character,

Nominal,

Formula(If(ColNumber( Hex To Number( Char To Hex( :Variable 1 ) ), :Variable 1 ) > Col Number( Hex To Number( Char To Hex( :Variable 1 ) ), :Variable 1 || :Name ), Char("Shared"), Char("Private")))

);

New Column( "Count of shared",

Numeric,

Continuous,

Formula((ColNumber( Hex To Number( Char To Hex( :Variable 1 ) ), :Variable 1 ) - Col Number( Hex To Number( Char To Hex( :Variable 1 )), :Variable 1 || :Name ))+1)

)

One question:

Col Number( Hex To Number( Char To Hex( :Variable 1 ) ) is to create a countable variable. To specificy what to count is after the comma on the:

Col number(Column with a numeric variable, Condition to count)

Re: Formula Countif column

ms — Mon, 27 Feb 2012 16:58:36 GMT

Yes, the optional "by column" is after the comma. With a by column you get the counts of each value in that column. Whithout a by column you would get the total number of non-missing values, including multiples.

Re: Formula Countif column

nacka — Thu, 04 Aug 2016 21:36:47 GMT

If anyone comes here looking for the JSL script to simply store a variable with the count of cells with a certain criteria, like I did, here's what i came up with that worked for me:

dt = current data table ();

CountCondition1 = nrows ( dt << get rows where (:[Column with condition] [LOGICAL CONDITION]));

...

CountConditionX = nrows ( dt << get rows where (:[Column with condition] [LOGICAL CONDITION]));

So, for a data table with the set up:

Name Number of Incidences

Apples 3

Oranges 4

Bananas 0

Grapes 0

and you want to know, for instance, what % of fruit has a non-zero value:

CountNonZeros = nrows( dt << get rows where (:Number of Incidences > 0));

// CountNonZeros variable becomes value 2

CountTotal = nrows (dt);

// CountTotal becomes value 4

PercentNonZero = CountNonZeros/CountTotal*100

// PercentNonZero becomes 50