How to loop over table to caluclate averages?

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

Answer 1

[Simon Chan]

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      

[VS]

Thanks, Simon. Worked like magic.