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

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

mri — Tue, 22 Sep 2015 20:10:47 GMT

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

3 M

4 M

5 F

Thanks, Jim

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

ron_horne — Thu, 21 Jun 2018 20:10:31 GMT

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

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

MikeD_Anderson — Wed, 23 Sep 2015 16:35:54 GMT

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,

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

hlrauch — Wed, 23 Sep 2015 18:34:09 GMT

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.

Person	Sex	N Rows
1	F	1
2	F	2
3	M	2
4	M	1
5	F	2

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

Sex	N Rows
F	3
M	2

Howard

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

vince_faller — Thu, 21 Jun 2018 20:11:06 GMT

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"));