Using CSV data with awk

Question

I am trying to find a way to take the individual data from a CSV file, and use it as a variable within a grep or awk command. Either seems appropriate but I'm not sure how to tell it to do this appropriately.

For example, I have a dataset in TSV format that looks like this:

ID    Name    Eye Color 

1     Bill    Blue 
2     Sam     Blue 
3     Fred    Brown 
4     Joe     Brown 
5     Ted     Blue 
6     Bob     Brown

This is not the actual dataset, but behaves the same way. This is the entire protein binding database, the TSV is 300MB with millions of entries, and dozens of columns, so I can't cleanly include the real thing.

I want to make a file containing the rows with individuals who have blue eye, so I have created a CSV file which is made up of the "ID" column, which in this case would look like this:

1, 2, 5

This CSV containing the "ID" was generated using the "Grep" command to search for the key term.

I ultimately want a TSV file which looks like this: 1 Bill Blue 2 Sam Blue 5 Ted Blue

But I cannot seem to figure out how to do it. I can create it individually for each entry using awk or grep and including the ID number as a criteria, however the CSV I'm using has 1200 entries so I would like to automate this process.

Below is code that will produce the desired result for a single entry, but I want to use the ID numbers to search automatically.

The BindindDB_All.tsv is my source file, with several million entries. This will produce a TSV called "new.tsv" and contains the entire row of the BindindDB_All.tsv file where the ID (in column 1) equals 66106.

awk '$1 == 66106' BindingDB_All.tsv >> new.tsv

I would like to do something like this:

awk '$1 == ID.csv' BindingDB_All.tsv >> new.csv

where it would read each ID, print the line to new.csv, then read the next ID and do the same.

The CSV file contains 1200 search terms, to be compared with several million possibilities each with a unique ID. I need it to ONLY search column 1, as it will find the ID within other variables in each row.

To summarize, I need it to look in column 1 of the row, compare it to the first number in my CSV file, and see if it is a match. If it is not a match, then it needs to check the next row in column 1, and so on until it finds the match. When it does find the row where column 1 matches the CSV first data point, I want it to output the row. Then I want it to repeat for the second entry in the CSV, until it has found all 1200 rows.

Any ideas? It sounds like a loop problem but I don't know how to make that work either.

EDIT:

Since people still seem willing to help, let me try to answer the questions that have been posted.

Here are the first 6 entries of my real data, containing ID numbers which will be used as search parameters.

66106     
66107    
66108    
66109     
66110    
50127715    

There are no column names, no other data. These are values which I want to search for individually in a different file, a TSV. I have also mispoken regarding the TSV size, I have a 4 GB TSV, which compresses to 300 MB. The file contains more entries than any of my programs allow it to even view. Below is an example of a single entry out of several million. I NEED all of this data to be pulled at once, so trimming it is not an option.

