topic Re: Calculate Column Difference for other column levels where lag varies in Discussions

Calculate Column Difference for other column levels where lag varies

ashwint27 — Thu, 08 Aug 2024 17:38:20 GMT

I have a column :EVENT (character-nominal) with various values. Also another column :datetime (numeric-continuous). I have :datetime column sorted ascending. I am interested in having another column calculate for whenever there is a "CONNECT" event, what is the time difference between it and the last "DISCONNECT" event. The issue is that the lag can vary between a CONNECT and previous DISCONNECT (see photo below) and I'd also prefer keeping this tall table format. A solution for datetime sorted-ascending is appreciated, but also not sorted would also be beneficial.

Re: Calculate Column Difference for other column levels where lag varies

jthi — Thu, 08 Aug 2024 17:53:05 GMT

You can for example use variable in the formula to store the value of last disconnect

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("E", Character, "Nominal", Set Values({"A", "B", "Disc", "Con", "A", "B", "C", "B", "Disc", "A", "B", "Con"})),
	New Column("T", Numeric, "Continuous", Format("Best", 12), Set Values([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]))
);

dt << New Column("Dif", Numeric, Continuous, Formula(
	If(Row() == 1,
		last_disc = .;
		retval = .;
	);
	If(:E == "Disc",
		last_disc = :T;
		retval = .;
	, :E == "Con",
		retval = :T - last_disc
	,
		retval = .;
	);
	retval;
));

Re: Calculate Column Difference for other column levels where lag varies

ashwint27 — Thu, 08 Aug 2024 18:15:49 GMT

This approach worked great, thanks!

Re: Calculate Column Difference for other column levels where lag varies

jthi — Thu, 08 Aug 2024 18:23:00 GMT

You can also use matrices

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("E", Character, "Nominal", Set Values({"A", "B", "Disc", "Con", "A", "B", "C", "B", "Disc", "A", "B", "Con"})),
	New Column("T", Numeric, "Continuous", Format("Best", 12), Set Values([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]))
);

events = :E << get values;
times = :T << get values;

discs = Loc(events, "Disc");
cons = Loc(events, "Con");

difs = J(1, N Rows(dt), .);
difs[cons] = times[cons] - times[discs];

dt << new column("difs", numeric, continuous, values(difs));

This does have quite a few assumptions but can be made more robust for all sorts of edge cases (Loc Sorted can be helpful for something like this, sometimes even KDTable/VPTree).

Re: Calculate Column Difference for other column levels where lag varies

ashwint27 — Tue, 13 Aug 2024 20:48:51 GMT

To throw a slight wrinkle into this, there are times where a CONNECT Event may show up a few times before a DISCONNECT Event comes up. In this case, I'd like the difference to only be calculated on the first CONNECT Event that appears after a DISCONNECT Event. Is there a slight modification to the script that can accommodate this? Thanks again for your help.

Re: Calculate Column Difference for other column levels where lag varies

txnelson — Tue, 13 Aug 2024 21:37:28 GMT

Here is a modification to @jthi code that should handle this.

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("E", Character, "Nominal", Set Values({"A", "B", "Disc", "Con", "A", "B", "C", "B", "Disc", "A", "B", "Con"})),
	New Column("T", Numeric, "Continuous", Format("Best", 12), Set Values([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]))
);

dt << New Column("Dif", Numeric, Continuous, Formula(
	If(Row() == 1,
		last_disc = .;
		retval = .;
	);
	If(:E == "Disc",
		last_disc = :T;
		retval = .;
		firstCon = .;
	, :E == "Con",
		If(ismissing(firstcon), firstcon=:t);
		retval = firstcon - last_disc
	,
		retval = .;
	);
	retval;
));

I suggest you take the time to read the Scripting Guide, available under JMP Help. Also please take the time to study the code provided by responding Discussion Forum members. The modifications to Jarmo's script did not require any new scripting knowledge. It only required some additional logic added to the flow of the script. It should be a user's goal to be able to make their own enhancements to the scripts