How to match cell arrays according to their sample ID?

I have two cell arrays of different size. Now I want to match them into a single cell array according to their Sample ID.

Here short samples how my initial cell arrays (A & B) look like.

%% Create sample data
A= {'Sample ID', 'Value'; 1, 1050; 2,1133; 3,1001; 4,1200}
B= {'Sample ID', 'Date'; 1, '01 Dez 2022 14:11:05'; 3,'02 Dez 2022 15:10:10'; 4,'02 Dez 2022 15:11:45'}

A:

Sample ID Value

1 1050

2 1133

3 1001

4 1200

B:

Sample ID Date

1 '01 Dez 2022 14:11:05'

3 '02 Dez 2022 15:10:10'

4 '02 Dez 2022 15:11:45'

They should be combined to one cell array so that sample IDs match. For samples that only exist in array A or array B the missing value should be filled with NaN. The output should look as follows:

C:

Sample ID Date Value

1 '01 Dez 2022 14:11:05' 1050

2 NaN 1133

3 '02 Dez 2022 15:10:10' 1001

4 '02 Dez 2022 15:11:45' 1200

I tried it with ismember, intersect and a loop however encoutered basic problems when using the functions:

%% Tried approaches
% intersect
[val,pos]=intersect(A{:,1},B{:,1});
% --> error with too many input arguments

% ismember
common=ismember(A{:,1},B{:,1});
% --> error with too many input arguments

% loop
C(:,[1 3])=A(:,1:2);
i=2;
k=2;
for i=2:length(A)
    if B{k,1}==A{i,1}
        C{i,2}==B{k,2};
        i=i+1;
        k=k+1;
    else
        C{i,2}=nan;
        i=i+1;
    end
end
% --> problems with incompatible sizes 

How can I do this? Thanks in advanced for any advice!

[Jan]

It would be useful, if you post the input data such, that they can be used by copy&paste to post an answer. It is tedious to construct such data, so it should be your turn.

Then I would not have to guess, if "Sample ID Date" belong to the data or not.

"I tried it with ismember and intersect but did not work as desired." - It is useful to post the code and to explain the occurring problems. Sometime a small modification can fix the code.

[as132]

Thank you for your input, I tried to adapt my question as suggested.

Answer 1

[Star Strider]

Try something like this —

%% Create sample data
A= {'Sample ID', 'Value'; 1, 1050; 2,1133; 3,1001; 4,1200};
B= {'Sample ID', 'Date'; 1, '01 Dez 2022 14:11:05'; 3,'02 Dez 2022 15:10:10'; 4,'02 Dez 2022 15:11:45'};

At = cell2table(A(2:end,:), 'VariableNames',A(1,:))

At = 4×2 table
    Sample ID    Value
    _________    _____

        1        1050 
        2        1133 
        3        1001 
        4        1200 

Bt = cell2table(B(2:end,:), 'VariableNames',B(1,:));
Bt.Date = datetime(Bt.Date,'Locale','de_DE', 'InputFormat','dd MMM yyyy HH:mm:ss')

Bt = 3×2 table
    Sample ID            Date        
    _________    ____________________

        1        01-Dec-2022 14:11:05
        3        02-Dec-2022 15:10:10
        4        02-Dec-2022 15:11:45

C = outerjoin(At,Bt, 'Keys',{'Sample ID'})

C = 4×4 table
    Sample ID_At    Value    Sample ID_Bt            Date        
    ____________    _____    ____________    ____________________

         1          1050           1         01-Dec-2022 14:11:05
         2          1133         NaN                          NaT
         3          1001           3         02-Dec-2022 15:10:10
         4          1200           4         02-Dec-2022 15:11:45

CVN = C.Properties.VariableNames;
C = removevars(C,CVN(3))

C = 4×3 table
    Sample ID_At    Value            Date        
    ____________    _____    ____________________

         1          1050     01-Dec-2022 14:11:05
         2          1133                      NaT
         3          1001     02-Dec-2022 15:10:10
         4          1200     02-Dec-2022 15:11:45

This can be done relatively easily using cell2table to create a table out of each cell array, then outerjoin and removevars to get the desired result. The ‘NaT’ (‘Not a Time’) entry is ‘NaN’ for datetime variables. Use table2cell to convert the final result back into a cell array, if necessary.

.

[as132]

Brilliant, thank you very much for your detailed answer!

[Star Strider]

As always, my pleasure!