I have the following problem:
In a table with multiple coulumns I want to cut off / drop / disable all rows of one column before and after reaching specific values. The tricky part is, that "=" don´t work because in some tables the values drop back in that range I want to keep.
For example
- Row 2 has the required min. value of "1.5" I want to keep
- Row 146 has the max. value of "6.3" I want to keep
- Rows 226-241 also have values between 1.5-6.3 but I only want to keep the range between row 2 and row 146
Of Course the row numbers with the min and max values Change from file to file
I never thought I would recommend Excel over R. However for this type of manipulation I would expect Excel might be easier. Then output the result to R as say a CSV file. Good luck, David Booth
Given the column is sorted and "x" is the column. The vector with row indices to keep is then obtained with:
which.min(x) : tail(x==max(x),1)
If x contains missing values, you might use max() with na.rm=TRUE.
Hello,
If I understood your question correctly you could solve your problem in Excel, as long as you have less than 1048576 rows (or you can otherwise split your data).
Paste your column of data starting in A1.
In D1 you can write the first value of interest (if you want the lowest value and you do not know it, use the MIN function. If you want the k-lowest, use SMALL).
In D2 you can write the last value of interest (if you want the highest value and you do not know it, use the MAX function. If you want the k-highest, use LARGE).
In F1 write: =MATCH(D1,A1:A100,0). The number that you obtain is the row of the first value corresponding to the min value of interest. Replace A100 with the last row of the data set.
In F2 write: =MATCH(D2,A1:A11,0). The number that you obtain is the row of the first value corresponding to the max value of interest. Replace A100 with the last row of the data set.
Then you can delete or filter out the rows above and below the two points that you found.
Hope this will help you.
Best regards,
Matteo
Thank you all for your fast Response!
Henning Teickner
first of all hello to Münster! Iam from Münster too.That Approach is really good and with the guidance easily to understand. But I have to plot my Graphs in the pgfplots Package of Latex, so I assume that it will still spit out all the unnecessary datapoints.
Right now Iam trying to NA all the rows of data in that column after reaching the required value for the first time and replace it with the last value that shows up.
- Badges
- Science topic
- Similar topics
- Mathematics
- Statistics
- Statistical Software
- R Statistical Package