Manipulating column cells

laumeister

Hello JMP community,

 

Have a series of columns with integers, ranging from 0-100. The column always starts with a 0, and then starts to increase from a certain row number. How can I delete the rows with all 0s, then upshift the non-zero cells? Also I need to apply that for all column on the same sheet. 

 

Example ( '.' means empty cell) initial data:

            Col-1    Col-2   Col-3

Row-1   0              0           0     

Row-2   0              0           0

Row-3   0              0           0

Row-4   1              0           1

Row-5   3              1           2

Row-6   8              4           7

 

 

End result after manipulation:

            Col-1    Col-2   Col-3

Row-1   1              1           1     

Row-2   3              4           2

Row-3   8              .           7

Row-4    .              .           .

Row-5   .              .           .

Row-6   .              .           .

 

Thanks a lot!

 

Steve

 

 

txnelson

Here is a sample script that moves the values up for each independent column.  The example data used is the data you provided

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 6 ),
	New Column( "Col 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0, 0, 0, 1, 2, 3] )
	),
	New Column( "Col 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0, 0, 0, 0, 1, 4] )
	),
	New Column( "Col 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [0, 0, 0, 1, 2, 7] )
	),
	Set Row States( [1, 1, 1, 1, 1, 1] )
);

colNames = dt << get column names( numeric, string );

For( i = 1, i <= N Items( colNames ), i++, 

	theMatrix = Column( colNames[i] ) << get values;
	Column( colNames[i] )[Index( 1, N Rows( dt ) )] = .;

	Column( colNames[i] ) << set values( theMatrix[Loc( theMatrix )] );
);