Find zeros and remove
5 views (last 30 days)
Show older comments
Hi, I have a wide excel file with several columns. I need to "clean" it from meaningless values, and obtain matrix with only three columns with the values I need to analyse. Here is an example of how the data look like in the excel file (numbers are invented):
Xdat X Y
0 0 0
0 0 0
0 0 0
0 12 12
8 2 2
8 2 2
8 2 2
8 2 2
8 2 2
8 2 2
0 16 24
0 16 24
0 0 0
0 10 10
0 0 0
0 0 0
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
0 16 24
0 16 24
0 0 0
0 10 10
0 0 0
0 0 0
8 5 5
8 5 5
8 5 5
8 5 5
8 5 5
8 5 5
The problem is simple: I need it to look through the Xdat column, find the zeros and removing the corresponding rows. At the end I should obtain a matrix like that:
8 2 2
8 2 2
8 2 2
8 2 2
8 2 2
8 2 2
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
8 5 5
8 5 5
8 5 5
8 5 5
8 5 5
8 5 5
Another thing I could say is that Xdat values go from 1 to 72, but are in random order and repeated twice. So there are in total 144 Xdat values (two ones, two twos, two threes and so on). I therefore need to be sure that columns and rows with the same Xdat value are not overwritten. (As shown above).
The only solution that comes to my mind is to eliminate all rows corresponding to the 0 Xdat.
Hope I've been clear. If you need further explanation please let me know. Thank you
Giulia
0 Comments
Answers (2)
Azzi Abdelmalek
on 14 Jun 2014
Edited: Azzi Abdelmalek
on 14 Jun 2014
Suppose M is your matrix
M=[1 1 10
0 0 0
4 0 0
0 12 12
8 2 2
0 2 2]
M(M(:,1)==0,:)=[]
0 Comments
Image Analyst
on 14 Jun 2014
Try this:
%numbers = xlsread(filename); % Uncomment to read workbook.
numbers = [... % Remove this if you read in a workbook.
0 0 0
0 0 0
0 0 0
0 12 12
8 2 2
8 2 2
8 2 2
8 2 2
8 2 2
8 2 2
0 16 24
0 16 24
0 0 0
0 10 10
0 0 0
0 0 0
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
1 3 3
0 16 24
0 16 24
0 0 0
0 10 10
0 0 0
0 0 0
8 5 5
8 5 5
8 5 5
8 5 5
8 5 5
8 5 5]
% MAIN CODE IS BELOW:
% Find rows to delete:
rowsToExtract = all(numbers, 2)
% Delete in place to change existing array:
numbers = numbers(rowsToExtract,:)
0 Comments
See Also
Categories
Find more on Spreadsheets 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!