0

I have two tab separated files (FileA.tsv and FileB.tsv).

FileA.tsv

id graph circle several columns... length
196-0 196 0 ---- 12874
195-1 195 1 ---- 12874
56-0 56 0 ---- 3349
115-1 115 1 ---- 5297

File A has hundreds of lines and 12 columns, not all depicted here. Each value of 2 and 3 is not unique, but their specific combination is. As such, event_id is an unique identifier, made of the values in 2 and 3 concatenated.

FileB.tsv

Column 1 Column 2 Column 3 several columns... Column 16
195 1 coverage ---- CTTGCTTGAGCTGCTCTGCAA...
196 0 coverage ---- TTCTAAAGTATAAAGCCTGTC...
196 9 coverage --- TTCTAAAGTATAAAGCCTGTC ...
196 11 coverage --- ACATTTAAAGAATTGCTTAAG...

FileB does not have headers.

Columns 2 and 3 match some of the columns 1 and 2 of file A. Once again the values in columns 1 and 2 are not unique but their specific combination is. All the rows appearing in FileB always have matches in FileA, but the opposite is not true.

Using awk, I would like to check if each row of FileA has $2 and $3 matching $1 and $2 of FileB, and if so,print the full FileA row and add the corresponding $16 value of FIleB to the end of that row. If not, print the FIleA row as it is.

Expected Output (FileC):

id graph circle several columns length Column 16
196-0 196 0 ---- 12874 TTCTAAAGTATAAAGCCTGTC...
195-1 195 1 ---- 12874 CTTGCTTGAGCTGCTCTGCAA...
56-0 56 0 ---- 3349 ----
115-1 115 1 ---- 5297 ----

So far, I have:

awk -F "\t" 'NR==FNR {a[$1,$2]=($16); next} ($2,$3) in a {print $0, a[$16]}' FileB.tsv FileA.tsv > FileC.tsv

This code does give me only the matched rows, however, it does not append $16 to the end of the matching rows:

Empty Empty Empty Empty Empty
196-0 196 0 ---- 12874
195-1 195 1 ---- 12874

If I try adding the If-Else statement:

awk -F "\t" 'NR==FNR {a[$1,$2]=($16); next} { if (($2,$3) in a) {print $0, a[$16]} else {print $0}}' FileB.tsv FileA.tsv > FileC.tsv

So as to keep the headers and non-matching rows of FileA, the output is simply FileA.

I'm new to awk, however I have researched a lot and found many examples of manipulation similar to this being done, and my code seems very similar to other examples I've seen.

However, I have yet to find an example in which there are two corresponding key rows between files, they are not in the same position AND the non-matching columns are also kept.

This is being run on several directories, each with their own set of FileA and FileB, using a Bash loop. No problems in that regard, all directories have their own outputting FileC, however wrong its contents may be:

