topic Re: How to replace empty rows until next entry (and continue)? in Discussions

How to replace empty rows until next entry (and continue)?

SDF1 — Wed, 27 May 2020 17:09:46 GMT

Dear JMP community,

(W10, 64-bit, JMP 15.0 Pro)

I have a large data table that is imported from an excel file and I need to have empty rows of a column filled with the entries of the previous list. I am trying to write JSL to go from a column as on the left to the one on the right, as shown below:

I'm thinking it should be pretty easy to code, but the issue I'm having is correctly stopping at the end of the previous product and starting with the new product. I must not be looping through correctly.

Any suggestions/help is welcome.

Thanks!,

Re: How to replace empty rows until next entry (and continue)?

Thierry_S — Wed, 27 May 2020 17:29:54 GMT

Hi, here is a simple formula to should to the trick

If( Is Missing( :Column 1 ),
	Lag( :Column 2, 1 ),
	:Column 1
)

Best,

Re: How to replace empty rows until next entry (and continue)?

SDF1 — Wed, 27 May 2020 18:10:09 GMT

Hi @Thierry_S ,

Your formula probably would work, but I couldn't get it to work ideally with the particular table I have. One problem I found is that sometimes I have several rows one after the other where the Product changes, and the lag wouldn't work.

I did come up with this code, which works perfectly. It's not very elegant, but it works!

Thanks for your help!,

RowReplace = dt << Get Rows Where( :Product != "" );
RowEnd = RowReplace;
Remove From( RowEnd, RowEnd[1] );
Insert Into( RowEnd, N Rows( dt ) );
		
		
For( l = 1, l <= N Items( RowReplace ), l++,
	For( irow = 2, irow <= RowEnd[l], irow++,
		If( Is Missing( :Product[irow] ) == 1,
			:Product[irow] = :Product[RowReplace[l]], 
		)
	)
);

Re: How to replace empty rows until next entry (and continue)?

Dan_Obermiller — Wed, 27 May 2020 19:03:04 GMT

You have a script that will do the job, but there is an interactive solution as well. Select the column that needs the replacement so that all rows are selected. Right-click in the data grid of that column and choose Fill > Replace Missing with Previous Value.

Re: How to replace empty rows until next entry (and continue)?

SDF1 — Wed, 27 May 2020 19:09:54 GMT

Hi @Dan_Obermiller ,

Thanks for the heads up on that. I have seen the Fill to End, to row, or fill pattern, but not that before.

Do you know the JSL code that does that? The reason I ask is I want this all in JSL code because I'm reading in several files to build a large data table and need to automate the process so the end user just runs the script and out comes their table with no missing values for the product. If it's quicker and more elegant than my solution, I'd like to modify the code and insert it into my script.

Thanks!,

Re: How to replace empty rows until next entry (and continue)?

Byron_JMP — Wed, 27 May 2020 19:13:54 GMT

there is a really fantastic add-in, a must-have called data table tools

there is an interpolation tool in there that does this, and so much more. Plus its much faster than the formulas on big tables.

Re: How to replace empty rows until next entry (and continue)?

Byron_JMP — Wed, 27 May 2020 19:27:35 GMT

These are some JSL notes that will help you if you're scripting the solution

//better version of Fill in Missing Cells, by Brady Brady

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]
);
);
);
///////////////////////////// short tweak for scripting

dtcollist=dtnr<<Get Column Names;
dtMat = (dtnr << Get All Columns As Matrix);
selection={}; insert into (selection, column(1));  insert into (selection, column(2));//these columns get acted on

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 = 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]
);
);
);





///////////////////////////// old version

/*
Names Default to Here(1);
dt = Current Data Table();
rows = N Row( dt );

dtcollist=dt<<get column names;

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

columns=nitems(selection);

Re: How to replace empty rows until next entry (and continue)?

Dan_Obermiller — Wed, 27 May 2020 19:34:17 GMT

I don't know the JSL code behind the interactive solution. That should be fine since you have multiple options there.

I do know that the interactive solution, like some of the scripts, can be applied to multiple columns simultaneously. Just select all of the columns and right-click, choose the Fill option.

Re: How to replace empty rows until next entry (and continue)?

Mark_Bailey — Wed, 27 May 2020 20:06:48 GMT

I can't find the JSL function or message that performs the same operation. I don't think it exists. There is a Main Menu() function but this command is associated with the selected column, not the main JMP menu.

Re: How to replace empty rows until next entry (and continue)?

SDF1 — Thu, 28 May 2020 18:36:13 GMT

Hi @Byron_JMP ,

Thanks for the scripting tweak code in your previous post. This one is better than my solution because it checks to see if the first entry in the column is missing, and if so, it replaces it with the next existing entry, which I have found happens in a couple imports I bring in. I am not experienced with some of the scripting solutions there, such as how you use the vec and matrix information to search up certain things. Anyway, I applied it to my specific problem, and it fixed it without a hiccup.

Thanks!,

Re: How to replace empty rows until next entry (and continue)?

Byron_JMP — Fri, 29 May 2020 11:28:10 GMT

A couple of the JMP systems engineers were going round and round on this problem and this was the best solution. This final version by @Brady Brady is heads a tails faster and more robust than the simple formula we started with. I've been using it for years and found it to work well in a number of situations. Glad it worked for you!