Compare table variables with input list and replace missing vars with NA
3 views (last 30 days)
Show older comments
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.
0 Comments
Accepted Answer
NIVEDITA MAJEE
on 30 Jun 2022
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!
More Answers (2)
Eric Sofen
on 30 Jun 2022
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 Comments
per isakson
on 2 Jul 2022
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
0 Comments
See Also
Categories
Find more on Text Files in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!