How to loop over table to caluclate averages?
6 views (last 30 days)
Show older comments
I have a .csv data file containing some ratios for 56 US states (denoted by state_fips) for each quarter for each year. I imported the file into Matlab using readtable. I want to create a loop that calculates avarages (low plus high divided by 2) for each state_fips for each quarter for each year. I then want to have a loop that computes annual ratios for each state_fips (meaning sum of quarterly ratios divided by 4). I need another loop then that calculates an average of annual ratio for any given period (data in the file ranges from 1999 to 2021). I need all ratios to be indexed with respective state_fips so that I can order them. I struggled with doing it so I ask for your help. Here is what I could think of:
mydata = readtable('HH.csv')
% attempt at computing quarterly ratios
for state_fips = 1:1:56
for year = 1999:1:2019
for qtr = 1:1:4
quarterly_dti = (low+high)/2;
end
end
end
% attempt at calculating annual ratios
for state_fips = 1:1:56
for year = 1999:1:2019
annual_dti = (quarterly_dti(i) + quarterly_dti(i+3))/4
end
end
% attempt at calculating overall average ratio for an arbitrary period (e.g. m years)
for state_fips = 1:1:56
overall_dti = (annual_dti(j) + annual_dti(j+m))/(m+1)
end
0 Comments
Accepted Answer
Simon Chan
on 31 Mar 2022
Check the following:
B: quarterly ratios
C:annual ratios
D:overall average ratio for an arbitrary period
rawdata = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/948019/HH.csv');
func = @(x,y) (x+y)/2;
B = rowfun(func,rawdata,'GroupingVariable',{'year','qtr','state_fips'},'OutputVariableName','MeanValue');
B.GroupCount=[]; % Optional
B
C = groupsummary(B,{'year','state_fips'},'mean','MeanValue');
C.GroupCount=[]; % Optional
C.Properties.VariableNames{3}='Annual_Ratio'; % Modify the Variable Name only
C
D = groupsummary(C,'state_fips','mean','Annual_Ratio');
D.GroupCount = []; % Optional
D
More Answers (0)
See Also
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!