Find averages given conditions

I have parameter values for several variables stored in an excel sheet. The first column has the parameter values a1, a2, b1, b2, c1, c3....etc for each of cities MA, CA, JA, KA, etc. The second column has their corresponding estimates. In total there are about 50 parameters for each country in the excel sheet. Here's a snapshot of the contents of the sheet:

a1_MA 9.38489

a1_CA 7.29373

a1_JA 5.2937

a1_TA 2.83746

a1_KA 4.293774

b1_MA 5.294774

b1_CA 4.29387

b1_JA 3.836763

b1_TA 2.38473

b1_KA 1.394773

......

I have to group the parameters based on the cities and find the average. For example,

group1 = [MA, CA]

a1_avg_g1 = (a1_MA+a1_CA)/2

b1_avg_g1 = (b1_MA+b1_CA)/2

group2 = [JA, TA, KA]

a1_avg_g2 = (a1_JA+a1_TA + a1_KA)/3

b1_avg_g2 = (b1_MA+b1_CA + b1_KA)/3

Likewise, after finding all the averages I have to store them in another excel file. How can I implement this process in MATLAB? I really appreciate your help.

Answer 1

[Githin George]

My understanding is that you will be having different groups and you need to compute average of each parameter for those cities. I am also making the assumption that all cities have the same parameters.

You can try out the following idea

% contains method checks whether MA or CA is within the string in Col1 and
% returns a logical array. It is used to index into only those rows of the
% table
filteredTable = tab(contains(tab.Col1,["MA" "CA"]),:)

Now Since you have the filteredTable you can do a for loop and compute the average for 'no_of_params' rows at a time.

You can add these averages to a new table and use 'writetable(newTable,"excel.xlsx")' to export it.

[alphabetagamma]

I see. thanks a lot. I forgot to reply earlier.

Answer 2

[Cris LaPierre]

When loading the original data into MATLAB, I would specify that "_" is a delimiter. This will cause the first column to be read in as 2 separate variables. Then you could just use logical indexing to find the parameter and state for each group. See Ch 11 of MATLAB Onramp.