Find averages given conditions
3 views (last 30 days)
alphabetagamma on 21 Jul 2022
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:
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.
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
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.
More Answers (1)
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.