8204_en-US

liliconcom

finding the mode<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

hi everyone,

i would like to produce a table summery that gives the mode of a variable for each category of another variable.

ideally, this would work:

dt = open ("$SAMPLE_DATA/Mail Messages.jmp");
dt << summary ( Group ( :From ), Mode (:To) );

alternatively, i would stack the table by "From" and calculate the mode at each row. yet, "Mode" is not an option in Table summary or column formula.

any ideas calculating the mode are welcome including in sending to SAS.

i am using JMP 10.

thank you,

Ron

Re: finding the mode

Hi - the mode is the most common item, so you're after the person (or presumably persons if there's a tie) who received the most number of letters from each sender, is that right?  If so, I think the following ought to do it, although I've no doubt there's a much more elegant and/or shorter way.  I've deliberately left all the intermediate calculations, and named all the tables to leave an audit trail:

dt1 = open ("$SAMPLE_DATA/Mail Messages.jmp");

dt1 << set name("dt1");

dt2 = dt1 << Summary( Group( :From ), N, Subgroup( :To ) );

dt2 << set name("dt2");

Sent_From = (dt2 << get column names);

show(Sent_From);

// Throw away the first two elements of this list;

remove from(Sent_From, {1, 2});

show(Sent_From);

// Stack them up;

dt3 = dt2 << Stack(columns(eval(Sent_From)), Source Label Column( "Label" ), Stacked Data Column( "Data" ));

dt3 << set name("dt3");

// dt3 now contains the number of letters sent from each person to each recipient;

dt4 = dt3 << Summary( Group( :From ), Max( :Data ) );

dt4 << set name("dt4");

// dt4 now contains the maximum number of letters sent from each person to any one recipient;

dt5 = dt4 << Join( With( dt3 ),

       SelectWith(:From, :Label, :Data ),

       By Matching Columns( :From = :From, :Name( "Max(Data)" ) = :Data ),

       Drop multiples( 0, 0 ),

       Name("Include non-matches" )(0, 0),

       Preserve main table order( 1 )

       );

dt5 << set name("dt5");

column(dt5, "Label") << set name("Recipient");

/*

     dt5 should now contain the maximum number of letters sent from each person to anyone recipient

     and the name of that recipient (or recipients in the event of a tie)

*/

That should give you that Ann sent 4 letters to Michael, Jeff sent 7 letters to Michael, John sent 12 letters to Michael, Katherine sent 12 letters to John and so on.  Would you like to try that out and see if it does what you need?  To get the maximum number of letters received by any recipient from any one sender, just swap over the :To and :From columns.

Re: finding the mode

Thank you Dodo,

it works fine.

Re: finding the mode

hi Dodo and all,

Dodo's solution is technically correct but is not useful when dealing with big data.

thinking it over i have an alternative solution which is scalable.

dt1 = Open( "$SAMPLE_DATA/Mail Messages.jmp" );

dt1 << set name( "dt1" );

// produce a distribution of revcivers by senders

dt1 << New Script( "Distribution", Distribution( Nominal Distribution( Column( :To ) ), By( :From ) ) );

Dist = Dt1 << Run Script( "Distribution" );

// extract the frequency tables as one data table.

dt2 = Report( dist[1] )["Frequencies"][Table Box( 1 )] << Make Combined Data Table;

dt2 << set name( "dt2" );

dist << close window;

// remove the "Total" rows from the data table - they do not represent relevant Frequencies

selection = dt2 << Select Where( :Level == "Total" );

selection << Delete Rows;

// summarize to get the mode "To" for each "From"

dt3 = Data Table( "dt2" ) << Summary( Group( :From ), Max( :Count ), statistics column name format( "column" ), Link to original data table( 0 ) );

dt3 << Set Name( "Mode" );

// update the summary table to get the values of "To"

dt3 << Update( With( Data Table( "dt2" ) ), Match Columns( :From = :From, :Count = :Count ), Add Columns from Update table( :Level ) );

dt2 << close window;

Column( dt3, "Level" ) << Set Name( "Mode" );

Re: finding the mode

There are often several ways to perform a task in JMP. I just recalled a function I wrote some time ago that's quite efficient for finding the most common item grouped by one a or more columns. I share it here as an alternative to the above approaches. The result is similar to Dodo's, i.e. all "tied" modes are returned, not only the last as in Ron's approach using update().

mode_by_groups = Function( {collist}, // list of column names

  // Find mode of last column in collist

  Summarize( g = by( Eval( collist ) ), n = Count() );

  X = Design( g[1] ) n;

  i = Loc( V Max( Transpose( X == Repeat( V Max( X ), N Row( n ) ) ) ) );

  //Add grouping columns to new table

  dt1 = New Table( "modes" );

  For( k = 1, k <= N Items( collist ) - 1, k++,

  dt1 << (New Column( char(collist[k]), Character, set values( g[k][i] ) ));

  );

  //Add Mode and N

  dt1 << New Column( "Mode of " || char(collist[k]), Character, set values( g[k][i] ) );

  dt1 << New Column( "N", set values( n[i] ) );

);

//Examples

Open( "$SAMPLE_DATA/Mail Messages.jmp" );

mode_by_groups( {From, To} );

Open( "$SAMPLE_DATA/Consumer Preferences.jmp" );

mode_by_groups( {Gender, Single Status, Birth Year} );