How to combine rows with some identical values in a table

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,

Answer 1

[Bhaskar R]

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);