topic Re: Calculate average of x highest hourly values per day in Discussions

Calculate average of x highest hourly values per day

FourPeaks — Sat, 10 Jun 2023 23:50:07 GMT

We are looking to average the highest x hourly values in a given day across large data sets. For example, the average of the 12 highest hourly values within a given calendar day over a five-year period. The data include date, hour, and value for various metrics. Thank you for any suggestions.

Re: Calculate average of x highest hourly values per day

jthi — Wed, 22 Jun 2022 14:50:43 GMT

There are many options for this:

Summary tables (and formulas)
Formulas with Col functions
Scripting

Attached is one option on how to do it with couple of formulas, first calculate rank of data for date (col number and col rank) and then based on that take top x (in example 3) and calculate average of those (col mean with if statement)

Re: Calculate average of x highest hourly values per day

FourPeaks — Wed, 22 Jun 2022 15:18:44 GMT

Works great - any suggestions on how best to do it by "group" (or location, for example)? I have several sampling locations that have hourly values on the same date.

Re: Calculate average of x highest hourly values per day

jthi — Wed, 22 Jun 2022 15:21:39 GMT

You should be able to add more grouping variables to the Col functions

Re: Calculate average of x highest hourly values per day

FourPeaks — Wed, 22 Jun 2022 15:52:27 GMT

Thanks for the help @jthi - don't know if I'm too novice but I can't seem to get it right.

Here is the formula that I'm attempting to group by "Site" in addition to date.

(1 + Col Number( 1, :Date )) - Col Rank( :"Gas"n, :Date )