1

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 ":".

2
  • 6
    welcome to U&L, can you edit your post with ... ? 1) a sample of input file, 2) a sample of output file whished 3) what you have tried so far.
    – Archemar
    Jul 4, 2016 at 14:22
  • Thanks Archemar, the data is a bit eye-sore and my attempts very beginner level but I've added them. I have a lot of data like this so a point in the right direction of how to deal with it would be very useful.
    – hatziiod
    Jul 5, 2016 at 8:04

3 Answers 3

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
0

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"
0

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

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.