How to sum up specific columns based on the values in a different column

Matlab coding. So basicially, I have an excel sheet with data that spans 9 years, and for every year, there is data for the 12 months. So the data looks like this:

2022 12 data data data

2022 12 data data data

2022 11 data data data

2022 11 data data data

2022 10 data data data

I want my code to sum all the data within columns 3:16 of the excel sheet for the rows marked a certain month, and then add that sum into one column of a new vector. All data within all years marked by month 12 will be in col 1, all data within all years marked by month 11 in col 2, etc until I have a 1x12 vector which holds the sum of that month in each column

I tried a while loop but my "sumbymonth" vector never produces anything

monthcounter = 12;

colcounter = 1;

while monthcounter >= 1

SumByMonth = sum(yeardata(yeardata(:,3:16)==monthcounter,:);

colcounter = colcounter+1;

monthcounter=monthcounter-1;

end

thank you for your help everyone

Answer 1

[Cris LaPierre]

If your only group is Month, then I'd just sum the columns first, and then use groupsummary to sum data from the same month.

yeardata = rand(10,14);
Year = [2022; 2022; 2021; 2021; 2020; 2020; 2019; 2019; 2018; 2018];
month = [12; 12; 11; 11; 10; 10; 9; 9; 8; 8];
data = [table(Year, month), array2table(yeardata)]

data = 10×16 table
    Year    month    yeardata1    yeardata2    yeardata3    yeardata4    yeardata5    yeardata6    yeardata7    yeardata8    yeardata9    yeardata10    yeardata11    yeardata12    yeardata13    yeardata14
    ____    _____    _________    _________    _________    _________    _________    _________    _________    _________    _________    __________    __________    __________    __________    __________

    2022     12       0.10279      0.80431      0.36033      0.83572      0.68671       0.8885         0.285     0.85394      0.42529       0.52056      0.96823       0.74524        0.67041      0.92074  
    2022     12        0.7683      0.32505      0.20127      0.49288      0.79828      0.93439       0.48929     0.74371      0.49114       0.36379       0.8391       0.17394       0.010713      0.64298  
    2021     11      0.016962      0.79614      0.98665      0.26439      0.90958      0.15447       0.25767     0.65566      0.45592       0.80317      0.92319       0.95365        0.45913      0.05308  
    2021     11       0.21645      0.11079      0.92978       0.8908      0.21676       0.3315       0.45337     0.89749      0.93755       0.40773      0.10452       0.55309        0.53685       0.1599  
    2020     10       0.69946      0.83505      0.15394      0.80627      0.56555       0.6089       0.98082     0.66056      0.36885          0.02      0.91226       0.60558        0.64722      0.64966  
    2020     10       0.54551      0.37085       0.6116      0.45649      0.56223      0.83487       0.13522      0.6691      0.96918       0.34899       0.1164       0.79014        0.71897      0.39069  
    2019      9        0.7188      0.55066      0.97594      0.31836      0.61418      0.93396        0.1218     0.75832      0.74985       0.66402      0.09538       0.99142        0.86906        0.243  
    2019      9        0.7868     0.020089      0.61696      0.89181      0.96485      0.39966       0.85347     0.49985      0.74969       0.69362      0.52788       0.52158        0.80699      0.51528  
    2018      8       0.54033      0.13343      0.32533      0.22922      0.28948      0.48148       0.62441     0.78501      0.11547       0.45379      0.62029        0.1023        0.66949      0.37854  
    2018      8        0.0142      0.24858      0.96697      0.63278       0.4382      0.91372     0.0013992    0.027259       0.3912      0.012276      0.83049       0.31768        0.85018      0.20515  

data.SumData = sum(data(:,3:16),2);
SumByMonth = groupsummary(data,"month","sum","SumData")

SumByMonth = 5×3 table
    month    GroupCount    sum_SumData
                              sum     
    _____    __________    ___________

      8          2           11.599   
      9          2           17.453   
     10          2           16.034   
     11          2           14.436   
     12          2           16.343   

You could also use rowfun

rowfun(@sum,data, "InputVariables", "SumData","GroupingVariables","month")

ans = 5×3 table
    month    GroupCount     Var3 
                            sum  
    _____    __________    ______

      8          2         11.599
      9          2         17.453
     10          2         16.034
     11          2         14.436
     12          2         16.343

[Idris]

thank you!