Index daily values in TT over many years to determine mean

I would like to determine the daily means over many years from an existing TT (attached).

Specifically, all July 01's are combined and a mean determined, all July 02's are combined and a mean detemined, etc.

I've started to do this using a brute force step-wise method and realize its going to take forever. Below is what I've done so far (and it works).

I'm just wondering if (and I think there should be) there's a much easire way to parse the data.

% Create indexes for July days.
    idx_Jul1 = ismember(day(TT4_staLCA_AT_Jul.Time), 1);
    idx_Jul2 = ismember(day(TT4_staLCA_AT_Jul.Time), 2);
    idx_Jul3 = ismember(day(TT4_staLCA_AT_Jul.Time), 3);
    idx_Jul4 = ismember(day(TT4_staLCA_AT_Jul.Time), 4);
    idx_Jul5 = ismember(day(TT4_staLCA_AT_Jul.Time), 5);
    idx_Jul6 = ismember(day(TT4_staLCA_AT_Jul.Time), 6);
    idx_Jul7 = ismember(day(TT4_staLCA_AT_Jul.Time), 7);
    idx_Jul8 = ismember(day(TT4_staLCA_AT_Jul.Time), 8);
    idx_Jul9 = ismember(day(TT4_staLCA_AT_Jul.Time), 9);
    idx_Jul10 = ismember(day(TT4_staLCA_AT_Jul.Time), 10);
    idx_Jul11 = ismember(day(TT4_staLCA_AT_Jul.Time), 11);
    idx_Jul12 = ismember(day(TT4_staLCA_AT_Jul.Time), 12);
    idx_Jul13 = ismember(day(TT4_staLCA_AT_Jul.Time), 13);
    idx_Jul14 = ismember(day(TT4_staLCA_AT_Jul.Time), 14);
    idx_Jul15 = ismember(day(TT4_staLCA_AT_Jul.Time), 15);
    idx_Jul16 = ismember(day(TT4_staLCA_AT_Jul.Time), 16);
    idx_Jul17 = ismember(day(TT4_staLCA_AT_Jul.Time), 17);
    idx_Jul18 = ismember(day(TT4_staLCA_AT_Jul.Time), 18);
    idx_Jul19 = ismember(day(TT4_staLCA_AT_Jul.Time), 19);
    idx_Jul20 = ismember(day(TT4_staLCA_AT_Jul.Time), 20);
    idx_Jul21 = ismember(day(TT4_staLCA_AT_Jul.Time), 21);
    idx_Jul22 = ismember(day(TT4_staLCA_AT_Jul.Time), 22);
    idx_Jul23 = ismember(day(TT4_staLCA_AT_Jul.Time), 23);
    idx_Jul24 = ismember(day(TT4_staLCA_AT_Jul.Time), 24);
    idx_Jul25 = ismember(day(TT4_staLCA_AT_Jul.Time), 25);
    idx_Jul26 = ismember(day(TT4_staLCA_AT_Jul.Time), 26);
    idx_Jul27 = ismember(day(TT4_staLCA_AT_Jul.Time), 27);
    idx_Jul28 = ismember(day(TT4_staLCA_AT_Jul.Time), 28);
    idx_Jul29 = ismember(day(TT4_staLCA_AT_Jul.Time), 29);
    idx_Jul30 = ismember(day(TT4_staLCA_AT_Jul.Time), 30);
    idx_Jul31 = ismember(day(TT4_staLCA_AT_Jul.Time), 31);

