[SOLVED] How to match a string, take the second column, match a string for the next five rows and write the value of third column separated by a delimiter?

Issue

I have a dataset as follows:

Query= AAA18960.1 basic-leucine zipper-like protein, partial [Gallus
CDD:400559 pfam08317, Spc7, Spc7 kinetochore protein. This domain...  31.4    0.17 
CDD:405770 pfam15136, UPF0449, Uncharacterized protein family UPF...  28.0    0.70 
CDD:403831 pfam12744, ATG19_autophagy, Autophagy protein Atg19, A...  28.8    1.1  
CDD:373271 pfam14755, NSP2_middle, IMiddle region of RNA-arterivi...  28.0    1.3  
CDD:400539 pfam08286, Spc24, Spc24 subunit of Ndc80. Spc24 is a c...  26.8    2.1  
CDD:396080 pfam01346, FKBP_N, Domain amino terminal to FKBP-type ...  26.7    2.3  
CDD:405969 pfam15390, WDCP, WD repeat and coiled-coil-containing ...  28.0    2.8  
CDD:397844 pfam03938, OmpH, Outer membrane protein (OmpH-like). T...  26.8    3.0  
CDD:399074 pfam05816, TelA, Toxic anion resistance protein (TelA)...  27.5    3.2  
CDD:395386 pfam00482, T2SSF, Type II secretion system (T2SS), pro...  26.1    4.0  
Query= AAA39919.1 phosphoglycerate kinase-1, partial [Mus musculus]
CDD:399415 pfam06399, GFRP, GTP cyclohydrolase I feedback regulat...  21.2    2.2  
CDD:405915 pfam15328, GCOM2, Putative GRINL1B complex locus prote...  20.5    3.5  
CDD:401947 pfam10138, vWA-TerF-like, vWA found in TerF C-terminus...  19.5    8.0  
Query= AAA39964.1 pp105 Rb protein [Mus musculus domesticus]
CDD:401034 pfam08934, Rb_C, Rb C-terminal domain. The Rb C-termin...  294     3e-95
CDD:396434 pfam01858, RB_A, Retinoblastoma-associated protein A d...  282     4e-90
CDD:396433 pfam01857, RB_B, Retinoblastoma-associated protein B d...  157     2e-45
CDD:403219 pfam11934, DUF3452, Domain of unknown function (DUF345...  54.5    2e-09
CDD:404201 pfam13273, DUF4064, Protein of unknown function (DUF4064)  28.0    2.3  
CDD:405353 pfam14650, FAM75, FAM75 family                             28.8    4.4  
CDD:407391 pfam17278, DUF5343, Family of unknown function (DUF534...  27.7    4.6  
CDD:372078 pfam12371, TMEM131_like, Transmembrane protein 131-lik...  26.5    5.4  
CDD:397438 pfam03358, FMN_red, NADPH-dependent FMN reductase          27.2    7.1  
CDD:397933 pfam04042, DNA_pol_E_B, DNA polymerase alpha/epsilon s...  27.6    7.7  
Query= AAA42090.1 retinoblastoma-associated protein, partial [Rattus
CDD:401034 pfam08934, Rb_C, Rb C-terminal domain. The Rb C-termin...  175     2e-58
CDD:367437 pfam03288, Pox_D5, Poxvirus D5 protein-like. This fami...  23.5    6.7  
CDD:396246 pfam01582, TIR, TIR domain. The Toll/interleukin-1 rec...  23.5    8.0  
Query= AAA70267.1 phosphoglycerate kinase [Mus musculus]
CDD:395110 pfam00162, PGK, Phosphoglycerate kinase                    577     0.0  
CDD:280357 pfam02176, zf-TRAF, TRAF-type zinc finger                  24.3    8.9  
Query= AAA79194.1 centrin [Xenopus laevis]
CDD:404394 pfam13499, EF-hand_7, EF-hand domain pair                  57.3    2e-12
CDD:404310 pfam13405, EF-hand_6, EF-hand domain                       42.6    4e-07
CDD:404678 pfam13833, EF-hand_8, EF-hand domain pair                  42.3    7e-07
CDD:394992 pfam00036, EF-hand_1, EF hand. The EF-hands can be div...  37.4    3e-05
CDD:405361 pfam14658, EF-hand_9, EF-hand domain                       30.5    0.022
CDD:404151 pfam13202, EF-hand_5, EF hand                              28.8    0.038
CDD:401068 pfam08976, EF-hand_11, EF-hand domain. This domain is ...  28.9    0.19 
CDD:400222 pfam07766, LETM1, LETM1-like protein. Members of this ...  28.6    0.45 
CDD:396256 pfam01595, DUF21, Domain of unknown function DUF21. Th...  27.2    1.1  
CDD:399080 pfam05823, Gp-FAR-1, Nematode fatty acid retinoid bind...  26.9    1.2  
CDD:289529 pfam12763, EF-hand_4, Cytoskeletal-regulatory complex ...  26.2    1.4  
CDD:404506 pfam13623, SurA_N_2, SurA N-terminal domain. This doma...  26.0    2.1  
CDD:400792 pfam08626, TRAPPC9-Trs120, Transport protein Trs120 or...  26.4    2.9  
CDD:395447 pfam00564, PB1, PB1 domain                                 24.9    2.9  
CDD:397745 pfam03810, IBN_N, Importin-beta N-terminal domain          23.4    8.4  
CDD:401128 pfam09068, EF-hand_2, EF hand. Members of this family ...  24.0    9.0  
CDD:402466 pfam10865, DUF2703, Domain of unknown function (DUF270...  23.8    9.8  

I want to match the string "Query=" and then take the second column (protein ID), extract the next 5 rows and then take the third column and print it beside the protein ID separated by a delimiter '|' and move to the next line.

The output must be:

AAA39964.1 Spc7|UPF0449|ATG19_autophagy|NSP2_middle|Spc24

AAA39919.1 GFRP|GCOM2|vWA-TerF-like

AAA39964.1 Rb_C|RB_A|RB_B|DUF3452|DUF4064

and so on.

I've tried using the following script:

cat file.txt | grep -A 1 "Query=" | awk 'NR%3==0' | awk '!a[$1]++' | awk '{print $1 $2"|"$3"|"$4"|"$5"|"$6"|"$7}' | sed 's/\.[0-9] / /g' | sed 's/Query= //g' | sed 's/\.[0-9]//g' | sed 's/ /_/g'

But it gave the following output:

--|||||
Query=AAC53301|RET|ligand|2|[Rattus|norvegicus]
CDD:396776pfam02351,|GDNF,|GDNF/GAS1|domain.|This|cysteine
CDD:395169pfam00225,|Kinesin,|Kinesin|motor|domain|470
CDD:397971pfam04089,|BRICHOS,|BRICHOS|domain.|The|BRICHOS
CDD:401849pfam10018,|Med4,|Vitamin-D-receptor|interacting|Mediat...|112
CDD:396434pfam01858,|RB_A,|Retinoblastoma-associated|protein|A|d...
CDD:394960pfam00001,|7tm_1,|7|transmembrane|receptor|(rhodopsin
CDD:395526pfam00651,|BTB,|BTB/POZ|domain.|The|BTB
CDD:394996pfam00041,|fn3,|Fibronectin|type|III|domain
CDD:396739pfam02291,|TFIID-31kDa,|Transcription|initiation|facto...|222
CDD:401034pfam08934,|Rb_C,|Rb|C-terminal|domain.|The
CDD:395110pfam00162,|PGK,|Phosphoglycerate|kinase|579|0
CDD:404697pfam13855,|LRR_8,|Leucine|rich|repeat|62
CDD:405852pfam15255,|CAP-ZIP_m,|WASH|complex|subunit|CAP-Z
CDD:397333pfam03170,|BcsB,|Bacterial|cellulose|synthase|subunit....
CDD:395022pfam00069,|Pkinase,|Protein|kinase|domain|177
CDD:398625pfam05033,|Pre-SET,|Pre-SET|motif.|This|protein

Let me know in case of any doubts.

Solution

The code you supply cannot produce the output you show:

• grep -A 1 "Query=" will discard all lines except a query line itself and a single line following it
• awk 'NR%3==0' then discards two-thirds of the remaining lines for no obvious reason
• awk '!a[$1]++' attempts to print unique lines for no obvious reason
• the final awk command makes no sense since you expect to print at most 6 fields
• and the sed commands perform some sort of filtering that bears no resemblence to your textual description of what you are trying to achieve

Here is an awk solution that may do what you intend, based on your sample input and output:

awk -F',?[[:space:]]+' '
    /^Query=/ {
        if (n) print s "\n"
        s = $2 " "
        n = 5
        next
    }
    n==5 {
        s = s $3
        n--
        next
    }
    n {
        s = s "|" $3
        if (!--n) print s "\n"
    }
    END {
        if (n) print s "\n"
    }
' dataset.in

With your sample input, this outputs:

AAA18960.1 Spc7|UPF0449|ATG19_autophagy|NSP2_middle|Spc24

AAA39919.1 GFRP|GCOM2|vWA-TerF-like

AAA39964.1 Rb_C|RB_A|RB_B|DUF3452|DUF4064

AAA42090.1 Rb_C|Pox_D5|TIR

AAA70267.1 PGK|zf-TRAF

AAA79194.1 EF-hand_7|EF-hand_6|EF-hand_8|EF-hand_1|EF-hand_9

We tell awk that the field delimiter is whitespace optionally preceded by a comma. This avoids having to remove the comma later.

The code starts out looking for lines starting Query=.

Until it finds one, the n==5 and n patterns cannot match.

Once it finds that line, it initialises an output string with the value of the second column, sets a counter (n=5), and skips the remaining actions.

On the next (up to) five lines, n==5 or n patterns will match.

The second action appends to the output string, decrements the counter, then skips remaining actions.

The third action appends a column to the output string. Then it decrements the counter. If it has reached zero, the prepared string is printed.

It is possible to reach the end of the file, or a Query line, before 5 intervening lines are reached. In that case the partially-prepared string would not be printed. So, at the start of the Query action, and in an END action, we test if n is non-zero. If so, we know a partially-prepared string is pending, and so we print it.

Answered By - jhnc

Answer Checked By - Gilberto Lyons (WPSolving Admin)