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

[#1]

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!

[#2]

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

[#3]

@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!

[#4]

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";
	);
);

[#5]

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

[#6 · Accepted solution]

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

[#7]

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'

[#8 · Accepted solution]

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"
)

[#9]

Excellent, many thanks Jim!