MATLAB Answers

How to combine rows with some identical values in a table

1 view (last 30 days)
012786534
012786534 on 7 Feb 2020
Answered: Bhaskar R on 7 Feb 2020
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,

  0 Comments

Sign in to comment.

Accepted Answer

Bhaskar R
Bhaskar R on 7 Feb 2020
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 Comments

Sign in to comment.

More Answers (0)

Tags

Community Treasure Hunt

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

Start Hunting!