Clear Filters
Clear Filters

Plotting repeating values in excel files vs the files that they overlap in

2 views (last 30 days)
I am trying to plot the number of times that values repeat between excel files. This code will be dealing with 200+ excel files per run that are formatted the same way.
For example, between file names "output3" and "output4", I want to see how many times a value repeats in excel columns 2 through 4. If there would be 5 values that overlap between these two excel files then on the plot, there would be a bar with a value of 5 and a label saying "between output3 and output4". I have attached my code attempt below with comments as to what I am trying to do in each section:
%% beginning of code imports excel files and the column values as variables
%wanting to find where the values overlap in columns 2 - 4
L = length(fieldnames(data));
data.idx2 = cell(L-1);
data.idx3 = data.idx2;
data.idx4 = data.idx2;
for j = 2:L
for i = 1:L-1
firstoutput = data.(sprintf("variables_%d", i));
secondoutput = data.(sprintf("variables_%d",j));
data.idx2{i} = ismember(firstoutput(:,2),secondoutput(:,2));
data.idx3{i} = ismember(firstoutput(:,3),secondoutput(:,3));
data.idx4{i} = ismember(firstoutput(:,4),secondoutput(:,4));
end
end
%wanting to obtain only the number "1" (i.e. true) to count the number of times that the values overlap
isTrue2 = cellfun(@(x)isequal(x,1),data.idx2);
isTrue3 = cellfun(@(x)isequal(x,1),data.idx3);
isTrue4 = cellfun(@(x)isequal(x,1),data.idx4);
%wanting to create a variable with all of the output file names
names = [];
for n = 1:length(cycle)
x = sprintf("output%d",n);
names = [names;x];
end
%wanting to plot the # of overlapping/ repeating values vs which files they appear in
bar(length(idx2_true),names)
bar(length(idx3_true),names)
bar(length(idx4_true),names)

Answers (0)

Categories

Find more on MATLAB Functions in Microsoft Excel in Help Center and File Exchange

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!