How to match cell arrays according to their sample ID?

2 次查看（过去 30 天）
as132
as132 2022-12-3
评论： Star Strider 2022-12-3
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!
  2 个评论
Jan
Jan 2022-12-3
编辑：Jan 2022-12-3
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
as132 2022-12-3
Thank you for your input, I tried to adapt my question as suggested.

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Star Strider
Star Strider 2022-12-3
Ran in:
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.
.

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by