Count Sum of Repeat Occurrences By Specified Variable?

[#1]

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

[#2 · Accepted solution]

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

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

[#3]

Thank you.  Very helpful.

[#4]

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.

[#5]

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

[#6]

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

[#7]

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