0

I am trying to extract some fields from a .txt file to generate another table. It is a matrix (size varies time to time) separated with ; (further by \ in header), each field can have >= 1 numerical values which corresponds to the string at the ; position of it's header too.

If that field does contain a value, I want to copy that header field of the same ; separated field and get something like below. I have tried processing each Column one by one but i don't even know how to awk/grep the correct field and put it as new column.

If you look at the Original picture which I opened it in excel as tab-separated file for easier viewing, each column header contains a very long string separated by " \ " which represents three types of info: Name \ HGVSc \ HGVSp. Name is always one per column but HGVSc \ HGVSp are further separated into variable number of fields by " ; " but the number ";" fields is always the same for the column header down along it's row.

For each "cell" which is separated by " ; " only, it can have number or just "." as empty values and the position of the number tells the sub-position of HGVSc and HGVSp fields in the header which that cells refers to.

e.g. In first cell of Row CLAM19_S18, the two number is at 1st and 2nd position of itself and it refers to 1st and 2nd substring of HGVSc and HGVSp respectively.... i.e. c.646_647insTGCTCCGTGGGC;c.457_458insGG and .;p.Val153fs

So the final table would be something that have three new columns added for each Original Column, contain: Gene Name which is always the same down it's column but HGVSc and HGVSp could be different or empty depending on position of cells which contained a numerical value

Can someone give me some suggestion or approach? I am new to bash and perl scripting but any comment is appreciated. Thanks

  • Example

    \   ACD\c.646_647insTGCTCCGTGGGC;c.457_458insGG\.;p.Val153fs    ANKRD26\c.3270_3271insTTCTAGGCTATTGAGTTTACTTTCAGTTTTAAAT;c.2251_2252delTGinsCA;c.1683+1408_1683+1409insTTGTG;c.741-709G>A\p.Ser1091fs;p.Cys751His;.;.   BCOR\c.1260T>C\p.Asp420Asp  CBL\c.298T>A;c.1223_1227+1delGGCAGG;c.1223G>T;c.1536T>A\p.Ser100Thr;p.Trp408fs;p.Trp408Leu;p.Ser512Arg  
    CLAM10_S10  .;. .;.;.;. .   .;.;.;.  
    CLAM11_S11  .;. .;.;0.102;. .   .;.;.;.  
    CLAM12_S12  .;. .;.;.;. .   .;.;.;.  
    CLAM13_S13  .;. .;.;.;. .   .;.;.;.  
    CLAM14_S14  .;. .;.;.;. .   .;.;.;0.038  
    CLAM15_S15  .;. .;.;.;. .   .;.;.;.  
    CLAM16_S16  .;. .;.;.;. .   .;.;.;.  
    CLAM18_S17  .;. .;.;.;. .   .;.;.;.  
    CLAM19_S18  0.073;0.088 .;.;.;. .   .;.;.;.  
    CLAM1_S1    .;. .;.;.;. .   .;.;.;.  
    CLAM20_S19  .;. .;.;.;. .   .;.;0.233;.  
    CLAM21_S20  .;. .;.;.;. .   .;.;.;.  
    CLAM23_S21  .;. .;.;.;. .   .;.;.;.  
    CLAM24_S22  .;. .;.;.;. .   .;.;.;.  
    CLAM25_S23  .;. .;.;.;. .   .;.;.;.  
    CLAM26_S24  .;. .;.;.;. .   .;.;.;.  
    CLAM27_S1   .;. .;.;.;. .   .;.;.;.  
    CLAM28_S2   .;. .;.;.;. .   .;.;.;.  
    CLAM29_S3   .;. .;.;.;. .   .;.;.;.  
    
  • Desired tab seperated result

    \   ACD ACD_HGVSc   ACD_HGVSp   ACD_VAF ANKRD26 ANKRD26_HGVSc   ANKRD26_HGVSp   ANKRD26_VAF BCOR    BCOR_HGVSc  BCOR_HGVSp  BCOR_VAF    CBL CBL_HGVSc   CBL_HGVSp   CBL_VAF
    CLAM10_S10                                                              
    CLAM11_S11                      c.1683+1408_1683+1409insTTGTG   .   0.102                               
    CLAM12_S12                                                              
    CLAM13_S13                                                              
    CLAM14_S14                                                      c.1536T>A   p.Ser512Arg 0.038
    CLAM15_S15                                                              
    CLAM16_S16                                                              
    CLAM18_S17                                                              
    CLAM19_S18  ACD c.646_647insTGCTCCGTGGGC;c.457_458insGG .;p.Val153fs    0.073;0.088                                             
    CLAM1_S1                                                                
    CLAM20_S19                                                      c.1223G>T   p.Trp408Leu 0.233
    CLAM21_S20                                                              
    CLAM23_S21 
    

Original Source

Desired Results

  • @ abu-ahmed: thanks a lot for assisting. I am still new to here so sorry for the misformatting. – Rita Dec 3 '18 at 13:21
  • 2
    It's very unclear how you go from input text to your desired output. Please explain how to transform it. – glenn jackman Dec 3 '18 at 16:04
  • 1
    Also, where does the new header come from? – glenn jackman Dec 3 '18 at 16:08
  • I hope I can explain it clearer: if you look at the picture which I opened it in excel as tab-separated file for earsier looking, each column header contains a very long string separated by " \ " which represents three types of info: Name \ HGVSc \ HGVSp. – Rita Dec 4 '18 at 2:16
  • For each "cell" which is separated by " ; " only, it can have number or just "." as empty values and the position of the number tells the sub-position of HGVSc and HGVSp fields in the header which that cells refers to. e.g. In first cell of Row CLAM19_S18, the two number is at 1st and 2nd position of itself and it refers to 1st and 2nd substring of HGVSc and HGVSp respectively.... i.e. c.646_647insTGCTCCGTGGGC;c.457_458insGG and .;p.Val153fs. Any other rows of this column which does not contain number would be just empty at the end. – Rita Dec 4 '18 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.