Jtdylktuy

the problem: I a large excel file of data, there are over 1000 columns and over 40,000 rows. I have to identify where a given row has a value of >199 in any given cell. If any row does not have >199 in any given cell then I want to delete those rows. So that I am left with only rows where at least one cell has a value of >199.

I also have the same data file as a text file, so I was thinking that the best approach might be to use linux command line to do this problem rather than using the excel file (which is bulky to work with given the number of rows and columns). But I am a novice at linux and awk so I was looking for general advice of how to approach this issue? thanks so much

Thank you for your help.

Example image of data set below. Here I would want only the rows that have highlighted cells (because those are >200) but I can't just use the sort function or complicated if than statements because there are so many columns in my data set, so that is too time consuming...

enter image description here

Since you said "looking for general advice of how to approach this issue?" here is one approach:

If you know how to use Python, you could save the file as a comma separated file and run through the file simply by writing a small script and then use csv to do something with the data. You can use any operating system that supports python.

I don't have much general advice. 
Specifically, I advise you to use this awk command:

awk '{

        over=0

        for (i=1; i<=NF; i++) if ($i > 199) over=1

        if (over) print

     }'

I created a small data file,
based on numbers from your file, and a few I made up on my own:

$ cat input

81      23      40

31      0       416     12

2       2       1

157     41      80      201

417     42      17



$ ./myscript input

31      0       416     12

157     41      80      201

417     42      17

To delete rows from your file, do

$ ./myscript input > input.new

$ mv input.new input

Notes:

• For your own sake,
  you should decide whether your requirement is > 199, > 200, ≥ 200, or what.


• If you need to keep Row 1 (i.e., line 1, the header row), say so.


• I haven't tested this on a large file. 
  awk shouldn't have any problem with a huge number of rows (lines). 
  A thousand columns (fields) might be an issue, but I doubt it.