2

So, I have a file with insertions hits with some features marks with following aspect (chr, start, end, chr, star, end, number of overlapped base pairs:

    chr1    69744110    69793325    .   -1  -1  0
    chr1    82791976    82831348    chr1    82792114    82792615    501
    chr1    82791976    82831348    chr1    82816285    82817077    792
    chr1    82791976    82831348    chr1    82828015    82829891    1876
    chr1    88599340    88658398    .   -1  -1  0
    chr1    137772945   137830035   .   -1  -1  0
    chr1    137875312   137920590   .   -1  -1  0
    chr1    193433080   193446861   .   -1  -1  0
    chr10   26483800    26501370    chr10   26484794    26485295    501
    chr10   68069913    68089436    .   -1  -1  0
    chr10   95098349    95113967    .   -1  -1  0
    chr10   97310211    97335589    .   -1  -1  0
    chr10   111083097   111118237   chr10   111088928   111090274   1346
    chr10   117904141   117947090   chr10   117905334   117906320   986
    chr10   117904141   117947090   chr10   117918966   117919852   886
    chr10   117904141   117947090   chr10   117926867   117927368   501
    chr11   11521339    11587607    chr11   11523970    11524747    777
    chr11   11521339    11587607    chr11   11555497    11559868    4371
    chr11   11521339    11587607    chr11   11560639    11562128    1489
    chr11   11521339    11587607    chr11   11564617    11565370    753

So what I need is to concatenate the values in column 5 (column5/column5...), column 6 (column6/column6...) and column 7(column/column7)... IF I have a match in the first 3 columns. I would also like to keep column 4, but it is okay if I miss it.

The output should look like:

    chr1    69744110    69793325    .   -1  -1  0
    chr1 82791976 82791976 chr1 82792114/82816285/82828015 82792615/82817077/82829891 501/792/1876
    chr1    88599340    88658398    .   -1  -1  0
    chr1    137772945   137830035   .   -1  -1  0
    chr1    137875312   137920590   .   -1  -1  0
    chr1    193433080   193446861   .   -1  -1  0
    chr10   26483800    26501370    chr10   26484794    26485295    501            (...)
    chr10   117904141   117947090   chr10 117905334/117918966/117926867 117906320/117919852/117927368   986/886/501
    (...)

I have been in several trials and the best I could do was this:

    awk '{ k=$1 FS $2 FS $3;  a[k]=(k in a)? a[k]"/"$5 : $5 }
 END{ for(i in a) { 
          split(i,b,FS); b[5]=a[i]"\t"b[5]; r=""; 
          for(j=1;j<=NF;j++) { 
              r=(r!="")? r"\t"b[j] : b[j] 
          } 
          print r} 
    }' input.bed > output.bed

But with this I am missing values and I am not able to concatenate more than one column.

Can you help me please?

EDIT:

new attempt:

    awk -F'\t' -v OFS='\t' '{
        if ($2 in a) {
            a[$2] = a[$2]";"$5;
            b[$2] = b[$2]";"$6;
        } else {
            a[$2] = $5;
            b[$2] = $6;
       }
    }
    END { for (i in a) print i, a[i], b[i] }' input.bed > output.bed

But I continue to lose the fields that are not being evaluated.

1
  • my GNU does not support it. It crashes in the '4g' statement. Meanwhile I tried this: awk -F'\t' -v OFS='\t' '{ if ($2 in a) { a[$2] = a[$2]";"$5; b[$2] = b[$2]";"$6; } else { a[$2] = $5; b[$2] = $6; } } END { for (i in a) print i, a[i], b[i] }' input.bed > output.bed But I continue to lose the fields that are not being evaluated – José Dias Dec 12 '17 at 14:59
0

Question:

Concatenate the values in column 5,6 and 7 IF I have a match in the first 3 columns

Answer:

