How do select values in one table which match those in another...

I have two tables which look like the following. Where the MMSI number in column one of IMO_file matches Var4 in AISfile, I want to extract the MMSI, IMO and Callsign from IMO_file and add it to columns 5:7 of AIS file. I am having trouble doing this!

I have tried here below, but I cannot get the variables into the correct classes to allow this to work... and even then, is there a simpler way to do this than a forloop?

Thanks!

filtered_AIS=('Y:\AIS data\CPA_FILT');
filtered_AIS_files=dir(fullfile(filtered_AIS,'*.csv'));

%for each filtered AIS file, we wanna loop through relevant month of
%AIS_IMO 

for a=1:length(filtered_AIS_files)
    filename=filtered_AIS_files(a).name;
    AISfile=readtable(fullfile(filtered_AIS,filename));
    
    monthYR=strsplit(filename,{'_','.'});
    monthYR=char(monthYR(3))
    YRmonth=strcat(monthYR(4:7),monthYR(1:3));
    
    IMO_AIS=strcat('Y:\AIS data\',YRmonth,'\input_data\not required');
    IMO_files=dir(fullfile(IMO_AIS,'*.csv'));
    
    for b=40:length(IMO_files)
        
        pathparts=strsplit(IMO_files(b).name,'_');
        
        if (pathparts(3)=='ITU5') %only ITU5 files have IMO data

            opts=delimitedTextImportOptions;
            IMO_file=readtable(fullfile(IMO_AIS,IMO_files(b).name),opts);
            IMO_file=cell2table(IMO_file); 
            
            for c=1:length(AISfile)
                
            ais_mmsi=AISfile(c,4);
            
                for d=1:length(IMO_file)
                
                    itu5_mmsi=str2double(IMO_file(d,:));
                    
                    if (ais_mmsi==itu5_mmsi)
                        
                        AISfile(c,5:7)=IMO_file(d,1:3);
                        
                    else
                        %do nothing
                    end
                end
            end
        else
            %do nothing
        end
        
    end
end

        

[Sindar]

check out join.

Also, I'm confused by these lines:

IMO_file=readtable(fullfile(IMO_AIS,IMO_files(b).name),opts);
IMO_file=cell2table(IMO_file);

readtable should already return a table, not a cell array

[Louise Wilson]

Thanks Sidnar, I confused myself with this also! I must have changed the table in the command window by some error, and then I couldn't get it to do what I wanted. Once I sorted this out I have used 'find' to find the value of interest and then copy the values over. I couldn't get join to work.

AISfolder=('Y:\AIS data\CPA_FILT');
AISfiles=dir(fullfile(AISfolder,'*.csv'));

for a=1:length(AISfiles) %for each ais file 
    filename=AISfiles(a).name; %get filename
    AISfile=readtable(fullfile(AISfolder,filename)); %read file
    AISfile.Properties.VariableNames{4} = 'MMSI'; %change variable name 
    
    monthYR=strsplit(filename,{'_','.'}); %get index for reading correct utc15 file for each AIS month
    monthYR=char(monthYR(3));
    YRmonth=strcat(monthYR(4:7),monthYR(1:3));
    
    AISutc15=strcat('Y:\AIS data\',YRmonth,'\input_data\not required');
    AISutc15_files=dir(fullfile(AISutc15,'*.csv'));
    
    for b=1:length(AISutc15_files) %for each utc15 file per AIS month
        
        pathparts=char(strsplit(AISutc15_files(b).name,'_'));
        
        if (pathparts(3,1:4)==char('ITU5')) %only ITU5 files have IMO data

            utc15_file=readtable(fullfile(AISutc15,AISutc15_files(b).name)); %read file
            [tablesize,~]=size(AISfile); %get size of AIS file
            
            for c=1:tablesize %for each row in AIS file
                 
                ais_mmsi=AISfile.MMSI(c); %get MMSI
                index=find(utc15_file{:,1}==ais_mmsi); %find index of rows of MMSI in utc15 file which match it
            
                if (index>1) 
                    row=index(1); %take first (all are the same)
                    AISfile.IMO(c)=utc15_file.IMONumber(row); %assign utc15 of relevant MMSI IMO to AISfile
                    AISfile.Callsign(c)=utc15_file.Callsign(row); %assign utc15 callsign of relevant MMSI to AISfile
                end

            end
            
        else
            %do nothing
        end
        
    end
    
    outfolder=('Y:\AIS data\AIS with IMO');
    writetable(AISfile,fullfile(outfolder, filename));
    
    clearvars -except a AISfolder AISfiles
end

 

Answer 1

[Cris LaPierre]

If you are unfamiliar with joining tables, I suggest using the Join Tables task in the live editor. This allows you to interactively explore various settings until you get the result you want.

[Louise Wilson]

Thanks! This is a cool way of looking at the problem but the issue when I try to use the join functions is that I run out of memory. I think outerjoin is what I want but just doing t=outerjoin(fileone,filetwo); doesn't work as I run out of space.

[Cris LaPierre]

That's interesting. Your tables don't seem to be that large. Can you share your two tables in a mat file? You can attach them using the paper clip icon.