summary analysis on repeated data

dingo

Hi everyone,

 

I need to get the number of rows of  reaped sequence of grouped data e.g. a column data of the form:

aaabbbbbccaaadddbbb...

Using Summary Statistics option and grouping by this column will result in total number of occurrences for each value of abcd (N Rows): 6,8,2,3. what I need is the number of rows for each group separately in sequence:

a,b,c,a,b,d (N Rows): 3,4,2,3,3,3

 

any idea?

 

Thanks,

 

 - Dean

txnelson

My suggestion is that you create a new formula column using the following formula:

If( Row() == 1, counter = 0 );
If( Lag( :yourgrouppedcolumnname ) != :yourgrouppedcolumnname,
	counter++,
	counter
);
counter;

Replacing "yourgrouppedcolumnnmae" with the actual column name that has the repeating columns.

Then you can run the Summary Platform 

     Tables==>Summary

on the new column, and it will give you the counts for each of the groupings.

If you need the results back into the original table, you can simply use the Update Platform on the original data table, pointing to the Summary table, and matching on the new column you created.  

Below is a sample script using the Big Class sample data table.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );

// Create the new binning column based upon the "Sex" column
dt << New Column( "Bin",
	formula(
		If( Row() == 1, counter = 0 );
		If( Lag( :Sex ) != :Sex,
			counter++,
			counter
		);
		counter;
	)
);

// Calculate the counts of the new binning column
dtSummary = dt << Summary(
	Group( :Bin ),
	Freq( "None" ),
	Weight( "None" ),
	Link to original data table( 0 )
);

// Move the counts into the original data table
dt << Update( With( dtSummary ), Match Columns( :Bin = :Bin ) );