How to compare the colums ( 2nd and 3rd) of 2 .csv files based on a condition
20 views (last 30 days)
Show older comments
I have a program based on some functionality and the results are displayed on 2 .csv files ( file1 and file2)
The files consists of same rows and columns, the columns are named as condition,supply1,supply2
I want to read both the files into the program, load them and then compare the contents of supply1,supply2, based on the condition and number.
We have compare the respective contents and display the difference of supply1 and suppy2 between those 2 files.
Please help me on this.
9 Comments
Answers (2)
KSSV
on 24 Jun 2020
Edited: KSSV
on 24 Jun 2020
clc; clear all
file1 = fopen('file1.csv');
file2 = fopen('file2.csv');
data1 = textscan(file1, "%s,%f,%f,%d\n", "HeaderLines", 1, "Delimiter",",") ;
data2 = textscan(file2, "%s,%f,%f,%d\n", "HeaderLines", 1, "Delimiter",",") ;
fclose(file1);
fclose(file2);
str1 = data1{1} ;
str2 = data2{1} ;
data1{1} = [] ; M1 = cell2mat(data1) ;
data2{1} = [] ; M2 = cell2mat(data2) ;
data1 = M1(:,1:2) ;
data2 = M2(:,1:2) ;
C1 = M1(:,end) ;
C2 = M2(:,end) ;
s = unique(str1) ; N = unique(C1) ;
m = length(s) ; n = length(N) ;
iwant = cell(m,n) ;
for i = 1:m
idx = strcmp(str1,s{i}) ;
idy = strcmp(str2,s{i}) ;
% Check same values in C1, C2
for j = 1:n
id1 = C1(idx)==N(j) ;
id2 = C2(idx)==N(j) ;
iwant{i,j} = data1(id1,:)-data2(id2,:) ;
end
end
8 Comments
dpb
on 24 Jun 2020
Edited: dpb
on 24 Jun 2020
"Error using cell2mat (line 45)
All contents of the input cell array must be of the same data type."
Of course -- the first column is nonumeric so is cellstr(), remainder are.
I'd suggest using readtable instead of textscan first, then turn the first column into a categorical variable and use it and the fourth for grouping.
dpb
on 25 Jun 2020
Edited: dpb
on 25 Jun 2020
Has to be a more direct way, but following works if I understand the request --
tF1=readtable('file1.csv'); tF1.condition=categorical(tF1.condition); % read tables, condition
tF2=readtable('file2.csv'); tF2.condition=categorical(tF2.condition); % turned to categorical
[g1,id1,id2]=findgroups(tF1.condition,tF1.number); % get group number each file
g2=findgroups(tF2.condition,tF2.number);
[~,ixg1]=sort(g1); [~,ixg2]=sort(g2); % groups may not be in order
dS1=accumarray(g2,tF2.supply1)-accumarray(g1,tF1.supply1); % compute Supply 1 differences
dS2=accumarray(g2,tF2.supply2)-accumarray(g1,tF1.supply2); % compute Supply 1 differences
tF1F2=table(id1,id2,tF2.supply1(ixg2),tF1.supply1(ixg1),dS1,tF2.supply2(ixg2),tF1.supply2(ixg1),dS2);
tF1F2.Properties.VariableNames={'condition','number','F2 S1','F1 S1','diff S1','F2 S2','F1 S2','diff S2'}
% results in...
tF1F2 =
8×8 table
condition number F2 S1 F1 S1 diff S1 F2 S2 F1 S2 diff S2
_________ ______ _____ _____ _______ _____ _____ _______
ta 1.00 5.01 1.27 3.74 -6.11 -1.57 -4.54
ta 2.00 0.07 1.37 -1.30 -4.23 -1.37 -2.86
tb 1.00 1.12 1.07 0.05 -7.67 -1.11 -6.56
tb 2.00 3.34 0.07 3.27 -8.57 -1.23 -7.34
tb 3.00 4.77 1.17 3.60 -2.37 -1.67 -0.70
tc 1.00 4.50 1.47 3.03 -2.07 -1.07 -1.00
tc 2.00 2.40 1.47 0.93 -4.47 -1.47 -3.00
tc 3.00 1.55 1.67 -0.12 -9.97 -3.97 -6.00
>>
The alternative could be to catenate the two files vertically adding a "fileID" variable and operate over it.
But, none of the splitapply workflows are set up to let one use more than one grouping variable over a selection of variables. accumarray builds the differences from the grouping variable ok, but assembling the final table requires ensuring put the rows in with the results of it in the proper location by using the sorted order of the grouping variable.
I'm sure I'm overlooking something simple and obvious but doesn't come to me at the moment.
2 Comments
dpb
on 25 Jun 2020
Edited: dpb
on 26 Jun 2020
This is a MATLAB forum...but there was a response earlier that said was trying both.
ADDENDUM:
Altho the table really has no bearing on the solution other than the output display. I was thinking rowfun or varfun would probably be the ticket when wrote first comment...but the different groupings need makes funky.
Actually, the simplest is probably to resort both data sets so the groupings are in order to begin with -- then the two grouping vectors will be the same and sequential so don't have to resort them.
ADDENDUM SECOND:
" the simplest is probably to resort both data sets ..."
And, then, in fact, there's no longer a need for the explicit grouping variables as long as the two datasets have the same number observations for the same groups -- one can just do the analysis on a row-by-row basis.
See Also
Categories
Find more on Data Import and Export 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!