topic Re: Using a formula column, how do I find a value in a subset of the data based on other columns in Discussions

Using a formula column, how do I find a value in a subset of the data based on other columns

Cactus123456 — Thu, 26 Oct 2023 21:09:02 GMT

If I have two different time series in a table, and there is a findable event in both series, but they occur at different times. How can I specify a formula column to subtract off the time such that the finable events start at time zero?

Re: Using a formula column, how do I find a value in a subset of the data based on other columns

hogi — Thu, 26 Oct 2023 21:42:44 GMT

If the signal is very clean, you can just use lag to find the jump - otherwise you have to invest more effort to remove noise etc.

Then you just have to subtract the offset of the jump from your original time.

Col ... aggregations like Col Maximum can be used to spread one value to all rows - which then can be subtracted.

A second argument of the Col ... aggregation can be used as GroupBy argument (1 maximum per group) to subtract individual offsets for the 2 traces.

t1 = random integer(1000);
t2 = random integer(1000);

dt = new table("test",
add rows(2000),
New Column( "time",  Set Each value(Mod(row()-1,1000)) ),
New Column( "sample",  Set Each value(floor((row()-1)/1000)+1) ),

New Column( "trace",  Set Each value(if(:time<match(:sample,1,t1,2,t2),1,0 )))
);

New Column( "marker",  Set Each value(if(lag(trace)==1 & :trace==0,time,.)));

New Column( "time_adjusted",
	set each value( :time - Col Maximum( :marker, :sample ) ),
	Set Selected
);

Graph Builder(
	Variables(
		X( :time ),
		X( :time_adjusted ),
		Y( :trace ),
		Group Y( :sample )
	),
	Elements( Position( 1, 1 ), Line( X, Y, Legend( 14 ) ) ),
	Elements( Position( 2, 1 ), Line( X, Y, Legend( 15 ) ) )
)

Re: Using a formula column, how do I find a value in a subset of the data based on other columns

Cactus123456 — Mon, 30 Oct 2023 17:49:39 GMT

This solved my problem exactly!

I think col ... aggregations was something I was sorely missing in my mental toolbox.

Thank you!