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 !