Merging the content of two tables

I have two tables Ta and Tb that have different number of rows and different number of columns. The content is all cell text, but maybe in the future it could also contain cell number.

.

I want to merge the content of these tables together under the following set of rules:

• Take the value of Ta(i,j) if Tb(i*,j*) is empty and vice versa.
• If both are available, then take the value of Ta(i,j) (or optionally, check whether they are the same).

.

The tricky part however is that we do not have unique row keys, we only have unique column keys. Note above that I make a distinction between i* and i. The reason is that the row in Ta can be at a different index than Tb, same holds for the columns j* and j. The implications are:

• that we first need to identify which row for Ta corresponds to the row of Tb and vice versa. We can do this by trying to crossmatch any of the columns that the tables share in common.

To make it also harder:

• the number of rows are not equal for the tables (sometimes there are rows in Ta but not in Tb and vice versa).

.

How can we merge the content of these two tables together in the most efficient way?

.

Example variables to play with:

Ta = cell2table({...
     'a1', 'b1', 'c1'; ...
     'a2', 'b2', 'c2'}, ...
      'VariableNames', {'A','B', 'C'})

Tb = cell2table({...
     'b2*', 'c2', 'd2'; ...
     'b3', 'c3', 'd3'; ...
     'b4', 'c4', 'd4'}, ...
      'VariableNames', {'B','C', 'D'})

The resulting table Tc should be something like this:

Tc = cell2table({...
    'a1' 'b1' 'c1'   ''; ...
    'a2' 'b2' 'c2' 'd2'; ...
    ''   'b3' 'c3' 'd3'; ...
    ''   'b4' 'c4' 'd4'}, ...
     'VariableNames', {'A', 'B','C', 'D'})

[Guillaume]

Looking a bit more deeply into this, the tricky bits are not the ones you mention (different numbers of rows, and matching rows are not at the same index is just business as usual for databases). No, the tricky bit is that your key sets (the columns to match) changes from row to row.

What should the result be for the merging of:

Ta = cell2table({...
      'a1',  'b1',   'c1'
      'a2',  'b2',   'c2'}, ...
      'VariableNames', {'A', 'B', 'C'});

Tb = cell2table({...
      'a1',  'b2',   'c3'}, ...
      'VariableNames', {'A', 'B', 'C'});

Should the row in b be merged with row 1 or row 2 of a or should all rows be merged or are you guaranteed that a situation like this will never happen?

[William Schaefer]

Hmm that is a good one that I didnt think of. In that case, no merge would be the best, but a separate row.

[William Schaefer]

I've opened a bounty on Stackoverflow: https://stackoverflow.com/questions/46791489/merge-the-content-of-two-tables-looking-for-matlab-or-pseudo-code

[Peter Perkins]

William, this sounds suspiciously close to an outer join with merged keys. But I'm not understanding the basic goal.

You say, "We can do this by trying to crossmatch any of the columns that the tables share in common". In your example, that sounds like you'd consider only B and C to find the row correspondence between Ta and Tb. In your result, there is a row that seems to have matched up 'a2','b2','c2' from Ta and 'b2*','c2','d2' from Tb.

Perhaps you have explained this elsewhere, but how did that happen if b2 and b2* are supposed to represent different values: "I put the * on purpose to indicate that it is a different value". Can you describe exactly what you are trying to do in more detail?

[Guillaume]

My understanding is that you get a match if any key in the keyset match, not just all the keys (or all the non-null keys which would be more manageable). When one key matches but not the other you take any of the value as the value for the result.

[William Schaefer]

@Peter Perkins:

the basic goal is to combine information coming from two tables as efficient as possible.

The crossmatch was just an idea to identify which row in table 1 corresponds to row in table 2. The reason why we need to do this is that we do not have unique row keys, and we also cannot construct ones from our data: we often have missing values in either 1 or 2, making it hard to use one of the columns as row key identifiers.

As for the values, you can use whatever you want foo, bla, bar, x1, x5, the idea was just to show that the values are supposed to be different, despite that c2 is here used to identify that we are talking about some second "row key", but we have different values for the column B.

.

@Guillaume: that is correct, we take one or the other.

Answer 1

[KSSV]

Tc = outerjoin(Ta,Tb,'MergeKeys', 1)

[William Schaefer]