% Daily indexes Jul.
    TT4_staLCA_AT_Jul1 = TT4_staLCA_AT_Jul(idx_Jul1,:);
    TT4_staLCA_AT_Jul1_mean = nanmean(TT4_staLCA_AT_Jul1.Var1);
    TT4_staLCA_AT_Jul2 = TT4_staLCA_AT_Jul(idx_Jul2,:);
    TT4_staLCA_AT_Jul2_mean = nanmean(TT4_staLCA_AT_Jul2.Var1);
    TT4_staLCA_AT_Jul3 = TT4_staLCA_AT_Jul(idx_Jul3,:);
    TT4_staLCA_AT_Jul3_mean = nanmean(TT4_staLCA_AT_Jul3.Var1);
    TT4_staLCA_AT_Jul4 = TT4_staLCA_AT_Jul(idx_Jul4,:);
    TT4_staLCA_AT_Jul4_mean = nanmean(TT4_staLCA_AT_Jul4.Var1);
    TT4_staLCA_AT_Jul5 = TT4_staLCA_AT_Jul(idx_Jul5,:);
    TT4_staLCA_AT_Jul5_mean = nanmean(TT4_staLCA_AT_Jul5.Var1);
    TT4_staLCA_AT_Jul6 = TT4_staLCA_AT_Jul(idx_Jul6,:);
    TT4_staLCA_AT_Jul6_mean = nanmean(TT4_staLCA_AT_Jul6.Var1);
    TT4_staLCA_AT_Jul7 = TT4_staLCA_AT_Jul(idx_Jul7,:);
    TT4_staLCA_AT_Jul7_mean = nanmean(TT4_staLCA_AT_Jul7.Var1);
    TT4_staLCA_AT_Jul8 = TT4_staLCA_AT_Jul(idx_Jul8,:);
    TT4_staLCA_AT_Jul8_mean = nanmean(TT4_staLCA_AT_Jul8.Var1);
    TT4_staLCA_AT_Jul9 = TT4_staLCA_AT_Jul(idx_Jul9,:);
    TT4_staLCA_AT_Jul9_mean = nanmean(TT4_staLCA_AT_Jul9.Var1);
    TT4_staLCA_AT_Jul10 = TT4_staLCA_AT_Jul(idx_Jul10,:);
    TT4_staLCA_AT_Jul10_mean = nanmean(TT4_staLCA_AT_Jul10.Var1);
    TT4_staLCA_AT_Jul11 = TT4_staLCA_AT_Jul(idx_Jul11,:);
    TT4_staLCA_AT_Jul11_mean = nanmean(TT4_staLCA_AT_Jul11.Var1);
    TT4_staLCA_AT_Jul12 = TT4_staLCA_AT_Jul(idx_Jul12,:);
    TT4_staLCA_AT_Jul12_mean = nanmean(TT4_staLCA_AT_Jul12.Var1);
    TT4_staLCA_AT_Jul13 = TT4_staLCA_AT_Jul(idx_Jul13,:);
    TT4_staLCA_AT_Jul13_mean = nanmean(TT4_staLCA_AT_Jul13.Var1);
    TT4_staLCA_AT_Jul14 = TT4_staLCA_AT_Jul(idx_Jul14,:);
    TT4_staLCA_AT_Jul14_mean = nanmean(TT4_staLCA_AT_Jul14.Var1);
    TT4_staLCA_AT_Jul15 = TT4_staLCA_AT_Jul(idx_Jul15,:);
    TT4_staLCA_AT_Jul15_mean = nanmean(TT4_staLCA_AT_Jul15.Var1);
    TT4_staLCA_AT_Jul16 = TT4_staLCA_AT_Jul(idx_Jul16,:);
    TT4_staLCA_AT_Jul16_mean = nanmean(TT4_staLCA_AT_Jul16.Var1);
    TT4_staLCA_AT_Jul17 = TT4_staLCA_AT_Jul(idx_Jul17,:);
    TT4_staLCA_AT_Jul17_mean = nanmean(TT4_staLCA_AT_Jul17.Var1);
    TT4_staLCA_AT_Jul18 = TT4_staLCA_AT_Jul(idx_Jul18,:);
    TT4_staLCA_AT_Jul18_mean = nanmean(TT4_staLCA_AT_Jul18.Var1);
    TT4_staLCA_AT_Jul19 = TT4_staLCA_AT_Jul(idx_Jul19,:);
    TT4_staLCA_AT_Jul19_mean = nanmean(TT4_staLCA_AT_Jul19.Var1);
    TT4_staLCA_AT_Jul20 = TT4_staLCA_AT_Jul(idx_Jul20,:);
    TT4_staLCA_AT_Jul20_mean = nanmean(TT4_staLCA_AT_Jul20.Var1);
    TT4_staLCA_AT_Jul21 = TT4_staLCA_AT_Jul(idx_Jul21,:);
    TT4_staLCA_AT_Jul21_mean = nanmean(TT4_staLCA_AT_Jul21.Var1);
    TT4_staLCA_AT_Jul22 = TT4_staLCA_AT_Jul(idx_Jul22,:);
    TT4_staLCA_AT_Jul22_mean = nanmean(TT4_staLCA_AT_Jul22.Var1);
    TT4_staLCA_AT_Jul23 = TT4_staLCA_AT_Jul(idx_Jul23,:);
    TT4_staLCA_AT_Jul23_mean = nanmean(TT4_staLCA_AT_Jul23.Var1);
    TT4_staLCA_AT_Jul24 = TT4_staLCA_AT_Jul(idx_Jul24,:);
    TT4_staLCA_AT_Jul24_mean = nanmean(TT4_staLCA_AT_Jul24.Var1);
    TT4_staLCA_AT_Jul25 = TT4_staLCA_AT_Jul(idx_Jul25,:);
    TT4_staLCA_AT_Jul25_mean = nanmean(TT4_staLCA_AT_Jul25.Var1);
    TT4_staLCA_AT_Jul26 = TT4_staLCA_AT_Jul(idx_Jul26,:);
    TT4_staLCA_AT_Jul26_mean = nanmean(TT4_staLCA_AT_Jul26.Var1);
    TT4_staLCA_AT_Jul27 = TT4_staLCA_AT_Jul(idx_Jul27,:);
    TT4_staLCA_AT_Jul27_mean = nanmean(TT4_staLCA_AT_Jul27.Var1);
    TT4_staLCA_AT_Jul28 = TT4_staLCA_AT_Jul(idx_Jul28,:);
    TT4_staLCA_AT_Jul28_mean = nanmean(TT4_staLCA_AT_Jul28.Var1);
    TT4_staLCA_AT_Jul29 = TT4_staLCA_AT_Jul(idx_Jul29,:);
    TT4_staLCA_AT_Jul29_mean = nanmean(TT4_staLCA_AT_Jul29.Var1);
    TT4_staLCA_AT_Jul30 = TT4_staLCA_AT_Jul(idx_Jul30,:);
    TT4_staLCA_AT_Jul30_mean = nanmean(TT4_staLCA_AT_Jul30.Var1);
    TT4_staLCA_AT_Jul31 = TT4_staLCA_AT_Jul(idx_Jul31,:);
    TT4_staLCA_AT_Jul31_mean = nanmean(TT4_staLCA_AT_Jul31.Var1);
    
