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 ); MONTHFRUIT1ORANGE 1ORANGE 1APPLE2ORANGE2APPLE // 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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Count Sum of Repeat Occurrences By Specified Variable?

aandw

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

Accepted answers

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

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

All comments

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

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

pmroz

You can use the SUMMARIZE command:

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

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

aandw

Thank you. Very helpful.

jimloughlin

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

pmroz

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

lloyd_tripp

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.