CODEWITHSUNDEEP
for-loopif-statementawk
Ashi
Ashi

Reputation: 389

Compare Two Files with duplicate input value

I am having the following two files

BC.txt

"PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
"PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;

PB.txt

c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10";
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10";
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10";
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10";

I am trying to compare Col1 of BC.txt with Col12 of PB.txt and print the matches next to each other. For same value in col1 of BC.txt has different value in col2 and Col3. So while comparing I am getting output for only one entry of BC.txt. But I want for all. 

    awk 'BEGIN {OFS=FS} NR==FNR {a[$1]=($2" "$3);next} $12 in a {print $0,a[$12]}' BC.txt PB.txt

Expected Output

c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;

I want to compare all the entries of BC.txt with that of PB.txt; but since its the same value my code is not working.

Upvotes: 1

Views: 85

Answers (3)

dr-who
dr-who

Reputation: 189

Could you use join to do this? (if the columns were sorted, or <() through sort.

$ join BC.txt <(awk '{print $12,$0}' PB.txt) | cut -d' ' -f 4-
c4 PB tr 41258945 41270445 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB Ex 41258945 41259026 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB Ex 41259626 41259754 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB Ex 41262664 41262814 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB tr 41258945 41270445 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB Ex 41258945 41259026 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB Ex 41259626 41259754 . + . g_i "PB.50262"; t_i "PB.50262.10";
c4 PB Ex 41262664 41262814 . + . g_i "PB.50262"; t_i "PB.50262.10";

and cut/awk the columns you want out of the join?

Upvotes: 1

Ed Morton
Ed Morton

Reputation: 204458

If you don't care about output line order compared to the expected output in your question then read BC.txt into memory as it's briefer:

$ cat tst.awk
NR==FNR {
    map[$1,++cnt[$1]] = $2 OFS $3
    next
}
{
    for (c=1; c<=cnt[$12]; c++) {
        print $0, map[$12,c]
    }
}

$ awk -f tst.awk BC.txt PB.txt
c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;

but if you do care then:

$ cat tst.awk
NR==FNR {
    map[$12,++cnt[$12]] = $0
    next
}
{
    for (c=1; c<=cnt[$1]; c++) {
        print map[$1,c], $2, $3
    }
}

$ awk -f tst.awk PB.txt BC.txt
c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AGCGGCCT; BC=TTTCAGCGCCGA;
c4  PB  tr  41258945    41270445    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41258945    41259026    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41259626    41259754    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;
c4  PB  Ex  41262664    41262814    .   +   .   g_i "PB.50262"; t_i "PB.50262.10"; UMI=AAGCGGCC; BC=TTTCAGCGCCGA;

Upvotes: 1

RavinderSingh13
RavinderSingh13

Reputation: 133730

Could you please try following(tested with your provided samples only).

awk '
FNR==NR{
  a[++count]=$0
  b[count]=$12
  next
}
{
  for(i=1;i<=count;i++){
    split(a[i],array," ")
    if($1==array[12]){
       print a[i],$2,$3
    }
  }
}'  PB.txt BC.txt

Explanation: Adding explanation for above code now.

awk '                         ##Starting awk program here.
FNR==NR{                      ##Checking condition FNR==NR which will be TRUE when PB.txt is being read.
  a[++count]=$0               ##Creating an array named a whose index is variable count with incrment value of 1 and value is current line.
  b[count]=$12                ##Creating an array named b whose  index is variabe count and value if 12th column.
  next                        ##next will skip all further statements from here.
}
{
  for(i=1;i<=count;i++){      ##Starting a for loop from here from i=1 to till value of count.
    split(a[i],array," ")     ##Splitting value of a[i] into array named array whose delimiter is space.
    if($1==array[12]){        ##Checking condition if $1 is equal to array[12] then do following.
       print a[i],$2,$3       ##Printing array a value along with 2nd and 3rd column value.
    }
  }
}'  PB.txt BC.txt             ##Mentioning Input_files names here.

Upvotes: 0

Related Questions