2

I want to merge rows based on the value in column 1. My data look like (columns are separated by tabs):

OG  FC_AG_NICO  FC_AG_ZEA   FC_AG_BRAS  FC_MB_NICO  FC_MB_ZEA   FC_MB_BRAS  FC_TN_NICO  FC_TN_ZEA   FC_TN_BRAS  FC_SL_NICO  FC_SL_ZEA   FC_SL_BRAS  FC_SE_NICFC_SE_ZEA  FC_SE_BRAS 
OG0004400   -0.787302663    -0.710790578    0.663333543                                             
OG0004400                                                   -1.659046364    -1.019969932    0.588969542
OG0004400                                       -0.373838773    0.277055943 0.481626213         
OG0004400               -0.360799687    -0.0958126  0.056722264                                 
OG0004400                           -1.77626686 -0.971114297    0.707963822                     
OG0004402   -0.304209641    -0.259080399    0.44366888                                              
OG0004402                                                   0.442748804 0.042958499 -0.316412832
OG0004402                                       -0.274550145    0.1933262   0.374095809         
OG0004402               0.253000346 0.338511357 -0.121760564

So the rows that share the same OG number should be merged into a single row. Per column there is only a single value so there will be no doubles causing problems.

Multiple other posts were dealing with a similar issue and I have found this answer pretty useful and edited it a bit. But here all the values are just written directly after each other. However, it is very important that the column values stay in the same positions.

awk '{if(NR!=1){a[$1]=$2"\t"a[$1]} else print $0} END {n = asorti(a, b); for (n in b) {print b[n],a[b[n]]}}'

Can anyone help me editing the above code?

3
  • Is your file really like that? Do you have OG0004400 -0.787302663 -0.710790578 0.663333543 in the first line? Shouldn't that be the second line instead? Also, what defines a column? Do you have tabs, or is it multiple spaces? – terdon Apr 17 '20 at 16:40
  • Hi Terdon, I see that my first line got messed up, I will edit it! Thanks for this. The first line of my file are headers: {OG FC_AG_NICO FC_AG_ZEA} etc. The second line is than the OG identifier with values organised per column (with 15 columns). The columns are tab separated. – TUnix Apr 17 '20 at 16:45
  • Please add your desired output (no description) for that sample input to your question (no comment). – Cyrus Apr 17 '20 at 16:57
2
awk 'BEGIN{FS="\t"} NR==1{print; next} {a[$1]=$1; for(i=2; i<=NF; i++){if($i!="") {f[$1,i]=$i; if(i>last[$1]){last[$1]=i}}} } END{for(j in a){printf("%s", a[j]); for(k=2; k<=last[j]; k++){printf("%s%s", FS, f[j,k])} print ""}}' file
awk 'BEGIN{FS="\t"}          # use tab as field separator
     NR==1{print; next}      # print header
     {
       a[$1]=$1              # save first column in current row
       for(i=2; i<=NF; i++){ # loop with all columns but first
         if($i!=""){         # if column not empty
           f[$1,i]=$i        # save content to array
           if(i>last[$1]){
             last[$1]=i      # save number of last element in current row
           }
         }
       }
     }
     END{
       for(j in a){
         printf("%s", a[j])           # print first element
         for(k=2; k<=last[j]; k++){   # print second to last element
           printf("%s%s", FS, f[j,k])
         }
         print ""
       }
     }' file

Array a contains first column. Array f contains rows without first column. Array last contains position of last element in current row.

Not tested yet.

1
  • Thanks for this Cyrus. The nice thing of this piece of code is that also OG's without any values still remain in the file. – TUnix Apr 20 '20 at 6:48
2

Here's one way:

