CODEWITHSUNDEEP
matlabjoinmerge
Uche
Uche

Reputation: 13

Merge array rows based on the first digit in a column

I have two arrays, A and B. The first digit of each row is the serial number.

How do I combine A and B to have an array C, such that all rows in A with the same serial number in B are concatenated horizontally?

A = [ 12345;
      47542;
      32673;
      65436;
      75343;
      23496;
      54765 ]
B = [ 23566;
      33425;
      65438;
      75354 ]

y = ismember(A(:,1), B(:,1), 'rows');
t=find(y);
C= [A(t,1:12),B(t,1:12)];

I need C to be:

C = [ 12345, 00000;
      23496, 23566;
      32673, 33425;
      47542, 00000;
      54765, 00000;
      65436, 00000;
      75343, 75354]

Upvotes: 0

Views: 76

Answers (3)

Wolfie
Wolfie

Reputation: 30046

If it's only the first digit, we only need to check if the first digit (i.e. floor(A/1e4)) matches 0 to 9, and index accordingly...

% Add some zeros at the front to make indexing work with the unmatched ismember outputs
Az = [zero(; A]; Bz = [0; B];  
% Find the indices for 0 to 9 within the first digits of A and B 
[~,ia] = ismember( 0:9, floor( A/1e4 ) );
[~,ib] = ismember( 0:9, floor( B/1e4 ) );
% Assign to C and discard unmatched rows
C = [Az(ia+1), Bz(ib+1)];
C( all( C==0, 2 ), : ) = [];

Note that keeping things numeric with the floor operation should always be preferable to flipping between numeric and character data with things like num2str...

Edit

You changed the scope of the question by commenting with new data. Here is the same method, written to be more generic so it handles A and B with more columns and different magnitude IDs

% Add some zeros at the front to make indexing work with the unmatched ismember outputs
Az = [zeros(1,size(A,2)); A]; Bz = [zeros(1,size(A,2)); B]; 
% Function for getting first digit
f = @(x) floor(x./(10.^floor(log10(x))));
% Find the indices for 0 to 9 within the first digits of A and B 
[~,ia] = ismember( 0:9, f(A(:,1)) );
[~,ib] = ismember( 0:9, f(B(:,1)) );
% Assign to C and discard unmatched rows
C = [Az(ia+1,:), Bz(ib+1,:)];
C( all( C==0, 2 ), : ) = [];

Upvotes: 1

HansHirse
HansHirse

Reputation: 18905

First of all, the whole script. At first glance, I couldn't find a solution without using loops.

A = [ 12345;
      47542;
      32673;
      65436;
      75343;
      23496;
      54765; ]

B = [ 23566;
      33425;
      65438;
      75354; ]

A = sort(A);                              % Sort A and B.
B = sort(B);                             

A_str = int2str(A);                       % Convert integers to chars.
B_str = int2str(B);

A_sn = A_str(:, 1);                       % Extract first columns.
B_sn = B_str(:, 1);                       % Basically, these are the serial numbers.                                              

C = zeros(size(A, 1), size(A, 2) * 2);    % Initialize C.

C(:, 1) = A;                              % First column of C is just A.

for i = 1:length(A_sn)                    % For all serial numbers in A...
  for j = 1:length(B_sn)                  % For all serial numbers in B...
    if (A_sn(i) == B_sn(j))               % Check if serial number in B equals the serial number in A.
      C(i, 2) = B(j);                     % If so, set i-th row in C to the corresponding value in B.
    end
  end
end

C

Results in:

A =
    12345
    47542
    32673
    65436
    75343
    23496
    54765

B =
    23566
    33425
    65438
    75354

C =
    12345       0
    23496   23566
    32673   33425
    47542       0
    54765       0
    65436   65438
    75343   75354

Upvotes: 0

Irreducible
Irreducible

Reputation: 899

My approach would be the following, extract the leading digits of both arrays and compare those:

  a=num2str(A)-'0';
  b=num2str(B)-'0';
  [ida,idb]=ismember(a(:,1),b(:,1));

Now get the sorting index of A

  [~,ids]=sort(a(:,1));

Create output array

  C=zeros(size(A,1),2);

Finally assign and sort output

  C(:,1)=A;
  C(ida,2)=B(idb(idb>0));
  %sort result
  C=C(ids,:)

Upvotes: 2

Related Questions