MATLAB Answers

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

2 views (last 30 days)
Louise Wilson
Louise Wilson on 16 Oct 2020
Commented: Cris LaPierre on 19 Oct 2020
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

  2 Comments

Sindar
Sindar on 16 Oct 2020
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
Louise Wilson on 17 Oct 2020
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

Sign in to comment.

Answers (1)

Cris LaPierre
Cris LaPierre on 17 Oct 2020
Edited: Cris LaPierre on 17 Oct 2020
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.

  2 Comments

Louise Wilson
Louise Wilson on 17 Oct 2020
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
Cris LaPierre on 19 Oct 2020
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.

Sign in to comment.

Tags

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!