topic Re: JSL - Calculate Mean of Last 3 Rows Per Date in Discussions

JSL - Calculate Mean of Last 3 Rows Per Date

terapin — Fri, 09 Jun 2023 18:02:26 GMT

I have a data set where I'd like to calculate the mean of the last 3 observations for each date and have that value be in its own column and paired with the last date as shown below.

Names Default To Here( 1 );

// Create an example data table
dt = New Table( "Date",
	Add Rows( 30 ),
	New Column( "Date",
		Numeric,
		"Continuous",
		Format( "m/d/y", 10 ),
		Set Values( [3713817600, 3713817600, 3713817600, 3713817600, 3713817600, 3713817600, 3713817600, 3713817600, 3713817600, 3713817600, 
		3715027200, 3715027200, 3715027200, 3715027200, 3715027200, 3715027200, 3715027200, 3715027200, 3715027200, 3715027200,
		3716236800, 3716236800, 3716236800, 3716236800, 3716236800, 3716236800, 3716236800, 3716236800, 3716236800, 3716236800] )
	),

	New Column( "Data",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2,4,6,8,10,12,14,16,18,20,2,4,6,8,10,12,14,26,28,30,2,4,6,8,10,12,14,36,38,40] )
	),	
	New Column( "Desired Result",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [.,.,.,.,.,.,.,.,.,18,.,.,.,.,.,.,.,.,.,28,.,.,.,.,.,.,.,.,.,38] )
	),	
	
);

While I can create a summary table that calculates the mean and then insert the mean back in the original data table, I'm looking for a suggestion for a more elegant and less process intensive method. Any suggestions on how to accomplish this would be appreciated, either as a column formula, or via JSL (preferred).

Re: JSL - Calculate Mean of Last 3 Rows Per Date

txnelson — Wed, 13 Oct 2021 15:37:16 GMT

Here is one way to write the formula to do this

If( :Date != :Date[Row() + 1] | Row() == N Rows( Current Data Table() ),
	Mean( :Data[Row()], :Data[Row() - 1], :Data[Row() - 2] )
)

Re: JSL - Calculate Mean of Last 3 Rows Per Date

jthi — Wed, 13 Oct 2021 16:21:46 GMT

Other option with a formula:

If(Row() == Col Max(Row(), :Date),
	Mean(:Data[Index(Row() - 2, Col Max(Row(), :Date))])
)

Re: JSL - Calculate Mean of Last 3 Rows Per Date

terapin — Fri, 15 Oct 2021 16:28:01 GMT

Thanks for the suggestion, especially one that I could easily wrap my head around. I'll need to dig into the suggestion by jthi more since the solution isn't as intuitive to me as yours is.

Re: JSL - Calculate Mean of Last 3 Rows Per Date

jthi — Fri, 15 Oct 2021 16:36:50 GMT

I can also open my solution a bit as Col functions can be fairly powerful. Take a look at the explanation when you have time:

Use Col Max(Row(), :Date) to get last row for each date
Check with the if-statement with Row() for last row for date
Use Mean to calculate mean of last three rows for a date. Index(Row() - 2, Col Max(Row(), :Date) creates matrix of row numbers. And then with:Data[Index] we can get values for those row numbers

I have attached datatable in which the formula is split in parts