How to extract specific rows from large data file

Hi I have a large data file (attached) and I'm only interested in rows with specific x values of 4.125 as shown below. Now because the value of 4.125 relates to the stop position of the ion, the corresponding start position is also of interest to me and I want to keep this information in the array. How do I write a program which effectively finds the x stop position of 4.125 and retains the ion start position. It is a 120982 * 9 array and in the example shown below I would be interested in keeping the information of ion # 3849096. Your assistance is most appreciated.

"Ion N","Mass","Charge","X","Y","Z","Azm","Elv","KE" 3849094,0.00054858,-1,66.5216,-51,-3.8,-180,88.7,18160 3849094,0.00054858,-1,27.3925,30.3532,-4.07076,-177.1,41.5494,17697.2 3849095,0.00054858,-1,66.5216,-51,-3.7,-180,88.7,18160 3849095,0.00054858,-1,26.6277,31.0039,-3.91402,-177.096,40.8293,17699.4 3849096,0.00054858,-1,66.5216,-51,-3.6,-180,88.7,18160 3849096,0.00054858,-1,4.125,44.9887,-2.47517,-176.363,25.715,17711.1

[Lucien Robinault]

Is opening the file a problem ? Because if not, the function find() should do the trick in my opinion. https://fr.mathworks.com/help/matlab/ref/find.html

[Guillaume]

find is very much over used and is unlikely to be necessary.

[ariane]

Could you kindly suggest a more rigorous regime to help me locate the rows I'm interested in? Any information will be much appreciated.

Answer 1

[Guillaume]

I wouldn't call your file large. Probably, the easiest (tested in R2018a, older versions may have a harder time detecting the import options):

opts = detectImportOptions('Z_EnergySequence.dat');
data = readtable('Z_EnergySequence.dat', opts);
filtereddata = data(data.X == -4.125, :)

Note that if you try to filter for some other x value, there may be some values for which the above doesn't work due to the way floating point comparison work. A more reliable implementation would be:

tol = 1e-6;  %or some other arbitrarily small value relative to the difference of your x values
filter = -4.125;
filtereddata = data(abs(data.X - filter) < tol, :)

[ariane]

Thank you. I'll give this a try and let you know how I get on.

[ariane]

Thank you very much for this. It provides me with the solution I was hoping for. I have one more question though. How do I keep the start position information of the ion in the array? Many thanks again.

[Guillaume]

What do you mean by the start position?

[ariane]

The raw data is presented in this format:

"Ion N","Mass","Charge","X","Y","Z","Azm","Elv","KE" 3849096,0.00054858,-1,66.5216,-51,-3.6,-180,88.7,18160 3849096,0.00054858,-1,4.125,44.9887,-2.47517,-176.363,25.715,17711.1

For each Ion N, there are two instances. The 4.125 filter is useful in letting me know which ion number is of interest to me. However, 4.125 relates to the stop position and what I wish to have is an array with the start position only/ or an option to combine both start and stop.

[ariane]

In the raw data presented above, i'd be interested in the first line only, or a combination of both lines.

[Guillaume]

Once you've filtered the data according to your criteria, you can get the ion numbers with filtereddata.IonN. You can use these numbers to extract all the matching rows of the original table, using ismember:

filteredions = data(ismember(data.ionN, filtereddata.ionN), :)

[ariane]

Many thanks for this. I'll try this and let you know how I get on.

[ariane]

Thank you. This works perfectly. Many thanks for your assistance.

[ariane]

If I wish to save the file as a .dat or CSV, how can I apply the function csvwrite or fprintf?

[Guillaume]

I would recommend you use the function writetable. It can be as simple as

writetable(filteredions, 'C:\somewhere\somename.csv');

[ariane]

Great! Thank you.

[ariane]

Hi,

I have a question relating to data binning. If I wanted to bin this data by initial elevation, Kinetic energy and the ion start position (X,Y,Z), how can I go about doing this? The purpose of this would be to allow me to plot input energy/elevation versus count rate. Thank you.