1

I have 3 files: list_file, file1, and file2. I want to extract entire lines from file1 and file2 based on a list_file pairwise and concatenate the results on output.

Namely, I need to extract only lines from file1 and file2 with names on 4th column matching names on first and second column of list_file (respectively), then concatenate both entire lines on output following the same paired order displayed on list file.

Names in column 1 of list_file are present in file1, and names in column 2 of list_file are present in file2.

list_file:

uth1.g20066 uth2.g18511
uth1.g3149  uth2.g22348
uth1.g20067 uth2.g18512
uth1.g20068 uth2.g18514
uth1.g3154  uth2.g22355

file1

ut1A    11256   13613   uth1.g20065
ut1A    25598   47989   uth1.g20066
ut1A    39912   40142   uth1.g3148
ut1A    40324   40617   uth1.g3149
ut1A    40699   41034   uth1.g3150

file2

ut1B    16951   39342   uth2.g18511
ut1B    31265   31495   uth2.g22347
ut1B    31677   31970   uth2.g22348
ut1B    32052   32387   uth2.g22349
ut1B    41596   46862   uth2.g18522

Desired output:

ut1A    25598   47989   uth1.g20066 ut1B    16951   39342   uth2.g18511
ut1A    40324   40617   uth1.g3149  ut1B    31677   31970   uth2.g22348

In order to perform this task I tried the below python code and it works, however it is clumsy (many loops) and it is very slow on large input files, so it would be great to make it more concise. It would also be interesting to have entirely new script as an alternative, perhaps with awk. Thanks.

data = open("list_file.txt")

data1 = open("file1.txt")
all_lines1 = data1.readlines() 

data2 = open("file2.txt")
all_lines2 = data2.readlines()

output = open("output.txt", "w")

for line in data:

    columns = line.split( )
    geneH1data = columns[0]
    geneH2data = columns[1]

    for line1 in all_lines1:

        columns1 = line1.split( )
        chr1 = columns1[0]
        start1 = int(columns1[1])
        end1 = int(columns1[2])
        geneH1data1 = columns1[3]

        for line2 in all_lines2:

            columns2 = line2.split( )
            chr2 = columns2[0]
            start2 = int(columns2[1])
            end2 = int(columns2[2])
            geneH2data2 = columns2[3]

            if geneH1data==geneH1data1 and geneH2data==geneH2data2:
                output.write(chr1 + " " + str(start1) + " " + str(end1) + " " + geneH1data + " " + chr2 + " " + str(start2) + " " + str(end2) + " " + geneH2data + '\n')

output.txt

ut1A 25598 47989 uth1.g20066 ut1B 16951 39342 uth2.g18511
ut1A 40324 40617 uth1.g3149 ut1B 31677 31970 uth2.g22348
4
  • What have you tried? Commented Dec 28, 2022 at 12:50
  • I tried 'join file1 file2' but it doesn't work with three files.
    – rseg
    Commented Dec 28, 2022 at 15:56
  • Then I tried using a python script looping the columns of each file with ‘for’, and ‘if’ to extract only the lines of file1 and file2 that match the condition. However this doesn’t work either.
    – rseg
    Commented Dec 28, 2022 at 16:15
  • 1
    I edited my question and provided more details.
    – rseg
    Commented Dec 28, 2022 at 22:48

1 Answer 1

0

Using GNU awk for ARGIND:

$ awk '
    ARGIND<3 { a[ARGIND,$4]=$0; next }
    ((1,$1) in a) && ((2,$2) in a) { print a[1,$1], a[2,$2] }
' file1 file2 list_file
ut1A    25598   47989   uth1.g20066 ut1B    16951   39342   uth2.g18511
ut1A    40324   40617   uth1.g3149 ut1B    31677   31970   uth2.g22348

If you don't have GNU awk just tweak it to:

$ awk '
    FNR==1 { argind++ }
    argind<3 { a[argind,$4]=$0; next }
    ((1,$1) in a) && ((2,$2) in a) { print a[1,$1], a[2,$2] }
' file1 file2 list_file
ut1A    25598   47989   uth1.g20066 ut1B    16951   39342   uth2.g18511
ut1A    40324   40617   uth1.g3149 ut1B    31677   31970   uth2.g22348

and then it'll work in any awk. If you want the output tab-separated instead of blank-separated just tweak it again:

$ awk '
    BEGIN { OFS="\t" }
    FNR==1 { argind++ }
    argind<3 { a[argind,$4]=$0; next }
    ((1,$1) in a) && ((2,$2) in a) { print a[1,$1], a[2,$2] }
' file1 file2 list_file
ut1A    25598   47989   uth1.g20066     ut1B    16951   39342   uth2.g18511
ut1A    40324   40617   uth1.g3149      ut1B    31677   31970   uth2.g22348
3
  • Hi Ed, thanks for your help. The codes you provided worked beautifully well. Please it would be great if you could describe the role of each portion of the code, so that I can modify it for future similar jobs. I am still learning.
    – rseg
    Commented Dec 29, 2022 at 7:12
  • 1
    @rseg you're welcome. Everything in the script is very common, basic awk and we can't explain that in every answer so please take a look at the awk man page, get the book Effective AWK Programming (5th Edition) by Arnold Robbins, and/or just google the constructs I used and then let me know if you still have any specific questions.
    – Ed Morton
    Commented Dec 29, 2022 at 12:25
  • 1
    That makes sense, and thanks for suggesting the book.
    – rseg
    Commented Dec 29, 2022 at 21:09

You must log in to answer this question.

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