Subscribe Bookmark RSS Feed
Byron_JMP

Staff

Joined:

Apr 26, 2012

Fill in Missing Cells

Report tables often end up having incomplete columns where labels need to be copied to the sequential cells below until the next label where the process is repeated again and again. The Excel Import Wizard has an option to deal with spans of missing cells that are merged. This script can be used after the data is imported and it doesn't require the spans of missing cells to be merged.

I've been using this script, or versions of it, for so long that I'm not sure where the bones of it came from.

Comments
martindemel

I wonder if one could do this without Expressions. For one single column "MyCol1" you could do sth. like this:

For Each Row( If( Is Missing( MyCol1 ), :MyCol1= Lag( MyCol1, 1 ) ) );

Using an automated approach to select the columns like you do combined with this should be able to do. 

Byron_JMP

Making the script into an add-in or putting it into a menu option is my crutch for forgetting the formulas to make it happen,

This is another easy formula to do this. 

:Complete is the new column where the formula lives.

:Missing is the source column with missing rows.

//Rows with Character

If( :Missing == "",

  :Complete[Row() - 1, Empty()],

  :Missing)

//Rows with Numbers

If( Is Missing( :Missing ) == 1,

  :Complete [Row() - 1, Empty()],

  :Missing)

Byron_JMP

I'm updateing this add in with this script. Thanks to Brady Brady for some tweaks that make this run faster on really big tables. On a 5MM row table with something to fill in every 10K rows it takes 10-15 sec.

 

Names Default To Here( 1 );
dt = Current Data Table();

dtcollist = dt << get column names;

dlg = Dialog( "Select Column", vlist( selection = List Box( dtcollist ), button( "OK" ) ) );
Remove From( dlg, 2 );
Eval List( dlg );

dtMat = (dt << Get All Columns As Matrix);

For( i = 1, i <= N Items( selection ), i++,
colNum = Contains( dtColList, Name Expr( selection[i] ) );
vMat = dtMat[0, colNum];
mis = Loc( Is Missing( vMat ) );
If( N Row( mis ),
col = column(selection[i]);
If( mis[1] == 1,
col[1] = col[(Loc( vMat ))[1]]
);

For( j = if(mis[1]==1, 2, 1), j <= N Row( mis ), j++,
col[mis[j]] = col[mis[j] - 1]
);
);
);
martindemel

A similar issue, but a bit different is the case where you want a certain number of cells before the actual entry to have the same value. There you need to make adjustments to not overwrite the previous e.g. 4 cells in case there is a nonmissing entry. Attached a script and a screenshot of the datatable before and after the script has been run. In this script I only covered the numeric case.

Names Default To Here( 1 );
// Get Current Data Table
dt = Current Data Table();

// Get Column Names from selection box (where you want to fill)
dtcollist = dt << get column names;
dlg = Dialog(
	"Select Column",
	vlist( selection = List Box( dtcollist ), button( "OK" ) )
);
Remove From( dlg, 2 );
Eval List( dlg );

// Get matrix of values in selected columns 
dtMat = (dt << Get All Columns As Matrix);

// For loop to loop through all selected columns 
For( i = 1, i <= N Items( selection ), i++,
	// get i-th selected column
	colNum = Contains( dtColList, Name Expr( selection[i] ) );
	// get values as matrix
	vMat = dtMat[0, colNum];
	// get rows where matrix is non-empty
	mis = Loc( vMat );
	// If there is non-missing value loop through previous rows
	If( N Row( mis ),
		// Get i-th Column name
		col = Column( selection[i] );
		/*
		If( mis[1] == 1,
			col[1] = col[(Loc( vMat ))[1]]
		);
		*/
		// Loop to fill previous 4 rows from the row with the k-th non-missing value
		For( k = 1, k <= N Rows(mis), k++,
		For( j = If(mis[k]-4 < 1, 1,mis[k]-4), j < mis[k], j++,
			If(Is Missing(col[j]), col[j] = col[mis[k]],col[j])
		);
		);
	);
);

Data Table before and after applying the scriptData Table before and after applying the script

 

Byron_JMP

Another update:

Instead of using this script, take a look at this collection of tools: https://community.jmp.com/t5/JMP-Add-Ins/Data-Table-Tools-Add-in/ta-p/28582

 

This add-in has a better version of the script above plus a whole lot more.

 

Cheers,

Byron

Article Labels
Article Tags