Cleaning data from excel file

2 次查看（过去 30 天）
Sonia Lenehan
Sonia Lenehan 2020-2-27
评论： Sonia Lenehan 2020-2-27
Hi,
I need to clean data from an excel sheet which has is 11 x 78.
There are 3 columns that I am interested in 2, 3, and 9.
The data from column 2 is dependent on the data from column 9. If any of the cells in column 9 is equal to 1 then I need the corresponding cells in columns 2 and 3 to be deleted/left empty/marked as NaN.
Then if any of the cells left in column 2 are equal to zero I need the corresponing cells in column 3 to be deleted/left empty/marked as NaN. I then need the remaining cells in column 2 to be summed and the remaining cells in column 3 to be averaged.
Is this possible?
So far I have tried the below code to try and remove the unwated cells from column 2 but I am not having much look. I am reletaviely new to MATLAB and just cant seem to get this right.
[~, ~,dat] = xlsread('filename');
dat = [dat(:,2) dat(:,3) dat(:,9)];
if (dat(:,2) == 1 && dat(:,9) == 1)
dat(:,2) = [];
dat(:,9) = [];
end

请先登录，再回答此问题。

采纳的回答

Adam Danz
Adam Danz 2020-2-27
If any of the cells in column 9 is equal to 1 then I need the corresponding cells in columns 2 and 3 to be deleted/left empty/marked as NaN.
dat(dat(:,9) == 1, [2,3]) = nan;
Then if any of the cells left in column 2 are equal to zero I need the corresponing cells in column 3 to be deleted/left empty/marked as NaN.
Apply the same logic as above.
I then need the remaining cells in column 2 to be summed and the remaining cells in column 3 to be averaged.
sum(dat(:,2),'omitnan')
mean(dat(:,3),'omitnan')
  5 个评论
显示 3更早的评论
Adam Danz
Adam Danz 2020-2-27
编辑：Adam Danz 2020-2-27
Are the elements of dat(:,3) all scalar values (not matrices or vectors, but single values)?
If so,
dat([dat{:,3}].' == 1, [1,2]) = {nan};
BTW, the messages you are receiving are errors, not warnings (unless you're using try/catch).
Sonia Lenehan
Sonia Lenehan 2020-2-27
Ah great! Thank you very much for all your help!

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by