Grouping and comparing Data

Hello Matlab Community,

I have to compare the montlhy average values to check if it was low, med or high voltage year with Threshold values . I have variable 'M_Avg' which has monthly averages for every month for 'x' number of years. For comparison, there are 12 threshold values one for each month.

Criteria: If all 12 'Mon_Avg' values from Jan to Dec for particular year was greater than all the 12 thershold values from Jan to Dec, then that year is considered to have high voltage.

If all 12 'Mon_Avg' values from Jan to Dec for particular year was less than all 12 thershold values from Jan to Dec, then that year is considered to have low voltage.

Problem: My problem is that how should I compare the values as I have to compare the 12 monthly average values of every year with the 12 threshold values and store the output in three bins 'High', 'Med' and 'Low'.

Code:

data = readtable('test1.xlsx');
M_Avg = groupsummary(data,'Date','monthofyear','mean')
Th_Val are stored in table as shown below:
'1'		1049.19120309990
'2'		961.862060607920
'3'		899.732130459928
'4'		884.837887900055
'5'		1075.62948358790
'6'		1245.70427769469
'7'		1418.83660830230
'8'		1446.05197526924
'9'		1389.40728025644
'10'    	1315.04507488937
'11'		1221.84615229043
'12'		1099.94221963989

Answer 1

[dpb]

One way amongst many --

BINS=categorical([-1:1],[-1:1],["Low","Med","High"]);      % the end categories
YrTotal=sum(sign(M_Avg-Th_Val));                           % year difference relative thresholds
YrBin=BINS(interp1([-12,-11,11,12],[1,2,2,3],YrTotal).');  % the year bin classification

Algorithm: There is only one set of values that will equate to high or low as long as the criterion is "all" must exceed or be below the threshold(*). In that case the sum of the sign() of the difference is +/-12; anything else is "Med". Since the sum() must also be an integer in the range [-12,12], it's simple to interpolate between and return the bin number as integer between [1;3], the array of BINS.

This seems an extreme definition; one would think this would almost never occur.