turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Count Sum of Repeat Occurrences By Specified Variable?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2015 7:38 AM
(5046 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2015 7:51 AM
(8225 views)

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

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

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2015 7:51 AM
(8226 views)

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2015 8:11 AM
(4443 views)

Thank you. Very helpful.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2015 8:00 AM
(4443 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2015 2:01 PM
(4443 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 20, 2015 8:41 AM
(4443 views)

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