perl -lane 'if($.==1){@a=@F;next} if($F[0]eq$a[0]&&$F[1]eq$a[1]&&$F[2]eq$a[2]){$a[4].="/$F[4]";$a[5].="/$F[5]";$a[6].="/$F[6]";}else{for($i=0;$i<@a;$i++){printf "\t%s",$a[$i]};print"";@a=@F}END{for($i=0;$i<@a;$i++){printf "\t%s",$a[$i]};print""}' input.bed

Output:

    chr1    69744110        69793325        .       -1      -1      0
    chr1    82791976        82831348        chr1    82792114/82816285/82828015      82792615/82817077/82829891      501/792/1876
    chr1    88599340        88658398        .       -1      -1      0
    chr1    137772945       137830035       .       -1      -1      0
    chr1    137875312       137920590       .       -1      -1      0
    chr1    193433080       193446861       .       -1      -1      0
    chr10   26483800        26501370        chr10   26484794        26485295        501
    chr10   68069913        68089436        .       -1      -1      0
    chr10   95098349        95113967        .       -1      -1      0
    chr10   97310211        97335589        .       -1      -1      0
    chr10   111083097       111118237       chr10   111088928       111090274       1346
    chr10   117904141       117947090       chr10   117905334/117918966/117926867   117906320/117919852/117927368   986/886/501
    chr11   11521339        11587607        chr11   11523970/11555497/11560639/11564617     11524747/11559868/11562128/11565370     777/4371/1489/753

Note:

There might be a shorter or more elegant solution

1
  • Thanks. This works perfectly! If I want to the same but combining the column, do you have any suggestion? So, it would be 82792114-82792615; 82816285-82817077; 82828015-82829891 when having multiple hits. – José Dias Dec 12 '17 at 21:04
1

With awk. Unfortunately, awk does not have a builtin array join function, but the gawk online manual has an example of how to write one.

If this is in the file aggregate.awk (I'm assuming the input file is tab-separated)

BEGIN {
    FS = OFS = "\t"
}

# ref https://www.gnu.org/software/gawk/manual/html_node/Join-Function.html#Join-Function
function join(array, start, end, sep,    result, i)
{
    if (sep == "")
        sep = " "
    else if (sep == SUBSEP) # magic value
        sep = ""
    result = array[start]
    for (i = start + 1; i <= end; i++)
        result = result sep array[i]
    return result
}

function print_record() {
    last_line[5] = join(col5, 1, n, "/")
    last_line[6] = join(col6, 1, n, "/")
    last_line[7] = join(col7, 1, n, "/")
    print join(last_line, 1, NF, OFS)
}

{
    key = $1 OFS $2 OFS $3
}

key != prev_key {
    if (n > 0) {
        print_record()
    }
    delete col5
    delete col6
    delete col7
    n = 0
}

{
    n++
    col5[n] = $5
    col6[n] = $6
    col7[n] = $7
    prev_key = key
    split($0, last_line)
}

END {print_record()}

Then we have:

$ awk -f aggregate.awk input.bed
chr1    69744110        69793325        .       -1      -1      0
chr1    82791976        82831348        chr1    82792114/82816285/82828015      82792615/82817077/82829891      501/792/1876
chr1    88599340        88658398        .       -1      -1      0
chr1    137772945       137830035       .       -1      -1      0
chr1    137875312       137920590       .       -1      -1      0
chr1    193433080       193446861       .       -1      -1      0
chr10   26483800        26501370        chr10   26484794        26485295        501
chr10   68069913        68089436        .       -1      -1      0
chr10   95098349        95113967        .       -1      -1      0
chr10   97310211        97335589        .       -1      -1      0
chr10   111083097       111118237       chr10   111088928       111090274       1346
chr10   117904141       117947090       chr10   117905334/117918966/117926867   117906320/117919852/117927368   986/886/501
chr11   11521339        11587607        chr11   11523970/11555497/11560639/11564617     11524747/11559868/11562128/11565370     777/4371/1489/753

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.