How to loop over table to caluclate averages?

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

Accepted Answer

Simon Chan
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
B = 4590×4 table
year qtr state_fips MeanValue ____ ___ __________ _________ 1999 1 1 1.165 1999 1 2 1.37 1999 1 4 1.37 1999 1 5 0.74 1999 1 6 1.685 1999 1 8 1.37 1999 1 9 0.74 1999 1 10 0.74 1999 1 11 0.74 1999 1 12 1.46 1999 1 13 1.165 1999 1 15 2.01 1999 1 16 1.46 1999 1 17 0.74 1999 1 18 0.74 1999 1 19 0.74
C = groupsummary(B,{'year','state_fips'},'mean','MeanValue');
C.GroupCount=[]; % Optional
C.Properties.VariableNames{3}='Annual_Ratio'; % Modify the Variable Name only
C
C = 1173×3 table
year state_fips Annual_Ratio ____ __________ ____________ 1999 1 1.0588 1999 2 1.3063 1999 4 1.3188 1999 5 0.74 1999 6 1.6062 1999 8 1.3188 1999 9 0.84625 1999 10 0.9525 1999 11 0.74 1999 12 1.4425 1999 13 1.195 1999 15 1.8337 1999 16 1.4163 1999 17 0.74 1999 18 0.74 1999 19 0.74
D = groupsummary(C,'state_fips','mean','Annual_Ratio');
D.GroupCount = []; % Optional
D
D = 51×2 table
state_fips mean_Annual_Ratio __________ _________________ 1 1.4766 2 1.5883 4 1.7999 5 1.2716 6 1.8077 8 1.8037 9 1.4717 10 1.5317 11 0.74 12 1.7787 13 1.669 15 1.9339 16 1.8915 17 1.3743 18 1.35 19 1.1552

More Answers (0)

Tags

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by