Find averages given conditions

3 views (last 30 days)
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.

Accepted Answer

Githin George
Githin George on 21 Jul 2022
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.
  1 Comment
alphabetagamma
alphabetagamma on 27 Jul 2022
I see. thanks a lot. I forgot to reply earlier.

Sign in to comment.

More Answers (1)

Cris LaPierre
Cris LaPierre on 21 Jul 2022
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.

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by