That works pretty smoothly, but the problem is that when there is no exact match, e.g. when at one column is different, then it treats it as a new row. E.g. the above command produces:

     A        B       C       D  
    ____    _____    ____    ____

    ''      'b2*'    'c2'    'd2'
    ''      'b3'     'c3'    'd3'
    ''      'b4'     'c4'    'd4'
    'a1'    'b1'     'c1'    ''  
    'a2'    'b2'     'c2'    ''

Here the rows

''      'b2*'    'c2'    'd2'
'a2'    'b2'     'c2'    ''

Should have been merged into one:

 'a2'    'b2'     'c2'    'd2'

So we need one more step to stack those two together?

[KSSV]

In that case..you need to modify your Tables....there should be no * with b2.

Ta = cell2table({...
    'a1', 'b1', 'c1'; ...
    'a2', 'b2', 'c2'}, ...
    'VariableNames', {'A','B', 'C'})
Tb = cell2table({...
    'b2', 'c2', 'd2'; ...
    'b3', 'c3', 'd3'; ...
    'b4', 'c4', 'd4'}, ...
    'VariableNames', {'B','C', 'D'})

Tc = outerjoin(Ta,Tb,'MergeKeys', 1)

[William Schaefer]

That is not an option. There is always the possibility that there will be a another value like e.g. b2* and b2. I put the * on purpose to indicate that it is a different value, despite that c2 is the same for that row. That is the tricky part in the question.

.

UPDATE:

I have created a function to stack those common rows together, but it is very slow (4.5 minutes for an outerjoined Tc table with 16000 rows and 29 columns):

function Tc = mergecontentTables(Ta, Tb, ColsToCheck)
% A generic function to merge the content of the tables. This is a work
% under progress. Current best run time: 4.5 minutes for 16000 x 29.
%
% The following inputs are relevant:
%
%   - Ta/Tb: a table with independent dimensions
%
%   - ColsToCheck: which columsn to check for common rows (e.g. identifiers
%   SECCODE and TICKER_BB)

% outer join the tables
Tbig     = outerjoin(Ta, Tb, 'MergeKeys', true);
allcolls = Tbig.Properties.VariableNames;

if nargin < 3
    cols = allcolls;
else
    cols = ColsToCheck;
end

t = 1;
T = height(Tbig);

while t < T

      % check if we can find a common row
      % if mod(t, 100)==0; fprintf(' * %4.2f\n',t/T); end
      commonrows = {};
      for c=1:length(cols)
          colname    = cols{c};
          value      = Tbig.(colname)(t);
          if ~isempty(char(value))
              commonrows = find(strcmp(Tbig.(colname), value));
              if length(commonrows)>1
                  break
              end
          end
      end

      % for the common row, merge the diff, delete the row and append the
      % merged
      LCR = length(commonrows);
      if LCR>1
          combos    = combnk(commonrows,2);
          mergedRow = cell2table( repmat({''},1,length(allcolls)), 'VariableNames', allcolls);
          for c=1:length(allcolls)
              colname = allcolls{c};
              for o=1:size(combos,1)

                  % find the first non empty value
                  vA = Tbig.(colname)(combos(o,1));
                  vB = Tbig.(colname)(combos(o,2));
                  vDiff_1 = setdiff(vA,vB);
                  vDiff_2 = setdiff(vB,vA);
                  if ~isempty(char(vDiff_1))
                      mergedRow.(colname) = vDiff_1;
                      break
                  end
                  if ~isempty(char(vDiff_2))
                      mergedRow.(colname) = vDiff_2;
                      break
                  end
                  if isempty(char(vDiff_1)) && isempty(char(vDiff_2))
                      mergedRow.(colname) = vA;
                  end

              end
          end

          % update table
          Tbig(commonrows,:) = [];
          T                  = T - LCR;
          Tbig               = [Tbig; mergedRow]; %#ok<AGROW>
          T                  = T + 1;

      end

      % ++
      t = t + 1;
  end

Tc = Tbig;
end

[Guillaume]

You need to learn how to index tables

colname = T.Properties.VariableNames{somecolumnindex};
charvalue = char(T.(colname)(somerowindex));

would be faster as

charvalue = T{somerowindex, somecolumnindex}{1};

The first pair of {} is to access the element of the table (instead of your convoluted get the column name, then use that name), the second pair is to dereference the cell array (instead of your char)

However, whatever speed up you can get from the above is marginal compared to the complexity of your current algorithm, which is at least O(M^2*N), where M is the height and N the width of your table, for identifying the duplicate rows.

Then there is the merging bit which I don't fully understand. Why the setdiff on sets that only have one element? It certainly look way more complex than it needs to be.

