How can I combine information of three files to create a fourth one?

Question

I have three csv files, each of them containing the information I need to get a fourth one. I'm describing toy files but the actual ones contain much more columns and rows, still on the same models.

File 1 is the raw data file. One letter is given for each 'rs' in 'Individual'. For example. Individual0 has an 'A' in rs009, a 'T' in rs888,etc. :

        Individual0 Individual1 Individual2 Individual3
rs009   A       T       A       A
rs888   T       T       T       T
rs103   C       C       C       C
rs591   A       A       A       A
rs112   A       A       A       A
rs004   C       C       C       C

File 2 clusters the 'rs' variables in 'Groups'. Each of these "Groups' is a combination of succesive 'rs' variables, according to the order in File 1. For example, rs009 and rs103 are clustered in a variable named "Group0". There is no possibility of having a 'Group' that would overlap to another. When "Group0" ends, "Group1" starts, and so on. Importantly, some 'rs' variables are filtered out at this step (ie rs888):

Group0 rs009 rs103
Group1 rs591 rs112 rs004

File 3 is the result file in which each 'Group' variable gets a number assigned for each "individual'. For example Individual2 has a 3 in his Group0 and a 2 in his Group1:

        Individual0 Individual1 Individual2 Individual3
Group0  1       3       3       3
Group1  3       3       2       3

The file that I'd like to get is similar to the File 1 but in which all 'rs' variables that would not be in a 'Group' = 3 for a given 'Individual' in File 3 would be set as 0, while the others would keep their letter. For example, rs009 belongs to the Group0 (cf File 2), then File 3 tells that the Group0 for Individual0 has a '1', meaning that the letter mentioned in File 1 should be changed to 0. On the contrary, for Individual1, File 3 tells that Group0 has a '3', so the letter in File1 should be kept (ie 'T'). The 'rs' variables that were filtered out would get the same number as the previous one in the list (ie rs888 has the same 'Group' assignment as rs009) :

        Individual0 Individual1 Individual2 Individual3
rs009   0       T       A       A
rs888   0       T       T       T
rs103   0       C       C       C
rs591   A       A       0       A
rs112   A       A       0       A
rs004   C       C       0       C

In other words, I need to :

1) take the File 1

2) check in which 'Group' each 'rs' belongs to in File 2. If an 'rs' is not in File2, then it belongs to the same "Group' as the previous 'rs', according to the order in File1.

3) get the value assigned for each 'Group' in each "Individual' in File 3

4) if the latter value is '3' then keep the letter (A/C/G/T) assigned for each "individual' in File 1, otherwise change it to '0'

I tried several options with awk but my knowledge is rather limited so I could'nt figure out the way to get what I need in the end. It's my first post so I hope that enough information is provided. I can run Perl scritps, but not write it I'm afraid. Thanks in advance for your help !

Answer 1

Well, this is a hack, but anyway:

Let's use file3 and file2 and resolve the groups to a intermediate file resolved with something like:

awk '
    FNR == NR {
        group = $1
        $1 = ""
        groups[group] = $0
    }
    FNR != NR {
        for (i = 2; i <= NF; i++) {
            print $i" "groups[$1]
        }
    }
' <(tail -n+2 file3) file2 > resolved

to:

$ cat resolved
rs009  1 3 3 3
rs103  1 3 3 3
rs591  3 3 2 3
rs112  3 3 2 3
rs004  3 3 2 3

and then use resolved to combine it with file1 with:

awk '
    FNR == NR {
        group = $1;
        for (i = 2; i <= NF; i++) {
            groups[group][i] = $i
        }
    }
    FNR != NR {
        if ($1 in groups) {
            group = $1
        }
        for (i = 2; i <= NF; i++) {
            if (groups[group][i] != 3) {
                $i = 0
            }
        }
        print
    }

' resolved <(tail -n+2 file1)

which produces:

rs009 0 T A A
rs888 0 T T T
rs103 0 C C C
rs591 A A 0 A
rs112 A A 0 A
rs004 C C 0 C

Utilizing column and adding back the header and we are fine.