Monthly Average for Large Dataset

Hello I am looking to calculate the monthly mean, max and min of a very large dataset. The data set includes 8 readings every day for 15 years so the dataset is huge.

I then need to calculate the monthly mean, min and max for every January, February March etc month over the 15 year period. How would I do this? Each month obviously has a different number of days so how would I take this into consideration? Attached is a picture of the layout of the data to give you an idea of what the dataset looks like. The highlighted column is what I am looking at.

Answer 1

[Andrei Bobrov]

f = fopen('NG1_all_wav');
c = textscan(f,['%s',repmat(' %f',1,15)],'CollectOutput',1);
fclose(f);

[a,~,c1] = unique(c{2}(:,1:2),'rows');
c2 = accumarray(c1,c{2}(:,5),[],@(ii){[min(ii),max(ii),mean(ii)]});
out = [a,cat(1,c2{:})];

Answer 2

[Azzi Abdelmalek]

If your text file looks like

2000 02 01 1001 25 25
2000 02 01 2310 45 46
2000 03 02 1121 33 36
2000 03 02 1141 33 36
2000 03 02 1151 33 36
2001 02 05 1452 47 85
2001 02 05 1442 470 805
2001 02 05 1422 407 8005

fid=fopen('file.txt')
out=textscan(fid,'%s')
fclose(fid)
a=reshape(out{:},6,[])'
b=str2double(a(:,1:2))
data=str2double(a(:,5:6))
[ii,jj,kk]=unique(b,'rows')
c=accumarray(kk,(1:numel(kk))',[],@(x) {mean(data(x,:))})
out=[a(jj,1:2) num2cell(cell2mat(c))]

[Rodney Dwyer]

What if my file is in a excel file and not text file?

Answer 3

[Muhammad Usman Saleem]

Hint: make a function that read text file and calculate statistics.

Answer 4

[Peter Perkins]

Hard to tell if that file is space- or tab-delimited, and if that's the top of the file, or what. It helps to provide more precise information, preferable a small example of your data file. So you may need to adjust the followng for another delimiter, or whatever. In any case, read your data into a table, and do a grouped calculation using varfun:

t = readtable('yourdata.txt','delimiter','\t','ReadVariableNames',false);
t.Properties.VariableNames(2:5) = {'Year' 'Month' 'Day' 'TimeOfDay'};
monthlyMeans = varfun(@mean,t,'GroupingVariables',{'Year' 'Month'},'InputVariables',6);

Hope this helps.

Answer 5

[Tiffany de klerk]

Hi all Im sorry I was not specific enough. I have however resolved the issue of reading in the dates correctly. I have separated the data for convenience into a 53236x3 matrix (double) with the serial date in the first column (year, month, day) the time in the second column and HMO (significant wave height) in the 3rd.

I have done some calcs thankfully and got some results but I still require a method of calculating the mean of every January, every February, every March and so on over the entire data series that spans around 15 odd years.

The new 53236x3 matrix is called 'NG1allwavdataonlyCopy'.

Thank you for your help. Ive attached a picture of what I am aiming to create.

Answer 6

[Walter Roberson]

dv = datevec(YourMatrix(:,1));
monidx = dv(:,1) * 12 + dv(:,2);
[unique_monidx, ~, rel_monidx] = unique(monid);
mean_by_month = accumarray(rel_monidx, YourMatrix(:,3), [], @mean);
uyear = floor(unique_monidx / 12);
umon = mod(unique_monidx, 12);
output_table = [uyear, umon, mean_by_month];

[Tiffany de klerk]

This is an answer to my previous initial question about a mean for each month for each year. That problem was solved, although with a different result.

I am looking to find the mean of every january, every february, every march etc. I.e the mean of every single data point in any january throughout the data set. Resulting in a 12x2 matrix with the first column as each month (January, February, March, April, May, June, July, August, September, October, December) and the second column with the mean for each month over the 15 year data series.

[Walter Roberson]

dv = datevec(YourMatrix(:,1));
monidx = dv(:,2);
mean_by_month = accumarray(monidx, YourMatrix(:,3), [12,1], @mean);
output_table = [(1:12).', mean_by_month];

[Tiffany de klerk]

Hi Walter this looks like it could work. I ran the script and it comes up with an error using accumarray

"First input SUBS must contain positive integer subscripts"

[Walter Roberson]

Leave out the dv = datevec and use

monidx = YourMatrix(:,3);    %if that is the column with the month number

[Walter Roberson]

Wait, the dv = datevec(YourMatrix(:,1)); solution was in response to you saying that you had a matrix with the serial date number in the first column. Is that not the case?

Answer 7

[Tiffany de klerk]

This is what I am trying to achieve

[Azzi Abdelmalek]

Is this an answer or a comment?

[Tiffany de klerk]

Azzi no this is a comment to try demonstrate what I am looking to achieve with the data to clarify what I need help with. This graph is from another document.

[Azzi Abdelmalek]

Ok, then delete this answer, and post a comment by clicking on comment on this answer