-1

If I have two files

file1:

Reference Position
905894
1197693
3703749
92108275
114940633
114940633

file2:

Mapping  Reference Position Type    Length  Reference   Allele  Linkage Zygosity    Count
1 mapping   877831  SNV 1   T   C       Homozygous  48
1 mapping   883625  SNV 1   A   G       Homozygous  23
1 mapping   905894  SNV 1   C   T       Heterozygous    41
1 mapping   909768  SNV 1   A   G       Homozygous  85
1 mapping   1153944 SNV 1   T   G       Heterozygous    65
1 mapping   1197693 SNV 1   G   A       Heterozygous    23
1 mapping   1276973 SNV 1   G   C       Heterozygous    4
1 mapping   1276974 Insertion   4   -   ACAC        Heterozygous    52
1 mapping   1277533 SNV 1   T   C       Homozygous  73

I want the reference positions in both files to match and return the whole column in file 2 that matches with file 1.

  • 3
    do you mean column or row? – Sebastian Sep 9 '14 at 20:03
  • It's going to take a little work, but it may help to line up the data with the column headings. – eyoung100 Sep 9 '14 at 20:14
  • i mean row i wrote the opp so value from file1 ex "905894" match the value in file2 in 2nd column and return the whole row as output i.e 1 mapping 905894 SNV 1 C T Heterozygous 41 – user83587 Sep 10 '14 at 16:12
0

Using egrep should help. Try:

grep -E '(905894|1197693|3703749|92108275|114940633)' file2

This will check for an occurence of the pattern provided in file1. When testing it out, I got this for the results:

[rkahil@xxxxxx ~]$ grep -E '(905894|1197693|3703749|92108275|114940633)' file2
1 mapping   905894  SNV 1   C   T       Heterozygous    41
1 mapping   1197693 SNV 1   G   A       Heterozygous    23
  • what if i have a bigger row(more values) just mention the row 1 in file 1 to grep? – user83587 Sep 9 '14 at 20:46
  • You would just add a pipe symbol in parenthesis and add the pattern to the regex – ryekayo Sep 9 '14 at 20:46
  • its taking too long to give the output file – user83587 Sep 10 '14 at 13:36
  • What that is doing is grepping through file2. The results are dependant on how big file2 is. – ryekayo Sep 10 '14 at 13:41
0

To match a number, you can use grep return a row:

$ grep 883625 file2
1 mapping   883625  SNV 1   A   G       Homozygous  23

if you want to output all the lines in file2 which have numbers in file1:

$ grep -f file1 file2
Mapping  Reference Position Type    Length  Reference   Allele  Linkage Zygosity    Count
1 mapping   905894  SNV 1   C   T       Heterozygous    41
1 mapping   1197693 SNV 1   G   A       Heterozygous    23

i.e. the -f option of grep scans your file1 and tries to find matches in file2. Here, also the header is matched by the first line in file1. From man grep:

-f FILE, --file=FILE
     Obtain  patterns  from  FILE,  one per line.  The empty file
     contains zero patterns, and therefore matches nothing.  
0

Use grep reading patterns from file

We can use the whole lines in file1 as fixed stings patterns - the option -F tells grep to not interpret the patterns as regexps.
Grep has an option -f to read patterns for a file, with one pattern in each line. That's just what we have, so the patterns can be directly read from the file.

$ grep -F -f file1 file2       
Mapping  Reference Position Type    Length  Reference   Allele  Linkage Zygosity    Count
1 mapping   905894  SNV 1   C   T       Heterozygous    41
1 mapping   1197693 SNV 1   G   A       Heterozygous    23

Avoid matching substrings

The command above uses patterns from file1, like 905894 for example. If there would be a row in file2 that has 9058940, the 905894 would match, because it matches the first six characters. That's wrong. So we need to make it match only whole words. We could change the patterns to match word starts and ends, like '\b905894\b', but grep has an option for this common case: -w:

grep -F -w -f file1 file2       

Exclude header

We do not want the headers, even though it's technically correct that they are shown, as the header Reference Position occurs in both files.

With tail -n +2, we pick the lines starting from the second, and use - as the file name to read the output of tail from stdin:

$ tail -n +2 file1 | grep -F -w -f - file2
1 mapping   905894  SNV 1   C   T       Heterozygous    41
1 mapping   1197693 SNV 1   G   A       Heterozygous    23

In bash, this does about the same:

grep -F -w -f <(tail -n +2 file1) file2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.