$ awk -F"\t" '{if(NR==1){ cols=NF; print; } else{for(i=2;i<=NF;i++){if(length($i)>0){data[$1][i]=$i}}}}END{for(id in data){ printf "%s",id; for(i=2;i<=cols;i++){printf "\t%s", data[id][i]} print ""}}' file 
OG  FC_AG_NICO  FC_AG_ZEA   FC_AG_BRAS  FC_MB_NICO  FC_MB_ZEA   FC_MB_BRAS  FC_TN_NICO  FC_TN_ZEA   FC_TN_BRAS  FC_SL_NICO  FC_SL_ZEA   FC_SL_BRAS  FC_SE_NICFC_SE_ZEA  FC_SE_BRAS 
OG0004400   -0.787302663    -0.710790578    0.663333543 -0.360799687    -0.0958126  0.056722264 -1.77626686 -0.971114297    0.707963822 -0.373838773    0.277055943 0.481626213 -1.659046364    -1.019969932
OG0004402   -0.304209641    -0.259080399    0.44366888  0.253000346 0.338511357 -0.121760564                -0.274550145    0.1933262   0.374095809 0.442748804 0.042958499

Or, a bit easier to read:

awk -F"\t" '{
                ## Print the headers and store the number of columns.
                if(NR==1){ 
                    cols=NF;
                    print; 
                } 
                else{
                    ## Iterate over all columns, starting from the 2nd.
                    for(i=2;i<=NF;i++){
                        ## If this one isn't empty, store it.
                        if(length($i)>0){
                            data[$1][i]=$i
                        }
                    }
                }
            }
           ## After reading everything, print.
            END{
                for(id in data){ 
                    printf "%s",id; 
                    for(i=2;i<=cols;i++){
                        printf "\t%s", data[id][i]
                    } 
                    print ""
                }
            }' file 

Note that this assumes that every ID (1st field) has a value for every column on one line and on one line only. If you can have columns that are empty for an ID, you will need a slightly different approach.

2
  • Thanks terdon! It works perfectly. OG's with empty fields simply stay blank. But OG's without any value are filtered out. – TUnix Apr 20 '20 at 6:50
  • 1
    @TUnix ah, I thought you would never have that since you said that "Per column there is only a single value so there will be no doubles causing problems". I took that to mean that there would always be a value. That's why I added the note at the end mentioning that I assume a value for every column. – terdon Apr 20 '20 at 8:32
1

Yet another awk.

I did wonder whether iterating over an associative array for (f in fields) would scramble the field output but, having run a few tests up to 20 fields wide it doesn't seem to.

Assuming that your headers are in line 1, data is sorted (as per the post) and that you have a mass of data you don't want in memory all at once

awk 'BEGIN{getline; split($0,out,"\t"); old=$1}
    old!=$1{for (o in out) printf "%s\t", out[o]; print""; delete out;old=$1}
    {split($0,tmp,"\t"); for (t in tmp) out[t]=(t==1)?tmp[t]:out[t]+tmp[t]}
    END{for (o in out) printf "%s\t", out[o];}' file

This input

head    c1  c2  c3
H1  -0.71       
H1      2   
H1          3
H2  11  12  
H2          13

Gives

head    c1      c2      c3
H1      -0.71   2       3
H2      11      12      13

Walkthrough

Grab the first line and remember the first field in old

awk 'BEGIN{getline; split($0,out,"\t"); old=$1}

If the first field in the next record differs from the last (new header) then the last aggregation is complete, so print it out, empty the aggregating array out and make a note that you are in new set old=$1

    old!=$1{for (o in out) printf "%s\t", out[o]; print""; delete out;old=$1}

Split $0 into an array tmp, iterate over the array adding the tmp value to out unless it is a header column when you just take the value

    {split($0,tmp,"\t"); for (t in tmp) out[t]=(t==1)?tmp[t]:out[t]+tmp[t]}

Clear out the last set of records held in out at the END

    END{for (o in out) printf "%s\t", out[o];}' file
2
  • Thanks for this bu5hman! Strangely the order of the columns get shifted. All OG IDs are now below "FC_SE_NICO" and one of the values are below OG. – TUnix Apr 20 '20 at 6:51
  • 1
    If you posted a file with the tab delimiters, or using a , replacement as a sample, could probably fix that as it was a bit of a thumbsuck without.....But as long as you have a solution from someone that works. Job done – bu5hman Apr 20 '20 at 14:34

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.