0

I have a file like this: (has 308545 lines)

head output11.frq
 CHR               SNP   A1   A2          MAF  NCHROBS
   1      1:775852:T:C    T    C       0.1707     3444
   1     1:1120590:A:C    C    A      0.08753     3496
   1     1:1145994:T:C    C    T       0.1765     3496
   1     1:1148494:A:G    A    G       0.1059     3464
   1     1:1201155:C:T    T    C      0.07923     3496
...

And another file (marker-info) which has the first 24 commented lines and is comma separated that looks like this (has total of 500593 lines):

1,742429,SNP_A-1909444,ss66079302,rs3094315,36.2,G,A,C,T,A,GCACAGCAAGAGAAAC[A/G]TTTGACAGAGAATACA,Sty,+,-,y,,,127,phs000018
1,769185,SNP_A-4303947,ss66273559,rs4040617,36.2,A,G,A,G,A,GCTGTGAGAGAGAACA[A/G]TGTCCCAATTTTGCCC,Sty,+,+,n,,,127,phs000018
1,775852,SNP_A-1886933,ss66317030,rs2980300,36.2,T,C,A,G,A,GAATGACTGTGTCTCT[C/T]TGAGTTAGTGAAGTCA,Nsp,-,+,y,,,127,phs000018
1,782343,SNP_A-2236359,ss66185183,rs2905036,36.2,C,T,C,T,A,CTCGATTTGTGTTCAA[C/T]ATATTTCATTTGTACC,Sty,-,-,n,,,127,phs000018
1,1201155,SNP_A-2205441,ss66174584,rs4245756,36.2,C,T,C,T,A,CCAGTGCTTTCAACCA[C/T]ACTCACTTTTCACTGT,Sty,+,+,n,,,127,phs000018
...

I want to replace in output11.frq second column with the 5th column in marker-info that has the matching value in 1st and 2nd column so for this example the result of the output11.frq would look like this:

1      rs2980300    T    C       0.1707     3444
1      rs4245756    T    C      0.07923     3496

I tried doing this but I got empty file:

vi tst.awk
NR==FNR { map[$1,$2]=$5; next }
($1,$4) in map { $2=map[$1,$4]; print }


awk -f tst.awk FS=',' marker-info FS='\t' output11.frq  > output11X.frq

EDIT:

I tried to run this:

 vi test2.awk
 NR==FNR { map[$2]=$5 }
 NR!=FNR { split($4, x, ":"); if(x[2] in map){ $4=map[x[2]]; print }}

awk -f test2.awk FS=',' marker-info FS='\t' output11.frq > output11X.frq

but I got this:

head output11X.frq
 CHR               SNP   A1   A2          MAF  NCHROBS   rs41340551
   1      1:775852:T:C    T    C       0.1707     3444   rs41340551
   1     1:1120590:A:C    C    A      0.08753     3496   rs41340551
   1     1:1145994:T:C    C    T       0.1765     3496   rs41340551
...
10
  • Plese edit your question and show the complete expected output that should result from the example input files in your question. The technique in your awk script requires that you specify at least two files on the command line. The first file is read to fill map and all other files will be processed using map. If you specify one file only you won't get any output. Please explain in your question what you think are the values in the relevant columns $1, $2, $5, $4 in your example input. The values in the two files don't seem to match without further processing.
    – Bodo
    Commented Mar 30, 2020 at 22:37
  • Thank you so much, I edited it!
    – anamaria
    Commented Mar 30, 2020 at 22:45
  • Please explain in your question why the expected output has only 2 lines? and how exactly the data is expected to match. A difficulty is that the two files have different field separators. As I understand the files, in output11.frq column 1 is 1 and column 2 is 1:775852:T:C. In the other file column 1 is 1 and column 2 is 742429. This will never match without further processing. It is unclear which values 1 in the two files must match because output11.frq has a 1 in two places.
    – Bodo
    Commented Mar 30, 2020 at 22:59
  • two lines is just for this snippet of data shown here which can be used to test code. I only need to match by position here 775852
    – anamaria
    Commented Mar 30, 2020 at 23:01
  • Please add all information to the question. Your comment does not explain how exactly the data must match. I suggest to add some debug output to the awk script, e.g. NR==FNR { map[$1,$2]=$5; printf "$1=%s, $2=%s, $5=%s\n", $1, $2, $5; next }. If this doesn't make some errors obvious, show the resulting output in the question.
    – Bodo
    Commented Mar 30, 2020 at 23:08

1 Answer 1

0

The problem with your script was that you were trying to use strings like 11:775852:T:C as keys in a map whose keys are of the format 775852.

I have omitted the first column from this processing since you mentioned in a comment that you don't think it's needed.

NR==FNR { map[$2]=$5 }
NR!=FNR { split($2, x, ":"); if(x[2] in map){ $2=map[x[2]]; print }}

I used split to obtain the relevant part of the string and added a conditional since until that substring is processed, there's no way to perform the lookup you need.

This appears to perform as requested:

[gnubeard@mothership: ~/dna]$ awk -f test.awk FS=',' marker-info FS=' ' output11.frq
 1  rs2980300 T C 0.1707 3444
 1  rs4245756 T C 0.07923 3496

Make sure the fields you have line up with the columns you think you have. If you'd like the output to be tab-delimited, you can set the OFS variable in the second half of the script, like so: NR!=FNR { OFS="\t"; split($2, x, ":"); if(x[2] in map){ $2=map[x[2]]; print }}

EDIT: I have changed the FS variable in the command to change the delimiter for output11.frq to whitespace. This will prevent fiddliness with number of tabs.

3
  • Thank you for getting back to me, I did run what you wrote above and this is what I got:
    – anamaria
    Commented Mar 31, 2020 at 2:26
  • I will put in my post the output, under EDIT
    – anamaria
    Commented Mar 31, 2020 at 2:26
  • This worked wonderfully! Thank you so much!
    – anamaria
    Commented Mar 31, 2020 at 3:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .