Subscribe Bookmark RSS Feed

Count Sum of Repeat Occurrences By Specified Variable?

aandw

Community Trekker

Joined:

Mar 12, 2015

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
Solution

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

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

5 REPLIES
Solution

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

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

aandw

Community Trekker

Joined:

Mar 12, 2015

Thank you.  Very helpful.

pmroz

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 23, 2011

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