cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
aandw
Level II

Count Sum of Repeat Occurrences By Specified Variable?

I would like to create a count sum formula which sums the number of occurrences of a value by a specified column variable.

I want to know by a specified column variable value (i.e. Month) how many times did the same value appear in the column of interest.

Example:  Sum Type of Fruit By Month

New Column ( "Count_Fruit_Mo",

                        numeric,

                         continuous,

                         // formula or command TBD

                          );

MONTHFRUIT
1

ORANGE

1

ORANGE

1APPLE
2ORANGE
2APPLE

// a Summation command appears to function for the entire column, but does not allow specification by another column variable.

// formula( Summation( i = 1, N Row(), :FRUIT == :FRUIT))

//  ORANGE = 3; APPLE = 2

I'm hoping to generate,

:Month = 1;  :Fruit = "ORANGE";  :Count_Fruit_Mo = 2

:Month = 2;  :Fruit = "APPLE";  :Count_Fruit_Mo = 1

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Count Sum of Repeat Occurrences By Specified Variable?

Consider using the function Col Number() with optional By-arguments:

Col Number(Row(), :MONTH, :FRUIT)

View solution in original post

6 REPLIES 6
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Count Sum of Repeat Occurrences By Specified Variable?

Consider using the function Col Number() with optional By-arguments:

Col Number(Row(), :MONTH, :FRUIT)

aandw
Level II

Re: Count Sum of Repeat Occurrences By Specified Variable?

Thank you.  Very helpful.

lloyd_tripp
Level II

Thanks!

Thanks MS,

 

This helped me count up duplicate columns that I know are replicate measurements. It's very fast for a 4000 row table we have. Now I can weight columns appropriately by the amount of replicate measurements are taken.

pmroz
Super User

Re: Count Sum of Repeat Occurrences By Specified Variable?

You can use the SUMMARIZE command:

 

 

dt = New Table( "Month-Fruit",
     Add Rows( 5 ),
     New Column( "Month", Numeric, Continuous,
           Format( "Best", 12 ), Set Values( [1, 1, 1, 2, 2] )
     ),
     New Column( "Fruit", Character, Nominal,
           Set Values( {"ORANGE", "ORANGE", "APPLE", "ORANGE", "APPLE"} )
     )
);

summarize(f = by(:month, :Fruit), c = count);
show(f, c);

 

 

 

f = {{"1", "1", "2", "2"}, {"APPLE", "ORANGE", "APPLE", "ORANGE"}};
c = [1, 2, 1, 1];

 

The way to interpret the output is that there are 4 unique combinations of month and fruit.

 

For month 1, APPLE the count is 1
For month 1, ORANGE the count is 2
For month 2, APPLE the count is 1
For month 2, ORANGE the count is 1

jimloughlin
Level III

Re: Count Sum of Repeat Occurrences By Specified Variable?

I get an error.  Here's the log:

 

 

Name Unresolved: f{5} in access or evaluation of 'f' , f/*###*/

In the following script, error marked by /*###*/

dt = New Table( "Month-Fruit",

Add Rows( 5 ),

New Column( "Month",

Numeric,

Continuous,

Format( "Best", 12 ),

Set Values( [1, 1, 1, 2, 2] )

),

New Column( "Fruit",

Character,

Nominal,

Set Values( {"ORANGE", "ORANGE", "APPLE", "ORANGE", "APPLE"} )

)

);

dt << Summarize( f = by( :month, :Fruit ), c = count );

Show( f/*###*/, c );

 

 

f = {{"1", "1", "2", "2"}, {"APPLE", "ORANGE", "APPLE", "ORANGE"}};

 

c = [1, 2, 1, 1];

 

Jim Loughlin
Loughlin Consulting
pmroz
Super User

Re: Count Sum of Repeat Occurrences By Specified Variable?

Sorry - I removed "dt <<" and it's working.  The post has been corrected above.