Count Sum of Repeat Occurrences By Specified Variable?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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
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.
Re: Count Sum of Repeat Occurrences By Specified Variable?Sorry - I removed "dt <<" and it's working. The post has been corrected above.
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];
Re: Count Sum of Repeat Occurrences By Specified Variable?Thank you. Very helpful.
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
Re: Count Sum of Repeat Occurrences By Specified Variable?Consider using the function Col Number() with optional By-arguments:
Col Number(Row(), :MONTH, :FRUIT)
