turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Scripting average of last five points for each row...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2017 7:17 AM
(1144 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2017 7:52 AM
(2244 views)

Solution

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

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2017 7:52 AM
(2245 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2017 11:55 AM
(1121 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2017 12:23 PM
(1116 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 8, 2017 1:46 PM
(1111 views)