It seems that your algorithm will simply merge all rows which have at least one identical value in the same column, regardless of the values in the other columns. When merging values in other columns it uses the first non-empty value. You certainly don't need four levels of nested loops for that and the outerjoin prior to that seems like a waste of time since a property written merging loop would achieve the same result.

I'll have a go at writing that later this evening if nobody has done it.

[William Schaefer]

Okay that makes sense. I see more what you mean but I am not entirely getting how to merge all rows which have at least one identical value in same column, regardless of the values in other column.

As for the indexing, I am not sure how to index it as there is not really a unique row key. Sometimes it is the ISIN, sometimes the SECCODE, it really depends on what is available and within the table there can be many gaps. There is not one column that is available for each row, and especially not cross-table.

That being said, I have tried to minimize the for loops in the code. This is my best effort so far (reduced the time to 2.7 minutes):

function Tc = mergecontentTables2(Ta, Tb, colsToCheck)
% A generic function to merge the content of the tables. This is a work
% under progress. Current best run time: 4.5 minutes for 16000 x 29.
%
% The following inputs are relevant:
%
%   - Ta/Tb: a table with independent dimensions
%
%   - colsToCheck: which columsn to check for common rows (e.g. identifiers
%   SECCODE and TICKER_BB)

% outer join the tables
Tbig     = outerjoin(Ta, Tb, 'MergeKeys', true);
colsAll  = Tbig.Properties.VariableNames;

if nargin < 3
    colsToCheck = colsAll;
end

t = 1;
T = height(Tbig);
nbMerged = 1;

while t < T

      % check if we can find a common row
      if mod(nbMerged, 100)==0; fprintf(' * %4.2f (%.0f merged)\n',t/T, nbMerged); end

      commonrows = {};
      for c=1:length(colsToCheck)
          colname    = colsToCheck{c};
          value      = Tbig.(colname)(t);
          if ~isempty(char(value))
              commonrows = find(strcmp(Tbig.(colname), value));
              if length(commonrows)>1
                  break
              end
          end
      end

      % for the common row, merge the diff, delete the row and append the
      % merged
      LCR = length(commonrows);
      if LCR>1
          mergedRow = cell2table( repmat({''},1,length(colsAll)), 'VariableNames', colsAll);
          for c=1:length(colsAll)
              colname = colsAll{c};
              uvalues = unique(Tbig.(colname)(commonrows));
              binzero = strcmp(uvalues,{''});
              uvalues(binzero)=[];
              if ~isempty(uvalues)
                  mergedRow.(colname) = uvalues(1);
              end
          end
          % update table
          Tbig(commonrows,:) = [];
          T                  = T - LCR;
          t                  = t - 1;
          Tbig               = [Tbig; mergedRow]; %#ok<AGROW>
          T                  = T + 1;

          nbMerged = nbMerged + LCR;
      end

      % ++
      t = t + 1;
  end

Tc = Tbig;
end

[Life is Wonderful]

Suggestion :

Original Requirement was

        A           B         C           D     
    __________    ______    ______    __________

    {'a1'    }    {'b1'}    {'c1'}    {0×0 char}
    {'a2'    }    {'b2'}    {'c2'}    {'d2'    }
    {0×0 char}    {'b3'}    {'c3'}    {'d3'    }
    {0×0 char}    {'b4'}    {'c4'}    {'d4'    }

Actual Output of

Tbig =

  4×4 table

        A           B         C           D     
    __________    ______    ______    __________

    {'a1'    }    {'b1'}    {'c1'}    {0×0 char}
    {0×0 char}    {'b3'}    {'c3'}    {'d3'    }
    {0×0 char}    {'b4'}    {'c4'}    {'d4'    }
    {'a2'    }    {'b2'}    {'c2'}    {'d2'    }

The incorrect/Bug outcome is from

Tbig = [Tbig; mergedRow]; %#ok<AGROW>

Suggestion and proposal is

Tbig = outerjoin(Tbig,mergedRow,'MergeKeys', true);

You get the desired outcome

        A           B         C           D     
    __________    ______    ______    __________

    {'a1'    }    {'b1'}    {'c1'}    {0×0 char}
    {'a2'    }    {'b2'}    {'c2'}    {'d2'    }
    {0×0 char}    {'b3'}    {'c3'}    {'d3'    }
    {0×0 char}    {'b4'}    {'c4'}    {'d4'    }