awk to consolidate large tabular file?

Question

I have a file with 21 tabular fields in columns. Tabs 14 and 15 are sets of data which are repeated several times for variables in tab 10(up to ":") and tab 11 has numeric descriptive data for tab 10 .

Here's an example of the input:

399 3   0   0   0   0   0   0   -   chromosome_1_Contig0.1980:10701-11103   402 0   402 gi|952977790|ref|NM_001317128.1|    849 447 849 1   402 0   447
281 0   0   0   0   0   0   0   -   chromosome_1_Contig0.1980:11209-11490   281 0   281 gi|952977790|ref|NM_001317128.1|    849 166 447 1   281 0   166
166 0   0   0   0   0   0   0   -   chromosome_1_Contig0.1980:11588-11754   166 0   166 gi|952977790|ref|NM_001317128.1|    849 0   166 1   166 0   0
51  0   0   0   0   0   0   0   +   chromosome_1_Contig0.3916:1547-1598 51  0   51  gi|733214878|ref|NM_001303082.1|    708 0   51  1   51  0   0
132 0   0   0   0   0   0   0   +   chromosome_1_Contig0.3916:3201-3333 132 0   132 gi|733214878|ref|NM_001303082.1|    708 282 414 1   132 0   282
294 0   0   0   0   0   0   0   +   chromosome_1_Contig0.3916:3412-3706 294 0   294 gi|733214878|ref|NM_001303082.1|    708 414 708 1   294 0   414
103 4   0   0   0   0   0   0   +   chromosome_1_unplaced_Contig0.3951:379-486  107 0   107 gi|526117967|ref|NM_001281232.1|    1518    1236    1343    1   107 0   1236
212 1   0   0   0   0   0   0   -   chromosome_1_unplaced_Contig0.12366:214-427 213 0   213 gi|526117831|ref|NM_001281196.1|    1025    738 951 1   213 0   738
178 2   0   0   0   0   0   0   -   chromosome_1_unplaced_Contig0.12366:633-813 180 0   180 gi|526117831|ref|NM_001281196.1|    1025    558 738 1   180 0   558
243 1   0   0   0   0   0   0   -   chromosome_1_unplaced_Contig0.12366:909-1153    244 0   244 gi|526117831|ref|NM_001281196.1|    1025    314 558 1   244 0   314
313 1   0   0   0   0   0   0   -   chromosome_1_unplaced_Contig0.12504:1668-1887   314 0   314 gi|526117831|ref|NM_001281196.1|    1025    0   314 1   314 0   0

I would like to get a new summarized tabular file from this in which;

for lines where the values in tabs 10 up to ":" and 14 are the same in a new line, tab 11 is summed for that combination. I would like to keep the lines in which these combinations appear only once. This gives me 3 new summarized tabs. Then I would like to include the previous tab 15 and in a new tab the difference between the new tab 3 and the old tab 15. The output should look like this:

example output:

old_tab_10  old_tab_14  sumof_old_tab11 old_tab15   (old_tab15)-(sumof_old_tab11)
chromosome_1_Contig0.1980   gi|952977790|ref|NM_001317128.1|    849 849 0
chromosome_1_Contig0.3916   gi|733214878|ref|NM_001303082.1|    477 708 231
chromosome_1_unplaced_Contig0.3951  gi|526117967|ref|NM_001281232.1|    107 1518    1411
chromosome_1_unplaced_Contig0.12366 gi|526117831|ref|NM_001281196.1|    637 1025    388
chromosome_1_unplaced_Contig0.12504 gi|526117831|ref|NM_001281196.1|    314 1025    711

I started messing around with something on the lines of

awk '{S[$14]+=$11;N[$14]+} END{for(i in S){print i, N[i]}}' 

then realized this is way out of my capabilities, I'm not even sure how to separate the fields for both tabs and ":" and if thats a good idea or if it would be better to use a different approach to separate the ":".

Answer 1

You can use split to extract the two parts of field 10 into an array (here called arr10) like this:

split($10, arr10, ":")

Then you can build an index out of a combination of the first element of that array and the whole of element 14. Using that index, you can build two new arrays, e.g. sum_of_11 and old_15:

sum_of_11[arr10[1]"\t"$14] += $11 # sum of all rows that have this index
old_15[arr10[1]"\t"$14] = $15     # just the value in the single most recent row

Putting it together (and setting OFS = "\t"):

awk '{ split($10, arr10, ":");
       sum_of_11[arr10[1]"\t"$14] += $11;
       old_15[arr10[1]"\t"$14] = $15
     } END {
       OFS = "\t";
       for (i in sum_of_11) {
         print i, sum_of_11[i], old_15[i], old_15[i] - sum_of_11[i]
       }
     }' file

Result:

chromosome_1_Contig0.3916   gi|733214878|ref|NM_001303082.1|    477 708 231
chromosome_1_unplaced_Contig0.12366 gi|526117831|ref|NM_001281196.1|    637 1025    388
chromosome_1_unplaced_Contig0.3951  gi|526117967|ref|NM_001281232.1|    107 1518    1411
chromosome_1_unplaced_Contig0.12504 gi|526117831|ref|NM_001281196.1|    314 1025    711
chromosome_1_Contig0.1980   gi|952977790|ref|NM_001317128.1|    849 849 0

Answer 2

Actually, you are on the right track. However, you need to use field ten as an index to your data structure:

awk '{data[$10] = $14} END { for (d in data) print d " " data[d]; }'

If you need to distinguish several fields, use something like

data[$10, "14"] = "x"; data[$10, "11"] = "y"

Answer 3

using as awk file

 { split($10,A,":") ;
  B[A[1]]=$14 ; C[A[1]] += $11  }
 END { for ( a in B ) printf "%s\t%s\t%d\n",a,B[a],C[a] ;}

gives

chromosome_1_unplaced_Contig0.12366 gi|526117831|ref|NM_001281196.1|        637
chromosome_1_unplaced_Contig0.12504 gi|526117831|ref|NM_001281196.1|        314
chromosome_1_unplaced_Contig0.3951  gi|526117967|ref|NM_001281232.1|        107
chromosome_1_Contig0.1980   gi|952977790|ref|NM_001317128.1|        849
chromosome_1_Contig0.3916   gi|733214878|ref|NM_001303082.1|        477

I didn't quiet understand other requirements. however

• split($10,A,":") will split 10th field in array A, according to ":"
• B[A[1]]=$14 ; is pretty straightforward

program file is called using

awk -f se.awk data