I have a raw data set that includes hourly pH values at multiple sites ("Sonde_Names") for two three-month periods: one in year 2013 and another in year 2014.
For each of the "Sonde_Names", I need to generate a list showing the number and duration of events where the pH is below a value of 5.5 (the field "BlwThresh" indicates which records are below 5.5). An 'event' is defined as one or more contiguous values of 1 in the "BlwThresh" field. This list also needs to include the associated values for "Sonde_Names", "Year", "Month", "Day", and "Hour", in which the event began.
(If possible, the "Event_num" field should reset to a value of 1 when starting to process each unique set of "Sonde_Names" and "Year" values.)
The worksheet named "tally_duration" shows a few manual examples of the objective.
Many thanks in advance for a (hopefully portable) automated solution.