Filtering rows according to values in different columns

4 vues (au cours des 30 derniers jours)
Fausto Moretti
Fausto Moretti le 23 Mai 2019
Réponse apportée : Aditya le 20 Nov 2024 à 20:36
I am new at Matlab and I am struggling to use it. Please, need some help.
I have a spreadsheet 3189x89 and I need to split rows according to nine different criteria in columns.
The first column contains Date & Time data. The others contains only numerical data.
What I need is to export all the rows that meet a criteria in a spreadsheet. For example: all the rows that meet the criteria C2>1 AND C3>2 AND 4<C4<=6 ... AND C10 <= 7, need to be copied, stored and exported to an Excel spreadsheet.
I tried to import excel data to a table and work with it in Matlab, but it is not working.
If somebody could help me, I would appreciate! Thanks!
  4 commentaires
Guillaume
Guillaume le 23 Mai 2019
all the rows that meet the criteria C2>1 AND C3>2 AND 4<C4<=6 ... AND C10 <= 7
How is the criteria actually known and stored?
What you want to do is easily done, and for a few columns it could be hardcoded (e.g. mytable(mytable.C1 > 1 & mytable.C3 > 2, :)). With many columns, hardcoding is not practicle and would be a waste of time since the computer can do the work of working out what needs to be compared with what for you. To tell you how to do that, we need to understand how the conditions are stored or where they come from.
Fausto Moretti
Fausto Moretti le 23 Mai 2019
The purpose here is to identify different scenarios within lots of experimental data. Each scenario is characterized by a combination of this 9 variables. Conditions are predetermined bands and, for now, are being inputed manually. I know this is not the wisest way but, as I said, I am beginning...
I will try to clarifi with an example (spreadsheet attached).
Data available:
Data.JPG
Criteria:
18 < C2 <=18.5 ; 64.6< C3 <= 65 ; 8.5<C4<=8.8; 4.0 < C5 <=4.4 ; 0.57<C5<=0.6 ; 0.6 <C7<=0.8; 0.5< C8 <=0.7; 41 < C9 < 44; 4650 < C10 <= 4750
Results:
Results.JPG
Here I included the next desired step, wich is calculate the mean of columns values to the selected rows.
Again, criteria are bands that will vary for each one of the 9 variables. I believe these conditions can be stored as vector or matrix, but I did not tried yet...

Connectez-vous pour commenter.

Réponses (1)

Aditya
Aditya le 20 Nov 2024 à 20:36
To filter out the data based on some specific criteria in MATLAB and export it back to excel, you can use the following approach. This involves reading the data into a table, applying logical indexing, and then exporting the filtered data to an Excel spreadsheet.
Here's how you can acheive it:
1] Import the Data:
  • Use readtable to import your Excel data into MATLAB
dataTable = readtable('yourfile.xlsx');
2] Apply the criteria:
  • Use logical indexing to filter rows based on your conditions.
% Define the criteria and filter the data
% Example criteria: 18 < C2 <= 18.5, 64.6 < C3 <= 65, ..., 4650 < C10 <= 4750
filteredData = dataTable(dataTable.C2 > 18 & dataTable.C2 <= 18.5 & ...
dataTable.C3 > 64.6 & dataTable.C3 <= 65, :)
3] Export the Filtered Data:
  • Use writetable to export the filtered data to a new Excel spreadsheet.
% Export the filtered data to a new Excel file
writetable(filteredData, 'filteredData.xlsx');
4] Calculate the Mean of Selected Rows:
  • Calculate the mean of the selected rows for each column.
% Calculate the mean of the selected rows for each column
means = varfun(@mean, filteredData, 'InputVariables', 2:10);
% Display the means
disp(means);
Hope it helps!

Catégories

En savoir plus sur Data Import from MATLAB dans Help Center et File Exchange

Produits


Version

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by