2

I have a file consisting of 5 tab separated fields (irrelevant fields are empty in this example).

1       2       URL                     email               5

                https://www.a.com/t     [email protected]
                https://www.a.com       [email protected]
                https://www.b.fr        [email protected]
                https://www.b.fr/s/faq  [email protected]

Desired output:

domain          email(s)    
        
a.com           [email protected]
b.fr            [email protected], [email protected]

Steps:

  1. Isolate column 3 and 4
awk -F "\t" '{print $3 "\t\t" $4}' 

This yields what is shown in the first block above.

How do I go on from here?

I know how to grep the domain only, but the isolated domains don't help much in achieving the desired output lines.

I am not restricted to awk, it was just the only tool I knew that could isolate fields easily (via the -F flag).

1
  • Note that it's a mistake to just remove all but the last two elements from a domain name. The domain registries for most countries only issue domains with at least three parts - e.g. example.net.au or example.co.uk. Stripping them back to just net.au or co.uk is as useful as stripping a.com back to just com (i.e. not very). It's usually more useful to just remove common prefixes like www., mail., ftp. and so on.
    – cas
    Jun 14, 2021 at 5:09

5 Answers 5

4

Using awk and GNU datamash:

awk 'BEGIN{ OFS=FS="\t" }
  NR>2{                       # skip first two records
    split($3, a, "/" )        # split $3 into array a on /
    domain=a[3]               # 3rd element is the domain name
    sub(/^www\./, "", domain) # remove www. prefix
    print domain, $4          # print domain and email
  }
' file | datamash -g 1 unique 2

The awk part prints the domain and email for all records skipping the first two lines. This would be

a.com   [email protected]
a.com   [email protected]
b.fr    [email protected]
b.fr    [email protected]

The output is then piped to datamash to group the input on the first field and print a comma-separated list of unique values of the second field.

Output:

a.com   [email protected]
b.fr    [email protected],[email protected]

The header line is left as exercise.

0
3

With GNU awk for arrays of arrays and gensub():

$ cat tst.awk
BEGIN { FS=OFS="\t" }
NR>1 { d2e[gensub(/[^.]+\.([^.]+\.[^./]+).*/,"\\1",1,$3)][$4] }
END {
    print "domain", "emails(s)"
    for (domain in d2e) {
        cnt = 0
        for (email in d2e[domain]) {
            row = (cnt++ ? row ", " : domain OFS) email
        }
        print row
    }
}

$ awk -f tst.awk file
domain  emails(s)
a.com   [email protected]
b.fr    [email protected], [email protected]
2

Using Miller (with a little help from sed):

$ mlr --prepipe 'sed "/^$/d"' --tsv   put -q -S '
  $domain = joinv(mapexcept(splitnvx(joinv(mapselect(splitnvx($URL,"/"),3),""),"."),1),".");
  @e[$domain] = mapsum(@e[$domain],{$email:1});
  end {
    for(k,v in @e){@{email(s)}[k] = joink(v,",")};
    emit @{email(s)}, "domain"
  }' File.tsv
domain  email(s)
a.com   [email protected]
b.fr    [email protected],[email protected]

The sed --prepipe command just removes the extaneous empty line, so that the input is parsable as TSV. The $domain variable is obtained by splitting the URL field twice, first on / (selecting the 3rd element) then on . (selecting all except the 1st element, ex. www). Then the out-of-stream map @e is constructed as a map of the email fields - this is the step that de-duplicates emails for the same domain. At the end, convert the email maps to comma-delimited strings and emit them.

1
  • It appears that miller is turning out to be quite a Swiss army knife of text processing.
    – guest_7
    Jun 15, 2021 at 3:29
1

here's a go at it:

cat inputfile |
awk '{
    match($3, "^https?://.*\\.([^./]+\\.[^./]+)", matches);
    print(matches[1], $4)
}' |
sort |
uniq |
awk '{
    domains[$1] = domains[$1] " " $2;
}
END {
    for (d in domains) {
        printf("%-15s ", d);
        $0 = domains[d];
        for (i = 1; i < NF; i++) {
            printf("%s, ", $i);
        };
        print($NF);
    };
}'

The first awk matches the trailing foo.bar component of the domain, followed by the email. Filtering through sort | uniq eliminates duplicate domain/email combos, and makes sure the results are in domain-order, but we still have to join on the domain field and combine the matching emails with commas, so the second awk just keeps appending emails to a dictionary keyed by domain (using concatenation, with a space in-between). Then at the end we iterate through all the emails and print them with a trailing comma, unless it's the last domain, where we don't want a comma so we just make the second-to-last the iterate-stop, and print the last field ($NF) by itself at conclusion.

I tried using one awk script with a two-dimensional array[domain][email] but wasn't getting very far because I think they're only simulated in awk, ie one cannot do for (d in domain) { for (e in d[email]) } with awk. So I resorted to the shell filter.

I also don't like the hacky string concatenation loops which will become ridiculous with large numbers of emails in a domain. But, it should work if you have just thousands; a better method should be used for millions, etc.

I picked 15 char field for domain arbitrarily. You could remove the field length if you don't need them aligned. You'll need a BEGIN {} print if you want to print a header row...

the output looks like this given your inputs from the question:

a.com           [email protected]
b.fr            [email protected], [email protected]

(with dummy data added for fields 1 and 2 which you left blank; those fields must contain actual data without whitespace for the script to work, otherwise you need a field separator like tab or comma in the input file)

8
  • You don't need cat. awk sends the data to stdout so it can just be run with the file as an argument. Jun 14, 2021 at 0:04
  • sure, not needed, but doesn't hurt anything, and starts the pipeline with the inputfile, which looks cleaner to me. I developed it on one line iteratively. most of the semicolons are also unnecessary when there are newlines.
    – smemsh
    Jun 14, 2021 at 0:11
  • It doesn't matter where the input file is. The command is being used unnecessarily. You also don't need to pipe sort into unique as sort already has a -u switch that does the same thing. Even that isn't needed as awk can already do it so there's no need to use awk twice. Jun 14, 2021 at 0:59
  • as a pipeline it reads better to me with the input first, and doesn't add a perceptible overhead. sort | uniq vs sort -u (habit from piping to uniq -c) doesn't add any either, tested on 500M of duplicated english dictionaries. but thanks for pointing those out. will be glad to see a shorter version in pure awk.
    – smemsh
    Jun 14, 2021 at 3:53
  • Regarding otherwise you need a field separator like tab or... - the OP said I have a file consisting of 5 tab separated fields.
    – Ed Morton
    Jun 14, 2021 at 14:54
0

This problem can be tackled via constructing a dictionary keyed on the third field(actually a portion of it) and whose corresponding value is a set where the fourth field gets thrown in. The usefulness of a set is that it keeps it's elements intrinsically unique , so we don't have to extend our efforts in any sort of programming calisthenics to keep the values unique.

python3 -c 'import sys
ifile = sys.argv[1]
fs = ofs = "\t"
d = {}

with open(ifile) as fh:
  for i,l in enumerate(fh,1):
    if i < 3: continue
    x,x,y,email,x = l.split(fs)
    domain = y.split("/")[2].split(".",1)[1]
    if domain in d:
      d[domain].add(email)
    else:
      d[domain] = { email }

print(f"domain{ofs}email(s)",
      *[k+ofs+", ".join(v) for k,v in d.items()],
      sep="\n")
' file
domain  email(s)
a.com   [email protected]
b.fr    [email protected], [email protected]

You must log in to answer this question.

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