How to extract certain rows from an excel sheet based on two categorical value columns?
7 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
ADJE JEREMIE ALAGBE
el 9 de Abr. de 2022
Comentada: Voss
el 11 de Abr. de 2022
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!
0 comentarios
Respuesta aceptada
Voss
el 9 de Abr. de 2022
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 comentarios
Más respuestas (0)
Ver también
Categorías
Más información sobre Matrices and Arrays en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!