BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
mri
mri
Community Trekker

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

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ron_horne
Super User

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

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 4
ron_horne
Super User

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

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?

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

0 Kudos
Highlighted
hlrauch
Community Trekker

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

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

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

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

 

0 Kudos