How to combine rows with some identical values in a table

4 次查看（过去 30 天）
012786534
012786534 2020-2-7
回答： Bhaskar R 2020-2-7
Hi,
I am using the short script below to assign points to boxes. The script works fine but I am struggling to combine duplicated rows (rows with the same lat and lon but with different boxes). Thats because points that fall in two overlaping boxes end up on two rows, one for each box. But I would like to combine those points on only one row as demonstrated in the desired output below.
It should be noted that in real life there is a large number of boxes, not just two.
Thoughts ?
lat = [47.5, 45.5, 46.5, 46.6, 47.8]';
lon = [-63.5, -61.5, -62.5, -62.4, -63.6]';
pts_positions = table(lat, lon);
% Min and max values of boxes
box_name = {'box_A'; 'box_B'};
min_lat = [46, 45]';
max_lat = [48, 47]';
min_lon = [-64, -63]';
max_lon = [-62, -61]';
boxes_positions = table(box_name, min_lat, max_lat, min_lon, max_lon);
% Assign points to boxes based on latitude/longitude
assigned = cell(height(boxes_positions),1);
for g = 1:height(boxes_positions)
mask = pts_positions.lon > boxes_positions.min_lon(g) & pts_positions.lon < boxes_positions.max_lon(g) & ...
pts_positions.lat > boxes_positions.min_lat(g) & pts_positions.lat < boxes_positions.max_lat(g);
temp = pts_positions(mask, :);
temp.box = repmat(box_name(g), height(temp), 1);
assigned{g} = temp;
end
pts_position_assigned = vertcat(assigned{:});
%output_desired
lat = [47.5, 45.5, 46.5, 46.6, 47.8]';
lon = [-63.5, -61.5, -62.5, -62.4, -63.6]';
box = {'box_A', 'box_B', 'box_A,box_B', 'box_A,box_B', 'box_A'}';
output = table(lat, lon, box);
Thank you,

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

采纳的回答

Bhaskar R
Bhaskar R 2020-2-7
lat = [47.5, 45.5, 46.5, 46.6, 47.8]';
lon = [-63.5, -61.5, -62.5, -62.4, -63.6]';
pts_positions = table(lat, lon);
% Min and max values of boxes
box_name = {'box_A'; 'box_B'};
min_lat = [46, 45]';
max_lat = [48, 47]';
min_lon = [-64, -63]';
max_lon = [-62, -61]';
boxes_positions = table(box_name, min_lat, max_lat, min_lon, max_lon);
% Assign points to boxes based on latitude/longitude
assigned = cell(height(boxes_positions),1);
for g = 1:height(boxes_positions)
mask = pts_positions.lon > boxes_positions.min_lon(g) & pts_positions.lon < boxes_positions.max_lon(g) & ...
pts_positions.lat > boxes_positions.min_lat(g) & pts_positions.lat < boxes_positions.max_lat(g);
temp = pts_positions(mask, :);
temp.box = repmat(box_name(g), height(temp), 1);
assigned{g} = temp;
end
box_a_pos = ismember(assigned{1}(:,1:2),assigned{2}(:, 1:2));
box_b_pos = ismember(assigned{2}(:,1:2),assigned{1}(:, 1:2));
assigned{1}(box_a_pos,:).box = strcat(assigned{1}(box_a_pos,:).box, ',', ...
assigned{2}.box{1});
assigned{2} = assigned{2}(~box_b_pos,:);
pts_position_assigned = vertcat(assigned{:});
% output_desired
lat = [47.5, 45.5, 46.5, 46.6, 47.8]';
lon = [-63.5, -61.5, -62.5, -62.4, -63.6]';
box = {'box_A', 'box_B', 'box_A,box_B', 'box_A,box_B', 'box_A'}';
output = table(lat, lon, box);

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by