How to loop over table to caluclate averages?

2 次查看（过去 30 天）
VS
VS 2022-3-31
评论： VS 2022-3-31
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

请先登录，再回答此问题。

采纳的回答

Simon Chan
Simon Chan 2022-3-31
Ran in:
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

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 MATLAB 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by