MATLAB: Averaging data sets with certain criteria (month and year)

average specific datesaveraging uneven data sets

I have a large dataset consisting of weather data from 400 counties in the US, in each of these counties I have temperature data from 1-10 stations from the years 1985-2016 on monthly basis. My problem is that I want to average the temperatures in the set but only if the temperatures are from the same year and month. Sometimes this means two rows of data and sometimes it means 10 rows.
Also I am rather new to matlab, I have been trying to build some sort of loop with an else and elseif statement.
I am attaching one of the .csv files, can someone please give me some help on this, before I loose my mind

Best Answer

See <splitapply> For your specific case and data as coded, if you leave the date as is but turn into categorical variable in a table, then you can use it directly as the grouping variable.
I've got to run at the moment, but that should get you started down the right track...
ADDENDUM:
Had a few minutes...
>> t=readtable('joek.csv');
>> whos t
  Name        Size             Bytes  Class    Attributes
  t         794x8             357035  table              
>> t.Properties.VariableNames
ans =
  1×8 cell array
    'STATION'    'NAME'    'LATITUDE'    'LONGITUDE'    'ELEVATION'    'DATE'    'PRCP'    'TAVG'
>> t.STATION=categorical(t.STATION);
>> t.NAME=categorical(t.NAME);
>> t.DATE=categorical(t.DATE);
>> t(1:5,:)
ans = 
      STATION           NAME         LATITUDE    LONGITUDE    ELEVATION     DATE       PRCP     TAVG 
    ___________    ______________    ________    _________    _________    _______    ______    _____
    USC00031632    CORNING, AR US    36.42       -90.59       91.40        1985-01     43.40    -1.80
    USC00031632    CORNING, AR US    36.42       -90.59       91.40        1985-02    119.60     2.40
    USC00031632    CORNING, AR US    36.42       -90.59       91.40        1985-03    155.30    12.70
    USC00031632    CORNING, AR US    36.42       -90.59       91.40        1985-04    137.30    17.90
    USC00031632    CORNING, AR US    36.42       -90.59       91.40        1985-05    186.10    21.30
>> [G,date]=findgroups(t.DATE);
>> whos G date
  Name        Size            Bytes  Class     Attributes
  G         794x1              6352  double              
  date      372x1             47618  categorical              
>> meantemp=splitapply(@mean,t.TAVG,G);
>> whos meantemp
  Name            Size            Bytes  Class     Attributes
  meantemp      372x1              2976  double              
>> T=table(date,meantemp);
>> T(1:5,:)
ans = 
     date      meantemp
    _______    ________
    1985-01    NaN     
    1985-02    NaN     
    1985-03    NaN     
    1985-04    NaN     
    1985-05    NaN     
>>
Yeech! Must be NaN in your dataset...
>> sum(isnan(T.meantemp))
ans =
      362.00
>> sum(isfinite(T.meantemp))
ans =
       10.00
>>
Well there are a few, so let's pick them out to display...
>> is=isfinite(meantemp);
>> T=table(date(is),meantemp(is));
>> T
T = 
     Var1      Var2 
    _______    _____
    1997-10    15.70
    1999-10    15.70
    2000-01     2.80
    2000-02     7.40
    2000-03    11.40
    2000-04    14.90
    2000-11     7.70
    2003-10    16.10
    2004-07    25.50
    2007-01     3.10
>>
You'll probably be better of to clean up the data first rather than later, but that's the idea...
ADDENDUM:
NB: I inadevertently used the variable date above; use a different name as the above aliases the builtin Matlab function of the same name which could lead to confusion on down the road...
>> which -all date
date is a variable.
C:\ML_R2016b\toolbox\matlab\timefun\date.m  % Shadowed
>>
Related Question