deleting lines between rows in a text file using awk or sed

Question

I am wondering if there is a sed or awk command to delete all lines between the 'Query_' headers in column 1, if the number of lines between each header is less than 5. The following is an extract from a large file ~1Gb. I have tried a number of different methods but all have failed.

Query_10      26   KMGKWYPTEDAPAKKRKTQSWRQNKSKLRGGIVPGQVLIILAGKHKGKRVVYLTQLSTGE  205
XP_010718494  131  KMPRYYPTEDVPRKSHGKKPFSQHKRRLRASITPGTVLILLTGRHRGKRVVFLKQLGTGL  192
NP_001291831  111  KMPRYYPTEDVPRKSHGKKPFSQHVRKLRASITPGTILIILTGRHRGKRVVFLKQLSSGL  172
Query_10      206  IVVTGPHKFNRCPLKKLAQSFTMPTSTFVDI*GLNFDITEQHFVKEKP**SSEEAQFFAK  385
XP_010718494  193  LLVTGPLVVNRVPLRRAHQKFVIATSTKVDISGVKIHLTDAYFKKKKLRKPKQEGEIFDT  255
NP_001291831  173  LLVTGPLSLNRVPLRRTHQKFVIATSTKIDISSVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_012359817  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_009246541  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_003225150  155  LLVTGPLAINRVPLRRAHQKFVIATSTKVDISSVKLHLNDVYFKKKKLRKPKQEGEIFDT  217
Query_13      31    MEEQKEKGLSNPEVV*KYRQCSEIVNQVLSTVVSSCVPGADVASICTNGDFLIEDGLRNI  210
XP_002947167  7     IQGEQEPNLSVPEVVTKYKAAADICNRALQAVIDGCKDGSKIVDLCRTGDNFITKECGNI  66
XP_004993505  1     MELDRQSKVVDADALSKYRAAAAIANDCVQQLVANCIAGADVYTLAVEADTYIEQKLKEL  60
XP_006961234  1     MSETKEYSLNNPDTLTKYKTAAQISEKVLAAVSDLCVPGAKIVDICQQGDKLIEEELAKV  62
XP_008089018  1     MSEETDYTLNNPDTLTKYKTAAQISEKVLAAVAELVVPGEKIVTICEKGDKLIEEELAKV  60
Query_13      211   EPDTNIEKGIAIPVCLNINNICSYYSPLPDASTTLQEGDLVKVDLGAHFDGYIVSAASSI  390
XP_004029906  65    YTKKKVEKGPAFPTCISINEICGHYSPLLSDSSLLKEGDVVKIDLGTHIDGFIALGAHTV  131
XP_004031065  64    FTKKKLQKGPAFPTCISVNEICGHYSPLISDSSLLKEGDVVKIDLGAQIDGFIALAAHTV  130
XP_003223249  65    KKEKDMKKGIAFPTSISVNNCVCHFSPLKDQDYILKEGDLVKIDLGVHVDGFISNVAHSF  125
XP_002947167  67    YKGKQIEKGVAFPTCVSVNSVVGHFSPNADDTSALKAGDVVKFDMGCHIDGFIATQATTV  126
XP_003880798  73    ENGKKMEKGIAFPTCISINEICGHFSPVEENAETLTEGDVVKIDMGCHIDGYISVVAYTV  135
XP_004348044  69    KANKKVKKGIAFPTCVSLNSTVCHQSPLSDAAITLQAGDVAKVDLGVHVDGLIAVVAHTI  129
XP_003284133  69    HSKKKIEKGIAFPTCISVNNCVGHYSPLKATSRSLVDGDIVKIDLGVHINGFIAVGAHTI  128
NP_001241588  65    YKNVKIERGVAFPTCLSINNVVCHFSPLASDEAVLEEGDILKIDMACHIDGFIAVVAHTH  126
XP_009039553  76    YQKKIIDKGVAFPTCVSVNECVCHNSPLESDTTSLSEGDLVKLDVGCYVDGYIAVAAHTM  141

The desired outcome would be as follows:

Query_10      206  IVVTGPHKFNRCPLKKLAQSFTMPTSTFVDI*GLNFDITEQHFVKEKP**SSEEAQFFAK  385
XP_010718494  193  LLVTGPLVVNRVPLRRAHQKFVIATSTKVDISGVKIHLTDAYFKKKKLRKPKQEGEIFDT  255
NP_001291831  173  LLVTGPLSLNRVPLRRTHQKFVIATSTKIDISSVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_012359817  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_009246541  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_003225150  155  LLVTGPLAINRVPLRRAHQKFVIATSTKVDISSVKLHLNDVYFKKKKLRKPKQEGEIFDT  217
Query_13      211   EPDTNIEKGIAIPVCLNINNICSYYSPLPDASTTLQEGDLVKVDLGAHFDGYIVSAASSI  390
XP_004029906  65    YTKKKVEKGPAFPTCISINEICGHYSPLLSDSSLLKEGDVVKIDLGTHIDGFIALGAHTV  131
XP_004031065  64    FTKKKLQKGPAFPTCISVNEICGHYSPLISDSSLLKEGDVVKIDLGAQIDGFIALAAHTV  130
XP_003223249  65    KKEKDMKKGIAFPTSISVNNCVCHFSPLKDQDYILKEGDLVKIDLGVHVDGFISNVAHSF  125
XP_002947167  67    YKGKQIEKGVAFPTCVSVNSVVGHFSPNADDTSALKAGDVVKFDMGCHIDGFIATQATTV  126
XP_003880798  73    ENGKKMEKGIAFPTCISINEICGHFSPVEENAETLTEGDVVKIDMGCHIDGYISVVAYTV  135
XP_004348044  69    KANKKVKKGIAFPTCVSLNSTVCHQSPLSDAAITLQAGDVAKVDLGVHVDGLIAVVAHTI  129
XP_003284133  69    HSKKKIEKGIAFPTCISVNNCVGHYSPLKATSRSLVDGDIVKIDLGVHINGFIAVGAHTI  128
NP_001241588  65    YKNVKIERGVAFPTCLSINNVVCHFSPLASDEAVLEEGDILKIDMACHIDGFIAVVAHTH  126
XP_009039553  76    YQKKIIDKGVAFPTCVSVNECVCHNSPLESDTTSLSEGDLVKLDVGCYVDGYIAVAAHTM  141

Python script I tried:

lines = [line.rstrip() for line in open('infile.txt')]
for line in lines: 
    data = line.split()
    sequence = data[2]
    if data[0].startswith("Query_"):
        hits = [i for i,c in enumerate(sequence) if c == <50]
        continue
    else:
        print(list(sequence[plus50] for plus50 in hits))

you mentioned that you tried a few things, would help to add them here.. so that others can see what went wrong and help you — Sundeep
– Sundeep, Commented Jul 13, 2016 at 10:18
I tried writing python and perl scripts but they weren't very successful, just wondering if awk or sed could provide the same results as I am not fully aware of their potential. — sheaph
– sheaph, Commented Jul 13, 2016 at 10:30
lines = [line.rstrip() for line in open('infile.txt')] for line in lines: data = line.split() sequence = data[2] if data[0].startswith("Query_"): hits = [i for i,c in enumerate(sequence) if c == <50] continue else: print(list(sequence[plus50] for plus50 in hits)) — sheaph
– sheaph, Commented Jul 13, 2016 at 10:41
sorry I'm having difficulty in formatting the script... I'm new to the forum — sheaph
– sheaph, Commented Jul 13, 2016 at 10:47

Costas · Accepted Answer · 2016-07-13 13:16:22Z

3

With sed:

sed '
    /^Query_/{                #starts loop when meet patten
        :a
        $!{
            N
            /\nQuery_/!ba     #untill meet next pattern
        }
        /\(\n.*\)\{6,\}/{     #checks how many lines in block
            $b                #for end of file
            s/\nQuery_/\n&/   #marks lines to print
        }
    }
    /\n\n/P                   #prints marked lines
    D                         #remove 1st line in block, go to start
    '

Other script form for awk:

awk '
    /^Query/{c=0;lines=$0;next}
    ++c<5{lines=lines "\n" $0;next}
    c==5{print lines}
    1                         #short for {print}
    '

edited Jul 13, 2016 at 13:16

answered Jul 13, 2016 at 11:47

Costas

15.1k24 silver badges38 bronze badges

A bit of explanation would be nice...

Kusalananda
– Kusalananda ♦

2016-07-13 12:03:34 +00:00
Commented Jul 13, 2016 at 12:03
1

@Kusalananda Done.

Costas
– Costas

2016-07-13 13:20:12 +00:00
Commented Jul 13, 2016 at 13:20

Add a comment |

chaos · Accepted Answer · 2016-07-13 11:07:12Z

With awk:

awk '{if($1~/^Query_/){c=0;delete a;a[0]=$0}else{c++}
    if(c<5){a[c]=$0}
    if(c==5){for(i in a){print a[i]}}
    if(c>5){print}}' file

In the first line the first field $1 is checked whether it begins with Query_. If so, the counter variable c is set to 0. The array a is removed, and the first element of the array is set to the value of that line. Else the counter variable is incremented.
In the second line, the array is filled line by line, until 5 more line are in it.
Third line: If we have 5 more rows, loop trough the array and print its elements line by line.
Fourth line: All lines from now on can be printed.

Output with your example data:

Query_10      206  IVVTGPHKFNRCPLKKLAQSFTMPTSTFVDI*GLNFDITEQHFVKEKP**SSEEAQFFAK  385
XP_010718494  193  LLVTGPLVVNRVPLRRAHQKFVIATSTKVDISGVKIHLTDAYFKKKKLRKPKQEGEIFDT  255
NP_001291831  173  LLVTGPLSLNRVPLRRTHQKFVIATSTKIDISSVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_012359817  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_009246541  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
Query_13      211   EPDTNIEKGIAIPVCLNINNICSYYSPLPDASTTLQEGDLVKVDLGAHFDGYIVSAASSI  390
XP_004029906  65    YTKKKVEKGPAFPTCISINEICGHYSPLLSDSSLLKEGDVVKIDLGTHIDGFIALGAHTV  131
XP_004031065  64    FTKKKLQKGPAFPTCISVNEICGHYSPLISDSSLLKEGDVVKIDLGAQIDGFIALAAHTV  130
XP_003223249  65    KKEKDMKKGIAFPTSISVNNCVCHFSPLKDQDYILKEGDLVKIDLGVHVDGFISNVAHSF  125
XP_002947167  67    YKGKQIEKGVAFPTCVSVNSVVGHFSPNADDTSALKAGDVVKFDMGCHIDGFIATQATTV  126
XP_004348044  69    KANKKVKKGIAFPTCVSLNSTVCHQSPLSDAAITLQAGDVAKVDLGVHVDGLIAVVAHTI  129
XP_003284133  69    HSKKKIEKGIAFPTCISVNNCVGHYSPLKATSRSLVDGDIVKIDLGVHINGFIAVGAHTI  128
NP_001241588  65    YKNVKIERGVAFPTCLSINNVVCHFSPLASDEAVLEEGDILKIDMACHIDGFIAVVAHTH  126
XP_009039553  76    YQKKIIDKGVAFPTCVSVNECVCHNSPLESDTTSLSEGDLVKLDVGCYVDGYIAVAAHTM  141

once I increase the the number of lines to remove from 5 to 200+ for example the output tents to get mixed up and incorrect lines are reported between different Query_. Any advise? — sheaph
– sheaph, Commented Jul 13, 2016 at 12:42

Sundeep · Accepted Answer · 2017-09-29 06:18:46Z

With GNU awk

$ awk -F'\n' -v RS='Query_' -v ORS= 'NF>6{print RS $0}' ip.txt
Query_10      206  IVVTGPHKFNRCPLKKLAQSFTMPTSTFVDI*GLNFDITEQHFVKEKP**SSEEAQFFAK  385
XP_010718494  193  LLVTGPLVVNRVPLRRAHQKFVIATSTKVDISGVKIHLTDAYFKKKKLRKPKQEGEIFDT  255
NP_001291831  173  LLVTGPLSLNRVPLRRTHQKFVIATSTKIDISSVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_012359817  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_009246541  173  LLVTGPLVLNRVPLRRTHQKFVIATSTKIDISNVKIHLTDAYFKKKKP--RHQEGEIFDT  235
XP_003225150  155  LLVTGPLAINRVPLRRAHQKFVIATSTKVDISSVKLHLNDVYFKKKKLRKPKQEGEIFDT  217
Query_13      211   EPDTNIEKGIAIPVCLNINNICSYYSPLPDASTTLQEGDLVKVDLGAHFDGYIVSAASSI  390
XP_004029906  65    YTKKKVEKGPAFPTCISINEICGHYSPLLSDSSLLKEGDVVKIDLGTHIDGFIALGAHTV  131
XP_004031065  64    FTKKKLQKGPAFPTCISVNEICGHYSPLISDSSLLKEGDVVKIDLGAQIDGFIALAAHTV  130
XP_003223249  65    KKEKDMKKGIAFPTSISVNNCVCHFSPLKDQDYILKEGDLVKIDLGVHVDGFISNVAHSF  125
XP_002947167  67    YKGKQIEKGVAFPTCVSVNSVVGHFSPNADDTSALKAGDVVKFDMGCHIDGFIATQATTV  126
XP_003880798  73    ENGKKMEKGIAFPTCISINEICGHFSPVEENAETLTEGDVVKIDMGCHIDGYISVVAYTV  135
XP_004348044  69    KANKKVKKGIAFPTCVSLNSTVCHQSPLSDAAITLQAGDVAKVDLGVHVDGLIAVVAHTI  129
XP_003284133  69    HSKKKIEKGIAFPTCISVNNCVGHYSPLKATSRSLVDGDIVKIDLGVHINGFIAVGAHTI  128
NP_001241588  65    YKNVKIERGVAFPTCLSINNVVCHFSPLASDEAVLEEGDILKIDMACHIDGFIAVVAHTH  126
XP_009039553  76    YQKKIIDKGVAFPTCVSVNECVCHNSPLESDTTSLSEGDLVKLDVGCYVDGYIAVAAHTM  141

-v RS='Query_' set Query_ as input record separator
-v ORS= set empty string as output record separator
-F'\n' set newline as input field separator
NF>6 question is to preserve blocks with 5 entries. Including the header there are 6 lines, which means 6 newlines. Splitting this minimum required string will give 7 fields - hence the condition NF>6
print RS $0 print RS as well as input record when the condition is satisfied

Stack Exchange Network

deleting lines between rows in a text file using awk or sed

3 Answers 3

You must log in to answer this question.

Hot Network Questions

deleting lines between rows in a text file using awk or sed

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions