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
    Commented Apr 17, 2020 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
    Commented Apr 17, 2020 at 16:45
  • Please add your desired output (no description) for that sample input to your question (no comment).
    – Cyrus
    Commented Apr 17, 2020 at 16:57

3 Answers 3

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
    Commented Apr 20, 2020 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
    Commented Apr 20, 2020 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
    Commented Apr 20, 2020 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
    Commented Apr 20, 2020 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
    Commented Apr 20, 2020 at 14:34

You must log in to answer this question.

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