2

I have 'FileA':

10      10011300        10011301        T       C
10      10012494        10012495        G       A
10      10028691        10028692        A       T
10      10093496        10093497        G       A
10      10102457        10102458        C       T
10      10103252        10103253        G       C
10      10122271        10122272        T       C
10      10128778        10128779        T       C
10      10130299        10130300        C       A
10      10148307        10148308        G       A

and I have files 1-22:

1       10177   rs367896724
1       10235   rs540431307
1       10352   rs555500075
1       10505   rs548419688
1       10506   rs568405545
1       10511   rs534229142
1       10539   rs537182016
1       10542   rs572818783
1       10579   rs538322974
1       10616   rs376342519

As you might be able to tell, I am dealing with genetic data. What I want to do is every time columns 1 and 3 from FileA match columns 1 and 2 from files 1-22, columns 1, 2 and 3 from files 1-22 (or columns 1 and 3 from FileA and column 3 from files 1-22) are printed into a text file with the number shared between FileA and files 1-22 in column 1 as the text file's name.

So far, I'm stuck on sorting the files to be able to invoke join. FileA keeps giving me an error, even though I am trying to sort it exactly the same way I was successfully able to sort files 1-22.

Essentially, what I am hoping for is a tutorial to do exactly the thing I am trying to do here. I have checked with multiple different sources and have attempted this several times to no avail.

Commands I have used:

sort -k 1,1 FileA.txt

join -j 1 File1.txt FileA.txt > output.txt

EDIT:

Here's a sample of the desired output

18      3320671 rs375411568
18      3320673 rs550898405
18      3320676 rs73366565
18      3320704 rs536519819
18      3320720 rs118037107
18      3320736 rs566910986
18      3320755 rs567626849
18      3320786 rs183777311
18      3320860 rs528977928
18      3320887 rs577743595
18      3320897 rs530122744

It looks very similar to the earlier example of files 1-22, and that's because it is essentially the same thing. I want for matches between columns 1 and 3 from FileA with columns 1 and 2 of the file x (x being any one file of a series of files numbered 1-22; in this example, 18), a line corresponding with the matching line of file x appears and is saved in output.txt.

  • Would you be able to provide some sample output? It helps understand what you're trying to accomplish. – Zachary Brady Aug 22 '18 at 19:35
  • Ok, hold on while I edit this post – CelineDion Aug 22 '18 at 19:38
  • Posted an example – CelineDion Aug 22 '18 at 20:14
  • We need to see some sample input that matches that sample output. We need that so we can test our solutions. – glenn jackman Aug 22 '18 at 20:43
  • a sample input would be the second block of code I have in OP. Filename could be x_out. If it's easier, you can make the code such that it does this for one file at a time, and then I could submit it as multiple jobs to the HPC, each handling a different file. – CelineDion Aug 22 '18 at 20:55
0

Okay, I actually forgot I asked this question but someone just upvoted this post and I have since figured it out so I'll go ahead and post the answer.

For the first snippet of output I listed above,

10      10011300        10011301        T       C
10      10012494        10012495        G       A
10      10028691        10028692        A       T
10      10093496        10093497        G       A
10      10102457        10102458        C       T
10      10103252        10103253        G       C
10      10122271        10122272        T       C
10      10128778        10128779        T       C
10      10130299        10130300        C       A
10      10148307        10148308        G       A

all I needed to do was use the following awk command,

cat [inputfile.txt] | awk '{print $1"_"$3"\t"$4"\t"$5}' | sort -k1,1 > outputfileA.txt

The output would look like this and all rows would be sorted by the first column:

10_10011301        T       C
10_10012495        G       A
10_10028692        A       T
10_10093497        G       A

I would also do the same thing for the second snippet of code,

1       10177   rs367896724
1       10235   rs540431307
1       10352   rs555500075
1       10505   rs548419688
1       10506   rs568405545

and use a similar awk command to generate another file:

cat [inputfile.txt] | awk '{print $1"_"$2"\t"$3}' | sort -k1,1 > outputfileB.txt

Resulting in:

1_10177   rs367896724
1_10235   rs540431307
1_10352   rs555500075
1_10505   rs548419688
1_10506   rs568405545

Since both of these output files have one column in common, and those columns are sorted identically, we could then invoke the join command:

join -1 1 -2 2 outputfileA.txt outputfileB.txt | tr ' ' '\t' > outputfileC.txt

tr ' ' '\t' translates all whitespaces into tabs in the output. All lines with identical matches between the first column of the first file and the first column of the second file are written into the output file, which might look something like this:

1_101850899     A       C       rs138958619
1_101856556     T       C       rs191666242
1_101867058     C       T       rs188447998
1_101874381     A       C       rs143747209
1_101877269     G       A       rs186149522
1_101878704     C       A       rs192815769
1_101885657     G       T       rs150829467
1_101891797     T       G       rs141886478
1_101893793     T       A       rs182950692
1_101897192     T       C       rs189527356

I hope I explained that well. Let me know if I need to clarify anything.

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.