Fill Missing Cells

[#1]

This tool allows user to fill missing values based on previous/next/nearest found value.There are already tools which can do this in a way or another (even JMP has fill rows), but the ones I have used/seen are missing at least the grouping option this Add-in you to do, also this shouldn't require you to have data table sorted if you use Order column.

Current Options available

These are the current options found from the tool

Generic

Option	Description	Value
Keep dialog open	Keeps dialog open if checked after run is pressed	0,1
Data sorted	Possibility to choose if it is know that data is sorted. Makes script faster	Unknown, Not-sorted, Sorted
Maximum Distance	Maximum distance of values which will still be considered	Missing (.) or num

Different fill types

Type	Description
backward	Looks for last found value before missing value
forward	Looks of first value after missing value
nearest	Looks for nearest found value
nearest-backward	Looks for two closest values and if the distances are tied, uses value found before
nearest-forward	Looks for two closest values and if the distances are tied, uses value found after
interpolate	Uses JMP's Interpolate() function

Fill Unknown

Can be used with backward and forward fills. Copies latest values to missing cells which have no found value from the found values

New Column

Creates new column with Fill Type prefixed to name

Using the tool can be a bit tricky as the UI isn't best possible and I have yet to write proper instructions. You can post improvement ideas here here or in open issue in GitHub - Fill Missing Cells .

This tool is part of JMP-Tools I have been working on for some time now and I will release more Add-Ins when time permits in JMP Community.

Updates:

* Hopefully fixes bug with ordering column not being used and forward lookup not working if ordered data was used.

[aadecarlojr]

This was great, thanks.  I can get it to "group" according to column A, but it is filling up (forward) to the relevant data which happens to be, in this table, one row above the end of the character group in column A. It does this only if "data not sorted" is checked.  So close, but not perfect for me, yet.

[Steffen_Bugge]

Hi @jthi 
I am having some challenges with the add-in. I want to fill in cells forwards, i.e. copy first value after missing value. The backward function works as intended, but the forward is not as I would expect. The rows are sorted in the correct order, so the only column I have added is the column to be filled in. Do you have any suggestions? 

[jthi]

For me it seems to work fine (without any groups)

I have fixed some bugs to this add-in but I haven't published those to JMP community as it is a nightmare to keep JMP add-ins updated in four different locations. I might update this and join nearest if I ever have enough time and motivation to do it.

If you don't need to have groups, JMP's fill might be enough

[Steffen_Bugge]

Thanks @jthi !

JMP's fill will to the trick. I will sort the table backward to get the forward fill

[FeralCass]

This is great! Thank you! Helped me to complete some material traceability. Does anyone know how to incorporate this into a jsl script? I copied the log text, but it can't run as is:

FMC:fill_missing_cells(DataTable("report"), "Work Order_backward", "", {}, "backward", -1, ., 1)
FMC:fill_missing_cells(DataTable("report"), "WO Batch", "", {}, "backward", -1, ., 1)
FMC:fill_missing_cells(DataTable("report"), "WO Qty", "", {}, "backward", -1, ., 1)
FMC:fill_missing_cells(DataTable("report"), "RM Part Number", "", {}, "backward", -1, ., 1)
FMC:fill_missing_cells(DataTable("report"), "RM Description", "", {}, "backward", -1, ., 1)

Are there some edits that would allow it to run automatically?

Thank you,

Cassidy

[jthi]

You have to include the correct files from the add-in. You should be able to access the add-in folder with path variable $ADDIN_HOME(jthi.jmp.tools.fill.missing.cells)

Convert File Path("$ADDIN_HOME(jthi.jmp.tools.fill.missing.cells)")

I quickly took a look at the code (it is due a big rewrite...) and you should be able to get it working with something like this

Names Default To Here(1);

FMC = Include("$ADDIN_HOME(jthi.jmp.tools.fill.missing.cells)\bin\FillMissingCells.jsl");

// demo
dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt[10::40, "sex"] = "";

// This script doesn't create the column, if you don't wish to overwrite values (I think you shouldn't)
// then create a "copy" first
dt << New Column("sex_backward", Character, Nominal, Values(dt[0, "sex"]));
FMC:fill_missing_cells(DataTable("Big Class"), "sex_backward", {}, {}, "backward", -1, ., 1);

// If you replace in place just use original column
FMC:fill_missing_cells(DataTable("Big Class"), "sex", {}, {}, "backward", -1, ., 1);