topic Re: how to count value in columns in Discussions

how to count value in columns

ReliabilityWolf — Fri, 12 Jul 2024 13:35:33 GMT

I am fairly new to JMP. I have a data table here

I like to have X count column to show the count number of the value in X-COORD, similar thing, X-Y Count column to show the count number of the value [X-COORD, Y-COORD].

in fact, I can get the count number by tabulate. but I like to show the numbers in column in order to do next level calculation.

I really appreciate if you would do me a favor to show which formula is supposed to apply in column of X count, which formula for X-Y count. thanks

Re: how to count value in columns

Thierry_S — Fri, 12 Jul 2024 14:12:12 GMT

Hi,

To get the count of items per user-specified categories, you could use:

Col Number( Y_COORD, X_COORD)

Which will return the number of Y_COORD items for each X_COORD group.

NOTE: you can have multiple grouping categories (second terms) if needed.

Best,

Re: how to count value in columns

ReliabilityWolf — Fri, 12 Jul 2024 15:05:23 GMT

sorry for my unclear statement. my intention is to look X-COORD and Y-COORD as matrix, the number in X-Y Count column is my purpose. I am looking forward to your help on the formula.

Re: how to count value in columns

txnelson — Fri, 12 Jul 2024 15:54:59 GMT

Fastest and easiest way is to use:

Tables=>Summary

and specify X-COORD and Y_COORD as grouping variables, and do not specify a statictic

Then use

Tables=>Update

from your original data table and specify the summary table as the update table, matching on S_COORD and Y-COORD

Re: how to count value in columns

jthi — Fri, 12 Jul 2024 16:48:17 GMT

Summarize + Update is the easiest option (or you can do interactive drag and drop if you can get it to work). You can also use Col Sum()

Col Sum(1, :X, :Y)

Col Number should also work, but you have to make slight modification to Thierry's suggestion

Col Number(:X, :X, :Y)

Re: how to count value in columns

ReliabilityWolf — Sun, 14 Jul 2024 06:07:52 GMT

@jthi this is a wonderful solution. thank you. by the way, I am rethinking why I failed to select col sum(1,:X,:Y). based on JSL syntax reference below, the first argument is name, a column name. but you use 1. this is out of my thinking.

Re: how to count value in columns

jthi — Sun, 14 Jul 2024 15:40:55 GMT

Using expressions inside those functions might disappear at some point if JMP decides to do that (in my opinion it would make them 1000 times worse if they did that), but you could add new column with only values of 1 to avoid that (if it were to happen). Here is my wish list item which attempts to "prevent" JMP for making the horrible mistake of "fixing" that and making it a feature not an unintended behaviour