Compare table variables with input list and replace missing vars with NA
显示 更早的评论
Hi,
I have a set of data attached in output.csv file which I would like to compare with my masterlist in txt file and see if there's anything missing, if there is a variable missing I want to add it and put its points as NA. How do i do that?
Thanks in advance.
采纳的回答
NIVEDITA MAJEE
2022-6-30
Hello,
You could do it like this:
[~,output] = xlsread('output.csv', 'A:A'); %reading the Names column from output.csv
masterlist = importdata('masterlist.txt'); %reading the data from masterlist.txt
diff = setdiff(masterlist, output); %finding the variables missing in output.csv after comparing it with the variables in masterlist.txt
NA_cell = cell(size(diff)); %creating a cell array with the same dimension as diff array
NA_cell(:) = {'NA'}; % populating it with 'NA'
diff = [diff NA_cell]; %concatenating NA into the diff array
writecell(diff, 'output.csv', 'WriteMode', 'append'); %appending the diff matrix into the output.csv
Hope this solves your issue!
更多回答（2 个）
Readtable and outerjoin do the trick (xlsread is discouraged at this point - readtable is preferred). FYI, there's also a join Live Task that you can use in the Live Editor to explore different join options interactively.
t = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1050265/output.csv",Delimiter=",");
m = readtable("https://www.mathworks.com/matlabcentral/answers/uploaded_files/1050270/masterlist.txt",Delimiter=",", ReadVariableNames=false);
m.Properties.VariableNames(1) = "Names";
outerjoin(t,m,Keys="Names",MergeKeys=true)
6 个评论
RD
2022-6-30
Eric Sofen
2022-6-30
What threw that error? outerjoin?
I wonder if you have some non-text values in the key variable.
RD
2022-6-30
Eric Sofen
2022-6-30
What version of MATLAB are you running? In recent versions with full string support, that should work. An equivalent using char is:
m.Properties.VariableNames{1} = 'Names';
RD
2022-6-30
RD
2022-7-6
This should work with 2017b.
%%
fid = fopen( 'output.csv', 'rt' );
output_csv = textscan( fid, '%s%*f', 'Headerlines',1, 'Delimiter',',' );
[~] = fclose( fid );
fid = fopen( 'masterlist.txt', 'rt' );
masterlist = textscan( fid, '%s' );
[~] = fclose( fid );
%%
lacking = setdiff( masterlist{1}, output_csv{1} );
%%
fid = fopen( 'output.csv', 'a' );
for cac = reshape( lacking, 1,[] )
[~] = fprintf( fid, '%s,%s\r\n', cac{1}, 'NA' );
end
[~] = fclose( fid );
%%
dbtype output.csv 96:102
类别
在 帮助中心 和 File Exchange 中查找有关 Data Type Identification 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