% Combine all means into one matrix.
    TT5_staLCA_AT_Jul = [TT4_staLCA_AT_Jul1_mean; TT4_staLCA_AT_Jul2_mean; TT4_staLCA_AT_Jul3_mean; TT4_staLCA_AT_Jul4_mean; TT4_staLCA_AT_Jul5_mean; TT4_staLCA_AT_Jul6_mean;...
        TT4_staLCA_AT_Jul7_mean; TT4_staLCA_AT_Jul8_mean; TT4_staLCA_AT_Jul9_mean; TT4_staLCA_AT_Jul10_mean; TT4_staLCA_AT_Jul11_mean; TT4_staLCA_AT_Jul12_mean;...
        TT4_staLCA_AT_Jul13_mean; TT4_staLCA_AT_Jul14_mean; TT4_staLCA_AT_Jul15_mean; TT4_staLCA_AT_Jul16_mean; TT4_staLCA_AT_Jul17_mean; TT4_staLCA_AT_Jul18_mean;...
        TT4_staLCA_AT_Jul19_mean; TT4_staLCA_AT_Jul20_mean; TT4_staLCA_AT_Jul21_mean; TT4_staLCA_AT_Jul22_mean; TT4_staLCA_AT_Jul23_mean; TT4_staLCA_AT_Jul24_mean;...
        TT4_staLCA_AT_Jul25_mean; TT4_staLCA_AT_Jul26_mean; TT4_staLCA_AT_Jul27_mean; TT4_staLCA_AT_Jul28_mean; TT4_staLCA_AT_Jul29_mean; TT4_staLCA_AT_Jul30_mean;...
        TT4_staLCA_AT_Jul31_mean;];

% Create a column vector for july days used in plotting.
    Jul_length = (1:31);
    Jul_length = Jul_length';
    
% Plot July average AT   
    figure
    scatter(Jul_length, TT5_staLCA_AT_Jul)

Answer 1

[Sindar]

This will create a table with the average for each day of the year (1-366). 'dayofmonth' might be preferable for just July data to avoid leap year issues

summary_table = groupsummary(TT4_staLCA_AT_Jul,'Time','dayofyear','mean');

[Eric Escoto]

Thanks Sindar,

