How to match two data columns?

Question

Amr Hashem 2016-1-7

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262927-how-to-match-two-data-columns

评论： Image Analyst 2016-1-11

在 MATLAB Online 中打开

I have 2 excel sheets (each contain two columns( Serial & ID) 1st sheet(3708*2) 2nd sheet(2360*2))

both has the same Serial data but not arranged.

I want to fill each ID cell (in sheet 2) with each corresponding ID cell(in sheet 1) by using Serial column.

(copy ID from 1 to 2)

I use this code:

 [ndata2 text2 alldata2] = xlsread('Basic.xlsx','WorksheetID4');
[ndata text alldata] = xlsread('Database.xlsx','WorksheetW4');
[R1,C1]=size(alldata2);
[R2,C2]=size(alldata);
colsize=max(C1,C2); 
for j=1:3708 
    for i=1:2360 %length(alldata)            
        if alldata{j,1}== alldata2{i,1}    
            alldata{j,2}=alldata2{i,2};  
        end
    end
           i=0
end
newdata=alldata;
     xlswrite('Database.xlsx',newdata,4);

but I got an error:

 matrix dimensions must agree
if alldata{j,1}== alldata2{i,1}

note: some Serial data include text (ex.: 3XGE0013 or N123)

How to match the two data?

3 个评论
显示 1更早的评论隐藏 1更早的评论

Image Analyst 2016-1-9

Try using ismember() to identify rows that you want to delete. I still think using readtable and then innerjoin and outerjoin will do it for you. Not sure why you didn't try my suggestion. Of course I can't even try anything because you didn't attach any data.

Amr Hashem 2016-1-10

在 MATLAB Online 中打开

serial.xlsx

I try :

out = all(ismember(DO,D),2);

but I got an error:

 Input A of class cell and input B of class cell must be cell
arrays of strings, unless one is a string.

here is the file of data, if you can help

Thanks in advance

请先登录，再进行评论。

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

Answer 1

Image Analyst 2016-1-7

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262927-how-to-match-two-data-columns#answer_205363

DO NOT call your text variable text, since that is the name of a built-in function. Also, try using readtable() instead of xlsread() - then you can use commands built just for this such as innerjoin and outerjoin.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

Image Analyst 2016-1-10

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/262927-how-to-match-two-data-columns#answer_205663

在 MATLAB Online 中打开

Try it this way:

tWith = readtable('serial.xlsx', 'Filetype', 'Spreadsheet', 'Sheet', 'with ID')
tWithout = readtable('serial.xlsx', 'Filetype', 'Spreadsheet', 'Sheet', 'without ID')
[Lia, Locb] = ismember(tWith(:, 1), tWithout(:, 1))
% Lia is the rows of tWith that are found somewhere in tWithout.
% If A and B are tables, then Locb contains the lowest index in B
% for each row in A that is also a row in B. 
% The output vector, Locb, contains 0 whenever A is not a row of B.
aIndexes = find(Lia);
Locb(Locb==0) = []; % Get rid of indexes of 0
newWith = tWithout;
% Transfer columns 2
newWith{Locb,2} = tWith{aIndexes, 2}

4 个评论
显示 2更早的评论隐藏 2更早的评论

Amr Hashem 2016-1-10

在 MATLAB Online 中打开

when i use xlsread(), I got an error:

 Input A of class cell and input B of class cell must be cell
arrays of strings, unless one is a string.

so I use:

 [ndata2 text2 alldata2] = xlsread('serial.xlsx','with ID');   
[ndata text1 alldata] = xlsread('serial.xlsx','without ID');
tWith=ndata2;  
tWithout=ndata;
[Lia, Locb] = ismember(tWith(:, 1), tWithout(:, 1));
aIndexes = find(Lia);
newWith = tWithout;
newWith{nLocb,2} = tWith{naIndexes, 2}

but also I got an error:

 Cell contents reference from a non-cell array object.
at
 newWith{nLocb,2} = tWith{naIndexes, 2}

Image Analyst 2016-1-11

I guess only alldata, alldata2, text2, and text1 will be cell arrays and ndata2 and ndata will be regular numerical arrays, so use parentheses instead of braces.

请先登录，再进行评论。