How do I average data to produce 1-m interval bins?

I've got arrays (from text files) of profiles, 1 -> 200m, and back up to the surface, with 40 variables (columns). The data are acquired continuously over depth. I'm looking for a routine that will give me depths at 1-m intervals across all 40 columns, so I end up with arrays of (1:200,1:40). An additional problem is that the profiles vary slightly in near-surface and bottom depths (e.g., 3-197, 2-198, etc), making it a challenge to create arrays of depth v. some variable across all profiles for contouring purposes.

[Star Strider]

This is obviously dependent on your file, not something any of us can guess. Attach it or a representative sample from it, including any header lines and such. (Use the ‘paperclip’ icon to attach the file.)

[John]

Attached are records 22-57 of the csv version of the file (depth is the 18th field)

[Star Strider]

No file yet. There are two steps: ‘Choose file’ and then click ‘Attach file’ over on the lower-right of the ‘Attach a <file:’> window.

[John]

Didn't see the 'attach' button. Sorry.

[Star Strider]

No worries. It’s on partially visible in the window on my browser, so it’s not easy to find. I just have experience with Answers, so I know to look for it.

EDIT — Which column is depth? Is Column A just a counter, or does it have other significance?

When I read it with xlsread, it’s a (36x19) double array. (I don’t have 40 columns, but that may not be important.)

[John]

Yeah, I noticed that 36x19. Not sure what happened. FWIW, depth is column 18.

[Star Strider]

That shouldn’t affect my code. I changed it to account for the depth in Col #18, although it ended up in Col 17 in the output of my code, since I deleted Col #1 (a line counter) in the output. You can always put the depth in any column you want. Just change the other column assignments.

If somehow it does affect my code, post a section of your complete data set and I will change my code to accommodate your full-column data file.

Answer 1

[Star Strider]

Assuming depth is in the second column read (Column ‘B’ in the Excel file):

[d,s,r] = xlsread('DataExtract.csv');

dep   = d(:,2);                                 % Depth Column
idep  = fix(dep)-min(fix(dep))+1;               % Create Integer Subscript Of Depths
vblm = d(:,3:end);                              % Rest Of Data

[idepu, ia, ic] = unique(idep);                 % Unique Depths & Indices

for k1 = 1:size(idepu,1)
    depmean(k1,:) = [mean(vblm(ic==k1,:))];     % Means By Metre
end
depmean = [idepu+min(fix(dep))-1 depmean];      % Depth = Col #1

The ‘depmean’ assignment is the output. Column #1 is the depth, the other columns are the respective means at that depth. The depths are sorted in ascending order.

If depth is another column in the Excel file, change the ‘dep’ assignment column reference. The code will work without other modification. I experimented with non-loop possibilities, but none would work.

[John]

Thanks! I'll give it a try.

[Star Strider]

My pleasure!

Let me know how it goes.

[John]

Couple of things I notice. The csv file requires cvsread Also, depth is column 18. Not sure how the vlbm assignment works in that case, since columns before and after that need to be bin-averaged.

[Star Strider]

Using csvread and making the other appropriate changes in column addressing, this works:

d = csvread('John DataExtract.csv');

dep   = d(:,18);                                        % Depth Column (Original)
idep  = fix(dep)-min(fix(dep))+1;                       % Create Integer Subscript Of Depths
vblm  = d(:,[2:17 19:end]);                             % Rest Of Data (‘Variable Matrix’)

[idepu, ia, ic] = unique(idep);                         % Unique Depths & Indices

for k1 = 1:size(idepu,1)
    depmean(k1,:) = [mean(vblm(ic==k1,:))];             % Means By Metre
end
depcol = idepu+min(fix(dep))-1;                         % ‘Depth’ Column
% depmean = [depcol depmean];                             % Depth = Col #1
depmean = [depmean(:,1:16) depcol depmean(:,18:end)];   % Depth = Col #17

I assume you don’t want to include Column #1, since it seems to be a counter. If you do, change the ‘vblm’ (‘variable matrix’) and ‘depmean’ addressing to include it. Otherwise, the columns in ‘depmean’ (with ‘Depth’ in Column #17) align with the original data.

[John]

Thanks! Being a relative newbie, I wondered how to subscript the columns before and beyond col. 18.

[Star Strider]

My pleasure!

This should work without modification with your larger data set.

[John]

It works! Thanks, again.

[Star Strider]

As always, my pleasure!

[anastasia pa]

for k1 = 1:size(idepu,1)

depmean(k1,:) = [mean(vblm(ic==k1,:))]; % Means By Metre

end

If idepu and vblm are cells how will be the above code for means by metre?

Thanks!!!

Answer 2

[Guillaume]

Assuming that each row of your array is of the form:

%depth var1 var2 ... var40
%e.g:
data = [1.1 rand(1,40); %depth is 1.1
        2.5 rand(1,40); %depth is 2.5 
        1.8 rand(1,40); %depth is 1.8
        ...
        ]

you can use discretize to find which rows to average together:

depths = 1:200
bins = discretize(data(:, 1), depths);

You can then use a for loop to average for each bin:

newdata = zeros(200, 40);
for depth = depths
   newdata(depth, :) = mean(data(bins == depth, 2:41));
end

[John]

Thanks, this looks like a simple solution. But unfortunately, I'm using R2014B, and discretize is not recognized.

[Guillaume]

The second return value of histc or the third of histcounts (can't remember if it was in 2014b) will give you the exact same result as discretize.