So I just tried this...My goodness so nice to see 50+ lines reduced to one!

However, I'm looking at the output and the results are not equivalent. Also, even thought there are only 31 days in July, a 32 row vector is produced. Why would this be?

**Update, the 'day of month' solved that issue that you described about leap years and the unequivalent values in the results!!!

I'll attach the result here so we can compare them. I'm looking to see if in all of my other lines there was a mistake (easily could be).

If not, any idea why the values might be different?

On a side note, am I able to use 'nanmean' in the code? I tried for this month but notice an error message (below).

Error using groupsummary (line 224)
Method must be a valid name, a function handle, or a cell array containing valid methods.

[Eric Escoto]

BTW, I still can't believe how much lines and effort are saved using this function 'groupsumary'. My goodness, thank you!

[Sindar]

Yeah, it's a pretty wonderful function.

The method/statistic option accepts several defaults as strings (like 'mean'), but you can put function handles with @:

summary_table = groupsummary(TT4_staLCA_AT_Jul,'Time','dayofmonth',@nanmean)

you can compare the results, too:

summary_table = groupsummary(TT4_staLCA_AT_Jul,'Time','dayofmonth',{@nanmean;'mean'})

are the results still different using dayofmonth? dayofyear would have leap years binned one off from what you want

[Eric Escoto]

The results are the same using 'dayofmonth'!

One other quick question: So I have combined a couple of months into one sumary table and would like to plot a figure using the 'scatter' function.

When I call out 'Time' as my x-variable I get markers on the same value (becasuse there is a day 1 in both months).

I can't recall how to plot the scatter with the row # as an x axis location. Do you know how I can do this?

