obtaining the average value for a data set

Hello, I have a data set which contains a value for a number of stations during some years (station, date, value). Data for some days is missing. I need to create a column with annual average value per station. I appreciate your help.

station date value average(yearly per station)

1 2001/1/1 2 2

1 2001/1/3 1 2

1 2001/1/5 3 2

2 2001/1/1 2 2.5

2 2001/1/4 3 2.5

1 2002/1/2 2 1.5

1 2002/1/6 1 1.5

2 2002/1/1 2 2.5

2 2002/1/2 3 2.5 . .

[Matt Fig]

It looks more like (station, date, value, value) to me. Can you tell us what form this data is in (i.e., cell array, Excel sheet, etc.) and what to do with the extra value?

[Azzi Abdelmalek]

can you specify the size of your data array, cell or .., how should the result looks like?

[FATEMEH]

sorry for the misspecification. The last column is the column that should be created(average). My data has the form of matrix. I have 3 columns for year month and day. To be precise, a typical row has the form of: ( I separated the elements by *)

station* year* month* day*value

1 * 2001 * 1 * 1 * 2

and I want to add a column with yearly average per station:

station* year* month* day*value* average(yearly per station)

1 * 2001 * 1 * 1 * 2 *2

Answer 1

[Matt Tearle]

It looks like the fourth column is what you want to calculate. If so, this will do the job, assuming that you have the first three columns as station, date, and value (numeric vectors except date which is a cell array of strings)

% get the years from the date strings
dv = datevec(date);
year = dv(:,1);

% get all combinations of station & year
[stlist,yrlist] = meshgrid(unique(station),unique(year));

% allocate space for the calculated average
avg = zeros(size(station));

% loop over every station/year combination
for k = 1:numel(stlist)
    % find the location of this combination in the data set
    idx = (year == yrlist(k)) & (station == stlist(k));
    % calculate the mean
    avg(idx) = mean(value(idx));
end

This could probably be simplified with prior knowledge of the station numbers and years.

EDIT TO ADD: Given your comment above, ignore the first couple of lines -- you already have year.

[FATEMEH]

Is there a way to avoid loop?

[Sean de Wolski]

Why would you want to avoid the loop? The loop will likely be very fast and is easy to read/understand.

[Matt Tearle]

Possibly, although I doubt it will be neat. More importantly, why do you want to avoid loops?