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 !

  • Can you use perl? This would be extremely easy to do in perl.
    – Bichoy
    May 24, 2015 at 19:02
  • I think I sort-of understand your objective, but I encourage you to edit your question in an attempt to make it clearer.  For one thing, please define “variable”.  At one point, it looks like you’re referring to each A, T, and C (and G?) as a variable; but then you refer to rs002 as a variable.  Next, the first row of File 2, and the first row of File 3 (not counting the headers), is Window0 — and yet, I suspect that that’s not what you’re talking about when you say “a window = 0”.  … (Cont’d) May 25, 2015 at 4:18
  • (Cont’d) …  (Would it help us to know what you mean by “window”? — because it’s a commonly used word, but used with an uncommon meaning.  Can you substitute another word that might make more sense to us?  Can you clarify your statement “File 3 is the file in which each window gets a number assigned” with the fact that you appear to be assigning four numbers to each window?)  Finally, is File 2 in order by rs?  (And, if these questions prompt you to think of anything else relevant that you may have left out, please provide it.)  Do not respond to me in a comment; edit your question. May 25, 2015 at 4:18
  • Thank you all to take a look at it. I made some changes that I hope would make my description clearer. If there is an "extremely easy" solution in Perl I'm happy with it, but part of running a script I wouldn't know how to do it. I'm a biologist and my informatic skills are limited (to say the least...).
    – nbrucato
    May 25, 2015 at 8:03

1 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


$ 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

' 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.