Better yet would be to use just the month/day (no year because we've meaned across many years) as the x axis marker.

[Sindar]

Also, if you have multiple months, this is one workaround (probably not the best) for getting averages by calendar day (month/day) vs day-of-year:

% create a new table with Month | Day (of month) | Var1
T=table(categorical(month(TT4_staLCA_AT_Jul.Time,'name')),day(TT4_staLCA_AT_Jul.Time,'dayofmonth'),TT4_staLCA_AT_Jul.Var1,'VariableNames',{'Month';'Day';'Var1'})
% compute averages for combinations of Month and Day-of-month columns
summary_table = groupsummary(T,["Month";"Day"],'mean');

it'll look like this:

 Month    Day    GroupCount    mean_Var1
 _____    ___    __________    _________

 July      1         20         27.992  
 July      2         20         28.196  
 ...

[Sindar]

scatter vs row number should be as simple as:

scatter(1:height(summary_table),summary_table.mean_Var1)

with the multiple-month code above, labeling isn't too hard:

(edit to make sure months appear in the right order)

month_order = ["January", "February", "March", "April", "May", "June", "July",...
    "August", "September", "October", "November", "December"];
% create a new table with Month | Day (of month) | Var1
T=table(categorical(month(TT4_staLCA_AT_Jul.Time,'name'),month_order,'Ordinal',true),day(TT4_staLCA_AT_Jul.Time,'dayofmonth'),TT4_staLCA_AT_Jul.Var1,'VariableNames',{'Month';'Day';'Var1'})
% compute averages for combinations of Month and Day-of-month columns
summary_table = groupsummary(T,["Month";"Day"],'mean');

% plot vs summary row number
scatter(1:height(summary_table),summary_table.mean_Var1)
% decide how many ticks you want. This does every 10 rows (10 days if none missing)
tick_idx = 1:10:height(summary_table);
xticks(tick_idx)
% label ticks with month and day (e.g., "July 1")
xticklabels(string(summary_table.Month(tick_idx))+" "+summary_table.Day(tick_idx))
% angle ticks at 45 degrees for easier reading when closely packed
xtickangle(45)

[Eric Escoto]

Thanks, Sindar!

Running into a slight hiccup with the resulting table. I'm combining july, august, and september. When I run the code you;ve provided august is placed before july for some reason. do you konw why this is happening?

I've atached the files needed.

% Create summer tables for study period 2001 to current
    TT3_staLCA_Psum_summerperiod = TT3_staLCA_Psum_summer(4921:end, :);
    
% create a new table with Month | Day (of month) | Var1
    T_summer=table(categorical(month(TT3_staLCA_Psum_summerperiod.Time,'name')),day(TT3_staLCA_Psum_summerperiod.Time,'dayofmonth'),TT3_staLCA_Psum_summerperiod.Var5,'VariableNames',{'Month';'Day';'Var5'})
% compute averages for combinations of Month and Day-of-month columns
    summary_table_summer = groupsummary(T_summer,["Month";"Day"],'mean'); 
    
% decide how many ticks you want. This does every 10
    tick_idx_summer = 1:10:height(summary_table_summer);
    xticks(tick_idx_summer)
% label ticks with month and day (e.g., "July 1")
    xticklabels(string(summary_table_summer.Month(tick_idx_summer))+" "+summary_table_summer.Day(tick_idx_summer))

% Plot Summer average AT   
    figure
    scatter(1:height(summary_table_summer), summary_table_summer.mean_Var5)

[Sindar]

just realized that myself and edited the code to (hopefully) fix it

[Eric Escoto]

It worked perfectly. Very much appreciated!

[Eric Escoto]

If I wanted to start the figure x axis at a certain month, say august instead of july, is this possible?

The summary table lists the month/days in order but say I had july august and september in the summary table, and wanted to plot it as august, september and july.

Is there a way to do this using what we've already run?

[Sindar]

starting at a certain month:

idx_start = find(summary_table.Month=="August",1);
xl = xlim();
xlim([idx_start xl(2)])

reordering months is also not too bad. Here's one way

month_reorder = ["August";"September";"July"];
% create an empty table
summary_table_reorder = table();
% for each month you want to include:
% find rows for that month (summary_table.Month==month_reorder(ind))
% extract those rows from the original table
% add them to the new table
for ind=1:length(month_reorder)
    summary_table_reorder = [summary_table_reorder ; summary_table(summary_table.Month==month_reorder(ind),:)];
end

(if you have a big table, you'd want to collect indices then create the table all together, but this should be fine here)

[Eric Escoto]

Hmm. I a bit confused by these lines. I tried the first method as shown.

% Index for reordering months in winter season. 
    idx_start_winter = find(summary_table.Month=="November",1);
    xl_winter = xlim();
    xlim([idx_start_winter xl_winter(2)])      
    
    figure
    bar(1:height(summary_table),summary_table.mean_Var5)
    axis ij
    % Decide how many ticks you want. This does every 10 rows (10 days if none missing)
    tick_idx = 1:10:height(Psum_winterperiod_summarytable);
    xticks(tick_idx)
    % Label ticks with month and day (e.g., "July 1")
    xticklabels(string(summary_table.Month(tick_idx))+" "+summary_table.Day(tick_idx))
    % Angle ticks at 45 degrees for easier reading when closely packed
    xtickangle(45)
    title('Bar Plot', 'FontSize', 15)
    ylabel('[mm]')
    xlabel('Month and Day')

Using that indexing, I want to have the order Nov to March. Currently, I have Jan, Feb, Mar, Nov, Dec. in order.

I'm lost on how to use the indexing in the figure.

[Sindar]

teh first method just cuts at a certain point (and you'd put those lines after the plotting lines), so you could plot Nov-Dec

If you want to reorder (rolling counts), you need the second. Rolling can be done a little faster like this:

month_start = "November";
% ordered months allow you to use inequality tests
% summary_table.Month>=month_start selects, e.g., Nov & Dec
% summary_table.Month<month_start selects the rest
% then, restaple the rows in the new order
summary_table_reorder = [summary_table(summary_table.Month>=month_start,:); summary_table(summary_table.Month<month_start,:)];

figure
    bar(1:height(summary_table_reorder),summary_table_reorder.mean_Var5)
    axis ij
    % Decide how many ticks you want. This does every 10 rows (10 days if none missing)
    tick_idx = 1:10:height(summary_table_reorder);
    xticks(tick_idx)
    % Label ticks with month and day (e.g., "July 1")
    xticklabels(string(summary_table_reorder.Month(tick_idx))+" "+summary_table_reorder.Day(tick_idx))
    % Angle ticks at 45 degrees for easier reading when closely packed
    xtickangle(45)
    title('Bar Plot', 'FontSize', 15)
    ylabel('[mm]')
    xlabel('Month and Day')

[Eric Escoto]

Ah, I see. Makes sense.

Plot looks as it should now. Thanks again, Sindar!

Answer 2

[Steven Lord]

Use groupsummary with the GROUPBINS input specified as "dayofyear".