compare and remove duplicates, not unique alone!
5 次查看(过去 30 天)
显示 更早的评论
Hi all :) I have an excel file(3 columns, many rows): there are duplicates here, by the third columns (CANONICAL_SMILES), but what i want is not only to remove the duplicates but also check: 1. if the ratio between the values of the second columns (STANDARD_VALUE) > 50, i'll delete both rows 2. if its smaller i'll keep the row with the largest value
*could be more than 2 rows which have duplicates, so i have to compare 3 or 4 rows each time and decide ahich one to keep .. any help here, thank you
**test file attached
4 个评论
José-Luis
2016-12-21
编辑:José-Luis
2016-12-21
What you asked was not clear at all, at least to me. Help us help you. A good start is a self-contained, minimum working example.
Case in point, I didn't even try to make sense of:
" CMPD_CHEMBLID STANDARD_VALUE CANONICAL_SMILES CHEMBL201383 1234 Brc1ccc(cc1)C1N(c2ccc(Cl)cc2)C(=O)N(C1=O)c1ccc(Cl)cc1 CHEMBL256753 220 Brc1ccc(cc1S(=O)(=O)N1CCCC1)C(=O)N[C@H]1[C@@]2(CC[C@@H](C2)C1(C)C)C CHEMBL256752 280 Brc1ccc(cc1S(=O)(=O)N1CCCCC1)C(=O)N[C@H]1[C@@]2(CC[C@@H](C2)C1(C)C)C CHEMBL256962 30000 Brc1ccc(cc1S(=O)(=O)N1CCCCC1)C(=O)N[C@H]1[C@@]2(CC[C@@H](C2)C1(C)C)C CHEMBL383287 195 Brc1ccc(N2C(c3ccccc3)C(=O)N(c3ccc(Br)cc3)C2=O)cc1 CHEMBL381922 1624 Brc1ccc(N2C(c3ccccc3)C(=O)N(c3ccc(Br)cc3)C2=O)cc1 CHEMBL570683 10000 Clc1c(cccc1Cl)-c1cc(C(=O)N2CCOCC2)c(nc1)N1CCCCCC1 CHEMBL576079 10000 Clc1c(cccc1Cl)-c1ccc(nc1)N1CCCCCC1 "
because it is so poorly formatted and the variable names (if that's what they are) are nightmare-inducing.
Also, the snippet you posted subsequently is incomplete. Even if it was complete, we don't have the data to run it. Spurious errors might be easy to spot, but debugging is more efficient with actual data.
回答(1 个)
Guillaume
2016-12-21
编辑:Guillaume
2016-12-21
"if the ratio between the values of the second columns > 50"
"could be more than 2 rows which have duplicates, so i have to compare 3 or 4 rows"
What is the definition of the ratio when there are more than 2 rows to compare?
Ignoring that for now, here is how I would do it:
t = readtable('test1.xls'); %a lot more powerful than xlsread
assert(all(ismember({'CMPD_CHEMBLID', 'STANDARD_VALUE', 'CANONICAL_SMILES'}, t.Properties.VariableNames)), ...
'Check your columns headers');
[~, ~, subs] = unique(t.CANONICAL_SMILES, 'stable');
rows = (1:height(t)).';
rowstokeep = accumarray(subs, rows, [], @(r) filterrows(t, r));
tfiltered = t(nonzeros(rowstokeep), :)
With a separate function filterrows as follow:
function rowtokeep = filterrows(t, identicalrows)
if numel(identicalrows) == 1
%no duplicate. always keep
rowtokeep = identicalrows;
else
standardvalues = t.STANDARD_VALUE(identicalrows);
[minsv, minrow] = min(standardvalues);
standardvalues(minrow) = []; %never kept anyway
identicalrows(minrow) = []; %and remove from index
ratio = standardvalues / minsv;
if all(ratio > 50)
rowtokeep = 0; %don't keep anything. return 0 instead. 0 will be filtered by main function
else
%keep the row with the greatest ratio but less than 50? Don't know if that's what you want.
identicalrows(ratio > 50) = [];
ratio(ratio > 50) = [];
[~, maxrow] = max(ratio);
rowtokeep = identicalrows(maxrow);
end
end
end
You can tailor the filter function to whichever definition you have. It must return a scalar index of the row to keep for duplicate, or 0 if none is to be kept.
3 个评论
Guillaume
2016-12-22
That actually makes the filtering simpler:
function rowtokeep = filterrows(t, identicalrows)
if numel(identicalrows) == 1
%no duplicate. always keep
rowtokeep = identicalrows;
else
standardvalues = t.STANDARD_VALUE(identicalrows);
[maxsv, maxrow] = max(standardvalues);
if maxsv / min(standardvalues) > 50
rowtokeep = 0; %don't keep anything. return 0 instead. 0 will be filtered by main function
else
rowtokeep = maxrow;
end
end
end
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Adding custom doc 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!