Subscribe Bookmark RSS Feed

What is the easiest way to count the number of occurrences in a column when rows duplicate?

mri

Community Trekker

Joined:

May 19, 2015

What is the easiest way to count the number of occurrences in a column when rows duplicate based on another column?

I can of course create a new table without duplicate rows or a script, but can this be done with say Tabulate?

Example: I want to know how many men and women.  F(n=3) and M(n=2).

Person    Sex  

1               F

2               F

2               F

3               M

3               M

4               M

5               F

5               F

Thanks, Jim

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

hi Jim

you can do it with tabulate but i find it difficult to enplane in words the delicate positioning of the variables by drag and drop so here are two alternative scripts.

// create a source data table

dt1 = New Table( "source data",

     Add Rows( 8 ),

     New Column( "Person",

          Numeric,

          Continuous,

          Format( "Best", 15 ),

          Set Values( [1, 2, 2, 3, 3, 4, 5, 5] )

     ),

     New Column( "Sex",

          Character( 16 ),

          Nominal,

          Set Values( {"F", "F", "F", "M", "M", "M", "F", "F"} )

     )

);

// option 1

Tabulate(

     Show Control Panel( 0 ),

     Add Table(

          Column Table( Analysis Columns( :Person ), Statistics( N Categories ) ),

          Row Table( Grouping Columns( :Sex ) )

     )

);

// option 2

Tabulate(

     Show Control Panel( 0 ),

     Add Table(

          Column Table( Statistics( N Categories ) ),

          Row Table( Grouping Columns( :Sex ), Analysis Columns( :Person ) )

     )

);

4 REPLIES
Solution

hi Jim

you can do it with tabulate but i find it difficult to enplane in words the delicate positioning of the variables by drag and drop so here are two alternative scripts.

// create a source data table

dt1 = New Table( "source data",

     Add Rows( 8 ),

     New Column( "Person",

          Numeric,

          Continuous,

          Format( "Best", 15 ),

          Set Values( [1, 2, 2, 3, 3, 4, 5, 5] )

     ),

     New Column( "Sex",

          Character( 16 ),

          Nominal,

          Set Values( {"F", "F", "F", "M", "M", "M", "F", "F"} )

     )

);

// option 1

Tabulate(

     Show Control Panel( 0 ),

     Add Table(

          Column Table( Analysis Columns( :Person ), Statistics( N Categories ) ),

          Row Table( Grouping Columns( :Sex ) )

     )

);

// option 2

Tabulate(

     Show Control Panel( 0 ),

     Add Table(

          Column Table( Statistics( N Categories ) ),

          Row Table( Grouping Columns( :Sex ), Analysis Columns( :Person ) )

     )

);

M_Anderson

Staff

Joined:

Nov 21, 2014

A super easy way of doing it quickly would be to use the recode function on the column... as part of the function it gives you the number of occurrences of each item in the column.  Another option using tabulate is to change the pull down under "Tabulate" from "Interactive Table" to Dialog... then you can just set it up as you would with any other JMP platform.

Best,

M

hlrauch

Community Trekker

Joined:

Sep 19, 2014

One more simple way:

1. Select Tables > Summary. In the column dialog window, select Person and Sex and make them Group variables. (No Statistics columns needed here.) Click OK. This produces a table with no duplicate rows.

PersonSexN Rows
1F1
2F2
3M2
4M1
5F2

2. Select Tables > Summary for this new table. In the column dialog window, just select Sex and make it a Group variable. Click OK.

SexN Rows
F3
M2

Howard

vince_faller

Super User

Joined:

Mar 17, 2015

Like Mr. Anderson said, Recode is a real easy way. 

If you want a scripting method, this works quickly.

x = Associative Array(:Person, :Sex)<<Get Values;

M = nrows(loc(x, "M"));

F = nrows(loc(x, "F"));