BookmarkSubscribe
Choose Language Hide Translation Bar

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

1 ACCEPTED SOLUTION

Accepted Solutions

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 ),
Column Table( Analysis Columns( :Person ), Statistics( N Categories ) ),
Row Table( Grouping Columns( :Sex ) )
)
);

// option 2
Tabulate(
Show Control Panel( 0 ),
Column Table( Statistics( N Categories ) ),
Row Table( Grouping Columns( :Sex ), Analysis Columns( :Person ) )
)
);

4 REPLIES 4

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 ),
Column Table( Analysis Columns( :Person ), Statistics( N Categories ) ),
Row Table( Grouping Columns( :Sex ) )
)
);

// option 2
Tabulate(
Show Control Panel( 0 ),
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

Highlighted

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.

 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?

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

Vince Faller - Predictum