set -euo pipefail
IFS=$'\n\t'
for D in ~/Path/to/directories/with/tables/*; do
    if [ -d "${D}" ]; then
        cd "$D"
        awk -F "\t" 'NR==FNR {a[$1,$2]=($16); next} { if (($2,$3) in a) {print $0, a[$16]} else {print $0}}' *_FileB.tsv  *_FileA.tsv > "${D}".FileC.tsv
    fi
done ```

Any help or correction will be greatly appreciated.
0

2 Answers 2

1

Your awk script is a good start but there's a few fairly small things you need to fix:

  1. you need to set the output field separator (OFS) to be a tab, same as FS.
  2. you need to print the new column header
  3. you need to print a[$2,$3], not a[$16] on matching rows
  4. to keep the non-matching rows, you need to print them too, preferably with an empty field appended so that all output rows have the same number of columns.

For example:

$ awk -F "\t" -v OFS='\t' '
  NR == FNR { a[$1,$2] = $5; next };

  FNR == 1     { c = "column 16" };
  ($2,$3) in a { c = a[$2,$3] };

  {
    print $0, c;
    c = ""
  }' FileB.tsv  FileA.tsv 
id      graph   circle  several columns...      length  column 16
196-0   196     0       ----    12874   TTCTAAAGTATAAAGCCTGTC...
195-1   195     1       ----    12874   CTTGCTTGAGCTGCTCTGCAA...
56-0    56      0       ----    3349
115-1   115     1       ----    5297

I've used a[$1,$2]=$5 here because your FileB sample data only has 5 fields. Change that to $16 for your real data.

This uses variable c to hold the value of the column to be appended. It will contain either the new column name, an empty string, or the value of the column to be appended in matching rows. It is reset to the empty string after each output row is printed.

BTW, while the empty fields are normally invisible, you can verify that empty fields are being appended to the non-matching rows by piping the output to cat -T - you'll see ^I (a tab) at the end of those lines.


Alternate version that gets the column name from the first line of FileB.tsv instead of hard-coding it:

$ awk -F "\t" -v OFS='\t' '
  NR == 1      { c = $5 ; next };
  NR == FNR    { a[$1,$2] = $5; next };
  ($2,$3) in a { c = a[$2,$3] };

  { print $0, c; c = "" }' FileB.tsv  FileA.tsv 
3
  • Thank you so much for your answer! I actually noticed your points 1 and 3 soon after I posted and got it working mostly as needed. I can see now I had mistakenly thought -F was already setting both FS and OFS. My workaround then was actually to do print $0"\t"a[$2,$3] instead of print $0,a[$2,$3] . I'll try your code and let you know!
    – JAL
    Commented Aug 18, 2023 at 13:46
  • Back to say it works great! I really love how you used c here. Is there any advantage/disadvantage to using this method as opposed to an if/else loop?
    – JAL
    Commented Aug 18, 2023 at 15:20
  • not really, not in a short script like this. it avoids repeating the print statement in multiple if/then/else clauses. and IMO it's clearer and easier to read & understand. On another note, you can make the script copy the column name from FileB.txt rather than hard-coding it by deleting the FNR==1 line and inserting NR == 1 { c = $5; next } (or $16) before the NR==FNR line
    – cas
    Commented Aug 18, 2023 at 23:36
0

Using any awk:

$ cat tst.awk
BEGIN {
    FS = OFS = "\t"
}
NR == FNR {
    val = $NF
    if ( FNR == 1 ) {
        hdr = val
    }
    else {
        map[$1 FS $2] = val
    }
    next
}
{
    if ( FNR == 1 ) {
        val = hdr
    }
    else {
        key = $2 FS $3
        val = (key in map ? map[key] : "----")
    }
    print $0, val
}

$ awk -f tst.awk FileB.tsv FileA.tsv
id      graph   circle  several columns...      length  Column16
196-0   196     0       ----    12874   TTCTAAAGTATAAAGCCTGTC...
195-1   195     1       ----    12874   CTTGCTTGAGCTGCTCTGCAA...
56-0    56      0       ----    3349    ----
115-1   115     1       ----    5297    ----

Change $NF to $16 if $16 isn't the last column in the input.

10
  • Hello! Thank you for your time and your answer. I'm not entirely sure why (I'm very new to awk), but this script adds a column 16 match to the header row - I imagine it's because fileB has no headers, but I'm not sure. Would you be so kind as to add a description/comment of what your script does?
    – JAL
    Commented Aug 18, 2023 at 15:06
  • not sure why you say "but this script adds a column 16 match to the header row" - your expected output shows a Column16 added to the header row exactly as my script does - is that not what you wanted?
    – Ed Morton
    Commented Aug 18, 2023 at 20:15
  • I actually think my script is very clear and simple, just using very basic constructs common to most algol-based languages and anything else just being fundamental awk constructs described well in the man pages so I'm not sure what I could say about it. Let me know if you have any specific questions about any parts of it, after a glance at the man pages of course, and I'll be happy to answer them though.
    – Ed Morton
    Commented Aug 18, 2023 at 20:18
  • I apologize if my question wasn't very clear. The second file, FileB, has no headers, as it says in the body of the text. I put "column1, column2," etc. as headers for that table because stack's markdown table formatting requires headers. I realize I could have made this clearer by writing "empty" for all headers as I did for FIleC instead. Since there are no headers, it adds a sequence to the header row - so it reads "id | graph | circle | several columns... | length | TTCTAAAGTATAAAGCCTGTC...
    – JAL
    Commented Aug 22, 2023 at 13:35
  • Just provide your sample input/output as text (inside 3-ticks code delimiters), it doesn't need to be a markdown table.
    – Ed Morton
    Commented Aug 22, 2023 at 13:37

You must log in to answer this question.

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