how to average data points in column 3 based on values in columns 1 and 2

Question

Bradley 2024-3-6

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2091276-how-to-average-data-points-in-column-3-based-on-values-in-columns-1-and-2

评论： Mathieu NOE 2024-3-7

BathyScriptTest.xlsx

I have a bunch of single beam data points, about 50k points. I want to interpolate between them in a different bathymetric processing software. The data is a combination of two datasets (different surveys), meaning they were collected at different times, so the problem is the depth is not accurate. One survey is deeper than the other likely because of the tides, however the surveys cover the exact same area but at different approach angles (see picture). So when I plot them I have two sets of track lines that dont match up, when looking at my CSV file I have a lot of points that are very similar but not exact. Ive tried filtering unique values in excel and deleting duplicates, only 14/50000 were exactly the same. My question is how do I average these two surveys? Im honestly not sure where to start and there are probably numerous ways to do this so im getting lost in my research.

My goal is to bring these two surfaces to the same depth, and eventually smooth the lines enough to remove the bumpy surface before exporting the points to my other bathymetric program and finally export a geotiff to GIS. Thanks agian, any help is really appreciated!

I forgot to add an example of the data points:

As you can tell some of these points are extremely close together, what I want to do is find ordered pairs in columns 1 and 2 that are very similar and replace all the corresponding values in column 3 with their average. Hopefully any of this makes sense.

Overview of tracklines:

Zoomed in section showing the difference in depth:

Heres my code to visualize the data:

[D,S] = xlsread("BathyScriptTest.xlsx");
lat = D(:,1);
lon = D(:,2);
dep = D(:,3);
depS = smooth(dep);
figure
plot3(lat, lon, depS, '.')
grid on 
view(35,60)
title('Bathy Lines')

2 个评论
显示无隐藏无

Image Analyst 2024-3-6

You forgot to attach "BathyScriptTest.xlsx" so I didn't pay much attention after that. I'll check back later.

If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

Bradley 2024-3-6

Attached. Thanks for the tips and taking a look at my data. The only code I have so far is included in the post. Thanks again.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Mathieu NOE 2024-3-6

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2091276-how-to-average-data-points-in-column-3-based-on-values-in-columns-1-and-2#answer_1422041

在 MATLAB Online 中打开

affine_fit.m

hello

as your data seemed to lie in a plane , why not make a plane fit and assume from now the data should belong to that plane ?

use the attached function to run the code

no smoothing required here (I haven't found any added value here - my personnal choice)

code :

[D,S] = xlsread("BathyScriptTest.xlsx");
lat = D(:,1);
lon = D(:,2);
dep = D(:,3);
% remove some outliers 
mdep = mean(dep);
ind = abs(dep-mdep)<1;
lat = lat(ind);
lon = lon(ind);
dep = dep(ind);
figure
plot3(lat, lon, dep, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')
%compute the normal to the plane and a point that belongs to the plane
ind = (1:50:numel(lat));
XYZ = [lat(ind) lon(ind) dep(ind)];
[n_1,~,p_1] = affine_fit(XYZ);
%plot the two adjusted planes
[Xf,Yf] = meshgrid(lat(ind),lon(ind));
%fitted plane
Zf =  - (n_1(1)/n_1(3)*Xf+n_1(2)/n_1(3)*Yf-dot(n_1,p_1)/n_1(3));
surf(Xf,Yf,Zf,'edgecolor','none','facecolor','red','facealpha',0.25);
zlim([mdep-5 mdep+5]);
figure
depf =  - (n_1(1)/n_1(3)*lat+n_1(2)/n_1(3)*lon-dot(n_1,p_1)/n_1(3));
plot3(lat, lon, depf, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')
zlim([mdep-5 mdep+5]);

5 个评论
显示 3更早的评论隐藏 3更早的评论

Mathieu NOE 2024-3-7

在 MATLAB Online 中打开

I changed a bit my mind this morning, so I reintroduced the smoothing as you did in first place

I opted for smoothdata with movmedian window

this way I don't need to specify the limits above / below which I want to remove outliers. Simpler !

Final results looks better IMO :

also replaced the obsolete xlsread with readmatrix

code updated :

D = readmatrix("BathyScriptTest.xlsx");
lat = D(:,1);
lon = D(:,2);
dep = D(:,3);
depS = smoothdata(dep,'movmedian',500);
figure
plot(dep)
hold on
plot(depS)
hold off
figure
plot3(lat, lon, depS, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')
%compute the normal to the plane and a point that belongs to the plane
downsampling = 100; % to reduce risk of out of memory" in affine_fit
ind = (1:downsampling:numel(lat));
XYZ = [lat(ind) lon(ind) depS(ind)];
[n_1,~,p_1] = affine_fit(XYZ);
%plot the two adjusted planes
[Xf,Yf] = meshgrid(lat(ind),lon(ind));
%fitted plane
Zf =  - (n_1(1)/n_1(3)*Xf+n_1(2)/n_1(3)*Yf-dot(n_1,p_1)/n_1(3));
surf(Xf,Yf,Zf,'edgecolor','none','facecolor','red','facealpha',0.25);
figure
depf =  - (n_1(1)/n_1(3)*lat+n_1(2)/n_1(3)*lon-dot(n_1,p_1)/n_1(3));
plot3(lat, lon, depf, '.')
hold on
grid on 
view(35,60)
title('Bathy Lines')

Bradley 2024-3-7

Awesome! Thank you so much this worked perfectly, I exported depf as depth and imported that with lat and long to my bathymetry program (EIVA) and the result is below. It looks great! Were not interested in fine detail of the seafloor, just the depth gradient so this worked great. Thanks again!

Mathieu NOE 2024-3-7

Glad it worked !!

all the best

请先登录，再进行评论。

how to average data points in column 3 based on values in columns 1 and 2

2 个评论
显示无隐藏无

采纳的回答

5 个评论
显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

how to average data points in column 3 based on values in columns 1 and 2

2 个评论 显示 无隐藏 无

采纳的回答

5 个评论 显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

2 个评论
显示无隐藏无

5 个评论
显示 3更早的评论隐藏 3更早的评论