50127715 CCCC(CCC)c1nc2N3[C@H]4CCC[C@H]4N=C3N(C)C(=O)c2[nH]1 InChI=1S/C18H27N5O/c1-4-7-11(8-5-2)15-20-14-16(21-15)23-13-10-6-9-12(13)19-18(23)22(3)17(14)24/h11-13H,4-10H2,1-3H3,(H,20,21)/t12-,13+/m1/s1 CSRSQFSFDXYRFV-OLZOCXBDSA-N 50073697 5-methyl-2-(1-propylbutyl)-(6aR,9aS)-3,4,5,8-tetrahydrocyclopenta[4,5]imidazo[2,1-b]purin-4-one::CHEMBL280307 Phosphodiesterase 1 Bos taurus 60 ChEMBL 10.1016/s0960-894x(98)00681-7 9990447 Ho, GD Silverman, L Bercovici, A Puchalski, C Tulshian, D Xia, Y Czarniecki, M Green, M Cleven, R Zhang, H Fawzi, A Schering-Plough Research Institute http://www.bindingdb.org/bind/chemsearch/marvin/MolStructure.jsp?monomerid=50073697 http://www.bindingdb.org/jsp/dbsearch/PrimarySearch_ki.jsp?energyterm=kJ/mole&tag=pol&polymerid=49000914&target=Phosphodiesterase+1&column=ki&startPg=0&Increment=50&submit=Search http://www.bindingdb.org/jsp/dbsearch/PrimarySearch_ki.jsp?energyterm=kJ/mole&tag=r21&monomerid=50073697&enzyme=Phosphodiesterase+1&column=ki&startPg=0&Increment=50&submit=Search 44272162 103967010 CHEMBL280307 ZINC28221715 1 MGSTATETEELENTTFKYLIGEQTEKMWQRLKGILRCLVKQLEKGDVNVIDLKKNIEYAASVLEAVYIDETRRLLDTDDELSDIQSDSVPSEVRDWLASTFTRKMGMMKKKSEEKPRFRSIVHVVQAGIFVERMYRKSYHMVGLAYPEAVIVTLKDVDKWSFDVFALNEASGEHSLKFMIYELFTRYDLINRFKIPVSCLIAFAEALEVGYSKYKNPYHNLIHAADVTQTVHYIMLHTGIMHWLTELEILAMVFAAAIHDYEHTGTTNNFHIQTRSDVAILYNDRSVLENHHVSAAYRLMQEEEMNVLINLSKDDWRDLRNLVIEMVLSTDMSGHFQQIKNIRNSLQQPEGLDKAKTMSLILHAADISHPAKSWKLHHRWTMALMEEFFLQGDKEAELGLPFSPLCDRKSTMVAQSQIGFIDFIVEPTFSLLTDSTEKIIIPLIEEDSKTKTPSYGASRRSNMKGTTNDGTYSPDYSLASVDLKSFKNSLVDIIQQNKERWKELAAQGEPDPHKNSDLVNAEEKHAETHS Calcium/calmodulin-dependent 3',5'-cyclic nucleotide phosphodiesterase 1A PDE1A_BOVIN P14100 Q08E30,Q28063

I am not sure how to make this read as a TSV within this box, but 50127715 is the first column, the ID column. I want to have my initial CSV file, containing the ID numbers of interest, search the large TSV one ID number at a time within the first column. If the number is contained within the first column, I want it to write that line to a file, then search for the next ID. I want all of the results in a single file.

I'm sure that throughout all of my steps to get here there's an easier way to do this, but I am clearly not sure how to make this clearer. I want it to search the large TSV for "66106" within column 1, and when it finds the line to write the entire line into a file. Then search for "66107" and once it finds it, adds it to the same file. This way I have a single file, Can be a CSV or TSV, with 1200 entries rather than several million.

Answer 1

$ awk -F'\t' '(NR==1) || ($3=="Blue")' file
ID      Name    Eye Color
1       Bill    Blue
2       Sam     Blue
5       Ted     Blue

It sounds like what you're really trying to do, though, is create a new file per ID which, assuming the IDs are unique like in your example, would be:

awk -F'\t' '{ out="out_" $1 ".txt"; print > out; close(out) }' BindingDB_All.tsv

or if you want each output file to include the header:

awk -F'\t' '
    NR==1 { hdr=$0; next }
    { out="out_" $1 ".txt"; print hdr ORS $0 > out; close(out) }
' BindingDB_All.tsv

Answer 2

For anyone who may find this in the future, I have a solution. The first thing I did was convert the TSV to a CSV using:

sed 's/\t/,/g' filename_with_tabs > filename_with_commas.csv

Then to search my file code I was looking for is:

awk -F, 'FNR==NR {h[$1] = $0; next} {print $0,h[$1]}' file1 file2 > new_file.csv

This will search the first column for the text contained in a separate CSV. In this case, "file1" is the file to search and "file2" contains the strings to search for. Both of those files are CSV format.

This produced a separate CSV file which contained all of the lines within file1 which had a certain ID in column 1 that matches one of the IDs contained in file2.

I hope that helps someone someday, because this has wracked my brain for weeks. I didn't even get the solution myself, my boss had to show it to me.