topic Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event' in Discussions

How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

WernerL — Fri, 07 Feb 2020 12:14:12 GMT

Hi JMP Community, I am working with a table with thousands of rows. In one column certain rows are marked 'event' (e.g. an out-of-spec lot on a particular day). How can I create a new column where the x rows before an 'event' are marker PRE and the y rows after the event are marked POST. In the example below x=3 and y=5.

Thank you for your help!

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

pmroz — Fri, 07 Feb 2020 16:19:21 GMT

It's not clear from your example when the next "PRE" should start. Can you provide a small example dataset?

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

WernerL — Fri, 07 Feb 2020 16:36:49 GMT

@pmroz Please find attached brief example.

Each 'PRE' should start (in this example) 3 rows before the 'Event'-row and stop 1 row before the 'Event'

Each 'POST' should start 1 row after the event and stop 5 rows after the 'Event'

Thank you!

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

pmroz — Fri, 07 Feb 2020 16:58:02 GMT

This should do it:

/* Each 'PRE' should start (in this example) 3 rows before the 'Event'-row and 
stop 1 row before the 'Event'
Each 'POST' should start 1 row after the event and stop 5 rows after the 'Event'
Thank you! */
dt = New Table( "pre-post example", Add Rows( 23 ),
	New Column( "Column 1", Character, "Nominal",
		Set Values(
			{"", "", "", "", "Event", "", "", "", "", "", "", "", "", "", "", "", "",
			"Event", "", "", "", "", ""}
		)
	),
	New Column( "Column 2", Character, "Nominal",
		Set Values(
			{"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
			"", "", "", "", ""}
		)
	)
);

event_rows = dt << get rows where(dt:Column 1 == "Event");
nr = nrows(event_rows);
if (nr > 0,
	for (i = 1, i <= nr, i++,
		start_row = maximum(event_rows[i] - 3, 1);	// Keep from trying to set a negative row
		for (k = start_row, k < event_rows[i] , k++,
			dt:column 2[k] = "PRE";
		);
		end_row = minimum(event_rows[i] + 5, nrows(dt));	// Prevent setting higher rows than are in the table
		for (k = event_rows[i] + 1, k <= end_row, k++,
			dt:column 2[k] = "POST";
		);
		dt:column 2[event_rows[i]] = "Event";
	);
);

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

txnelson — Fri, 07 Feb 2020 17:19:09 GMT

Here is a formula that is a real brut force method. It looks more complex than it really is, because of your choice of having a minus sign "-" as part of the column name, forces the code to use the :Name() function around the column name.

If(
	:Name( "Column 1 -Existing" )[Row() + 1] == "Event" |
	:Name( "Column 1 -Existing" )[Row() + 2] == "Event" |
	:Name( "Column 1 -Existing" )[Row() + 3] == "Event", "PRE",
	:Name( "Column 1 -Existing" )[Row() - 1] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 2] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 3] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 4] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 5] == "Event", "POST",
	:Name( "Column 1 -Existing" )[Row()] == "Event", "Event"
)

Attached is your sample data table with the new column added

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

WernerL — Sun, 09 Feb 2020 13:58:20 GMT

@pmroz, I tried to run your proposed solution, but it creates multiple new tables? (instead of one new column, in the existing table)

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

WernerL — Sun, 09 Feb 2020 14:09:49 GMT

Dear Jim (@ txnelson ) ,

Thank you for offering this solution, which does exactly what I was asking.

Playing around with it, I learned that I overlooked one challenge in my original question: when 'Event'-s come too close after each other, below rules may overwrite previous outcomes.

Is there a way to further fine-tune your solution by embedding following prioritization rules:

- 1st priority: An 'Event' row, always copy in the new column as an 'Event' row and cannot be overwritten by 'POST' or 'PRE';

- 2nd priority: A 'POST' outcome can never be overwritten by a 'PRE'

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

txnelson — Sun, 09 Feb 2020 15:20:30 GMT

A little adjustment for your additional logic and the following JSL I believe gives you what you want

If(
	:Name( "Column 1 -Existing" )[Row()] == "Event", "Event",
	(:Name( "Column 1 -Existing" )[Row() - 1] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 2] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 3] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 4] == "Event" |
	:Name( "Column 1 -Existing" )[Row() - 5] == "Event") &
	:Name( "Column 1 -Existing" )[Row()] == "", "POST",
	(:Name( "Column 1 -Existing" )[Row() + 1] == "Event" |
	:Name( "Column 1 -Existing" )[Row() + 2] == "Event" |
	:Name( "Column 1 -Existing" )[Row() + 3] == "Event") &
	:Name( "Column 1 -Existing" )[Row()] == "", "PRE"
)

Re: How do I create a new column and insert a text x-rows before an 'event' and y-rows after the 'event'

WernerL — Sun, 09 Feb 2020 17:07:50 GMT

Excellent, many thanks Jim!