cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
megball
Level I

Scripting average of last five points for each row type

Hi,

I am having issues trying to figure out the best way to average only the last five data points for each row identifier using jsl. I tried to set a sequence and then delete the first x points of the sequence, but the issue is that not each row identifier has the same number of rows. The number of identifiers is also subject to change based on the data pull.


Ex, for the following table I want to output the mean of just the last five points from A, B and C (but the identifier list could go all the way up to Z):

example table.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Scripting average of last five points for each row type

Would something like this work?

 

avgs = Associative Array( dt:ID );
ids = avgs << get keys;

For( i = 1, i <= N Items(ids), i++,
	these = As List( dt << get rows where( dt:ID == ids[i] ) );
	If( N Items(these) < 5, 
		n =  N Items(these),
		n = 5;
		Remove From( these, 1, N Items(these) - n );
	);
	these = Matrix( these );
	avgs[ids[i]] = Sum(dt:Value[these]) / n;
);

Show(avgs);

View solution in original post

4 REPLIES 4

Re: Scripting average of last five points for each row type

Would something like this work?

 

avgs = Associative Array( dt:ID );
ids = avgs << get keys;

For( i = 1, i <= N Items(ids), i++,
	these = As List( dt << get rows where( dt:ID == ids[i] ) );
	If( N Items(these) < 5, 
		n =  N Items(these),
		n = 5;
		Remove From( these, 1, N Items(these) - n );
	);
	these = Matrix( these );
	avgs[ids[i]] = Sum(dt:Value[these]) / n;
);

Show(avgs);
megball
Level I

Re: Scripting average of last five points for each row type

Thank you! I was able to adapt this to what I needed. The next question is though, how can I spit these average values out to a summary table?

Re: Scripting average of last five points for each row type

You can just create a new table using the keys and values of the associative array:

 

dtSummary = New Table( "Summary",
	New Column( "ID", "Character", Set Values( avgs << Get Keys ) ),
	New Column( "Average", "Numeric", Set Values( avgs << Get Values ) )
);
megball
Level I

Re: Scripting average of last five points for each row type

Works like a charm, thanks!