topic Re: Count Sum of Repeat Occurrences By Specified Variable? in Discussions

Count Sum of Repeat Occurrences By Specified Variable?

aandw — Thu, 19 Mar 2015 14:38:09 GMT

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

);

MONTH	FRUIT
1	ORANGE
1	ORANGE
1	APPLE
2	ORANGE
2	APPLE

// 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

Re: Count Sum of Repeat Occurrences By Specified Variable?

ms — Thu, 19 Mar 2015 14:51:45 GMT

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

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

Re: Count Sum of Repeat Occurrences By Specified Variable?

pmroz — Wed, 21 Oct 2020 19:43:58 GMT

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

Re: Count Sum of Repeat Occurrences By Specified Variable?

aandw — Thu, 19 Mar 2015 15:11:57 GMT

Thank you. Very helpful.

Re: Count Sum of Repeat Occurrences By Specified Variable?

jimloughlin — Wed, 21 Oct 2020 19:44:42 GMT

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];

Re: Count Sum of Repeat Occurrences By Specified Variable?

pmroz — Fri, 20 Mar 2015 15:41:22 GMT

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

Thanks!

lloyd_tripp — Wed, 21 Oct 2020 18:48:34 GMT

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.