Finding average data from a large data matrix

Hello,

I have a data matrix in the following format:

Row 1 is the years from 1990 to 2000

Row 2 is the month

Row 3 is the day

Row 4 is the data

Ex:

1990 1990 1990 1990…..until 2000

1 1 1 1 1 1 1 1…2 2 2 2 2 2….3 3 3 3

1 2 3 4 5 6

How can I calculate the average data of each month (ie. all the jan, feb, together)? The problem that I am encountering is finding a systematic way of dividing the data, given its format.

Thanks in advance

Answer 1

[Benjamin Thompson]

An index vector over the available columns would work well here. Cannot be sure without a complete example of the data. Then pass the index vector as the second subscript argument for the submatrix of A that you want to pass to the "mean" function to get the average.

>> A = [1990 1990 1991 1991 1992 2000; 1 2 3 4 1 2; 1 2 3 4 5 6; 1 2 3 4 5 6]

A =

1990 1990 1991 1991 1992 2000

1 2 3 4 1 2

1 2 3 4 5 6

1 2 3 4 5 6

>> I_feb = A(2,:) == 2

I_feb =

1×6 logical array

0 1 0 0 0 1

>> mean(A(4,I_feb))

ans =

4

[Matlab_G]

how can i expand this to satisfy two rows

example:

A=

2000 2000 2000 2001 2001

1 1 2 1 1

5 3 8 9 7

I would like the average of row 3 elements when row 1==2000 and row 2==1, which in this case would be 4.

[Benjamin Thompson]

The index vector can use a more complex logic test:

I_two_conditions = (A(1,:) == 2000) & (A(2,:) == 1)

Answer 2

[Cris LaPierre]

Use groupsummary.

data = [1990 1990 1990 1990 1995 1995 1995 1995;
    1 1 2 2 1 1 2 2;
    1 2 1 2 1 2 1 2;
    3 5 7 4 6 8 1 9]'

data = 8×4

1990 1 1 3 1990 1 2 5 1990 2 1 7 1990 2 2 4 1995 1 1 6 1995 1 2 8 1995 2 1 1 1995 2 2 9

avgData = groupsummary(data(:,4),[data(:,1),data(:,2)],'mean')

avgData = 4×1

4.0000 5.5000 7.0000 5.0000

This is probably easier to understand if you turn your data into a table.

dataT = array2table(data,'VariableNames',["Year","Month","Day","Data"])

dataT = 8×4 table
    Year    Month    Day    Data
    ____    _____    ___    ____

    1990      1       1      3  
    1990      1       2      5  
    1990      2       1      7  
    1990      2       2      4  
    1995      1       1      6  
    1995      1       2      8  
    1995      2       1      1  
    1995      2       2      9  

avgDataT = groupsummary(dataT,["Year","Month"],"mean","Data")

avgDataT = 4×4 table
    Year    Month    GroupCount    mean_Data
    ____    _____    __________    _________

    1990      1          2              4   
    1990      2          2            5.5   
    1995      1          2              7   
    1995      2          2              5   

One more may to do this is if you turn your date info into a datetime variable

dataT2 = table(datetime(data(:,1:3)),data(:,4),'VariableNames',["Date","Data"])

dataT2 = 8×2 table
       Date        Data
    ___________    ____

    01-Jan-1990     3  
    02-Jan-1990     5  
    01-Feb-1990     7  
    02-Feb-1990     4  
    01-Jan-1995     6  
    02-Jan-1995     8  
    01-Feb-1995     1  
    02-Feb-1995     9  

avgDataT2 = groupsummary(dataT2,"Date","month","mean","Data")

avgDataT2 = 4×3 table
    month_Date    GroupCount    mean_Data
    __________    __________    _________

     Jan-1990         2              4   
     Feb-1990         2            5.5   
     Jan-1995         2              7   
     Feb-1995         2              5   

Answer 3

[dpb]

tData=array2table(X.','VariableNames',{'Year','Month','Day','Data'});
tMonthAvg=rowfun(@mean,tData,'GroupingVariables',{'Month'}, ...
        'InputVariables','Data','OutputVariableNames','MonthAverage');

[Cris LaPierre]

To your latest question,

A= [2000 2000 2000 2001 2001;
1         1         2       1      1;
5          3        8       9      7]'

A = 5×3

2000 1 5 2000 1 3 2000 2 8 2001 1 9 2001 1 7

groupsummary(A(:,3),[A(:,1) A(:,2)],'mean')

ans = 3×1

4 8 8