How to extract certain rows from an excel sheet based on two categorical value columns?
9 次查看(过去 30 天)
显示 更早的评论
ADJE JEREMIE ALAGBE
2022-4-9
Hello everyone!
I'm facing a problem by trying to extract just certain rows from an excel sheet using readtable, which I couldn't because of the way of the criterion. Hope I can find a solution here. Thanks in adavance.
The excel sheet has 9 columns with a multitude of rows (see attached file). The first column contains categorical values which are all numbers (many values of 1, many values of 2, and so on). The fifth column also contains categorical values such as 'Left', 'Right', 'Down', and 'Up', but also empty cells.
Suppose that for a row R1, the value in the first column is 1 and the fifth column is empty, while for a row R2 the value in the first column is also 1, but the fifth column contains the value "Right".
If in a row R the fifth column value is "Right", I want to extract that row as well as all other rows that the first column value is same with the first column value in row R, and write them in a new sheet.
I used the following, while I properly knew it wouldn't achieve all what I want:
T = readtable('196.xlsx','Sheet','196');
T(1:5,1:9);
data = T(:,{'id','name','frame','speed','turn','acceleration','xdistance','ydistance','time'});
data.turn = categorical(data.turn);
data0=data(data.turn=='Right',:);
filename = '196.xlsx';
writetable(data0,filename,'Sheet',2);
This wrote table with only rows with "Right" in the fith column, but instead I also need other rows that the fifth column is empty or has another value, but that the first column value is same with the first column value of a row that the fifth column value is "Right".
I'll apreciate your effort, if you could help me complete this code. Thanks!
采纳的回答
Voss
2022-4-9
You can try this:
T = readtable('196.xlsx','Sheet','196');
T(1:5,1:9);
data = T(:,{'id','name','frame','speed','turn','acceleration','xdistance','ydistance','time'});
data.turn = categorical(data.turn);
% data0=data(data.turn=='Right',:);
data0 = data(ismember(data{:,1},data{data.turn=='Right',1}),:);
filename = '196.xlsx';
writetable(data0,filename,'Sheet',2);
An example to demonstrate how it works:
data = table([1 2 3 2 1].',{'Right' 'Right' 'Left' '' 'Right'}.','VariableNames',{'id','turn'})
data.turn = categorical(data.turn);
data0 = data(ismember(data{:,1},data{data.turn=='Right',1}),:)
8 个评论
ADJE JEREMIE ALAGBE
2022-4-10
编辑:ADJE JEREMIE ALAGBE
2022-4-10
Thank you so much for your kind support.
I have tried your suggestion and the output sheet looks like attached screenshot; except the table header, all cells are empty. Do you know the reason? Please see it with me. Thanks again!
I will also attach the excel file, so that you may try the code directly on it.
Image Analyst
2022-4-10
编辑:Image Analyst
2022-4-10
Uh, you didn't. Try again to attach the Excel workbook with the paperclip icon. And there is no "attached screenshot below" either.
Voss
2022-4-10
Use 'RIGHT' instead of 'Right':
data0 = data(ismember(data{:,1},data{data.turn=='RIGHT',1}),:);
and it should work.
The resulting file I got, with the second sheet containing results, is attached.
ADJE JEREMIE ALAGBE
2022-4-10
Thank you so much. Still having some imperfection that may be due to my wrong explanation somewhere.
Actually, I need all rows of id(s) with "RIGHT" turn (no need "LEFT", "UP" and "DOWN"). However, some ids (e.g., 16) may being "RIGHT" turn sometimes (e.g., E64) and "empty" turn sometimes (e.g., E17). So, because this id 16 has been "RIGHT" turn at least once, then I have to take all the rows with id 16, including when it is "RIGHT" turn and "empty" turn.
Voss
2022-4-10
I think the code is doing what you are saying. See comments in this code that examines rows 64 and 17 from the first sheet of the input file specifically, which both appear to me to make it to the second sheet as output, i.e., they both seem to be correctly included as part of the set of ids with any RIGHT turns:
T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/958895/196.xlsx','Sheet','196');
% check rows 64 and 17 from the file (which are rows 63 and 16 in table T):
T([64 17]-1,:)
ans = 2×9 table
id name frame speed turn acceleration xdistance ydistance time
__ ______ _____ _____ __________ ____________ _________ _________ _______
16 {'aC'} 5 21 {'RIGHT' } -0.07 80.764 7.2438 0.16667
16 {'aC'} 3 0 {0×0 char} 0 80.514 6.9801 0.1
% run the code
data = T(:,{'id','name','frame','speed','turn','acceleration','xdistance','ydistance','time'});
data.turn = categorical(data.turn);
idx = ismember(data{:,1},data{data.turn=='RIGHT',1});
data0 = data(idx,:);
% check that rows 64 and 17 from the file are in data0
% (yes, they are both there):
disp(idx([64 17]-1))
1
1
% look at all the rows of data0 where id is 16;
% these include lines 64 (3rd row below) and
% 17 (1st row below) from the file:
disp(data0(data0.id == 16,:))
id name frame speed turn acceleration xdistance ydistance time
__ ______ _____ _____ ___________ ____________ _________ _________ _______
16 {'aC'} 3 0 <undefined> 0 80.514 6.9801 0.1
16 {'aC'} 4 0 <undefined> 0 80.653 7.1466 0.13333
16 {'aC'} 5 21 RIGHT -0.07 80.764 7.2438 0.16667
16 {'aC'} 6 18 DOWN 0.04 80.847 7.4103 0.2
16 {'aC'} 7 16 DOWN -0.07 80.903 7.5074 0.23333
16 {'aC'} 8 13 DOWN 0.01 80.958 7.6184 0.26667
16 {'aC'} 9 12 DOWN -0.04 81.014 7.6878 0.3
16 {'aC'} 10 9 DOWN 0 81.014 7.7711 0.33333
16 {'aC'} 11 9 DOWN 0 81.069 7.8405 0.36667
16 {'aC'} 12 10 DOWN 0 81.125 7.9237 0.4
% also look at all the ids that have a RIGHT turn:
unique(data{data.turn=='RIGHT',1}).'
ans = 1×6
1 2 13 16 24 28
unique(data0.id).'
ans = 1×6
1 2 13 16 24 28
% write the new sheet:
filename = '196.xlsx';
writetable(data0,filename,'Sheet',2);
Warning: Added specified worksheet.
% check the new sheet's contents in the file:
new_T = readtable(filename,'Sheet',2);
disp(new_T);
id name frame speed turn acceleration xdistance ydistance time
__ ______ _____ _____ __________ ____________ _________ _________ _______
1 {'aC'} 3 0 {0×0 char} 0 6.7164 47.945 0.1
2 {'aC'} 3 0 {0×0 char} 0 86.62 51.789 0.1
13 {'aC'} 3 0 {0×0 char} 0 82.984 17.249 0.1
16 {'aC'} 3 0 {0×0 char} 0 80.514 6.9801 0.1
1 {'aC'} 4 0 {0×0 char} 0 6.7442 47.945 0.13333
2 {'aC'} 4 0 {0×0 char} 0 86.648 51.803 0.13333
13 {'aC'} 4 0 {0×0 char} 0 83.095 17.346 0.13333
16 {'aC'} 4 0 {0×0 char} 0 80.653 7.1466 0.13333
24 {'aC'} 4 0 {0×0 char} 0 93.558 0.91588 0.13333
1 {'aC'} 5 0 {0×0 char} 0 6.7997 47.959 0.16667
2 {'aC'} 5 0 {0×0 char} 0 86.731 51.803 0.16667
13 {'aC'} 5 16 {'DOWN' } -0.02 83.151 17.457 0.16667
16 {'aC'} 5 21 {'RIGHT' } -0.07 80.764 7.2438 0.16667
24 {'aC'} 5 0 {0×0 char} 0 93.947 1.1657 0.16667
1 {'aC'} 6 0 {0×0 char} 0 6.8552 47.973 0.2
2 {'aC'} 6 7 {'RIGHT' } -0.04 86.759 51.817 0.2
13 {'aC'} 6 13 {'DOWN' } -0.04 83.151 17.554 0.2
16 {'aC'} 6 18 {'DOWN' } 0.04 80.847 7.4103 0.2
24 {'aC'} 6 48 {'RIGHT' } -0.09 94.28 1.3461 0.2
1 {'aC'} 7 0 {0×0 char} 0 6.883 47.973 0.23333
2 {'aC'} 7 0 {0×0 char} 0 86.786 51.817 0.23333
13 {'aC'} 7 12 {'DOWN' } 0.01 83.151 17.665 0.23333
16 {'aC'} 7 16 {'DOWN' } -0.07 80.903 7.5074 0.23333
24 {'aC'} 7 47 {'RIGHT' } 0.07 94.641 1.6097 0.23333
1 {'aC'} 8 0 {0×0 char} 0 6.9107 47.987 0.26667
2 {'aC'} 8 0 {0×0 char} 0 86.814 51.803 0.26667
13 {'aC'} 8 11 {'DOWN' } -0.03 83.206 17.735 0.26667
16 {'aC'} 8 13 {'DOWN' } 0.01 80.958 7.6184 0.26667
24 {'aC'} 8 57 {'DOWN' } 0.11 95.029 2.0122 0.26667
1 {'aC'} 9 9 {'RIGHT' } 0.1 7.0495 48 0.3
2 {'aC'} 9 0 {0×0 char} 0 86.842 51.817 0.3
13 {'aC'} 9 12 {'RIGHT' } 0.06 83.317 17.818 0.3
16 {'aC'} 9 12 {'DOWN' } -0.04 81.014 7.6878 0.3
24 {'aC'} 9 60 {'DOWN' } -0.06 95.279 2.4423 0.3
1 {'aC'} 10 10 {'RIGHT' } -0.07 7.105 48.014 0.33333
2 {'aC'} 10 0 {0×0 char} 0 86.87 51.817 0.33333
13 {'aC'} 10 28 {'DOWN' } 0.22 83.484 18.123 0.33333
16 {'aC'} 10 9 {'DOWN' } 0 81.014 7.7711 0.33333
24 {'aC'} 10 61 {'DOWN' } 0.07 95.612 2.8864 0.33333
1 {'aC'} 11 0 {0×0 char} 0 7.1328 48.028 0.36667
2 {'aC'} 11 7 {'RIGHT' } 0.08 86.953 51.83 0.36667
13 {'aC'} 11 24 {'UP' } -0.29 83.512 18.054 0.36667
16 {'aC'} 11 9 {'DOWN' } 0 81.069 7.8405 0.36667
24 {'aC'} 11 67 {'DOWN' } 0.05 96.029 3.3443 0.36667
1 {'aC'} 12 0 {0×0 char} 0 7.1883 48.042 0.4
2 {'aC'} 12 9 {'RIGHT' } -0.03 87.036 51.83 0.4
13 {'aC'} 12 6 {'RIGHT' } -0.05 83.539 18.04 0.4
16 {'aC'} 12 10 {'DOWN' } 0 81.125 7.9237 0.4
24 {'aC'} 12 71 {'DOWN' } 0.03 96.389 3.8578 0.4
1 {'aC'} 13 0 {0×0 char} 0 7.216 48.056 0.43333
2 {'aC'} 13 0 {0×0 char} 0 87.036 51.844 0.43333
13 {'aC'} 13 7 {'DOWN' } 0.07 83.595 18.123 0.43333
24 {'aC'} 13 68 {'DOWN' } -0.1 96.695 4.3157 0.43333
1 {'aC'} 14 6 {'RIGHT' } 0.06 7.2993 48.07 0.46667
2 {'aC'} 14 0 {0×0 char} 0 87.064 51.817 0.46667
13 {'aC'} 14 19 {'RIGHT' } 0.16 83.789 18.29 0.46667
24 {'aC'} 14 65 {'RIGHT' } 0.05 97.111 4.732 0.46667
1 {'aC'} 15 11 {'RIGHT' } 0.03 7.4103 48.056 0.5
2 {'aC'} 15 0 {0×0 char} 0 87.12 51.83 0.5
13 {'aC'} 15 20 {'DOWN' } -0.15 83.817 18.387 0.5
24 {'aC'} 15 63 {'DOWN' } -0.08 97.416 5.1483 0.5
1 {'aC'} 16 8 {'RIGHT' } -0.09 7.438 48.056 0.53333
2 {'aC'} 16 0 {0×0 char} 0 87.12 51.83 0.53333
13 {'aC'} 16 9 {'DOWN' } -0.05 83.845 18.442 0.53333
24 {'aC'} 16 64 {'DOWN' } 0.1 97.805 5.6063 0.53333
1 {'aC'} 17 0 {0×0 char} 0 7.4936 48.056 0.56667
2 {'aC'} 17 0 {0×0 char} 0 87.147 51.83 0.56667
13 {'aC'} 17 8 {'DOWN' } 0.02 83.845 18.526 0.56667
24 {'aC'} 17 66 {'DOWN' } -0.07 98.082 6.0781 0.56667
28 {'aC'} 17 0 {0×0 char} 0 90.339 1.3877 0.56667
1 {'aC'} 18 8 {'RIGHT' } 0.03 7.5768 48.07 0.6
2 {'aC'} 18 0 {0×0 char} 0 87.175 51.83 0.6
13 {'aC'} 18 9 {'DOWN' } 0 83.872 18.609 0.6
24 {'aC'} 18 68 {'DOWN' } 0.09 98.415 6.6193 0.6
28 {'aC'} 18 0 {0×0 char} 0 90.589 1.7485 0.6
1 {'aC'} 19 8 {'RIGHT' } -0.01 7.6323 48.056 0.63333
2 {'aC'} 19 0 {0×0 char} 0 87.175 51.83 0.63333
13 {'aC'} 19 8 {'DOWN' } -0.03 83.9 18.665 0.63333
24 {'aC'} 19 67 {'DOWN' } -0.11 98.776 7.0217 0.63333
28 {'aC'} 19 52 {'DOWN' } 0.04 90.894 2.1093 0.63333
1 {'aC'} 20 8 {'RIGHT' } 0 7.7156 48.056 0.66667
2 {'aC'} 20 0 {0×0 char} 0 87.203 51.83 0.66667
13 {'aC'} 20 0 {0×0 char} 0 83.9 18.692 0.66667
24 {'aC'} 20 67 {'DOWN' } 0.11 99.165 7.5074 0.66667
28 {'aC'} 20 53 {'DOWN' } -0.02 91.172 2.4701 0.66667
1 {'aC'} 21 6 {'RIGHT' } -0.03 7.7433 48.042 0.7
2 {'aC'} 21 0 {0×0 char} 0 87.231 51.83 0.7
13 {'aC'} 21 6 {'DOWN' } 0.06 83.956 18.762 0.7
24 {'aC'} 21 66 {'DOWN' } -0.11 99.47 7.9376 0.7
28 {'aC'} 21 53 {'DOWN' } 0.01 91.449 2.8309 0.7
1 {'aC'} 22 8 {'RIGHT' } 0.07 7.8544 48.028 0.73333
2 {'aC'} 22 0 {0×0 char} 0 87.231 51.83 0.73333
13 {'aC'} 22 6 {'LEFT' } -0.07 83.956 18.748 0.73333
24 {'aC'} 22 69 {'DOWN' } 0.15 99.914 8.4511 0.73333
28 {'aC'} 22 54 {'DOWN' } 0.02 91.782 3.1917 0.73333
1 {'aC'} 23 10 {'RIGHT' } -0.05 7.9376 48.028 0.76667
2 {'aC'} 23 0 {0×0 char} 0 87.258 51.803 0.76667
13 {'aC'} 23 0 {0×0 char} 0 83.983 18.817 0.76667
24 {'aC'} 23 76 {'DOWN' } -0.02 100.33 8.9506 0.76667
28 {'aC'} 23 54 {'DOWN' } -0.01 92.032 3.5803 0.76667
1 {'aC'} 24 9 {'RIGHT' } 0.03 8.0209 48.028 0.8
2 {'aC'} 24 0 {0×0 char} 0 87.286 51.803 0.8
13 {'aC'} 24 6 {'LEFT' } -0.03 83.928 18.803 0.8
24 {'aC'} 24 74 {'DOWN' } -0.02 100.69 9.4641 0.8
28 {'aC'} 24 54 {'DOWN' } 0.01 92.365 3.9272 0.8
1 {'aC'} 25 12 {'RIGHT' } 0.01 8.1319 48.028 0.83333
2 {'aC'} 25 0 {0×0 char} 0 87.314 51.817 0.83333
13 {'aC'} 25 6 {'DOWN' } 0.03 83.956 18.873 0.83333
24 {'aC'} 25 72 {'DOWN' } -0.02 101.05 9.9637 0.83333
28 {'aC'} 25 54 {'DOWN' } -0.02 92.615 4.3157 0.83333
1 {'aC'} 26 10 {'RIGHT' } -0.04 8.2152 48.014 0.86667
2 {'aC'} 26 0 {0×0 char} 0 87.286 51.83 0.86667
24 {'aC'} 26 74 {'DOWN' } 0.06 101.5 10.463 0.86667
28 {'aC'} 26 48 {'DOWN' } -0.09 92.865 4.6071 0.86667
1 {'aC'} 27 8 {'RIGHT' } 0 8.2707 48.014 0.9
2 {'aC'} 27 0 {0×0 char} 0 87.314 51.817 0.9
24 {'aC'} 27 74 {'DOWN' } -0.05 101.88 10.949 0.9
28 {'aC'} 27 57 {'DOWN' } 0.27 93.281 5.079 0.9
1 {'aC'} 28 0 {0×0 char} 0 8.2984 48.014 0.93333
2 {'aC'} 28 0 {0×0 char} 0 87.314 51.83 0.93333
24 {'aC'} 28 73 {'DOWN' } 0.03 102.27 11.476 0.93333
28 {'aC'} 28 62 {'RIGHT' } -0.18 93.614 5.3843 0.93333
1 {'aC'} 29 9 {'RIGHT' } 0.12 8.4372 48 0.96667
2 {'aC'} 29 0 {0×0 char} 0 87.342 51.803 0.96667
24 {'aC'} 29 80 {'DOWN' } 0.11 102.74 12.059 0.96667
28 {'aC'} 29 52 {'DOWN' } -0.01 93.864 5.7728 0.96667
1 {'aC'} 30 12 {'RIGHT' } -0.06 8.5204 48.014 1
% check just the rows where id is 16;
% I see rows 64 and 17 from the first sheet in there:
disp(new_T(new_T.id == 16,:));
id name frame speed turn acceleration xdistance ydistance time
__ ______ _____ _____ __________ ____________ _________ _________ _______
16 {'aC'} 3 0 {0×0 char} 0 80.514 6.9801 0.1
16 {'aC'} 4 0 {0×0 char} 0 80.653 7.1466 0.13333
16 {'aC'} 5 21 {'RIGHT' } -0.07 80.764 7.2438 0.16667
16 {'aC'} 6 18 {'DOWN' } 0.04 80.847 7.4103 0.2
16 {'aC'} 7 16 {'DOWN' } -0.07 80.903 7.5074 0.23333
16 {'aC'} 8 13 {'DOWN' } 0.01 80.958 7.6184 0.26667
16 {'aC'} 9 12 {'DOWN' } -0.04 81.014 7.6878 0.3
16 {'aC'} 10 9 {'DOWN' } 0 81.014 7.7711 0.33333
16 {'aC'} 11 9 {'DOWN' } 0 81.069 7.8405 0.36667
16 {'aC'} 12 10 {'DOWN' } 0 81.125 7.9237 0.4
ADJE JEREMIE ALAGBE
2022-4-11
Thank you so much for you kind help! I believe your code might be correct and maybe my data has a problem. Actually, based on the original meaning of the data, the fifth column of the resulting sheet should include only RIGHT turns and empty cells.
I will check if there is something wrong with the data.
But I'm going to accept the answer.
Thanks again!
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Matrices and Arrays 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!发生错误
由于页面发生更改,无法完成操作。请重新加载页面以查看其更新后的状态。
您也可以从以下列表中选择网站:
如何获得最佳网站性能
选择中国网站(中文或英文)以获得最佳网站性能。其他 MathWorks 国家/地区网站并未针对您所在位置的访问进行优化。
美洲
- América Latina (Español)
- Canada (English)
- United States (English)
欧洲
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
亚太
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)