0

I have a tab seperated text file which as a following format

Name      city            info
Stef     NY=123         ID=1;CITY=NY
John    SE=981;819      ID=110;CITY=SE
Stef      SE=01         ID=20;CITY=SE
Kelly   SE=111;787      ID=100;CITY=NY
Lena    NY=815;981;1    ID=2111;CITY=NY

I want to extract lines into a new text file where ID in third column is greater than 100 so the expected output becomes:

Name      city            info
John    SE=981;819     ID=110;CITY=SE
Lena    NY=815;981,1   ID=2111;CITY=NY

The awk command I have thus far is:


awk '$3 !~ /^ID=[0-9];/' file.txt > output.txt

However, this would only remove those lines where ID is 9 or less. I am not sure how to exclude all those lines where ID is less than 100. How can I achieve that. Insights will be appreciated.

7 Answers 7

3

Using Miller

$ mlr --tsv filter 'i = splitkv($info, "=", ";"); i["ID"] > 100' file.txt
Name    city    info
John    SE      ID=110;CITY=SE
Lena    NY      ID=2111;CITY=NY

Using GNU awk:

$ gawk -F '\t' 'NR==1 || match($3,/ID=([0-9]+)/,arr) && arr[1]+0 > 100' file.txt
Name    city    info
John    SE      ID=110;CITY=SE
Lena    NY      ID=2111;CITY=NY
3

Assuming that your real input looks like the example you provided:

$ awk -F'[=;]' 'NR==1 || $(NF-2)>100' file
Name      city            info
John    SE=981;819      ID=110;CITY=SE
Lena    NY=815;981;1    ID=2111;CITY=NY

Here's how setting FS to [=;] is telling awk to split each record into fields:

$ awk -F'[=;]' '{print "---------" ORS $0; for (i=1;i<=NF;i++) print NF, i, "<"$i ">"}' file
---------
Name    city    info
1 1 <Name       city    info>
---------
Stef    NY=123  ID=1;CITY=NY
5 1 <Stef       NY>
5 2 <123        ID>
5 3 <1>
5 4 <CITY>
5 5 <NY>
---------
John    SE=981;819      ID=110;CITY=SE
6 1 <John       SE>
6 2 <981>
6 3 <819        ID>
6 4 <110>
6 5 <CITY>
6 6 <SE>
---------
Stef    SE=01   ID=20;CITY=SE
5 1 <Stef       SE>
5 2 <01 ID>
5 3 <20>
5 4 <CITY>
5 5 <SE>
---------
Kelly   SE=111;787      ID=100;CITY=NY
6 1 <Kelly      SE>
6 2 <111>
6 3 <787        ID>
6 4 <100>
6 5 <CITY>
6 6 <NY>
---------
Lena    NY=815;981;1    ID=2111;CITY=NY
7 1 <Lena       NY>
7 2 <815>
7 3 <981>
7 4 <1  ID>
7 5 <2111>
7 6 <CITY>
7 7 <NY>
6
  • Thank you for your reply. My first two fields can have = or ; in them which is why I said $3 in my awk command. Could you modify your solution so that it takes that into account? Thank you
    – John
    Commented Feb 13, 2022 at 23:32
  • Many times people provide information in questions that they consider relevant but actually isn't (and vice-versa) which is why it's important to provide sample input that looks like your real data. Yes, I can update my answer but I might end up with the wrong answer again if I don't know what your input really looks like (e.g. maybe your fields are actually within quotes or can contain blanks, or can have ID= in other fields too like GUID=1234, or anything else you haven't shown us so far, idk) so please edit your question to provide more truly representative sample input/output.
    – Ed Morton
    Commented Feb 14, 2022 at 10:56
  • I've made edits to my original question.
    – John
    Commented Feb 14, 2022 at 14:59
  • OK, I updated my answer.
    – Ed Morton
    Commented Feb 14, 2022 at 15:37
  • Can you explain what $(NF-2) does?
    – John
    Commented Feb 14, 2022 at 16:06
2

If the format is the same throughout the file below the header:

awk -F '=|;' 'NR==1; $2>100' location

That sets the delimeter as either = or ;, prints the header, and then, any lines after the first where the second field is greater than 100.

Output:

Name      city      info
John       SE     ID=110;CITY=SE
Lena       NY     ID=2111;CITY=NY
2
  • What if I have = and ; in other two columns too. How would you modify it then?
    – John
    Commented Feb 13, 2022 at 23:41
  • @John You'll need to edit the column that's printed and/or change what the delimiter(s) is. If you'll look over awk via the man page and Google, that'll give you what you need. Commented Feb 14, 2022 at 0:57
2

Split twice:

awk 'NR==1{ print; next } { split($3, array, /[=;]/); if(array[2]>100){ print} }' file

Output:

Name    city    info
John    SE      ID=110;CITY=SE
Lena    NY      ID=2111;CITY=NY
1

a really naive solution could be :

awk -F'\t' '{
    o=$3
    sub(/ID=/,"",o)
    if(o + 0 > 100) print
}' test 

A more advanced approach could be :

awk -F'\t' 'NR == 1 || (match($3, /[0-9]+/) && substr($3, RSTART, RLENGTH) + 0 > 100)' test

Try to match for numbert of at least 3 digit in the third field.

match will set for us the variable RLENGTH at the index where the regexp start to match, or at 0 if no match is found at all.

Then we could check if the entire match (from RSTART to RLENGTH) is greather then 100. If it is, print the entire line.

0
1

Why not simply

awk 'NR==1 || /ID=[0-9][0-9][0-9]+/ && !/ID=100/' file
Name      city      info
John       SE     ID=110;CITY=SE
Lena       NY     ID=2111;CITY=NY
-1
#!/usr/bin/python
import re
k=open('file.txt','r')
spa=re.compile(r'\s+')
print k.readline().strip()
for li in k:
    remspa=re.sub(spa," ",li)
    numb=remspa.strip().split(' ')[2].split(';')[0].strip().split('=')[1].strip()
    if (int(numb) > 100):
        print li.strip()

output

Name      city      info
John       SE     ID=110;CITY=SE
Lena       NY     ID=2111;CITY=NY
1
  • 1
    You know split() would split the string correctly, without the need for that regular expression and sub(), right?
    – Kusalananda
    Commented Feb 14, 2022 at 8:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .